Data Grip

Data Access Xtra for Macromedia Director and Authorware

User Manual Version 2.0 August 21, 1998

© Used under license by Integration New Media, Inc. www.INM.com

Datagrip Table of Contents CHAPTER 1 GETTING STARTED WITH DATAGRIP™..................................................................................... 4 INTRODUCTION ........................................................................................................................................... 4 WHAT’S NEW IN VERSION 2.0 .................................................................................................................... 4 SYSTEM REQUIREMENTS ............................................................................................................................. 5 TYPOGRAPHICAL CONVENTIONS ................................................................................................................. 5 PRODUCT RETURN POLICY.......................................................................................................................... 6 COPYRIGHTS AND TRADEMARKS ................................................................................................................ 6 INSTALLING DATAGRIP™ ........................................................................................................................... 6 CHAPTER 2: DATAGRIP TUTORIAL OVERVIEW ............................................................................................ 8 OVERVIEW OF THE APPLICATION ................................................................................................................ 8 THE CONTACT DATABASE .......................................................................................................................... 9 CHAPTER 3: DIRECTOR TUTORIAL ............................................................................................................. 12 OPENING THE DATABASE .......................................................................................................................... 12 CREATING A RECORDSET .......................................................................................................................... 13 WORKING WITH QUERYDEF OBJECTS ....................................................................................................... 16 PARAMETERIZED QUERIES ........................................................................................................................ 17 DISPLAYING THE CONTACT INFORMATION ............................................................................................... 18 FILLING THE LIST BOX WITH PHONE NUMBER INFORMATION ................................................................... 20 ADDING NEW RECORDS ............................................................................................................................ 23 NAVIGATING THROUGH THE CONTACTS .................................................................................................... 25 ADDING PHONE NUMBERS ........................................................................................................................ 26 SUMMARY................................................................................................................................................. 28 CHAPTER 4: AUTHORWARE TUTORIAL ...................................................................................................... 29 OPENING THE DATABASE .......................................................................................................................... 29 CREATING A RECORDSET .......................................................................................................................... 30 WORKING WITH QUERYDEFS .................................................................................................................... 32 PARAMETERIZED QUERIES ........................................................................................................................ 33 DISPLAYING THE CONTACT INFORMATION ............................................................................................... 34 ADDING NEW RECORDS ............................................................................................................................ 37 NAVIGATING THROUGH THE CONTACTS .................................................................................................... 38 ADDING PHONE NUMBERS ........................................................................................................................ 38 SUMMARY................................................................................................................................................. 39 CHAPTER 5 LANGUAGE REFERENCE .......................................................................................................... 40 CLEARDGERROR ...................................................................................................................................... 40 DGCLOSE ................................................................................................................................................. 41 DGCREATEGRID....................................................................................................................................... 42 DGCREATERECORDSET ............................................................................................................................ 43 DGEXECUTE ............................................................................................................................................. 44 DGGETVERSION....................................................................................................................................... 45 DGGRIDADDFIELD .................................................................................................................................. 46 DGGRIDDELETEFIELD ............................................................................................................................. 47 DGGRIDUPDATE ...................................................................................................................................... 48 DGISDAOREGISTERED ............................................................................................................................ 49 DGOPENDATABASE ................................................................................................................................. 50 DGQDCLOSE ........................................................................................................................................... 51 DGQDCREATE ......................................................................................................................................... 52 DGQDCREATERS .................................................................................................................................... 53 DGQDGETNAME ..................................................................................................................................... 54 DGQDGETPARAMETERCOUNT ................................................................................................................ 55 DGQDGETPARAMETERVALUE ................................................................................................................ 56 Datagrip User Manual, Version 2.0

i

August 21, 1998

DGQDGETSQL........................................................................................................................................ 57 DGQDISOPEN .......................................................................................................................................... 58 DGQDOPEN ............................................................................................................................................. 59 DGQDSETPARAMETERVALUE ................................................................................................................. 60 DGQDSETSQL ........................................................................................................................................ 61 DGRSADDNEW........................................................................................................................................ 62 DGRSBINDFIELD ..................................................................................................................................... 63 DGRSBINDVAR ....................................................................................................................................... 64 DGRSCANAPPEND ................................................................................................................................... 65 DGRSCANCELUPDATE............................................................................................................................. 66 DGRSCANSCROLL ................................................................................................................................... 67 DGRSCANTRANSACT .............................................................................................................................. 68 DGRSCANUPDATE................................................................................................................................... 69 DGRSCLOSE ............................................................................................................................................ 70 DGRSDELETE .......................................................................................................................................... 71 DGRSEDIT ............................................................................................................................................... 72 DGRSFILLLIST......................................................................................................................................... 73 DGRSFILLSTRING .................................................................................................................................... 74 DGRSFINDFIRST ...................................................................................................................................... 75 DGRSFINDLAST....................................................................................................................................... 76 DGRSFINDNEXT ...................................................................................................................................... 77 DGRSFINDPREV ...................................................................................................................................... 78 DGRSGETABSOLUTEPOSITION ................................................................................................................ 79 DGRSGETEDITMODE .............................................................................................................................. 80 DGRSGETFIELDCOUNT ........................................................................................................................... 81 DGRSGETFIELDVALUE ........................................................................................................................... 82 DGRSGETLOCKINGMODE ....................................................................................................................... 83 DGRSGETNAME ...................................................................................................................................... 84 DGRSGETPERCENTPOSITION ................................................................................................................... 85 DGRSGETRECORDCOUNT ....................................................................................................................... 86 DGRSGETSQL......................................................................................................................................... 87 DGRSIMPORTRESOURCE.......................................................................................................................... 88 DGRSISBOF ............................................................................................................................................ 90 DGRSISEOF ............................................................................................................................................ 91 DGRSISOPEN ........................................................................................................................................... 92 DGRSMOVEFIRST .................................................................................................................................... 93 DGRSMOVE ............................................................................................................................................. 94 DGRSMOVELAST .................................................................................................................................... 95 DGRSMOVENEXT .................................................................................................................................... 96 DGRSMOVEPREV .................................................................................................................................... 97 DGRSREQUERY ....................................................................................................................................... 98 DGRSSETABSOLUTEPOSITION ................................................................................................................. 99 DGRSSETFIELDVALUE .......................................................................................................................... 100 DGRSSETLOCKINGMODE ...................................................................................................................... 101 DGRSUNBINDFIELD............................................................................................................................... 103 DGRSUNBINDVAR ................................................................................................................................. 104 DGRSUPDATE ........................................................................................................................................ 105 DGGETREGVALUE ................................................................................................................................. 106 GETLASTDGERROR ............................................................................................................................... 107 DGSETAUDIOSAFEMODE....................................................................................................................... 108 DGSETREGVAL ...................................................................................................................................... 109 CHAPTER 6 DISTRIBUTING YOUR DATAGRIP APPLICATIONS .................................................................. 110 CREDITS REQUIREMENTS ........................................................................................................................ 110 DISTRIBUTING DATAGRIP ....................................................................................................................... 110

Datagrip User Manual, Version 2.0

ii

August 21, 1998

DATAGRIP FILES ..................................................................................................................................... 111 APPENDIX A: GLOSSARY ........................................................................................................................... 114 APPENDIX B: MORE READING .................................................................................................................. 116 APPENDIX C: EXAMPLE DIRECTOR CODE ................................................................................................ 117 STARTUP SCRIPTS ................................................................................................................................... 117 BUTTON SCRIPTS .................................................................................................................................... 118 FRAME SCRIPTS ...................................................................................................................................... 119 INTERFACE SCRIPTS ................................................................................................................................ 119 DATABASE SCRIPTS ................................................................................................................................ 120 APPENDIX D: EXAMPLE AUTHORWARE CODE ......................................................................................... 125 STARTUP SCRIPTS ................................................................................................................................... 125 SUBROUTINES ......................................................................................................................................... 126 APPENDIX E: WHAT’S NEW IN DATAGRIP 2.0........................................................................................... 128 DATAGRIP SOFTWARE LICENSING AGREEMENT ..................................................................... 130

Datagrip User Manual, Version 2.0

iii

August 21, 1998

Chapter 1 Getting Started with Datagrip™ Introduction Thank you for purchasing Datagrip™ from Integration New Media, Incorporated. Datagrip is an extension Xtra for Macromedia Director and Authorware that provides a powerful and simple interface to Microsoft Access-format databases1 . You can use Datagrip to quickly store, retrieve, and sort all kinds of information for electronic catalogs, computerbased training, and template-based presentations. You can read and write data to Access databases, return sets of records based on an SQL query, search for records, use stored queries, and perform most of the common database operations that you can do through Microsoft Access. In addition, since Datagrip is fully compatible with Microsoft Access version 1.0 through Access ‘97, you can use Access to create, edit, and browse the databases you use with Datagrip. You can even share a single database between multiple Director and Authorware programs. Here is a summary of Datagrip’s key features: •

Full access to Microsoft Access databases from the scripting language

•

Compatible with Microsoft Access databases from version 1.0 through Access 97

•

Support for stored queries allows you to store SQL queries in the database

•

Support for parameterized stored queries

•

Unlimited number of records and record sets (limited only by memory)

•

Read and write functions for the Windows™ registry.

•

Simple to use

•

Royalty-free distribution

What’s New in Version 2.0 Version 2.0 includes a number of powerful new features that will not only improve productivity, but will allow you to structure your multimedia application in new ways improving the maintainability of the product and allowing content updates to be as simple as shipping a new database file. Datagrip’s new features include:

1

•

Support for BMP bitmaps and Wave audio files

•

Bound fields that automatically update when a user changes the current record

Microsoft Access databases are usually stored in files with a .MDB extension. For example, the Datagrip tutorial database is stored in the file named TUTOR.MDB in the Datagrip installation directory.

Datagrip User Manual, Version 2.0

4

August 21, 1998

•

Automatic support for multi-column list boxes (grids)

•

The ability to run Datagrip without installing any software on a client’s hard drive

•

Auto-fill of lists and list boxes

A more detailed list of the new features in version 2.0 can be found in Appendix E. System Requirements To use Datagrip you’ll need the following: •

Windows ’95, Windows ‘98 or NT 4.0 or later

•

Macromedia Director version 5.0 (or later) or Macromedia Authorware 4.0 (or later)

•

Internet connection for downloading

•

At least 5 MB of free disk space

•

Microsoft Access

Typographical Conventions This manual uses several conventions designed to help clarify the text: •

Structured Query Language (SQL) statements are printed on a separate line or lines in a bold font like this unless they are part of Macromedia Scripting Language code. For example, the following is a simple SQL statement: “SELECT * FROM CONTACTS WHERE COMPANY = ‘Integration New Media’”

•

Datagrip function names used in the text are printed in bold type. For example, DGRSFindNext is a Datagrip function.

•

Macromedia Scripting Language code is printed a typewriter font like this: set dbHandle = DGOpenDatabase(the moviePath & "Tutor.mdb") if dbHandle = "#ERROR#" then alert "Error: " & GetLastDGError() alert "Now exiting the program." ClearDGError() quit end if

•

Macromedia Scripting Language handlers referred to in text blocks will use a typewriter font. As an example, note the font difference when referencing a handler named FillPhoneNumFields(). Note The text in code blocks use the Macromedia Scripting Language syntax, so you can copy them directly with one exception. On occasion, lines of code are split when they are too long to fit on a single line in this manual. In those cases, the last two characters of the line are “>>” These characters indicate that the next line should be appended to the end of the current line. Do not include the “>>” characters in the code.

Datagrip User Manual, Version 2.0

5

August 21, 1998

Product Return Policy A fully functional evaluation version of Datagrip is available for download from the Datagrip web site (www.INM.com). The evaluation version gives you a convenient way to try all the Datagrip functions and test it in your application. The only limitation of the evaluation version is that it cannot be redistributed and it displays a dialog box at startup. The dialog box does not appear in the product version. If you have any questions about Datagrip's suitability for your application, we suggest you download and try the evaluation version.

Copyrights and Trademarks Microsoft Access is a registered trademark of Microsoft Corporation. Macromedia Director Macromedia Authorware and Macromedia Xtra are registered trademarks of Macromedia, Inc. All other trademarks are the property of their respective owners. Installing Datagrip™ Datagrip™ is available for download from the Integration New Media web site: http://www.INM.com/products/ Note If you re-install Director in the future, you’ll need to re-install Datagrip as well because parts of Datagrip are installed in an Xtras folder under the main Director program folder.

Installing the Demo (Evaluation) Version: Download the installer for the evaluation version and unzip it. Then double-click the installer, named datagrip-demo-installer.exe and follow the instructions on screen. When installation is complete, a new folder named DataGrip Xtra is created within Program Files > Integration New Media. This DataGrip Xtra folder contains documentation and the following file: -

DGDemo.X32: Place this file in the Xtras folder within the Director/Authorware application folder, for use during authoring time. (Note that in Director MX 2004, the Xtras folder is in Program Files\Director MX 2004\Configuration\Xtras).

The evaluation version cannot be used in the "runime" environment, for playback of projectors/packaged pieces.

Datagrip User Manual, Version 2.0

6

August 21, 1998

Installing the Release Version: After evaluating the demo version of DataGrip, you can purchase a license from INM's secure online store: https://Store.INM.com Once you have purchased your license you will be sent a Release version of the Xtra, which you can test and distribute with your projects. Download the installer for the Release version and unzip it. Then double-click the installer, named datagrip-release-installer.exe and follow the instructions on screen. When installation is complete, the following Xtras will be placed in the folder named Program Files > Integration New Media > DataGrip Xtra: -

DGDes.X32: Place this file in the Xtras folder within Director/Authorware, for use during authoring time.

-

DGRel.X32: Place this file beside your executable Director projector or Authorware packaged piece, for playback and distribution.

Datagrip User Manual, Version 2.0

7

August 21, 1998

Chapter 2: Datagrip Tutorial Overview This chapter will help get you started with using Datagrip™ by showing you step-by-step how to build a simple Contact Manager application using Macromedia Director/Authorware and Datagrip. This particular contact manager is used to organize a list of photographers or artists and contains a bitmap field for showing a portfolio sample. Along the way, you’ll see most of the key features of Datagrip in action, including segments of the code from the sample application that illustrate the concepts. Following this overview chapter, there are detailed chapters covering the use of Datagrip with Director and Authorware. The complete application is included with Datagrip as a Director 6.0 file and as an Authorware 4.0 file, so you can open it, run the application, and see how it all works together. You can even copy code directly from the tutorial application as a starting point for your own projects. You’ll find the Director file, called TUTOR.DIR and the Authorware file, called TUTOR.A4P, in the Datagrip directory. Overview of the Application Our contact manager application is a lot like many other simple contact managers with two important differences. To better illustrate Datagrip’s capabilities we have included a bitmap field that is used to show a bitmap showing a sample of the person’s portfolio. We have also included the capability to store an unlimited number of phone numbers (because we have been frustrated in the past by contact managers that have a fixed number of phone number fields). This allows you to store the contacts two home numbers, a FAX number at home and at work, cellular number, pager number, and office number. You’ll see as we go through the tutorial how multiple tables in the database are used to implement this feature. Figure 1 shows the main contact manager screen when the application is complete.

Datagrip User Manual, Version 2.0

8

August 21, 1998

Figure 1. The tutorial program main screen. The Contact Database We could implement the contact manager program using a single table in the database. Each contact would have one record (row) in the database. However, if we use a single table, we will have to choose some maximum number of telephone numbers for each contact. We will have to define one field (column) in the table for each phone number we want to store. If we want to allow up to five phone numbers per contact, the table must have five phone number fields. More phone numbers requires more fields in the table. Worse yet, every record must have all of the phone number fields, whether they are used or not. Figure 2 shows a single database table with a fixed number of phone number fields in both the traditional row and column layout and as a Microsoft Access structure diagram. FirstName LastName Address

Company

Phone 1

Phone 2

Phone 3

Phone 4

Phone 5

Figure 2a. A single database table with five phone number fields laid out in a row and column format.

Datagrip User Manual, Version 2.0

9

August 21, 1998

Figure 2b. Microsoft Access structure view of the single table database.

A more efficient way to store the phone numbers and eliminate the restriction on the maximum number of telephone fields is to use two related tables. Figure 3 shows a design using related tables. In this design, the Contacts table stores information about the contact, but it doesn’t store any phone numbers. A separate table called the Phone Number table, stores only phone numbers and a description of each phone number. However, notice that each record in the Phone Number table also has a “ContactID” field that identifies which contact in the Contacts table this phone number belongs to. A single record in the Contacts table might have dozens—even hundreds—of corresponding phone number entries in the Phone Number Table. The entries in the two tables are linked together by ContactID field. ContactID

ContactID

Contacts Table FirstName LastName Address

Company

Phone Number Table NumberID PhoneNumber

Title

Description

Figure 3a. Row/column diagram of two tables related by the ContactID field make unlimited phone number storage easy.

Datagrip User Manual, Version 2.0

10

August 21, 1998

Figure 3b. Microsoft Access structure diagram of two tables related by the ContactID.

In this design, each contact can have as many phone numbers as needed, and the phone number table doesn’t need any empty records if a contact only has one phone number. The related tables might sound very complex to deal with. How would you go through the two tables and find all the telephone numbers that belong to an individual contact? The records for an individual contact might not even be all together in the Phone Number table! Fortunately, you don’t have to do any sorting or any special work to find related records in the two tables. Datagrip, working in concert with the Microsoft Access database system, does the hard work for you. You simply write structured query language (SQL) queries to retrieve the data you want. Datagrip does the rest. Note These tutorials assume some basic knowledge of database concepts, and of Macromedia’s scripting language. While you should be able to follow the tutorials with minimal database or scripting experience, if you need more help we suggest looking through a couple of the books listed in Appendix B, More Reading.

The tables in Figure 3 exist in the tutorial database file called TUTOR.MDB. You’ll find this database in the Datagrip directory. If you have Microsoft Access, you can open the database and examine the tables. You don’t need Microsoft Access to use Datagrip, but you’ll probably find Access useful for designing and examining databases. In addition, Microsoft Access makes it easy to import data into a database from a variety of other file formats. With that background, let’s get started on developing the contact manager application. Note The TUTOR.MDB file is written in Access version 7.0 format (Microsoft Office 95) so that it will be compatible with all later versions of Access. If you are using a later version of Access, you will probably see a message asking if you want to convert the database to the newer format. Datagrip is compatible with all database formats up to, and including Access ‘97, so it won't cause problems to convert the database to the format for your version of Access.

Datagrip User Manual, Version 2.0

11

August 21, 1998

Chapter 3: Director Tutorial This chapter will help get you started using Datagrip™ by showing you step-by-step how to build a simple Contact Manager application using Macromedia Director. If you are using Authorware, skip this chapter and refer to Chapter 4. The tutorial has been re-written to take advantage of some of the new features of version 2.0. So even if you are familiar with Datagrip, you may want to review this chapter.

Opening the database The first step in just about any database application is to open the database file. You’ll use the DGOpenDatabase function. DGOpenDatabase takes one parameter2 , the name of the database you want to open. If the database file exists in the same directory as your projector, you can simply use the name of the file like this: set dbHandle = DGOpenDatabase("Tutor.mdb")

Note In the design environment the local directory is actually the directory that Director is installed in. In the run-time environment, the local directory is the directory where the projector is stored.

Otherwise, you may want to construct the full name of the path by combining the location of the currently running movie with the name of the database. If, for example, the database is located in the same directory as the currently playing movie, you can open the database like this: set dbHandle = DGOpenDatabase(the moviePath & "Tutor.mdb")

In this case Tutor.mdb was a local file, but Datagrip can also open databases on a networked disk. This can be very useful when you need to share a database between multiple projectors. Opening a database on a network drive is essentially the same as opening a local database, except that you include the name of the computer that the database is stored on using the Universal Naming Convention (UNC) format. For example, to open a database on the computer named “FileServer” you would make the following call. set dbHandle = DGOpenDatabase("\\FileServer\Tutor.mdb")

If the DGOpenDatabase function succeeds, it returns a number called a handle. You’ll want to store this handle in a variable because you’ll use it in other Datagrip functions to refer to the open database. In the example above, the handle is stored in the variable dbHandle.

2

An alternate form of the DGOpenDatabase command takes four parameters and allows for opening the database exclusively, read-only, or with a specific connection string. Refer to the Language Reference section for details.

Datagrip User Manual, Version 2.0

12

August 21, 1998

Every database you open will have a unique handle, so you can simultaneously open as many databases as you need. Remember to store the handle for each open database in a separate variable so you’ll have a way to refer to the databases in other parts of your code. In the event that the DGOpenDatabase function fails, it will not return a number. Instead it returns the special error string #ERROR#. The error might be caused by any number of problems, such as a database file that is missing, or opened exclusively by another application (such as Microsoft Access). You should test the return value from DGOpenDatabase. If it is #ERROR#, you can use the GetLastDGError function to get information on the cause of the problem. Now let’s put what we’ve learned so far to practice. Following is the first part of the code we’ll use to initialize the application in the InitDB function. This script is called from startMovie. set dbHandle = DGOpenDatabase(the moviePath & "Tutor.mdb") if dbHandle = "#ERROR#" then alert "Error: " & GetLastDGError() alert "Now exiting the program." ClearDGError() Quit end if

In the first line, we open the database, which is located in the same directory as the movie file. We store the handle that is returned in the global variable called dbHandle. Then we check to see if an error occurred while opening by comparing dbHandle to #ERROR#. If DGOpenDatabase returned #ERROR#, we call to GetLastDGError to find out what went wrong and print out the error string that is returned. After we’ve retrieved the error information, we call ClearDGError to reset the error information. Since failing to open the database makes the program pretty useless, we exit at this point. Creating a Recordset At this point, we have an open database, but we still haven’t retrieved any data. To read data from the database, we need to create a recordset. A recordset, as the name implies, is a set of records (rows) from one or more database tables. The recordset might contain all the records in the table, or it might contain only a few records, based on some criteria we specify in an SQL query. To create a recordset, call the DGCreateRecordset function, which takes two parameters. The first parameter is an SQL query string that defines which fields and records in the database should be included in the recordset. The second parameter is the handle to the open database, which was returned from DGOpenDatabase. Here’s what a typical call to DGCreateRecordset looks like: set rsHandle = DGCreateRecordset("SELECT * FROM Contacts", dbHandle)

Datagrip User Manual, Version 2.0

13

August 21, 1998

The first parameter is the SQL string, which says, “create a recordset that contains all fields and all records from the Contacts table”. The query string could also specify that only records that meet a certain criteria be returned. For example, the SQL string: “SELECT * FROM CONTACTS WHERE COMPANY = ‘INM”

returns only contacts where the company field contains the string “INM”. Note If you’re not familiar with SQL, we recommend you refer to one of the books on Microsoft Access or SQL in the More Reading section.

The DGCreateRecordset function also returns a handle, much like the DGOpenDatabase function. You’ll want to store this handle as well because you’ll use it to read and write data from the recordset and perform other functions on the recordset. If DGCreateRecordset encounters an error, it returns the standard error string, #ERROR#. Now let’s put together the open database code from the previous example with the code required to open the recordset. -- Global data handles that will be used –- for the database and recordset. global dbHandle global rsHandle global qdHandle --- Open the contacts database assuming -- that it exists in the local directory. -- If it doesn't we will get an error back -- that we can use to communicate with the user. -set dbHandle = DGOpenDatabase(the moviePath & "Tutor.mdb") if dbHandle = "#ERROR#" then alert "Error: " & GetLastDGError() alert "Now exiting the program." ClearDGError() quit end if ---This SQL string will define our --recordset. It essentially means --Select everything that exists --inside the Contacts table. -set sqlString = "SELECT * FROM Contacts" ---Open a recordset using our above created --SQL string and our database handle -set rsHandle = DGCreateRecordset(sqlString, dbHandle) if rsHandle = "#ERROR#" then alert "Error: " & GetLastDGError() alert "Now exiting the program." Quit end if

Datagrip User Manual, Version 2.0

14

August 21, 1998

You’ve already seen the first block of code the opens the database, so let’s start with opening the recordset. We call the DGCreateRecordset with an SQL string that returns all the records and fields from the Contacts table. We store the return value in rsHandle and check it for the standard #ERROR# string. If an error occurs, we get the error information, print the error string, and exit. Typically, a recordset contains one or more records based on the criteria used to create the recordset. In the above example, the recordset contains all of the records in the “Contacts” table. Although the recordset can contain more than one record, there is a single record that is considered the current record – the record that is “pointed to”. It is this current record that many functions will operate on. For instance, deleting, editing, updating, etc all operate on the current record. If a record is added to the recordset, the current record position will point to the newly added record. This is an extremely important concept in understanding how databases work. At this point, the recordset contains all the information from our Contacts table. But what about the PhoneNumber table? There are several ways we can gain access to the PhoneNumber data. One simple method is to use a join in SQL. You simply replace the SQL string with this: SELECT Contacts.*, PhoneNumbers.* FROM Contacts INNER JOIN PhoneNumbers ON Contacts.ContactID = PhoneNumbers.ContactID;

Now the recordset contains all the fields from the Contacts table and the PhoneNumbers table (refer to Figure 4). Better still, the database has automatically matched up all the contacts with their corresponding Phone Numbers. The only disadvantage to using this technique is that the recordset actually contains one record for every contact and every phone number. This means that if a contact has three phone numbers, the contact will exist three times in the recordset— once with each of his or her phone numbers. For more information on SQL joins, refer to the one of the books in Appendix B, More Reading.

Figure 4. The recordset returned from the SQL join of the Contacts table and the PhoneNumber table.

For our purposes, the SQL join probably isn’t the best way to retrieve the phone numbers. Instead, for each contact, we’ll create a second recordset that contains all the phone numbers for the current contact.

Datagrip User Manual, Version 2.0

15

August 21, 1998

We could create the second record using another DGCreateRecordset call, much like the first one. Assuming that we have the ContactID value for the contact whose phone numbers we want to find, here’s what the code would look like: set queryStr = "SELECT * From PhoneNumbers Where ContactID=" & CurContact set rsHandle = DGCreateRecordset(queryStr, dbHandle)

The query string (queryStr) says “get all the records in the PhoneNumbers table whose ContactID is equal to CurContact. CurContact is the variable that contains the Contact ID for the current contact. For example, if the current contact contained “2” in the ContactID field, then the query above would return the recordset shown in Figure 5.

Figure 5. The set of records in the PhoneNumber table corresponding to a ContactID of 2.

Working with QueryDef Objects In all the previous examples, we’ve entered a query string in the DGCreateRecordset call. There, is however, another, more efficient way to handle queries. You can store queries in the database file itself, in a QueryDef (Query Definition). A QueryDef is simply a query, just like the ones we’ve seen up to this point, stored in the database file and given a name. The advantage of stored queries is that they are faster than query strings in your code and they improve the maintainability of your program. If you use a particular query several places in your code, it’s also easier to maintain a single query in the database than defining it all the places you need it in your code. We open a QueryDef using the DGQDOpen function. Here’s an example: set qdHandle = DGQDOpen("PhoneQuery", dbHandle) if qdHandle = "#ERROR#" then alert "Error: " & GetLastDGError() alert "Now exiting the program." Quit end if

The first parameter to DGQDOpen is the name of the stored QueryDef. The second parameter is the database handle returned from DGOpenDatabase. By now, you recognize the error checking code. The DGQDOpen function returns a handle just like the open database and create recordset functions we’ve used so far. You’ll use the

Datagrip User Manual, Version 2.0

16

August 21, 1998

handle returned from DGQDOpen to create a recordset based on the stored query. To create a recordset based on a stored query, use the DGQDCreateRS function: set rsHandle = DGQDCreateRS(qdHandle)

Notice that the DGQDCreateRS function takes only one parameter—a QueryDef handle. That’s because the QueryDef handle refers to a stored query in the database, so you don’t need an SQL string or database handle. Parameterized Queries You’ll often want to use a stored query, but change one of its parameters. In the case of our contact manager, we’ll use the following query to find the phone numbers for the current contact: PARAMETERS pContactID Long; SELECT *FROM PhoneNumbers WHERE (PhoneNumbers.ContactID=[pContactID]);

This query was created by using the Microsoft Access query designer. If you are comfortable with SQL, you can simply type the SQL directly to create the query. In any event, this SQL is saved as a query in Access. Notice the use of the parameter “pContactID”. This is called a “parameterized query” and is very useful in many applications where you want to create a recordset based on some set of criteria, typically a field equal to some value. You can specify more than one parameter but each parameter will have to be set before using the query. Here’s a segment of code that illustrates the process: -- Set the variable “val” equal to the value of the ContactID –- field of the current record. This field value will be used to –- set the value of a parameter in a saved query set val = DGRSGetFieldValue("ContactID", rsHandle) if val <> "#ERROR#" then DGQDSetParameterValue("pContactID", val, qdHandle) set tmpRS = DGQDCreateRS(qdHandle) end if

Note There are numerous advantages to using stored queries in Microsoft Access and working with QueryDef objects to access them in Datagrip. In addition to better performance, stored queries can improve the reliability and maintenance of your program by eliminating the use of hard-coded SQL in your code. Once a QueryDef has been defined, it can be modified to sort the records or join with another table without having to make changes in Lingo.

In the first line we use a new Datagrip function to read the value of the ContactID field from the Contacts table using a recordset we’ve previously created. As long as no error occurred in reading the value, we use that value in a DGQDSetParameterValue function. This function takes three parameters:

Datagrip User Manual, Version 2.0

17

August 21, 1998

pContactID

is the name of the parameter in the query that we want to set.

val

is the value that we’re setting for the parameter

qdHandle

is a handle to a previously opened QueryDef.

We won’t discuss the details of setting up a parameterized query in this tutorial. For more information, refer to the reference section for the Datagrip QueryDef functions, which all begin with DGQD. An example of using a parameterized query is included in the tutorial example. Refer to the handler named FillPhoneNumFields in the tutorial to see an example of using a parameterized QueryDef. Once the parameter values are set with DGQDSetParameterValue, we call DGQDCreateRS to create a recordset based on the QueryDef with the parameter values we set. When this code segment finishes executing, the tmpRS recordset should contain all the phone numbers for the particular ContactID we read from the Contacts table. If you need to change the value of the parameter and update the query you can call DGRSRequery. This is much faster than destroying the old recordset and opening a new one. Displaying the Contact Information Now we have the information we need from the database. It’s time to display the information on the stage. The main screen of our contact manager has eight fields that will hold the data from the database (see Figure 1). The names of the fields on the stage correspond with the fields in the database. There are fields for the first name, last name, address, company, title and portfolio sample (a bitmap image). A list box displays the phone numbers and descriptions for the current contact. There is also one hidden piece of data associated with contact records – that of a wave file used to auto dial the telephone. When the user clicks on the “Dial Phone” button, you will hear the tones associated with a telephone number. For this example, the wave file is associated with the contact record. In a real application, it would make more sense to give each phone record its own wave file. There are several different approaches to displaying the field information on the stage. Two methods will be described – field binding and manually updating field values. The tutorial actually uses both approaches in order to show exactly how each method is implemented. Binding Fields One very powerful feature of Datagrip is the ability to directly bind Director cast members to database fields. Binding will result in the automatic update of the cast member whenever the current record changes. Field binding is accomplished by using the function DGRSBindField as shown in the following code: --- Link each field to it's appropriate Database field -DGRSBindField("FirstName_Field", "FirstName", rsHandle) DGRSBindField("LastName_Field", "LastName", rsHandle) DGRSBindField("Company_Field", "Company", rsHandle) DGRSBindField("Title_Field", "Title", rsHandle)

Datagrip User Manual, Version 2.0

18

August 21, 1998

The DGRSBindField function takes three parameters. The first parameter specifies the name of the cast member that will be bound. The second parameter is the name of the field in the recordset, and the third parameter is a recordset handle. All of the code to this point in the tutorial can be found in the handler initDB, which is included in the script cast member, “StartupScripts”. Using DGRSGetFieldValue() There are some situations where automatic field binding may not be the best choice. For example, if the database stored codes in a particular field and you wanted to display the text associated with the code, you may have to get the field value and convert the code to a string using a property list or some other approach. In this case, you may want to use the function DGRSGetFieldValue. In the current tutorial, one field, the address field, is not bound and must be manually updated whenever the position of the current record changes. You will see code similar to the following code segment in a number of different functions (GoToNextRecord, GoToPrevRecord, etc.) set txt = DGRSGetFieldValue("Address", rsHandle) if txt <> "#ERROR#" then set the text of member "Address_Field" = txt end if

Prior to the field binding feature being added to Datagrip, the tutorial included a handler called FillFields that was called each time the record position was changed. The code below shows how FillFields managed the display of the contact information. on FillFields global qdHandle global rsHandle --- Check to see if we have any records -if DGRSGetRecordCount(rsHandle) <= 0 then return --- If so, fill the fields on the stage with the data -- from the database. -SetFieldVal("FirstName_Field", "FirstName") SetFieldVal("LastName_Field", "LastName") SetFieldVal("Address_Field","Address") SetFieldVal("Title_Field", "Title") SetFieldVal("Company_Field", "Company") . . .

Note The current tutorial uses field binding for all but the Address field. This section shows how field values where updated on stage in the Tutorial that accompanied version 1.0 of Datagrip.

Datagrip User Manual, Version 2.0

19

August 21, 1998

The FillFields function first declares the global variables for the recordset (rsHandle) and querydef (qdHandle) so we can use these values in the function. Next, we call DGRSGetRecordCount to find out if the recordset contains any records. If it doesn’t, there’s not much point in trying to display the data—there’s nothing to display. If there is at least one record in the recordset, we call another function called SetFieldVal to transfer the data between the recordset and the corresponding field on the screen. SetFieldVal takes two parameters. The first one is the name of the cast member where we will put the text we get from the database. The second is the name of the field in the database that contains the text. Here’s the SetFieldVal function: on SetFieldVal fieldName, dbField global rsHandle set val = DGRSGetFieldValue(dbField, rsHandle) if val = "#ERROR#" then alert "Error while retrieving from " & fieldName & RETURN & GetLastDGError() ClearDGError() set the text of member fieldName = "" else set the text of member fieldName = val end if end

Notice that we declare the rsHandle variable as global again, since we use it throughout the code. Next, we call a new Datagrip function called DGRSGetFieldValue that retrieves the data from a specified field. The first parameter to DGRSGetFieldValue is the name of the field we want to read data from. The second parameter is the recordset handle. If you look back up at the FillFields functions above, you’ll see that the first call to SetFieldVal passes a database field name of “FirstName” (the second parameter to SetFieldVal). That name is stored in the dbField variable inside SetFieldVal, which is, in turn, passed on to the DGRSGetFieldValue function. The other parameter to SetFieldVal is the name of the cast member on the stage that will receive the data from the database. DGRSGetFieldValue checks for an error using the standard method. If an error occurs, it clears the field on screen by setting it to the empty string. Otherwise, the field on the stage is set to the text read from the database. Once the FillFields function has called SetFieldVal for each of the fields in the contact table, all the contact information is filled out. The next step is to get the phone numbers for the current contact and display them. Filling the List Box with Phone Number Information Macromedia’s Director program supports only very simple list boxes that have but one column. Many times the developer would like to display more that one column in a list box. This can be done by using a constant character width font (like Courier) and “padding” or truncating spaces to line up the columns. This can be done directly in Macromedia’s scripting language if you want to take the time to write and test the functions yourself. Or, you can use the direct support for grids (multi-column list boxes) provided by Datagrip. Datagrip User Manual, Version 2.0

20

August 21, 1998

Using the Grid Functions This section will show you how to use the grid functions in Datagrip to automatically control the columns in a multi-column list box. Below is the FillPhoneNumFields handler implemented using the grid functions taken directly from the tutorial. on FillPhoneNumFields global rsHandle global qdHandle --- First, get the value of the contact ID from the current -- record of the contact recordset -set val = DGRSGetFieldValue("ContactID", rsHandle) if val <> "#ERROR#" then --- Set the parameter "pID" in the phone queryDef to -- the contact ID and open the recordset. -DGQDSetParameterValue("pID", val, qdHandle) set tmpRS = DGQDCreateRS(qdHandle) if tmpRS <> "#ERROR#" then ---Create a grid and add the needed fields -set gridHandle = DGCreateGrid("PhoneNum_Field", tmpRS) DGGridAddField("Description", 13, gridHandle) DGGridAddField("PhoneNumber", 14, gridHandle)

end

---Tell the grid to fill in all of the data -DGGridUpdate(gridHandle) end if end if

Here is how the handler works. First, the global variables rsHandle and qdHandle are declared as usual. Remember, rsHandle is the recordset handle for the Contacts table that was opened in the function InitDB. Likewise, the QueryDef handle qdHandle is the parameterized query that was created in the same function at startup time. It will be used to create a recordset of phone records that corresponds to the current contact. Next, the variable val is set to the value of the ContactID field of the current record of Contacts recordset. Once again, you will want to check to make sure the function did not return an error. Setting the parameter value and opening the recordset using the QueryDef object is done exactly as described earlier by calling DGQDSetParameterValue and DGQDCreateRS.

Datagrip User Manual, Version 2.0

21

August 21, 1998

The next step is to make a call to DGCreateGrid to create a grid object and call DGGridAddField for each of the columns that we want to display. Then, we call DGGridUpdate to automatically fill the grid with the field values specified for all of the records in the recordset. No looping required and much faster! The first argument of DGCreateGrid is the name of the Director field cast member while the second argument is the handle to a recordset. Following the creation of the grid, calling DGGridAddField will add new fields to the grid object in a left to right fashion. The first argument to DGGridAddField is the name of the field in the recordset. The second argument is the character width of the grid column and the third argument is a handle to a Datagrip grid object. Note If the contents of the field has more characters than the column width specified in the DGGridAddField function, the contents will be truncated.

Manually Filling a List Box Sometimes manually filling the grid may be advantageous compared to the automatic method. This might occur when the database contains codes instead of the actual text you want to display. In any case, you can manually fill the grid by looping through each of the records in the phone recordset and manually setting the list box data. This section will describe how to do just that. The recordset we created with the QueryDef in the previous section contains all the phone numbers associated with the current contact. The following repeat loop reads the phone number and description fields from each phone number record and fills a single large string that has carriage returns between each phone number and description. Here’s the code: repeat while not DGRSIsEOF(tmpRS) set desc = DGRSGetFieldValue("Description", tmpRS) if desc = "#ERROR#" then set desc = "" set num = DGRSGetFieldValue("PhoneNumber", tmpRS) if num = "#ERROR#" then set num = "" set tmpStr1 = tmpStr1 & padText(desc) & num & RETURN DGRSMoveNext(tmpRS) end repeat DGRSClose(tmpRS)

The loop begins with a call to DGRSIsEOF. This function returns a non-zero value if we’ve gone past the end of the phone number recordset. Remember that tmpRS is the recordset handle we created in the previous code segment. When we increment past the last record, the loop stops. For each pass through the loop (each phone number), we’ll read the value of the Description and Phone Number fields by calling DGRSGetFieldValue for each field. We do the standard error checking, and set the appropriate strings to empty if an error occurs. Once we’ve read both the description and phone numbers from the database, we append them together with

Datagrip User Manual, Version 2.0

22

August 21, 1998

some white space between them and a carriage return on the end. The results are appended to tmpStr1. The padText function is a little function that is used only to add enough white space to get the phone number and description fields to line up on screen. We won’t take the time to go through that function here. If you want to see how it works, it is included at the end of this section. At the bottom of the loop, we call DGRSMoveNext to move to the next record in the phone numbers recordset. This process continues until we reach the last record in the recordset. The DGRSClose function at the end of the loop closes the recordset we created to read the phone numbers. When the loop finishes, tmpStr1 will contain all the phone numbers and descriptions for the selected contact, separated by carriage returns as shown in Figure 1. Then it’s a simple matter to assign this string to the cast member that contains the list on screen: set the text of member "PhoneNum_Field" = tmpStr1

The PadText function is used to line up the two columns of the list box by adding white space or truncating characters thus forcing it to appear as a multi-column grid. Here is the code. on PadText str ---Set the num of chars before the next column -set padLength = 20 set count = length(str) if count <= (padLength - 1) then repeat with ii = 1 to (padLength - count) set str = str & " " end repeat else set str = chars(str, 1, padLength - 1) & " " end if end

return str

Adding New Records At this point, our contact manager application can display the first contact and all the phone numbers assigned to that contact. We’ve actually done most of the work, but the application isn’t too useful unless we can move through the contacts and add new ones. There are three major steps to follow whenever you want to add a new record to a database or change an existing record in the database. Here are the steps: 1. Call DGRSAddNew to add a new empty record or call DGRSEdit to edit an existing record.

Datagrip User Manual, Version 2.0

23

August 21, 1998

2. Add data to the new record or change data in the existing record. If you’re modifying an existing record, you can call DGRSGetFieldValue to read the current value of a field. Call DGRSSetFieldValue function to put data into a field. 3. Call DGRSUpdate to save the new record or changes to the database.

You must repeat this process for every record you want to add or change. Now that you know the process, let’s look at how we would implement code to add a new record to our contact database. The following script, which is placed under the “New” button does just that: on CreateNewRecord global rsHandle if DGRSGetEditMode(rsHandle)=2 then SaveCurrentData() end if DGRSAddNew(rsHandle) end

The CreateNewRecord function once again declares rsHandle as a global variable, so we can use the recordset we opened in the InitDB function at startup. Then we begin by calling the DGRSGetEditMode function, which will return an integer indicating the state of the database. If this function returns a 2, it means that a new record has been added to the database but not yet saved or that the recordset was opened for editing (DGRSEdit) but has not been updated (DGRSUpdate). If that is the case, we’ll call the SaveCurrentData function to transfer data on the screen to the database. Then we’ll call the DGRSUpdate function to save the data in the database. Now that the previous data, if any, has been saved, we call the DGRSAddNew function to add a new empty record. Let’s back up for a moment and look at what’s inside the SaveCurrentData function that we called to save the data. Here’s the code: on SaveCurrentData global rsHandle SaveDBField("FirstName") SaveDBField("LastName") SaveDBField("Address") SaveDBField("Title") SaveDBField("Company") DGRSUpdate(rsHandle) end

First you see the standard global declaration for the recordset handle. The main part of the function makes five calls to SaveDBField and passes the name of the database field we want to save. After we’ve saved all the fields, we call DGRSUpdate to store the results in the database.

Datagrip User Manual, Version 2.0

24

August 21, 1998

The SaveDBField function is a tiny function that relies on the fact that we named our database fields and the cast members on screen using the same names, but with “_field” appended to the end of the cast member names. Here’s the SaveDBField function: on SaveDBField fieldName global rsHandle DGRSSetFieldValue(FieldName, the text of member(fieldName & end

"_Field"),rsHandle)

The DGRSSetFieldValue function takes three arguments. The first function is the name of the field we want to store data in. That name is passed in to the function. The second argument is the data we want to store. In this case, the data comes from the text of the cast member whose name is the same as the field name with a “_Field” appended to the end. The last parameter is the global recordset handle. Navigating through the contacts Our contact manager program is getting pretty functional. We can display a contact, including all their phone numbers and we can add new contacts. However, you may have noticed that at this point, we have no way of moving through the contacts in the database. You can only see the first contact or the most recently added contact. We need to add some way to move through the contact database. Moving through the database one record at a time is pretty easy. We’ll add two functions called GoToNextRecord and GoToPrevRecord and we’ll tie these to the Next and Previous buttons on screen. The two functions are so close to identical, that we’ll just show you the GoToPrevRecord function and point out the differences between the two. You can see the GoToNextRecord function in TUTOR.DIR. on GoToPrevRecord global rsHandle if DGRSGetEditMode(rsHandle) = 2 then SaveCurrentData() end if DGRSMovePrev(rsHandle) if DGRSIsBOF(rsHandle) then DGRSMoveNext(rsHandle) end if end

The first block of code should look familiar. It’s the same code we used in the CreateNewRecord function. It calls DGRSGetEditMode function to find out if there’s some unsaved data. We want to save the data before we move to a new record. Once we’ve taken care of saving any unsaved data, we’re ready to move to the previous record. We call the DGRSMovePrev function to make the move. The only problem with this is that we might already be at the first record. To take care of that situation, we call DGRSisBOF. This function returns a non-zero value (true) if we are past the beginning of the recordset. It’s important to remember that it only returns true if we are past the beginning of the data. It won’t do any good to call DGRSisBOF before you move, because it doesn’t return true until you’ve moved past the beginning. Datagrip User Manual, Version 2.0

25

August 21, 1998

The result of this behavior is that you must move to the previous (or next) record, then test whether you’re past the end of the records. If so, you move back to where you were. In our case, we call DGRSMoveNext to move back to the first record if we move past the beginning. Remember that we said the GoToPrevRecord and GoToNextRecord functions were essentially identical. The only difference is that in GoToNextRecord, the DGRSMovePrev and DGRSMovePrev calls are swapped and we call DGRSisEOF instead of DGRSisBOF, since we’re testing to see if we moved past the end of the recordset, instead of testing for moving past the beginning. Apart from that, the two functions are identical. Each time the user clicks on the Next or Previous buttons, we call the corresponding function, which moves to the next record in the contact recordset, until we reach the beginning or end of the records. Each time we move to a new record, we’ll also need to call the code to get the corresponding phone numbers from the phone number table. We saw this code when we looked at QueryDefs. If field binding is used, the fields will automatically display the data corresponding to the new record. Otherwise, call the FillFields handler to update the screen. Adding Phone Numbers One of the key features of our contact program is the ability to store as many phone numbers as we want for a particular contact. However, we still need a way to add phone numbers to the phone number table. Following is the code for a function that will be called when the user clicks the “Add Phone Number” button.

Datagrip User Manual, Version 2.0

26

August 21, 1998

global rsHandle global dbHandle --- Save any un-saved data -if DGRSGetEditMode(rsHandle) = 2 then SaveCurrentData() DGRSUpdate(rsHandle) DGRSMoveLast(rsHandle) end if --- Get the phone numbers and descriptions from the screen -set descStr = the text of member "tmpDesc_Field" set NumStr = the text of member "tmpNum_Field" --- If there are no phone numbers or descriptions, stop here. -if the number of words in descStr=0 and the number of words in numStr=0 then return end if --- Get the ContactID value for the current Contact and –- build a recordset of phone numbers for that ContactID -set id = DGRSGetFieldValue("ContactID", rsHandle) if id <> "#ERROR#" then set tmpRS = DGCreateRecordset("SELECT * FROM PhoneNumbers Where ContactID =" && id, dbHandle) DGRSAddNew(tmpRS) DGRSSetFieldValue("ContactID", id, tmpRS) DGRSSetFieldValue("Description", descStr, tmpRS) DGRSSetFieldValue("PhoneNumber", numStr, tmpRS) DGRSUpdate(tmpRS) DGRSClose(tmpRS) end if

>>

The code begins in the usual way by declaring our global handles. The next block should also be familiar. It checks for unsaved data and saves the data if necessary. Now the real work begins. The next two lines copy the text from the cast members that store the phone numbers and descriptions (tmpNum_Field and tmpDesc_Field) to temporary variables. We need to check whether there are, in fact, any phone numbers or descriptions entered, so the next line checks the number of words in the temporary variables. If they’re both zero, we just quit. There’s no point in creating a new record to store empty strings. If there is something to store, we read the ContactID field from the current contact record. As long as there was no error in reading the ContactID value, we create a recordset from the PhoneNumber table based on that contact ID. The query in the DGCreateRecordset function says “get all the records in the PhoneNumbers table that have a ContactID of id. Id is the variable that stores the ContactID we read from the Contacts table. The result is that the tmpRS recordset contains all the phone numbers associated with the current contact. Next, we add a new record to the recordset we just created. Then we call DGRSSetFieldValue to store the ContactID, Description, and Phone Number fields for the new record. Finally, we call DGRSUpdate to store the new record in the database and DGRSClose to close the phone number recordset. Notice that we’re closing the phone number recordset (tmpRS), not the contacts recordset (rsHandle). Datagrip User Manual, Version 2.0

27

August 21, 1998

Summary We’ve created all the core functionality of a useful little contact management program. We haven’t covered every detail of the code, and there are certainly a number of areas for improvement in our program. Now would be a good time to look through the tutorial file or refer to Appendix C to see how the scripts have been organized. When you open TUTOR.DIR, you’ll find that any functions that work directly with the database are in the script titled “DB Scripts”. Any functions that deal with the stage or user interface are in “Interface Scripts”. Functions that initialize the entire application are located in “Startup Scripts”. This tutorial is designed to provide a high-level overview of Datagrip and show some examples of using it in code. The tutorial only covers a small part of Datagrip’s functionality. You’ll want to look through the Language Reference chapter to see all the other things Datagrip can do. As a final exercise, you might consider adding search capability to the contact manager. You’ll need to add a new button to invoke a search, and perhaps a dialog box to enter the search text. You can use the DGFindFirst function to search the database for a match. We expect to be adding new functionality to Datagrip as well as releasing complimentary products. We would like to know what you want in future releases of Datagrip as well as other products. If you find a unique application for Datagrip or would like to see the product improved in some way, tell us about it at our web site (www.INM.com). Thank you for choosing Datagrip from Integration New Media, Inc.

Datagrip User Manual, Version 2.0

28

August 21, 1998

Chapter 4: Authorware Tutorial This chapter will help get you started using Datagrip™ by showing you step-by-step how to build a simple Contact Manager application using Macromedia Authorware. If you are using Director, refer to the previous chapter.

Opening the database The first step in just about any database application is to open the database file. You’ll use the DGOpenDatabase function. DGOpenDatabase takes one parameter, the name of the database you want to open. If the database file exists in the same directory as packaged file, you can simply use the name of the file like this: dbHandle := DGOpenDatabase(“Tutor.mdb”)

In this case Tutor.mdb was a local file, but Datagrip can also open databases on a networked disk. This can be very useful when you need to share a database between multiple projectors. Opening a database on a network drive is essentially the same as opening a local database, except that you include the name of the computer that the database is stored on using the Universal Naming Convention (UNC) format. For example, to open a database on the computer named FileServer you would make the following call. dbHandle := DGOpenDatabase(“\\\\FileServer\\Tutor.mdb”)

If the DGOpenDatabase function succeeds, it returns a number called a handle. You’ll want to store this handle in a variable because you’ll use it in other Datagrip functions to refer to the open database. In the example above, the handle is stored in the variable dbHandle. Every database you open will have a unique handle, so you can simultaneously open as many databases as you need. Remember to store the handle for each open database in a separate variable so you’ll have a way to refer to the databases in other parts of your code. In the event that the DGOpenDatabase function fails, it will not return a number. Instead it returns the special error string #ERROR#. The error might be caused by any number of problems, such as a database file that is missing, or opened exclusively by another application (such as Microsoft Access). You should test the return value from DGOpenDatabase. If it is #ERROR#, you can use the GetLastDGError function to get information on the cause of the problem. Now let’s put what we’ve learned so far to practice. Following is the first part of the code we’ll use to initialize the application. This code is located in the OpenDatabase icon.

Datagrip User Manual, Version 2.0

29

August 21, 1998

dbHandle := DGOpenDatabase("Tutor.mdb") if dbHandle = "#ERROR#" then LastErrorTxt := "Error in icon: Open Database: "^GetLastDGError() ClearDGError() quit end if

In the first line, we open the database, which is located in the same directory as the movie file. We store the handle that is returned in the global variable called dbHandle. Then we check to see if an error occurred while opening by comparing dbHandle to #ERROR#. If DGOpenDatabase returned #ERROR#, we call to GetLastDGError to find out what went wrong and print out the error string that is returned. After we’ve retrieved the error information, we call ClearDGError to reset the error information. Since failing to open the database makes the program pretty useless, we exit at this point. Creating a Recordset At this point, we have an open database, but we still haven’t retrieved any data. To read data from the database, we need to create a recordset. A recordset, as the name implies, is a set of records (rows) from one or more database tables. The recordset might contain all the records in the table, or it might contain only a few records, based on some criteria we specify in an SQL query. To create a recordset, call the DGCreateRecordset function, which takes two parameters. The first parameter is an SQL query string that defines which fields and records in the database should be included in the recordset. The second parameter is the handle to the open database, which was returned from DGOpenDatabase. Here’s what a typical call to DGCreateRecordset looks like: rsHandle := DGCreateRecordset("SELECT * FROM Contacts", dbHandle)

The first parameter is the SQL string, which says, “create a recordset that contains all fields and all records from the Contacts table”. The query string could also specify that only records that meet a certain criteria be returned. For example, the SQL string: “SELECT * FROM CONTACTS WHERE COMPANY = ‘Integration New Media’”

returns only contacts where the company field contains the string “Integration New Media”. Note If you’re not familiar with SQL, we recommend you refer to one of the books on Microsoft Access or SQL in the More Reading section.

The DGCreateRecordset function also returns a handle, much like the DGOpenDatabase function. You’ll want to store this handle as well because you’ll use it to read and write data from the recordset and perform other functions on the recordset. If DGCreateRecordset encounters an error, it returns the standard error string, #ERROR#. Datagrip User Manual, Version 2.0

30

August 21, 1998

Now let’s put together the open database code from the previous example with the code required to open the recordset. --- Open the Database --- LastErrorTxt is shown across the bottom of the screen when it has a message in it. dbHandle := DGOpenDatabase("Tutor.mdb") if dbHandle = "#ERROR#" then LastErrorTxt := "Error in icon: Open Database: "^GetLastDGError() ClearDGError() quit end if --- Create the recordset -QueryStr := "Select * from Contacts ORDER BY Contacts.LastName" rsHandle := DGCreateRecordset(QueryStr, dbHandle) if rsHandle = "#ERROR#" then LastErrorTxt := LastErrorTxt^"\rError in icon Get Record Set ...: "^GetLastDGError() ClearDGError() quit end if

You’ve already seen the first block of code that opens the database, so let’s start with opening the recordset. We call the DGCreateRecordset with an SQL string that returns all the records and fields from the Contacts table. We store the return value in rsHandle and check it for the standard #ERROR# string. If an error occurs, we get the error information, print the error string, and exit. At this point, the recordset contains all the information from our Contacts table. But what about the PhoneNumber table? There are several ways we can gain access to the PhoneNumber data. One simple method is to use a join in SQL. You simply replace the SQL string with this: SELECT * From Contacts,PhoneNumbers Where Contacts.ContactID=PhoneNumber.ContactID

Or, alternatively: SELECT Contacts.*, PhoneNumbers.* FROM Contacts INNER JOIN PhoneNumbers >> ON Contacts.ContactID = PhoneNumbers.ContactID;

Now the recordset contains all the fields from the Contacts table and the PhoneNumbers table (refer to Figure 5). Better still, the database has automatically matched up all the contacts with their corresponding Phone Numbers. The only disadvantage to using this technique is that the recordset actually contains one record for every contact and every phone number. This means that if a contact has three phone numbers, the contact will exist three times in the recordset— once with each of his or her phone numbers. For more information on SQL joins, refer to the one of the books in Appendix B, More Reading.

Datagrip User Manual, Version 2.0

31

August 21, 1998

Figure 5. The recordset returned from the SQL join of the Contacts table and the PhoneNumber table.

For our purposes, the SQL join probably isn’t the best way to retrieve the phone numbers. Instead, for each contact, we’ll create second recordset that contains all the phone numbers for the current contact. We could create the second record using another DGCreateRecordset call, much like the first one. Assuming that we have the ContactID value for the contact whose phone numbers we want to find, here’s what the code would look like: queryStr := "SELECT * From PhoneNumbers Where ContactID=" ^CurContact rsHandle := DGCreateRecordset(queryStr, dbHandle)

The query string (queryStr) says “get all the records in the PhoneNumbers table whose ContactID is equal to CurContact. CurContact is the variable that contains the Contact ID for the current contact. Working with QueryDefs In all the previous examples, we’ve entered a query string in the DGCreateRecordset call. There, is however, another, more efficient way to handle queries. You can store queries in the database file itself, in a QueryDef (Query Definition). A QueryDef is simply a query, just like the ones we’ve seen up to this point, stored in the database file and given a name. The advantage of stored queries is that they are faster than query strings in your code- up to 25% faster than opening a recordset with an SQL string. If you use a particular query several places in your code, it’s also easier to maintain a single query in the database than defining it all the places you need it in your code. We open a QueryDef using the DGQDOpen function. Here’s an example: qdHandle := DGQDOpen("PhoneQuery", dbHandle) if qdHandle = "#ERROR#" then LastErrorTxt := LastErrorTxt^"\rError in icon Get Record Set ...: "^GetLastDGError() ClearDGError() quit end if

The first parameter to DGQDOpen is the name of the stored QueryDef. The second parameter is the database handle returned from DGOpenDatabase. By now, you recognize the error checking code. The DGQDOpen function returns a handle just like the open database and creates recordset functions we’ve used so far. You’ll use the

Datagrip User Manual, Version 2.0

32

August 21, 1998

handle returned from DGQDOpen() to create a recordset based on the stored query. To create a recordset based on a stored query, use the DGQDCreateRS function: rsHandle := DGQDCreateRS(qdHandle)

Notice that the DGQDCreateRS function takes only one parameter—a QueryDef handle. That’s because the QueryDef handle refers to a stored query in the database, so you don’t need an SQL string or database handle. Parameterized Queries You’ll often want to use a stored query, but change one of its parameters. In the case of our contact manager, we’ll use the following query to find the phone numbers for the current contact: "SELECT * From PhoneNumbers Where ContactID = " ^CurContact

We’ll store this query in the database as a QueryDef. However, we need to supply a value for the CurContact variable each time we use the query. This is called a “parameterized query” because we store the query, but we can modify one or more of the query’s parameters in our code. Here’s a segment of code that illustrates the process: val := DGRSGetFieldValue("ContactID", rsHandle) if val <> "#ERROR#" then DGQDSetParameterValue("pContactID", val, qdHandle) tmpRS := DGQDCreateRS(qdHandle) end if

In the first line we use a new Datagrip function to read the value of the ContactID field from the Contacts table using a recordset we’ve previously created. As long as no error occurred in reading the value, we use that value in a DGQDSetParameterValue function. This function takes three parameters: pContactID

is the name of the parameter in the query that we want to set.

Val

is the value that we’re setting for the parameter

qdHandle

is a handle to a previously opened QueryDef.

We won’t discuss the details of setting up a parameterized query in this tutorial. For more information, refer to the reference section for the Datagrip QueryDef functions, which all begin with DGQD or refer to the Microsoft Access help. Once the parameter values are set with DGSetParameterValue, we call DGCreateRS to create a recordset based on the QueryDef with the parameter values we set. When this code segment finishes executing, the tmpRS recordset should contain all the phone numbers for the particular ContactID we read from the Contacts table.

Datagrip User Manual, Version 2.0

33

August 21, 1998

Displaying the Contact Information Now we have the information we need from the database. It’s time to display the information on the screen. The main screen of our contact manager has seven fields that will hold the data from the database (see Figure 1). The names of the fields on the screen correspond with the fields in the database. There are fields for the first name, last name, address, company and title. A list box displays the phone numbers and descriptions for the current contact. There are several different approaches to displaying the field information on the screen. Two methods will be described – field binding and manually updating field values. The tutorial actually uses both approaches in order to show exactly how each method is implemented. Binding Fields One very powerful feature of Datagrip is the ability to directly bind Authorware variables to database fields. Binding will result in the automatic update of the variable whenever the current record changes. Field binding is accomplished by using the function DGRSBindField as shown in the following code: --- Link each variable to it's appropriate Database field -DGRSBindVar("FirstNameStr", "FirstName", rsHandle) DGRSBindVar("LastNameStr", "LastName", rsHandle) DGRSBindVar("TitleStr", "Title", rsHandle) DGRSBindVar("CompanyStr", "Company", rsHandle)

The DGRSBindField function takes three parameters. The first parameter specifies the name of the variable that will be bound. The second parameter is the name of the field in the recordset, and the third parameter is a recordset handle. All of the code to this point in the tutorial can be found in the “Setup Handlers & Fields” icon in the source file. Using DGRSGetFieldValue() There are some situations where automatic field binding may not be the best choice. For example, if the database stored codes in a particular field and you wanted to display the text associated with the code, you may have to get the field value and convert the code to a string using a property list or some other approach. In this case, you may want to use the function DGRSGetFieldValue. In the current tutorial, one field, the address field, is not bound and must be manually updated whenever the position of the current record changes. You will see code similar to the following code segment in a number of different icons (Next >, Prev <, etc.) AddressStr := DGRSGetFieldValue("Address", rsHandle)

Prior to the field binding feature being added to Datagrip, the tutorial was designed so that any time the recordset changed a series of calls would be made to DGRSGetFieldValue. This

Datagrip User Manual, Version 2.0

34

August 21, 1998

function takes two parameters. The first is the name of the fields in the recordset and the second is a handle to out previously opened recordset. DGRSGetFieldValue checks for an error using the standard method. If an error occurs, it clears the field on screen by setting it to the empty string.

Using the Grid Functions This section will show you how to use the grid functions in Datagrip to automatically control the columns in a multi-column list box. Below is the “Phone Fields” and “Get Contact ID” icons implemented using the grid functions taken directly from the tutorial. ---Get the ID of the current contact -CurContact := DGRSGetFieldValue("ContactID",rsHandle) DGQDSetParameterValue("pContactID", CurContact, dbHandle) phHandle := DGQDCreateRS(qdHandle) if phHandle = "#ERROR#" then LastErrorTxt := LastErrorTxt^"\rError in icon: Get Contact ID "^GetLastDGError() ClearDGError() end if ---Create a grid object and tie the phone number fields to it -gridHandle := DGCreateGrid("PhoneGrid", phHandle) DGGridAddField("Description", 7, gridHandle) DGGridAddField("PhoneNumber" ,15, gridHandle) DGGridUpdate(gridHandle) ---Close the recordset since it will change --every time we change records. -DGRSClose(phHandle)

Here is how the handler works. First, the variable CurContact is set to the value of the ContactID field of the current record of Contacts recordset. Once again, you will want to check to make sure the function did not return an error. Setting the parameter value and opening the recordset using the QueryDef object is done exactly as described earlier by calling DGQDSetParameterValue and DGQDCreateRS. The next step is to make a call to DGCreateGrid to create a grid object and call DGGridAddField for each of the columns that we want to display. Then, we call DGGridUpdate to automatically fill the grid with the field values specified for all of the records in the recordset. No looping required and much faster! The first argument of DGCreateGrid is the name of the Authorware variable while the second argument is the handle to a recordset. Following the creation of the grid, calling Datagrip User Manual, Version 2.0

35

August 21, 1998

DGGridAddField will add new fields to the grid object in a left to right fashion. The first argument to DGGridAddField is the name of the field in the recordset. The second argument is the character width of the grid column and the third argument is a handle to a Datagrip grid object. Note If the contents of the field has more characters than the column width specified in the DGGridAddField function, the contents will be truncated.

Manually Filling a List Box Sometimes manually filling the grid may be advantageous compared to the automatic method. This might occur when the database contains codes instead of the actual text you want to display. In any case, you can manually fill the grid by looping through each of the records in the phone recordset and manually setting the list box data. This section will describe how to do just that. The recordset we created with the QueryDef in the previous section contains all the phone numbers associated with the current contact. The following repeat loop reads the phone number and description fields from each phone number record and fills a single large string that has carriage returns between each phone number and description. Here’s the code: repeat while not DGRSIsEOF(tmpRS) desc := DGRSGetFieldValue("Description", tmpRS) if desc = "#ERROR#" then desc := "" num := DGRSGetFieldValue("PhoneNumber", tmpRS) if num = "#ERROR#" then num := "" tmpStr1 := tmpStr1^” “^num^”\r” DGRSMoveNext(tmpRS) end repeat DGRSClose(tmpRS)

The loop begins with a call to DGRSIsEOF. This function returns a non-zero value if we’ve gone past the end of the phone number recordset. Remember that tmpRS is the recordset handle we created in the previous code segment. When we increment past the last record, the loop stops. For each pass through the loop (each phone number), we’ll read the value of the Description and Phone Number fields by calling DGRSGetFieldValue for each field. We do the standard error checking, and set the appropriate strings to empty if an error occurs. Once we’ve read both the description and phone numbers from the database, we append them together with some white space between them and a carriage return on the end. The results are appended to tmpStr1. At the bottom of the loop, we call DGRSMoveNext to move to the next record in the phone numbers recordset. This process continues until we reach the last record in the recordset. The DGRSClose function at the end of the loop closes the recordset we created to read the phone numbers.

Datagrip User Manual, Version 2.0

36

August 21, 1998

When the loop finishes, tmpStr1 will contain all the phone numbers and descriptions for the selected contact, separated by carriage returns as shown in Figure 1. Then it’s a simple matter to assign this string to the variable that will display the text on screen. OnScreenVar := tmpStr1

Adding New Records At this point, our contact manager application can display the first contact and all the phone numbers assigned to that contact. We’ve actually done most of the work, but the application isn’t too useful unless we can move through the contacts and add new ones. There are three major steps to follow whenever you want to add a new record to a database or change an existing record in the database. Here are the steps: 1. Call DGRSAddNew to add a new empty record or call DGRSEdit to edit an existing record. 2. Add data to the new record or change data in the existing record. If you’re modifying an existing record, you can call DGRSGetFieldValue to read the current value of a field. Call DGRSSetFieldValue function to put data into a field. 3. Call DGRSUpdate to save the new record or changes to the database.

You must repeat this process for every record you want to add or change. Now that you know the process, let’s look at how we would implement code to add a new record to our contact database. The following script, which is in the “NewRecord” subroutine, does just that: DGRSAddNew(rsHandle) -- Add a new record CanWeEdit := DGRSGetEditMode(rsHandle) if CanWeEdit = 2 then DGRSSetFieldValue("FirstName", FirstNameStr, rsHandle) DGRSSetFieldValue("LastName", LastNameStr, rsHandle) DGRSSetFieldValue("Address", AddressStr, rsHandle) DGRSSetFieldValue("Title", TitleStr, rsHandle) DGRSSetFieldValue("Company", CompanyStr, rsHandle) DGRSUpDate(rsHandle) end if

In the “NewRecord” subroutine, we begin by calling the DGRSGetEditMode function, which will return an integer indicating the state of the database. If this function returns a 2, it means that a new record has been added to the database but not yet saved. Each of the field values is saved by calling the DGRSSetFieldValue function. This function takes three arguments. The first argument is the name of the field we want to store data in. The second argument is the data we want to store. The last parameter is the global recordset handle. Once the field values have been saved, we’ll call the DGRSUpdate function to save the data in the database. Datagrip User Manual, Version 2.0

37

August 21, 1998

Now that the previous data, if any, has been saved, we call the DGRSAddNew function to add a new empty record. Navigating through the contacts Our contact manager program is getting pretty functional. We can display a contact, including all their phone numbers and we can add new contacts. However, you may have noticed that at this point, we have no way of moving through the contacts in the database. You can only see the first contact or the most recently added contact. We need to add some way to move through the contact database. Moving through the database one record at a time is pretty easy. We’ll add two subroutines called Next and Prev and we’ll tie these to the Next and Previous buttons on screen. If the current button is the last record in the recordset, the “Next” button is disabled. Likewise, if we are positioned on the first record, the “Prevous” button is disabled. This behavior is accomplished in the “Active If” property of the button. --- Next Record Icon -DGRSMoveNext(rsHandle) NoCurrentEntry := NoCurrentEntry + 1 ---Previous Record Icon -DGRSMovePrev(rsHandle) NoCurrentEntry := NoCurrentEntry –1

Each time the user clicks on the Next or Previous buttons, we call the corresponding subroutines, which moves to the next record in the contact recordset, until we reach the beginning or end of the records. Each time we move to a new record, we’ll also need to call the code to get the corresponding phone numbers from the phone number table. We saw this code when we looked at QueryDefs. Adding Phone Numbers One of the key features of our contact program is the ability to store as many phone numbers as we want for a particular contact. However, we still need a way to add phone numbers to the phone number table. Following is the code for a function that will be called when the user clicks the AddPhoneNumber button.

Datagrip User Manual, Version 2.0

38

August 21, 1998

---Store new phone # -DGRSClose(phHandle) CurContact := DGRSGetFieldValue("ContactId",rsHandle) QueryStr := "Select * From PhoneNumbers Where ContactID=" ^CurContact phHandle := DGCreateRecordset(QueryStr, dbHandle) DGRSAddNew(phHandle) DGRSSetFieldValue("ContactID", CurContact, phHandle) DGRSSetFieldValue("Description", PhoneNum[NextPhone,1], phHandle) DGRSSetFieldValue("PhoneNumber",PhoneNum[NextPhone,2], phHandle) DGRSUpDate(phHandle)

Now the real work begins. The next two lines copy the text from the cast members that store the phone numbers and descriptions (tmpNum_Field and tmpDesc_Field) to temporary variables. We need to check whether there are, in fact, any phone numbers or descriptions entered, so the next line checks the number of words in the temporary variables. If they’re both zero, we just quit. There’s no point in creating a new record to store empty strings. If there is something to store, we read the ContactID field from the current contact record. As long as there was no error in reading the ContactID value, we create a recordset from the PhoneNumber table based on that contact ID. The query in the DGCreateRS function says “get all the records in the PhoneNumbers table that have a ContactID of id. Id is the variable that stores the ContactID we read from the Contacts table. The result is that the tmpRS recordset contains all the phone numbers associated with the current contact. Next, we add a new record to the recordset we just created. Then we call DGRSSetFieldValue to store the ContactID, Description, and Phone Number fields for the new record. Finally, we call DGRSUpdate to store the new record in the database and DGRSClose to close the phone number recordset. Notice that we’re closing the phone number recordset (tmpRS), not the contacts recordset (rsHandle). Summary We’ve created all the core functionality of a useful little contact management program. We haven’t covered every detail of the code, and there are certainly a number of areas for improvement in our program. Now would be a good time to look through the tutorial file or refer to Appendix D to see how the scripts have been organized. As a final exercise, you might consider adding search capability to the contact manager. You’ll need to add a new button to invoke a search, and perhaps a dialog box to enter the search text. You can use the DGRSFindFirst function to search the database for a match. We expect to be adding new functionality to Datagrip as well as releasing complimentary products. We would like to know what you want in future releases of Datagrip as well as other products. If you find a unique application for Datagrip or would like to see the product improved in some way, tell us about it at our web site (www.INM.com). Thank you for choosing Datagrip from Integration New Media, Inc.

Datagrip User Manual, Version 2.0

39

August 21, 1998

Chapter 5 Language Reference ClearDGError Description:

Clears the error buffer. When an error occurs a string buffer is filled with a detailed message explaining the error. If you want to test and see if an error occurred you could call GetLastDGError(), and then clear it with this function.

Syntax:

ClearDGError()

Returns:

Nothing is returned from this function.

See Also:

GetLastDGError.

Example:

The example below checks to see if an error occurred and then clears the error buffer. It’s a good idea to clear the error buffer after handling an error so that it will always represent the most recent error, if any. on exitFrame if not voidP(GetLastDGError()) put GetLastDGError() ClearDGError() end if end

Datagrip User Manual, Version 2.0

40

August 21, 1998

DGClose Description:

Closes an open database.

Syntax:

DGClose(dbHandle) dbHandle: A handle to a valid open database object.

Returns:

Nothing is returned unless an error occurs, in which case the standard error string is returned.

See Also:

DGOpenDatabase

Example: if DGClose(dbHandle) = "#ERROR#" then alert "Error closing the database." end if

Datagrip User Manual, Version 2.0

41

August 21, 1998

DGCreateGrid Description:

This function will create a multi-column version of the data in the database. For instance if you wanted to display the first name, last name, and age of each record in the database all in a nicely formatted way you would use this function. The spacing is automatically handled for the developer so the output looks very nice. For everything to line up correctly you should use a fixed width font, such as Courier.

Syntax:

DGCreateGrid(outName, rsHandle) outName: If your using Authorware this should be the name of the variable that will hold the grid once it is created. In Director this is the name of the field that will hold grid. rsHandle: A handle to a valid and open recordset.

Returns:

Upon success this function returns a handle to a newly created grid object. This handle will be used in later functions to format the grid. If the function fails the standard error string will be returned.

See Also:

DGGridAddField, DGGridDeleteField, DGGridUpdate

Example:

The following example will create a new grid in the My_Field cast member . set gridHandle = DGCreateGrid(“My_Field”, rs)

The following example will create a new grid in the My_Var variable. gridHandle := DGCreateGrid(“My_Var”, rs)

Datagrip User Manual, Version 2.0

42

August 21, 1998

DGCreateRecordset Description:

This function creates a recordset from an open database and a query string. The recordset contains a set of records that meet the criteria specified in the query string.

Syntax:

DGCreateRecordset(sqlString, dbHandle) sqlString: The SQL string that defines the recordset that you are opening. dbHandle: A handle to an open database object. You can get this handle by calling DGOpenDatabase.

Returns:

If the function is successful it returns a handle to the recordset object. If the call was not successful it returns the standard error string, #ERROR#. Call GetLastDGError() for a more detailed description of the error.

See Also:

DGOpenDatabase, GetLastDGError

Example:

The example below opens a database and creates a recordset object that will hold all of the fields in the 'Family' table. Finally it prints the handle of the recordset object to the message window. on exitFrame set db = DGOpenDatabase("C:\MyDb.mdb") set rs = DGCreateRecordset("SELECT * FROM Family", db) put rs end

Datagrip User Manual, Version 2.0

43

August 21, 1998

DGExecute Description:

Executes SQL commands on the database.

Syntax:

DGExecute(sqlString, dbHandle) sqlString: A string that contains the SQL commands to be executed. dbHandle: A handle to an open database object.

Returns:

Nothing if the function is successful. If an error occurs the standard error string, #ERROR#, will be returned.

Example:

The following example deletes all the records in the Family table. on exitFrame set db = DGOpenDatabase("C:\MyDb.mdb") DGExecute("Delete * from Family",db) end

Datagrip User Manual, Version 2.0

44

August 21, 1998

DGGetVersion Description:

This function will return the current version of Datagrip. It is important to remember that this function is only available in version 2.0 and above.

Syntax:

DGGetVersion()

Returns:

A string will be returned specifying the version of the currently installed Datagrip Xtra.

See Also:

None.

Example:

The following example ensures that Datagrip 2.0 is running. if DGGetVersion() is not “2.0” then return

Datagrip User Manual, Version 2.0

45

August 21, 1998

DGGridAddField Description:

Adds a field to the grid list. This list holds every database field that will be displayed in the grid.

Syntax:

DGGridAddField(fieldName, size, gridHandle) fieldName: The name of the field in the database to add to the grid list. size: The number of characters to be displayed in the column inside the grid. gridHandle: A handle to a grid which has been created by DGCreateGrid().

Returns:

If this function is successful nothing is returned. In the case that something went wrong the standard error string will be returned.

See Also:

DGCreateGrid, DGGridDeleteField, DGGridUpdate.

Example:

The following example will add the ID, Name, and PhoneNum fields which exist in the open recordset. The results will be stored in the My_Field cast member. set gridHandle = DGCreateGrid(“My_Field”, rsHandle) DGGridAddField(“ID”, 3, gridHandle) DGGridAddField(“Name”, 8, gridHandle) DGGridAddField(“PhoneNum”, 8, gridHandle)

Datagrip User Manual, Version 2.0

46

August 21, 1998

DGGridDeleteField Description:

This function deletes a field from the field list that the grid holds. When you call this function the grid will no longer pull data from the specified database field.

Syntax:

DGGridDeleteField(fieldName, gridHandle) fieldName: The name of the database field that you want to terminate a connection with. gridHandle: A valid grid handle created by DGCreateGrid().

Returns:

If this function is successful then nothing is returned. On the other hand if an error occurs then the standard error string is returned.

See Also:

DGCreateGrid, DGGridAddField, DGGridUpdate.

Example:

The following example will add two database fields to the grid and then delete the initial one. set gridHandle = DGCreateGrid(“My_Field”, rsHandle) DGGridAddField(“ID”, 2, gridHandle) DGGridAddField9(“Name”, 9, gridHandle) DGGridDeleteField(“ID”, gridHandle)

Datagrip User Manual, Version 2.0

47

August 21, 1998

DGGridUpdate Description:

This function will update the cast member or the variable with the new data from the grid object.

Syntax:

DGGridUpdate(gridHandle) gridHandlei: A handle to a valid grid object created by DGCreateGrid().

Returns:

Nothing is returned unless an error occurs. In this case the standard error string is returned.

See Also:

DGCreateGrid, DGGridAddField, DGGridDeleteField.

Example:

The following example will fill out the My_Field cast member with all of the contents of two fields in the recordset. When the data transfer is done all of the columns should line up. set grid = DGCreateGrid(“My_Field”, rsHandle) DGGridAddField(“Name”, grid) DGGridAdDField(“PhoneNum”, grid) DGGridUpdate(grid)

Datagrip User Manual, Version 2.0

48

August 21, 1998

DGIsDAORegistered Description:

This function will check to see if the DAO engine is installed. If by using this function you discover that DAO is not installed then you will need to register the DAO engine before Datagrip will run.

Syntax:

DGIsDAORegistered()

Returns:

A 1 meaning that the engine is installed, a 0 otherwise.

See Also:

None.

Example:

The following example checks to see if the engine is registered. If not then a call to regsvr32 is made. if not DGIsDAORegistered() open “DAO350.DLL” with “C:\windows\system\regsvr32.exe” end if

Datagrip User Manual, Version 2.0

49

August 21, 1998

DGOpenDatabase Description:

Use this function to open a database. When you open a database a database handle is returned which you will use to reference this database in other Datagrip functions. You can open as many databases simultaneously as you need. Each open database has a unique handle.

Syntax:

DGOpenDatabase(dbName) DGOpendatabase(dbName, bExclusive, bReadOnly, connectionString) dbName: The name of the database to be opened. bExclusive: A boolean value specifying exclusive rights to the database. bReadOnly: A boolean that allows you to open the database in a read only mode. This is useful when reading the data off of a CDROM. connectionString: A string that allows you to pass additional parameters to the database. For instance a string that looks like “;PWD=mypassword” will allow you to open a password protected database. Simply substitute mypassword for the real password.

Returns:

If the function is successful, it returns a handle. If the request was not successful then the standard error string, #ERROR#, is returned.

See Also:

GetLastDGError

Example:

This example opens the first database using the simple form and then opens another database using the extended form. The extended form example will open the database in a read only mode. on StartMovie set dbHandle1 = DGOpenDatabase("C:\MyDB.mdb") set dbHandle2 = DGOpendatabase(“DB1.mdb”, 0, 1, “”) end

Datagrip User Manual, Version 2.0

50

August 21, 1998

DGQDClose Description:

Closes an open QueryDef object.

Syntax:

DGQDClose(qdHandle) qdHandle: A handle to an open QueryDef object that was created using DGCreateQueryDef.

Returns:

If the function was successful, it returns 1. If the function was not successful, the standard error string is returned.

See Also:

DGQDCreate, DGQDOpen

Example:

The example closes the QueryDef only if it is open. if DGQDIsOpen(qd) then DGQDClose(qd) end if

Datagrip User Manual, Version 2.0

51

August 21, 1998

DGQDCreate Description:

This function will create a new stored query in the database and return the handle if the function was successful.

Syntax:

DGQDCreate(queryName, sqlString, dbHandle) queryName: The name that will be given to the query when it is stored in the database. sqlString: The SQL string that you want associated with the new query. dbHandle: A handle to a Database object created.

Returns:

If the function is successful a handle to the QueryDef is returned. If the function fails, it returns the standard error string. You should call GetLastDGError() for a more detailed description of the error.

See Also:

None.

Example:

The example will create a QueryDef and store it in the database. set db = DGOpenDatabase("C:\MyDB.mdb") set qd = DGQDCreate("MSQuery", "SELECT * FROM Family", db) DGQDSetSQL("SELECT * FROM Family", qd)

Datagrip User Manual, Version 2.0

52

August 21, 1998

DGQDCreateRS Description:

Creates a Recordet object from a QueryDef object.

Syntax:

DGQDCreateRS(qdHandle) qdHandle: A handle to an open QueryDef object

Returns:

If the function was successful it will return a handle to a Recordset that can be used in all of the other recordset functions. If the functions fails the standard error string, #ERROR#, is returned.

See Also:

DGQDCreate, DGRS

Example:

The following example will take a QueryDef, which was stored in the database, create a recordset from it, and then check to see how many fields are in the recordset. DGQDOpen("MSQuery", qd) rs = DGQDCreateRS(qd) if rs <> "#ERROR#" then put DGRSGetFieldCount(rs) else put GetLastDGError() end if

Datagrip User Manual, Version 2.0

53

August 21, 1998

DGQDGetName Description:

Returns the name of a QueryDef, given the QueryDef handle.

Syntax:

DGQDGetName(qdHandle) qdHandle: A handle to an open QueryDef.

Returns:

If the function is successful the name of the QueryDef will be returned as a string. If the QueryDef has not been named, it is a new QueryDef and is called a temporary query. If this is the case you will get a #Temporary QueryDef# back. If the function fails it will return the standard error string, #ERROR#.

See Also:

DGQDCreate

Example:

The example will print the name of the opened QueryDef to the message window. put DGQDGetName(qd)

Datagrip User Manual, Version 2.0

54

August 21, 1998

DGQDGetParameterCount Description:

Gets the number of parameters that are defined in a query.

Syntax:

DGQDGetParameterCount(qdHandle) qdHandle: A handle to an open QueryDef object.

Returns:

If the function is successful it returns an integer which is the number of parameters defined in the QueryDef. If the function fails the standard error string is returned.

See Also:

DGQDGetParameterValue, DGQDSetParameterValue

Example:

The example prints out the number of parameters to the message window. on exitFrame global qd ret = DGQDGetParameterCount(qd) if ret <> "#ERROR#" then put ret else put GetLastDGError() end if end

Datagrip User Manual, Version 2.0

55

August 21, 1998

DGQDGetParameterValue Description:

Returns the value of a specific parameter that has been defined in a QueryDef.

Syntax:

DGQDGetParameterValue(paramName, qdHandle) paramName: The name of the parameter whose value is being retrieved. qdHandle: A handle to an open QueryDef object.

Returns:

If the function was successful it returns the value of the parameter, which could be a number or a string. If the function fails, the standard error string is returned.

See Also:

DGQDSetParameterValue, DGQDGetParameterCount, DGQDCreateRS, DGRSRequery.

Example:

The example below reads the value of a parameter called "ID" global qd pValue = DGQDGetParameterValue("ID", qd) put "Parameter value = " & pValue

Datagrip User Manual, Version 2.0

56

August 21, 1998

DGQDGetSQL Description:

Returns the SQL string that defines a QueryDef.

Syntax:

DGQDGetSQL(qdHandle) qdHandle: A handle to an open QueryDef.

Returns:

If the function is successful is returns an SQL string. If the function fails, it returns the standard error string.

See Also:

DGQDSetSQL, DGQDGetParameterValue, DGQDSetParameterValue

Example:

The example gets the SQL string that defines the QueryDef and prints it in the message window. global qd put DGQDGetSQL(qd)

Datagrip User Manual, Version 2.0

57

August 21, 1998

DGQDIsOpen Description:

Tests to see if the QueryDef is open or not.

Syntax:

DGQDIsOpen(qd) qdHandle: A handle to a QueryDef object.

Returns:

If the function is successful it returns true (1) or false (0). If the function fails, the standard error string is returned.

See Also:

DGQDCreate, DGQDClose

Example:

The following example checks to see if the query is open. If the query is open the query is closed. if DGQDIsOpen(qd) then DGQDClose(qd) end if

Datagrip User Manual, Version 2.0

58

August 21, 1998

DGQDOpen Description;

Opens a QueryDef object that has been defined previously in the database.

Syntax;

DGQDOpen(Queryname,qdHandle) Queryname: The name of the predefined query to open. qdHandle: A handle to a database object.

Returns:

If the function is successful, it returns a handle. If the function fails, the standard error string is returned.

See Also:

DGQDClose, DGQDIsOpen.

Example:

The example opens a predefined query. global db DGQDOpen("MSQuery", db)

Datagrip User Manual, Version 2.0

59

August 21, 1998

DGQDSetParameterValue Description:

Sets the value of a specific parameter in a QueryDef.

Syntax:

DGQDSetParameterValue(paramName, value, qdHandle) paramName: The name of the parameter to be set value: The value of the parameter qdHandle: A handle to a QueryDef object

Returns:

If the function is successful nothing is returned. If the function fails, it returns the standard error string, #ERROR#.

See Also:

DGQDGetParameterValue, DGQDGetParameterCount

Example:

The example changes the value of the ID parameter to 0. if DGQDSetParameterValue("ID", 0, qd) = #ERROR# then put GetLastDGError() end if

Datagrip User Manual, Version 2.0

60

August 21, 1998

DGQDSetSQL Description:

Sets the SQL string that defines a QueryDef object.

Syntax:

DGQDSetSQL(sqlString, qdHandle) sqlString: The SQL string. qdHandle: A handle to a QueryDef object

Returns:

If the function fails, it returns #ERROR#. Otherwise, it returns nothing.

See Also:

DGQDGetSQL.

Example:

This example changes the SQL string so that it now references all of the fields in the Person table. if DGQDSetSQL("SELECT * FROM Person", qdHandle) = #ERROR# then put GetLastDGError() end if

Datagrip User Manual, Version 2.0

61

August 21, 1998

DGRSAddNew Description:

Adds a new record to the recordset.

Syntax:

DGRSAddNew(rsHandle) rsHandle: A handle to an open recordset.

Returns:

If the function fails, it returns #ERROR#. Otherwise it returns nothing.

See Also:

DGRSUpdate, DGRSEdit

Example:

This example will add a new record to a recordset object whose handle is defined globally. global rsHandle DGRSAddNew(rsHandle)

Datagrip User Manual, Version 2.0

62

August 21, 1998

DGRSBindField Description:

Binds a cast member field directly to a field in the database. This is useful since anytime the recordset changes the field will automatically be updated. The developer doesn’t have to call DGRSGetFieldValue() and set the text, it is all done automatically. This function is only available in Director.

Syntax:

DGRSBindField(fieldName, dbField, rsHandle) fieldName: The name of the field in Director to bind to. dbField: The name of the field in the recordset to bind to. rsHanddle: A handle to a valid and open recordset.

Returns:

Upon success this function returns nothing. If the function fails then the standard error string is returned.

See Also:

DGRSBindVar.

Example:

The following code will bind a field on the stage named My_Field to the name field in the database. Once this is done anytime you call DGRSMoveNext() the field will automatically contain the new data. DGRSBindField(“My_Field”, “FirstName”, rsHandle)

Datagrip User Manual, Version 2.0

63

August 21, 1998

DGRSBindVar Description:

Use this function to tie a variable to a specific field in a recordset so that when changes are made to the recordset the variable is automatically updated. This is useful when stepping through records one at a time.

Syntax:

DGRSBindVar(varName, dbField, rsHandle) varName: The name of the variable that will be bound to the recordset. This should be in the form of a string. dbField: The name of the field in the recordset which will be bound. rsHandle: A handle to valid and open recordset.

Returns:

On success this function returns nothing except that the variable is now tied to the recordset. If the function fails then the standard error string will be returned.

See Also:

DGRSBindField.

Example:

The following example will tie a variable called MyVar to the ID field in the recordset. set MyVar = 0 DGRSBindVar(“MyVar”, “ID”, rs)

Datagrip User Manual, Version 2.0

64

August 21, 1998

DGRSCanAppend Description:

Determines if the recordset supports adding new records.

Syntax:

DGRSCanAppend(rsHandle) rsHande: A handle to a recordset object.

Returns:

If the recordset supports adding new records the function returns 1. If the recordset does not allow new records, the function returns 0. If the function fails (which would happen if rsHandle is not a valid handle to an open recordset), the function returns #ERROR#.

See Also:

DGRSAddNew

Example:

The example below checks to see if the database allows new records. If it does it will add a new record. if DGRSCanAppend(rs) = 1 then DGRSAddNew(rs) end if

Datagrip User Manual, Version 2.0

65

August 21, 1998

DGRSCancelUpdate Description:

Cancels any pending updates. When a call has been made to either DGRSAddNew or DGRSEdit and the call to DGRSUpdate has not yet been made, this function will cancel any changes made after the AddNew or Edit operation.

Syntax:

DGRSCancelUpdate(rsHandle) rsHande: A handle to a recordset object.

Returns:

This function returns the standard error string, #ERROR#, if an error occurs. Otherwise, it returns nothing.

See Also:

DGRSUpdate

Example:

This example makes a change to a recordset, and then cancels it. DGRSEdit(rs) DGRSSetFieldValue("Phone", "633-3333", rs) DGRSCancelUpdate(rs)

Datagrip User Manual, Version 2.0

66

August 21, 1998

DGRSCanScroll Description:

Checks to see if the current record can be moved (scrolled).

Syntax:

DGRSCanScroll(rsHandle) rsHande: A handle to a records object

Returns:

The function returns 1 if scrolling is allowed or 0 if not. If an error occurs the standard error string, #ERROR#, is returned.

See Also:

DGRSCanTransact

Example:

The following example determines if we can scroll through the Recordset. If we can, move the recordset forward 2 records. if DGRSCanScroll(rs) then DGRSMoveNext(rs) DGRSMoveNext(rs) end if

Datagrip User Manual, Version 2.0

67

August 21, 1998

DGRSCanTransact Description:

Determines whether the database supports transactions.

Syntax:

DGRSCanTransact(rsHandle) rsHande: A handle to a recordset object.

Returns:

The function returns one if the database supports transactions and zero if it does not. If an error occurs, the standard error string, #ERROR# is returned.

See Also:

DGRSCanScroll

Example:

This example checks to see if the database supports transactions. if DGRSCanTransact(rs) then put "Transactions supported"

Datagrip User Manual, Version 2.0

68

August 21, 1998

DGRSCanUpdate Description:

Determines whether the database is ready for updating.

Syntax:

DGRSCanUpdate(rsHandle) rsHande: A handle to a recordset object.

Returns:

This function returns one if the database is read for updating, and zero if not. If an error occurs, the standard error string, #ERROR# is returned.

See Also:

DGRSUpdate, DGRSEdit

Example:

The following example checks to see if the database is currently able to update. If it returns true, DGRSUpdate is called. if DGRSCanUpdate(rs) = 1 then DGRSUpdate(rs) end if

Datagrip User Manual, Version 2.0

69

August 21, 1998

DGRSClose Description:

Closes an open recordset object.

Syntax:

DGRSClose(rsHandle) rsHande: A handle to a recordset object.

Returns:

This function returns nothing unless an error occurs. If an error occurs, the function returns the standard error string, #ERROR#.

See Also:

DGCreateRecordset.

Example:

The following example checks to see if the recordset is open and then closes it. if DGRSIsOpen(rs) then DGRSClose(rs) end if

Datagrip User Manual, Version 2.0

70

August 21, 1998

DGRSDelete Description:

Deletes the current record in the recordset.

Syntax:

DGRSDelete(rsHandle) rsHande: A handle to a recordset object.

Returns:

This function returns nothing unless an error occurs. If an error occurs, the function returns the standard error string, #ERROR#.

See Also:

DGRSUpdate, DGRSEdit.

Example:

The following example deletes the current record. if DGRSDelete(rs) = "#ERROR#" then put "Error" end if

Datagrip User Manual, Version 2.0

71

August 21, 1998

DGRSEdit Description:

Prepares the current record for editing.

Syntax:

DGRSEdit(rsHandlei) rsHande: A handle to a recordset object.

Returns:

This function returns nothing unless an error occurs. If an error occurs, the function returns the standard error string, #ERROR#.

See Also:

DGRSUpdate, DGRSAddNew.

Example:

This example puts the string "Rob" in the "Name" field of the current record. DGRSEdit(rs) DGRSSetFieldValue("Name", "Rob", rs) DGRSUpdate(rs)

Datagrip User Manual, Version 2.0

72

August 21, 1998

DGRSFillList Description:

This function will build a list of all records of a specified field in the passed recordset. For instance if you have a database full of names you could call this function to fill a list variable with the entire database all in one call.

Syntax:

listVar = DGRSFillList(dbField, rsHandle) listVar: A variable that will hold the list when the function is finished. dbField: The name of the field in the recordset that contains the needed data. rsHandle: A handle to a valid and open recordset.

Returns:

If this function fails it will return the standard error string. If this function succeeds then a list containing the new data will be returned.

See Also:

DGRSFillField, DGCreateGrid.

Example:

The following example makes a list of each record from the first name field in the recordset. Before you can use a list you must initialize the variable. set MyList = [] set MyList = DGRSFillList(“FirstName”, rsHandle)

Datagrip User Manual, Version 2.0

73

August 21, 1998

DGRSFillString Description:

This function will allow you to automatically fill a string with the data from a specific field for an entire recordset. For instance if you wanted to display all of the names in your database in a field you would use this function. This function is not supported in Authorware.

Syntax:

string = DGRSFillString(dbField, rsHandle) string: The new string that is returned. dbField: The name of the field in the recordset that contains the data. rsHandle: A handle to a valid and open recordset.

Returns:

When the function fails the standard error string is returned. If the function is successful then a string is returned that contains all of the new data.

See Also:

DGRSBindField, DGRSCreateGrid.

Example:

The following example will automatically fill the field with all of the names in my recordset. set str = DGRSFillField(“FirstName”, rs) set the text of member “My_Field” = str

Datagrip User Manual, Version 2.0

74

August 21, 1998

DGRSFindFirst Description:

Finds the first instance of a record that matches a specified criteria. The criteria is specified by a string that is the WHERE clause of an SQL statement without the WHERE keyword.

Syntax:

DGRSFindFirst(string, rsHandle) string: The criteria string. This is the WHERE clause of an SQL statement without the keyword WHERE. rsHande: A handle to a recordset object.

Returns:

If a record is found that matches the criteria, a non-zero number is returned. If no instances were found, the function returns zero. If an error occurs, the function returns the standard error string, #ERROR#.

See Also:

DGRSFindLast, DGRSFindNext, DGRSFindPrev

Example:

This example finds the first record whose "ID" field is equal to 5. if DGRSFindFirst("ID = 5", rs) <> 0 then put "Found record ID = 5" end if

Datagrip User Manual, Version 2.0

75

August 21, 1998

DGRSFindLast Description:

Finds the last instance of a record that matches the specified criteria. The criteria is specified by a string that is the WHERE clause of an SQL statement without the WHERE keyword.

Syntax:

DGRSFindLast(string, rsHandle) string: The criteria string. This is the WHERE clause of an SQL statement without the keyword WHERE. rsHande: A handle to a recordset object.

Returns:

If a record is found that matches the criteria, a non-zero number is returned. If no instances were found, the function returns zero. If an error occurs, the function returns the standard error string, #ERROR#.

See Also:

DGRSFindFirst, DGRSFindNext, DGRSFindPrev

Example:

This example finds the last record whose "ID" field is equal to 5. if DGRSFindLast("ID = 5", rs) <> 0 then put "Found record ID = 5" end if

Datagrip User Manual, Version 2.0

76

August 21, 1998

DGRSFindNext Description:

Finds the next instance of a record that matches a specified criteria. The criteria is specified by a string that is the WHERE clause of an SQL statement without the WHERE keyword.

Syntax:

DGRSFindNext(string, rsHandle) string: The criteria string. This is the WHERE clause of an SQL statement without the keyword WHERE. rsHande: A handle to a recordset object.

Returns:

If a record is found that matches the criteria, a non-zero number is returned. If no instances were found, the function returns zero. If an error occurs, the function returns the standard error string, #ERROR#.

See Also:

DGRSFindLast, DGRSFindFirst, DGRSFindPrev

Example:

This example finds the first record whose age is equal to 5. if DGRSFindNext("ID = 5", rs) <> 0 then put "Found record ID = 5" end if

Datagrip User Manual, Version 2.0

77

August 21, 1998

DGRSFindPrev Description:

Finds the previous instance of a record that matches a specified criteria. The criteria is specified by a string that is the WHERE clause of an SQL statement without the WHERE keyword.

Syntax:

DGRSFindPrev(string, rsHandle) string: The criteria string. This is the WHERE clause of an SQL statement without the keyword WHERE. rsHande: A handle to a recordset object.

Returns:

If a record is found that matches the criteria, a non-zero number is returned. If no instances were found, the function returns zero. If an error occurs, the function returns the standard error string, #ERROR#.

See Also:

DGRSFindLast, DGRSFindNext, DGRSFindFirst.

Example:

This example finds the first record whose age is equal to 5. if DGRSFindNext("ID = 5", rs) <> 0 then put "Found record ID = 5" end if

Datagrip User Manual, Version 2.0

78

August 21, 1998

DGRSGetAbsolutePosition Description:

Determines the absolute position of the current record within the recordset.

Syntax:

DGRSGetAbsolutePosition(rsHandle) rsHande: A handle to a recordset object

Returns:

If the function is successful it returns an integer between 0 and the number of records in the recordset. This value is the absolute position of the current record in the recordset. If the function fails, the standard error string, #ERROR# is returned.

See Also:

DGRSSetAbsolutePosition

Example:

The following example gets the current absolute position and prints the number to the message window. put DGRSGetAbsolutePosition(rs)

Datagrip User Manual, Version 2.0

79

August 21, 1998

DGRSGetEditMode Description:

Gets the current edit state of the recordset.

Syntax:

DGRSGetEditMode(rsHandle) rsHande: A handle to a recordset object.

Returns:

This function returns an integer that indicates the current edit mode of the recordset. The return value will be one of the following: 0

No editing operation in progress

1

DGRSEdit has been called.

2

DGRSAddNew has been called.

If an error occurs, such as rsHandle is not a valid recordset handle, the function returns the standard error string, #ERROR#. See Also:

DGRSEdit, DGRSAddNew, DGRSUpdate

Example:

This example checks the edit mode. If DGRSAddNew or DGRSEdit have been called, it will call DGRSUpdate to save the new data. if DGRSGetEditMode(rs) > 0 then DGRSUpdate(rs) end if

Datagrip User Manual, Version 2.0

80

August 21, 1998

DGRSGetFieldCount Description:

Determines the number of fields in the current recordset. Note This function may take a significant amount of time to execute when using large databases. In order to return a value, the records in the database must be counted, which can be a time-consuming operation.

Syntax:

DGRSGetFieldCount(rsHandle) rsHande: A handle to a recordset object.

Returns:

If the function is successful then it returns an integer that specifies the number of fields in the current recordset. If the function fails, it returns the standard error string, #ERROR#.

See Also:

None.

Example:

This example determines the number of fields in the recordset and prints it out to the message window. put DGRSGetFieldCount(rs)

Datagrip User Manual, Version 2.0

81

August 21, 1998

DGRSGetFieldValue Description:

Returns the value of a specified field in a recordset.

Syntax:

DGRSGetFieldValue(fieldName, rsHandle)

Returns:

fieldName:

The name of the field to be read.

rsHandle:

A handle to an open recordset object.

If the function is successful it returns the data from the field named in fieldname. in the current recordset. The value that is returned can be an integer, a float, or string. When currency values are returned they do not include the dollar sign and the values are returned as floats. If an error occurs (other than overflow described above) in retrieving the data from the database, this function returns the standard error string, #ERROR#. Access also support hyperlinks. When a hyperlink is returned you get a string in this format "address#protocol://address#" This is an example of what the database would return: "www.inm.com#http://www.inm.com#"

Note Hyperlinks are only supported in Access version 8.0 or later databases.

See Also:

DGRSSetFieldValue, GetLastDGError

Example:

The example below prints the value of the field named "Address" in the current record to the message window. put DGRSGetFieldValue("Address", rsHandle)

Datagrip User Manual, Version 2.0

82

August 21, 1998

DGRSGetLockingMode Description:

Determines the current locking mode for a given recordset.

Syntax:

DGRSGetLockingMode(handle) handle: A handle to a valid and open recordset.

Returns:

Upon success, the mode is returned which will be a 1 or a 0. If an error occurs the standard error string will be returned.

See Also:

DGRSSetLockingMode, DGRSGetEditMode.

Example:

The following example checks to see if the recordset is using optimistic locking. If it is, set it to pessimistic which is safer. if DGRSGetLockingMode(rsHandle) = 0 then DGRSSetLockingMode(1, rsHandle) end if

Datagrip User Manual, Version 2.0

83

August 21, 1998

DGRSGetName Description:

Gets the name of the recordset.

Syntax:

DGRSGetName(rsHandle) rsHande: A handle to a recordset object.

Returns:

If successful it returns a string which is the name of the recordset. In some cases the Recordset may not have a name. In these cases the string #Temporary QueryDef# is returned. If the function fails, it returns the standard error string, #ERROR#.

See Also:

None.

Example:

Prints the name of the recordset to the message window. put DGRSGetName(rs)

Datagrip User Manual, Version 2.0

84

August 21, 1998

DGRSGetPercentPosition Description:

Gets the position of the current record and returns it as a percentage. Note This function may not return an accurate indication of the record position under some circumstances, because the number of records in a recordset is not known until all the records have been accessed. This function may take a significant amount of time to execute when using large databases. In order to return a value, the records in the database must be counted, which can be a time-consuming operation.

Syntax:

DGRSGetPercentPosition(rsHandlei) rsHande: A handle to a recordset object.

Returns:

This function returns a two-digit float value which represent the position of the current record in the recordset expressed as a percentage. If the function fails, it returns the standard error string, #ERROR#.

See Also:

DGRSGetAbsolutePosition

Example:

This example will determine if the current record is more than half way through the entire Recordset and will print a message to the message window. if DGRSGetPercentPosition(rs) > .50 then put "We are at least 50% the way through the records." end if

Datagrip User Manual, Version 2.0

85

August 21, 1998

DGRSGetRecordCount Description:

Gets the number of records in the current recordset. Note This function may not return an accurate count of records in many cases. The record count that is returned is the number of the last record that has been accessed. Unless the current record has been moved to the last record prior to calling this function, the record count may not be accurate. Call DGRSMoveLast before calling DGRSGetRecordCount to insure that the record count is accurate.

Syntax:

DGRSGetRecordCount(rsHandle) rsHande: A handle to a recordset object.

Returns:

This function returns the number of records in the recordset. If the function fails, it returns the standard error string, #ERROR#.

See Also:

None.

Example:

This code segment will determine if we have more than 10 people in our address book database. if DGRSGetRecordCount(rs) > 10 then put "We have more than 10 people." end if

Datagrip User Manual, Version 2.0

86

August 21, 1998

DGRSGetSQL Description:

Returns the SQL string that defines the recordset.

Syntax:

DGRSGetSQL(rsHandle) rsHandle: A handle to an open Recordset.

Returns:

This function returns a string that contains the SQL query that defines the recordset. If the function fails, it returns #ERROR#.

See Also:

DGQDGetParameterValue, DGQDSetParameterValue

Example:

The example gets the SQL string that defines the recordset and prints it to the message window. global qd put DGQDGetSQL(qd)

Datagrip User Manual, Version 2.0

87

August 21, 1998

DGRSImportResource Description:

Use this function to import bitmaps and wave files that are stored in the database.

Syntax:

DGRSImportResource(memberRef, dbField, rsHandle) memberRef: A member reference that will hold the new resource. dbField: The name of the field in the recordset that contains the resource you are importing. rsHandle: A handle to a valid and open recordset.

Returns:

If successful nothing is returned from this function but the specified cast member should now contain the imported data. When the function fails it will return the standard error string.

See Also:

None.

Example:

This example will import a wave file that has been stored in the wave field of a previously opened recordset. When the function is done the 10th cast member of the third cast will contain the new wave. DGRSImportResource(member 10 of castLib 3, “Wave”, rs)

Notes:

For this function to work you must have a valid OLE server program registered to open bitmaps and waves. Windows comes configured with Paintbrush and Sound Recorder registered which will work just fine. When you are inserting data in to the database ensure that the data is being inserted as a Wave or a Bitmap. If Package ever appears in the database field inside Access then you don’t have a valid OLE server registered. The easiest way to solve this problem is to reinstall Paintbrush and Sound Recorder. This is only required on the system that is inserting the resources. The end users machine and other development machines will be able to read the resources without a problem. To re-install Paintbrush follow these steps: 1. Open the control panel 2. Double click on ‘Add/Remove Program’ 3. Select Windows Setup 4. Scroll down and select Multimedia

Datagrip User Manual, Version 2.0

88

August 21, 1998

5. Click on details 6. Scroll down and un-check Paintbrush 7. Continue clicking ‘OK’ until you at the control panel. 8. Go back in to ‘Add/Remove Programs’ 9. Select Windows Setup 10. Scroll down to Multimedia 11. Click on details 12. Scroll down and check Paintbrush. 13. Click ‘OK’ until you are back to the control panel. At this point you should be able to successfully insert bitmaps in to the database. To do the same thing for wave files follow the same procedure except select Sound Recorder instead of Paintbrush in steps 6 and 12. When importing resources it is possible that Director is currently reading that data. This will be a problem if you are playing a sound and then try and replace the cast member with this function. To prevent this Datagrip will run in two modes. The first mode is the Safe Mode. This means that you cannot import a wave file while any of the sound channels are busy. The function will simply return an error. You can turn off Safe Mode with a call to DGSetAudioSafeMode(). When you override this function Datagrip no longer performs the check to see if a sound is currently being played. In this case you need to make sure that you don’t try and replace data that Director is using.

Datagrip User Manual, Version 2.0

89

August 21, 1998

DGRSIsBOF Description:

Determines if the current record is past the beginning of the recordset.

Syntax:

DGRSIsBOF(rsHandle) rsHandle: A handle to a recordset object.

Returns:

This function returns one (true) if the current record is before the beginning of the recordset, or zero (false) if the current record is in the recordset. If the function fails (e.g. rsHandle is not a valid recordset handle), it returns the standard error string, #ERROR#.

See Also:

DGRSIsEOF

Example:

The following example moves to the previous record if we are not past the beginning of the recordset. Because DGRSisBOF only returns true after the current record moves past the beginning of the recordset, you must move, test, and move back if DGRSIsBOF returns true. DGRSMovePrev(rsHandle) if not DGRSIsBOF(rsHandle) then DGRSMoveNext(rsHandle) end if

Datagrip User Manual, Version 2.0

90

August 21, 1998

DGRSIsEOF Description:

Determines if the current record is past the end of the recordset.

Syntax:

DGRSIsEOF(rsHandle) rsHandle: A handle to a recordset object.

Returns:

This function returns one (true) if the current record is after the end of the recordset, or zero (false) if the current record is in the recordset. If the function fails (e.g. rsHandle is not a valid recordset handle), it returns the standard error string, #ERROR#.

See Also:

DGRSIsBOF

Example:

The following example moves to the next record if we are not past the end of the recordset. Because DGRSisEOF only returns true after the current record moves past the end of the recordset, you must move, test, and move back if DGRSIsEOF returns true. DGRSMoveNext(rsHandle) if not DGRSIsEOF(rsHandle) then DGRSMovePrev(rsHandle) end if

Datagrip User Manual, Version 2.0

91

August 21, 1998

DGRSIsOpen Description:

Determines if the current recordset is open.

Syntax:

DGRSIsOpen(rsHandle) rsHandle: A handle to a recordset object.

Returns:

This function returns one if the recordset is open, or zero if it is not open. If an error occurs (e.g. rsHandle isn't a valid recordset handle), the function returns the standard error string, #ERROR#.

See Also:

DGRSClose

Example:

This example will check to see if the current recordset is open. If it is open, the recordset is closed. if DGRSIsOpen(rs) then DGRSClose(rs)

Datagrip User Manual, Version 2.0

92

August 21, 1998

DGRSMoveFirst Description:

Moves to the first record of a recordset.

Syntax:

DGRSMoveFirst(rsHandle) rsHandle: A handle to a recordset object.

Returns:

If the function is successful it returns nothing. Otherwise, it returns the standard error string, #ERROR#.

See Also:

DGRSMoveLast, DGRSMoveNext, DGRSMovePrev

Example:

This example will move to the first record if we are past the end of the recordset. This would be convenient way to "wrap around" to the beginning of the records when you've reached the end of the recordset. if DGRSIsEOF(rs) then DGRSMoveFirst(rs)

Datagrip User Manual, Version 2.0

93

August 21, 1998

DGRSMove Description:

Moves forward or backwards a specified number of records.

Syntax:

DGRSMove(index, rsHandle) index: The number of records to move. A positive value will move forward and a negative value will move backwards. rsHandle: A handle to a recordset object.

Returns:

If the function is successful, nothing is returned. Otherwise, the standard string is returned, #ERROR#. Note that specifiying a number of records to move the current record past the end or beginning of the recordset does not generate an error. You can, however, test for this condition using DGRSIsEOF and DGRSIsBOF.

See Also:

DGRSMoveFirst, DGRSMoveLast, DGRSMoveNext, DGRSMovePrev, DGRSIsEOF, DGRSIsBOF

Example:

The example below will move forward by two records. DGRSMove(2, rs) if DGRSIsEOF then DGRsMovePrev(rs) end if

Datagrip User Manual, Version 2.0

94

August 21, 1998

DGRSMoveLast Description:

Moves to the last record of a recordset.

Syntax:

DGRSMoveLast(rsHandle) rsHandle: A handle to a recordset object.

Returns:

If the function is successful it returns nothing. Otherwise, it returns the standard error string, #ERROR#.

See Also:

DGRSMoveFirst, DGRSMoveNext, DGRSMovePrev

Example:

This example will move to the last record if we are past the beginning of the recordset. This would be convenient way to "wrap around" to the end of the records when you've reached the beginning of the recordset. if DGRSIsBOF(rs) then DGRSMoveLast(rs)

Datagrip User Manual, Version 2.0

95

August 21, 1998

DGRSMoveNext Description:

Moves to the next record of a recordset.

Syntax:

DGRSMoveNext(rsHandle) rsHandle: A handle to a recordset object.

Returns:

If the function is successful, nothing is returned. Otherwise, the standard error string, #ERROR# is returned.

See Also:

DGRSMoveFirst, DGRSMoveLast, DGRSMovePrev, DGRSIsEOF

Example:

The following example moves to the next record if we are not past the end of the recordset. Because DGRSIsEOF only returns true after the current record moves past the end of the recordset, you must move, test, and move back if DGRSIsEOF returns true. DGRSMoveNext(rsHandle) if not DGRSIsEOF(rsHandle) then DGRSMovePrev(rsHandle) end if

Datagrip User Manual, Version 2.0

96

August 21, 1998

DGRSMovePrev Description:

Moves to the previous record of a recordset.

Syntax:

DGRSMovePrev(rsHandle) rsHandle: A handle to a recordset object.

Returns:

If the function is successful, nothing is returned. Otherwise, the standard error string, #ERROR# is returned.

See Also:

DGRSMoveFirst, DGRSMoveNext, DGRSMoveLast, DSRSIsBOF

Example:

The following example moves to the previous record if we are not past the beginning of the recordset. Because DGRSIsBOF only returns true after the current record moves past the beginning of the recordset, you must move, test, and move back if DGRSIsBOF returns true. DGRSMovePrev(rsHandle) if not DGRSIsBOF(rsHandle) then DGRSMoveNext(rsHandle) end if

Datagrip User Manual, Version 2.0

97

August 21, 1998

DGRSReQuery Description:

Refreshes a recordset by running the query again.

Syntax:

DGRSReQuery(rsHandle) rsHandle: This is a handle to a recordset.

Returns:

If successful, the function will returns one, otherwise the standard error string, #ERROR#, is returned.

See Also:

DGQDCreateRS, DGQDSetParameterValue.

Example:

This example will open a querydef, set a parameter and use the resulting recordset to get a phone number from the database. Once we have the first number the parameter in the querydef is changed and we requery our original recordset to get the new data. global rsHandle set qdHandle = DGQDOpen(“PhoneQuery”, dbHandle) DGQDSetParameterValue(“ContactID”, CurContact, >> qdHandle) set rsHandle = DGQDCreateRS(qdHandle) set myNum = DGRSGetFieldValue(“PhoneNumber”, rsHandle) DGQDSetParameterValue(“ContactID”, CurContact + 1, >> qdHandle) DGRSRequery(rsHandle) Set myNum2 = DGRSGetFieldValue(“PhoneNumber”, rsHandle)

Datagrip User Manual, Version 2.0

98

August 21, 1998

DGRSSetAbsolutePosition Description:

Sets the current position of the recordset according to an absolute value.

Syntax:

DGRSSetAbsolutePosition(value, rsHandle) value: The record number to move to rsHandle: This is a handle to a recordset.

Returns:

If the function is successful, nothing is returned. Otherwise, the standard error string, #ERROR# is returned. Note that moving to a record number that is beyond the end of the recordset does not return an error. However, you can test for this condition using DGRSIsEOF.

See Also:

DGRSGetAbsolutePosition.

Example:

This example gets a position, move the record and then returns to it. val = DGRSGetAbsolutePosition(rs) DGRSMoveFirst(rs) DGRSSetAbsolutePosition(val, rs)

Datagrip User Manual, Version 2.0

99

August 21, 1998

DGRSSetFieldValue Description:

Use this function to set the value of a specified field in a recordset.

Syntax:

DGRSSetFieldValue (fieldName, value, rsHandle) fieldName: The name of the field to be set in the current record. value: The new value to be stored in the field named fieldname. rsHandle: The recordset handle

Returns:

If the function is successful, nothing is returned. Otherwise, the standard error string, #ERROR# is returned.

See Also:

GetLastDGError, DGRSGetFieldValue

Example:

The example below changes the value of the field named "Address" in the database MyDb.mdb. First it opens the database then creates a recordset and finally sets the value "123 Mystreet" set db = DGOpenDatabase("C:\MyDB.mdb") set rs = DGCreateRecordset("SELECT * FROM People", db) return DGRSSetFieldValue("Address", "123 Mystreet", rs)

Datagrip User Manual, Version 2.0

100

August 21, 1998

DGRSSetLockingMode Description:

This function will allow you to switch back and forth between optimistic and pessimistic recordset locking. Locking is a concern when you are dealing in a multi-user environment since you want to prevent two users from editing the same record at the same time. If you use pessimistic locking then only one user will be able to edit a record at a time. In fact what happens is the engine actually locks a 2K segment of data that no one can edit until the first user is done. It is important to remember that a record is usually less than 2K which means you could actually be locking more than one record at a time. Your other option is optimistic locking which allows multiple users to edit the same data at the same time but when they try and save the data with the update command an error will be returned. Remember that pessimistic locking is the default mode. Benefits: Pessimistic locking: Ensures that only one person will be editing the data at any given time. Optimistic locking: Only has the record locked for a very short period of time when the user is saving the data. Disadvantages: Pessimistic locking: Locks the recordset for a long period of time. In fact the user could initiate an edit command and then leave the keyboard which would mean the recordset would be locked for a very long period of time. Optimistic locking: Possibility that multiple users will be able to edit the same data at the same time. Which means when they save their data an error will occur.

Syntax:

DGRSSetLockingMode(mode, handle) mode: 1 is pessimistic and a 0 is optimistic. handle: A handle to a valid and open recordset object.

Returns:

Upon success nothing is returned but when an error occurs the standard Datagrip error string will be sent back.

See Also:

DGRSGetEditMode, DGRSGetLockingMode.

Datagrip User Manual, Version 2.0

101

August 21, 1998

Example:

The following example will turn pessimistic locking off so that we can prevent users from locking a recordset for to long. global rsHandle DGRSSetLockingMode(0, rsHandle)

Datagrip User Manual, Version 2.0

102

August 21, 1998

DGRSUnbindField Description:

This function breaks the connection between a bound field and the database. Once this function is called the field will no longer be automatically updated.

Syntax:

DGRSUnbindField(fieldName, rsHandle) fieldName: The name of the cast member to break the connection with. rsHandle: A handle to the recordset that the field was bound to.

Returns:

If this function was unsuccessful then the standard error string will be returned. Otherwise nothing will be returned.

See Also;

DGRSBindField, DGRSBindVar, DGRSUnBindVar.

Example:

The following example will break a previously made connection between the cast member “My_Field” and the datbase. global rsHandle DGRSUnbindField(“My_Field”, rsHandle)

Datagrip User Manual, Version 2.0

103

August 21, 1998

DGRSUnbindVar Description:

This function will break the automatic data transfer connection between the database and a variable.

Syntax:

DGRSUnbindVar(varName, rsHandle) varName: The name of the variable to break the connection with. This should be in the form of a string. rsHandle: The handle to the recordset to which the variable was bound.

Returns:

If this function succeeds nothing is returned. If this function fails then the standard error string is returned.

See Also:

DGRSBindVar, DGRSUnBindField, DGRSBindField.

Example:

The following example assumes that the recordset is already tied to my ID variable. Here we are just breaking the connection. global rsHandle DGRSUnbindVar(“ID”, rsHandle)

Datagrip User Manual, Version 2.0

104

August 21, 1998

DGRSUpdate Description:

Saves changes made to a recordset to the database. This function should only called after DGRSAddNew or DGRSEdit have been called.

Syntax:

DGRSUpdate(rsHandle) rsHandle: This is a handle to a recordset.

Returns:

If the function is successful, nothing is returned. Otherwise, the standard error string, #ERROR# is returned

See Also:

DGRSEdit, DGRSAddNew

Example:

The following example adds a new record to the recordset and then calls update to save it in the database. DGRSAddNew(rs) DGRSSetFieldValue("Name", "Rob", rs) DGRSUpdate(rs)

Datagrip User Manual, Version 2.0

105

August 21, 1998

DGGetRegValue Description:

Retrieves a value from the system registry.

Syntax:

DGGetRegValue(HKEY, path, key) HKEY: The HKEY to open. This can be one of the following: HKEY_LOCAL_MACHINE HKEY_CURRENT_USER HKEY_CLASSES_ROOT HKEY_USERS HKEY_CURRENT_CONFIG.

path: The path to the key that you are retrieving. key: A key name to lookup. Returns:

If an error occurred the standard error string, #ERROR#, will be returned. If the function was successful nothing will be returned.

See Also:

DGSetRegVal.

Example:

This example will get the value of the key HKEY_LOCAL_MACHINE\SOFTWARE\INM\Datagrip\Path put DGGetRegVal("HKEY_LOCAL_MACHINE", "SOFTWARE\INM\Datagrip", "Path")

Datagrip User Manual, Version 2.0

106

August 21, 1998

GetLastDGError Description:

Returns a descriptive string explaining the last error that occurred.

Syntax:

GetLastDGError()

Returns:

A string that gives more detail on the most recent error.

Datagrip User Manual, Version 2.0

107

August 21, 1998

DGSetAudioSafeMode Description:

This function sets the Safe Mode state of Datagrip. If Safe Mode is on then you won’t be able to import waves while a sound channel is in use. When Safe Mode is turned off you can import waves at any time but you must be careful not to overwrite any data that Director is currently reading.

Syntax:

DGSetAudioSafeMode(state) state: A 1 if you want Safe Mode on, a 0 otherwise.

Returns:

Nothing is returned.

See Also:

DGImportResource.

Example:

The following example turns Safe Mode on. DGSetAudioSafeMode(1)

Datagrip User Manual, Version 2.0

108

August 21, 1998

DGSetRegVal Description:

Sets the value of a key in the registry.

Syntax:

DGSetRegVal(HKEY, path, key, value) HKEY: The HKEY that you are opening. HKEY can be one of the following: HKEY_LOCAL_MACHINE HKEY_CURRENT_USER HKEY_CLASSES_ROOT HKEY_USERS HKEY_CURRENT_CONFIG.

path: The path to the key you are updating. key: The specific key you are creating or updating. If this value is set to an empty string ("") you will be setting the value of the default setting in the folder specified in path. value: This can be an integer or a text string. This is the value that the key will be set to. Returns:

An integer or a string will be returned if the function was successful. If an error occurred then the standard error string, #ERROR#, will be returned.

See Also:

DGGetRegVal.

Example:

The following example will create a new folder and new key just as you might do for your company when releasing a product. DGSetRegVal("HKEY_LOCAL_MACHINE", "SOFTWARE\INM", "", "") DGSetRegVal("HKEY_LOCAL_MACHINE", "SOFTWARE\INM", "Path", "C:\SSS")

Datagrip User Manual, Version 2.0

109

August 21, 1998

Chapter 6 Distributing your Datagrip Applications Credits Requirements When you distribute an application that uses Datagrip your license agreement requires that you display a credit line for Datagrip. The credit line should say “Database connectivity provided by Datagrip”. It must be readable, in a font of at least 10 points, and must be visible for at least four seconds. The ideal place to place this credit line is on the credits page required by Macromedia with the Director/Authorware logo. If your application will not be distributed, you are not required to include this credit line. An example of a presentation that doesn't require a credit line is a business presentation that is only used by a single presenter. Distributing Datagrip Parts of Datagrip are copyrighted by Microsoft. If you intend to distribute a Datagrip application, you must own one of the following Microsoft products in order to have the right to distribute the Microsoft DLL files that support Datagrip. Typically, you'll need Microsoft Access to create and maintain databases anyway, so this requirement shouldn't be a problem. You must own one of the following products in order to distribute Datagrip: •

Microsoft Access

•

Microsoft Visual Basic

•

Microsoft Visual C++

Datagrip can only be used in the Director/Authoware design environment on the licensed user's computer. When you distribute Datagrip with an application you create, the users who receive your application cannot use Datagrip in the Director/Authorware design environment.

Datagrip User Manual, Version 2.0

110

August 21, 1998

Datagrip Files When you distribute Datagrip you will need to ship some of the run time files along with the Xtra. You, the developer, have two options as to how you wish to handle this. The first option which is what most people will do is to copy each of the listed files to their appropriate locations on the user's hard drive. The second option is to place each of the files in the same directory as the EXE that will run your application If you choose the first option and decide to place the files on the users hard drive you will want to remember that the names of these directories may change for international versions of windows. There are instructions below to help you determine the correct folder placement. If you choose the second option which is to leave the files on the CD you will want to be sure and check to see if the files already exist on the users system. An easy way to determine this is to make a call to DGIsDAORegistered which will check and see if the necessary components are there. When the files don’t exist on the users system you will need to register them on the CD. But if they do exist you will never want to register them on the CD since it will break other applications. Also, remember to unregister the file once you are done if you registered it in the beginning! Location

File Name

Folder named "Xtras" next to your Director projector or Authorware packaged piece

DGREL.X32

Windows System Folder

MSVCRT20.DLL VBAR332.DLL MSJET35.DLL MSVCRT40.DLL MSVCRT.DLL MSVCIRT.DLL MSJINT35.DLL MSJTER35.DLL MSRD2X35.DLL (needs to be registered) VBAJET32.DLL

Program Files\Common Files\Microsoft Shared\DAO Folder

DAO350.DLL (needs to be registered) DAO2535.TLB Note

For international installations the ‘Program Files\Common Files’ directory may be named something different. To determine the location of this directory you can look in the user’s registry. In the key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\CommonFilesDir

Datagrip User Manual, Version 2.0

111

August 21, 1998

There are two DLL’s that are listed as needing to be registered. This means that windows will need to know some additional information about the DLL before it can be used. Registering a DLL is very easy to do. The first and by far the best choice is to use an installer package that will automatically handle the registration for you. If you don’t know how to turn on registration in your installer search the help system for “Self Registration”, “Active-X”, or “OCX”. Occasionally it is not feasible to put an installer on the front of your application. In this case you can use a program called REGSVR32.EXE. This program is located in the Windows system directory. To use it you execute it and on the command line pass the path to the file you are trying to register. This program comes with a variety of calling options. To see these options call REGSVR32.EXE without anything on the command line.

Datagrip User Manual, Version 2.0

112

August 21, 1998

Appendix A: Glossary Current record The record within a recordset that will be read from, written to, or deleted. Many Datagrip functions move the current record. For example, DGMoveNext moves the current record to the next record in the recordset. Field Columns in a database table are called fields. Fields have a name that you use to refer to them. For example, DGRSGetFieldValue gets the value of a field in the current record whose name you supply. When you create a recordset, the field names in the recordset are generally the same as the field names in the original database table. Handle A handle is a number that you use to refer to a Datagrip object, such as a database, recordset, or querydef. The Datagrip Open or Create functions typically return a handle that you’ll store in a variable and use until you close the object. Lingo Macromedia Director’s scripting language is called Lingo. More recently, both Director’s and Authorware’s scripting languages are referred to as the “Macromeda Scripting Language”. This manual assumes some familiarity with the scripting language. See the More Reading section for information on learning and using the Macromedia Scripting Language. Path A Path is the set of all the directories (sometimes called “folders”) that you need to open to get to a particular file. Query A Query is a statement that defines which records (rows) you want to retrieve from a database. Queries are written using the Structured Query Language (SQL). Querydef A Querydef is a query that is stored in a database file. Querydefs are more efficient than queries you enter as strings in your code because the database can examine the query in advance and prepare it for use. The Datagrip functions for using QueryDefs all start with DGQD (Datagrip QueryDef). Record A record is one row in a database table or the recordset you create from the database table using a query.

Datagrip User Manual, Version 2.0

114

August 21, 1998

Recordset A recordset, as the term implies, is a set of records. The set of records is typically created by executing a query, either using the Datagrip DGCreateRecordset function or the DGQDCreateRS function. Relational database A relational database is a database that allows you to create multiple tables and make relationships between the tables based on certain fields. Microsoft Access databases are relational. We used this feature in the tutorial by creating two tables and relating them using the ContactID field. Stored query See querydef SQL Structured Query Language. The SQL language defines a standard way to write queries to retrieve data and perform other actions on a database. Datagrip uses a Microsoft implementation of SQL, which is identical in most respects to the standard SQL. For more information on the differences, refer to the Microsoft Access documentation.

Datagrip User Manual, Version 2.0

115

August 21, 1998

Appendix B: More Reading Here are some other books that may be useful for understanding database concepts, SQL, and the Macromedia Scripting Language. 1. Understanding SQL, Martin Gruber , Sybex 2. Using Lingo Macromedia Director product documentation 3. Jet Database Engine Programmer's Guide, Dan Haught and Jim Ferguson, Microsoft Press, 1995 4. Microsoft Access Help File provided with the Access software

Datagrip User Manual, Version 2.0

116

August 21, 1998

Appendix C: Example Director Code Startup Scripts

----------------------------------------- StartMovie --- this is called when the projector is -- initially launched ---------------------------------------on startMovie InitGlobals InitDB end ----------------------------------------- InitGlobals --- This function will initialize and set -- all of the global variables.It should -- be called once at startup ---------------------------------------on InitGlobals global DBHandle --A handle to a database global RSHandle --A handle to a recordset global QDHandle --A handle to a querydef global picCastMem --The member to hold the pics global waveCastMem --The member that will hold the wave file ---Initially set these handles to 0 since --any handle that is returned will be --greater than 0 -set DBHandle = 0 set RSHandle = 0 set QDHandle = 0 set picCastMem = 64 set waveCastMem = 65 end -----------------------------------------InitDB --- This function will initially open the -- database and create a recordset that -- will allow us to read and edit all of -- the contacts in our database ---------------------------------------on InitDB global dbHandle global rsHandle global qdHandle ---Open the contacts database assuming --that it exists in the local directory. --If it doesn't we will get an error back --that we can use to communicate with the user -set dbHandle = DGOpenDatabase(the moviePath & "Tutor.mdb") if dbHandle = "#ERROR#" then alert "Error: " & GetLastDGError()

Datagrip User Manual, Version 2.0

117

August 21, 1998

alert "Now exiting the program." ClearDGError() quit end if ---This SQL string will define our --recordset. It essentially means --Select everything that exists --inside the Contacts table. -set sqlString = "SELECT * FROM Contacts" ---Open a recordset using our above created --SQL string and our database handle -set rsHandle = DGCreateRecordset(sqlString, dbHandle) if rsHandle = "#ERROR#" then alert "Error: " & GetLastDGError() alert "Now exiting the program." quit end if ---Link each field to its appropriate Database field -DGRSBindField("FirstName_Field", "FirstName", rsHandle) DGRSBindField("LastName_Field", "LastName", rsHandle) DGRSBindField("Company_Field", "Company", rsHandle) DGRSBindField("Title_Field", "Title", rsHandle) ---Open a stored query that will retrieve all of --the phone numbers for us -set qdHandle = DGQDOpen("PhoneQuery", dbHandle) if qdHandle = "#ERROR#" then alert "Error: " & GetLastDGError() alert "Now exiting the program." quit end if end

Button Scripts

--NEXT BUTTON SCRIPT on mouseUp GoToNextRecord() end --PREV BUTTON SCRIPT on mouseUp GoToPrevRecord() End --ADD BUTTON SCRIPT on mouseUp AddNumberToDB() set the text of field "tmpDesc_Field" = "" set the text of field "tmpNum_field" = "" FillPhoneNumFields() End --NEW RECORD BUTTON

Datagrip User Manual, Version 2.0

118

August 21, 1998

on mouseUp CreateNewRecord() ClearFields() End --DIAL BUTTON SCRIPT on mouseUp global rsHandle global waveCastMem ---Pull our wave file out of the database -DGRSImportResource(member waveCastMem, "Wave", rsHandle) puppetSOund member waveCastMem end

Frame Scripts

--GO TO THE FRAME on exitFrame go to the frame end

Interface Scripts

-----------------------------------------ClearFields --- Description: -Clears all of the fields on the stage ----------------------------------------on ClearFields global picCastMem set set set set set set

the the the the the the

text text text text text text

of of of of of of

member member member member member member

"FirstName_Field" = "" "LastName_Field" = "" "Address_Field" = "" "Title_Field" = "" "Company_Field" = "" "PhoneNum_Field" = ""

---picCastMem holds the member number where the --pictures are stored. When we create a --new entry we will want to erase this --since there won't be a pic in the DB -erase member picCastMem end -------------------------------------------SaveCurrentData --- Description: -Runs through each of the fields on the -stage and calls a save function which -takes the text and calls the save function -in the database. ------------------------------------------on SaveCurrentData global rsHandle global dbhandle

Datagrip User Manual, Version 2.0

119

August 21, 1998

---Put the database in edit mode so we --can change the data -if DGRSGetEditMode(rsHandle) = 0 then put DGRSEdit(rsHandle) end if ---Save all of the data -SaveDBField("FirstName") SaveDBField("LastName") SaveDBField("Address") SaveDBField("Title") SaveDBField("Company") ---Call the final save for the rest of --the data -DGRSUpdate(rsHandle) end

Database Scripts

-----------------------------------SetFieldVal --- params: -fieldName: the name of the field on the stage -dbField : the field in the DB --- Returns: -The value in the field. If an error -occurs nothing will be returned but -an error dialog will be displayed --- Notes: -This function uses a global variable as -the recordset handle. ---------------------------------on SetFieldVal fieldName, dbField global rsHandle set val = DGRSGetFieldValue(dbFIeld, if val = "#ERROR#" then alert "Error while retrieving from ClearDGError() set the text of member fieldName = else set the text of member fieldName = end if end

rsHandle) " & fieldName & RETURN & GetLastDGError() "" val

------------------------------------GoToNextRecord --- Description: -Moves to the next record in the -current recordset. ----------------------------------on GoToNextRecord global rsHandle global qdHandle global picCastMem --

Datagrip User Manual, Version 2.0

120

August 21, 1998

--Check to see if the user has --created a new record without --saving. If this is the case --save it and go on. -if DGRSGetEditMode(rsHandle) = 2 then SaveCurrentData() DGRSUpdate(rsHandle) end if ---Make sure we don't go past --the end of the file -DGRSMoveNext(rsHandle) if DGRSIsEof(rsHandle) then DGRSMovePrev(rsHandle) return end if ---There is one field in our user interface --that we have decided to handle manually. --This is for no other reason that to provide --an example of another data retrieval method. -set txt = DGRSGetFieldValue("Address", rsHandle) if txt <> "#ERROR#" then set the text of member "Address_Field" = txt end if ---The following will erase the last member --make it in visible, replace it, and turn --it back on. Usually this is not needed. --If you just replace the cast member with --a simple call to DGImportResource() it all --happens extremely fast! -erase member picCastMem set the visible of sprite 2 = 0 DGRSImportResource(member picCastMem, "Photo", rsHandle) set the visible of sprite 2 = 1 FillPhoneNumFields end ----------------------------------GoToPrevRecord --- Description: -Moves to the previous record -in the current recordset --------------------------------on GoToPrevRecord global rsHandle global qdHandle global picCastMem ---Check to see if the user has --created a new record without --saving. If this is the case --save it and go on. --

Datagrip User Manual, Version 2.0

121

August 21, 1998

if DGRSGetEditMode(rsHandle) = 2 then SaveCurrentData() DGRSUpdate(rsHandle) end if ---Make sure we don't go passed --the begining of the file -DGRSMovePrev(rsHandle) if DGRSIsBOF(rsHandle) then DGRSMoveNext(rsHandle) return end if ---There is one field in our user interface --that we have decided to handle manually. --This is for no other reason that to provide --an example of another data retrieval method. -set txt = DGRSGetFieldValue("Address", rsHandle) if txt <> "#ERROR#" then set the text of member "Address_Field" = txt end if ---The following will erase the last member --make it in visible, replace it, and turn --it back on. Usually this is not needed. --If you just replace the cast member with --a simple call to DGImportResource() it all --happens extremely fast! -erase member picCastMem set the visible of sprite 2 = 0 DGRSImportResource(member picCastMem, "Photo", rsHandle) set the visible of sprite 2 = 1 FillPhoneNumFields end ------------------------------------CreateNewRecord --- Description: -Creates a new record inside the -database that is ready to be -filled with data. ---------------------------------on CreateNewRecord global rsHandle ---Check to see if there is an --unsaved record. If so save it -if DGRSGetEditMode(rsHandle)=2 then SaveCurrentData() put DGRSUpdate(rsHandle) end if ---Once you create a new record and --append it to the database the engine

Datagrip User Manual, Version 2.0

122

August 21, 1998

--moves the current record to the record --that was being displayed before the --addnew call was made. This will mess --with our next/prev scheme so we need --to be sure that we are at the last --record before we create a new one -DGRSMoveLast(rsHandle) DGRSAddNew(rsHandle) end -----------------------------------SaveDBField --- params: -fieldName: -the name of the field on -the stage. --- Description: -This will pull all of the data -from the fields on the stage -and save it in the corresponing -database field ----------------------------------on SaveDBField fieldName global rsHandle ---Append a "_Field" to the member name, get its text --and then save it to the database. -put "SetFieldValue Result: " && DGRSSetFieldValue(FieldName , the text of member (fieldName & "_Field"), rsHandle) end ---------------------------------------------AddNumberToDB() --- Description: -Takes the number and description from -the temp fields and records them -in the database. -------------------------------------------on AddNumberToDB global rsHandle global dbHandle ---Check to see if the user has --created a new record without --saving. If this is the case --save it and go on. -if DGRSGetEditMode(rsHandle) = 2 then SaveCurrentData() DGRSUpdate(rsHandle) DGRSMoveLast(rsHandle) end if ---Get our data -set descStr = the text of member "tmpDesc_Field" set NumStr = the text of member "tmpNum_Field"

Datagrip User Manual, Version 2.0

123

August 21, 1998

--- Do a check to make sure there is data in -- atleast one of the fields. -if the number of words in descStr =0 and the number of words in numStr return end if

=0 then

---Get the associated contact id and enter it in the new records -set id = DGRSGetFieldValue("ContactID", rsHandle) if id <> "#ERROR#" then set tmpRS = DGCreateRecordset("SELECT * FROM PhoneNumbers Where ContactID =" && id, dbHandle) DGRSAddNew(tmpRS) DGRSSetFieldValue("ContactID", id, tmpRS) DGRSSetFieldValue("Description", descStr, tmpRS) DGRSSetFieldValue("PhoneNumber", numStr, tmpRS) DGRSUpdate(tmpRS) DGRSClose(tmpRS) end if end ---------------------------------------------FillPhoneNumFields() --- Description: -Creates a grid in the phone number box --------------------------------------------on FillPhoneNumFields global rsHandle global qdHandle --- First, get the value of the contact ID from the current -- record of the contact recordset -set val = DGRSGetFieldValue("ContactID", rsHandle) if val <> "#ERROR#" then --- Set the parameter "pContactID" in the phone queryDef to -- the contact ID and open the recordset. -DGQDSetParameterValue("pContactID", val, qdHandle) set tmpRS = DGQDCreateRS(qdHandle) if tmpRS <> "#ERROR#" then ---Create a grid and add the needed fields -set gridHandle = DGCreateGrid("PhoneNum_Field", tmpRS) DGGridAddField("Description", 13, gridHandle) DGGridAddField("PhoneNumber", 14, gridHandle) ---Tell the grid to fill in all of the data -DGGridUpdate(gridHandle) end if end if end

Datagrip User Manual, Version 2.0

124

August 21, 1998

Appendix D: Example Authorware Code Startup Scripts Initialize Arrays -----------------

Datagrip Demo V 1.0 This Data Grip Demo was Programmed by Don Cowper at TransTech Interactive Training Inc "After spending weeks fighting with ODBC, what a pleasure to find a Access database conductivity product that really works with Authorware. DataGrip has good error messaging, all the functions you could require and blazing speed. I wrote this PIM demo in a single day!" --Don Cowper Special Note: If you want to use a string to query the database enclose it in single quotes inside a set of double quotes. eg FindLastName := "'Jones'" -- single quotes within double quotes Array PhoneNum holds the description in field 1 and the number in field 2

PhoneNum := Array("",10,2)

Open Database -- LastErrorTxt is shown across the bottom of the screen when it has a message in it. dbHandle := DGOpenDatabase("Tutor.mdb") if dbHandle = "#ERROR#" then LastErrorTxt := "Error in icon: Open Database: "^GetLastDGError() ClearDGError() end if

Get Record Set & Phone Set Handles ---Create a recordset containing all of the contacts and sort them by last name -QueryStr := "Select * from Contacts ORDER BY Contacts.LastName" rsHandle := DGCreateRecordset(QueryStr, dbHandle) if rsHandle = "#ERROR#" then LastErrorTxt := LastErrorTxt^"\rError in icon Get Record Set ...: "^GetLastDGError() ClearDGError() end if qdHandle := DGQDOpen("PhoneQuery", dbHandle) if qdHandle = "#ERROR#" then LastErrorTxt := LastErrorTxt^"\rError opening stored Query...:"^GetLastDGError() ClearDGError() end if ---Bind the main fields to the database so that we don't have to --update the fields every time the data changes. -DGRSBindVar("FirstNameStr", "FirstName", rsHandle) DGRSBindVar("LastNameStr", "LastName", rsHandle) DGRSBindVar("TitleStr", "Title", rsHandle) DGRSBindVar("CompanyStr", "Company", rsHandle) ---Notice in the above list of bound fields we did not --include the Address field. This is for no other reason --than that we have decided to demonstrate the manual --approach to retrieving data. This next call will fill in the data --for the first record. Subsequent records will be updated --in the Next and Prev icons.

Datagrip User Manual, Version 2.0

125

August 21, 1998

-AddressStr := DGRSGetFieldValue("Address", rsHandle)

Retrieve First Record in Database DGRSMoveLast(rsHandle) HowManyEntries := DGRSGetRecordCount(rsHandle) DGRSMoveFirst(rsHandle) NoCurrentEntry := 1

Subroutines Initialize Phone # & Fields --Bank Previous Telephone Array repeat with J := 1 to 10 PhoneNum[J,1] := PhoneNum[J,2] := "" end repeat FirstNameStr := LastNameStr := AddressStr := TitleStr := CompanyStr := ""

Get Form Fields if DGRSGetRecordCount(rsHandle) > 0 then FirstNameStr := DGRSGetFieldValue("FirstName",rsHandle) LastNameStr := DGRSGetFieldValue("LastName",rsHandle) AddressStr := DGRSGetFieldValue("Address",rsHandle) TitleStr := DGRSGetFieldValue("Title",rsHandle) CompanyStr := DGRSGetFieldValue("Company",rsHandle) end if

Get Contact ID ---Get the ID of the current contact -CurContact := DGRSGetFieldValue("ContactID",rsHandle) DGQDSetParameterValue("pContactID", CurContact, dbHandle) phHandle := DGQDCreateRS(qdHandle) if phHandle = "#ERROR#" then LastErrorTxt := LastErrorTxt^"\rError in icon: Get Contact ID - "^GetLastDGError() ClearDGError() end if

Get Phone Fields ---Create a grid object and tie the phone number fields to it -gridHandle := DGCreateGrid("PhoneGrid", phHandle) DGGridAddField("Description", 7, gridHandle) DGGridAddField("PhoneNumber" ,15, gridHandle) DGGridUpdate(gridHandle) ---Close the recordset since it will change --every time we change records. -DGRSClose(phHandle)

Next DGRSMoveNext(rsHandle) NoCurrentEntry := NoCurrentEntry + 1 ---At this point we will need to fill in the address field -txt := DGRSGetFieldValue("Address", rsHandle) if txt <> "#ERROR#" then AddressStr := txt end if

Prev DGRSMovePrev(rsHandle) NoCurrentEntry := NoCurrentEntry -1 ---At this point we will need to fill in the address field --

Datagrip User Manual, Version 2.0

126

August 21, 1998

txt := DGRSGetFieldValue("Address", rsHandle) if txt <> "#ERROR#" then AddressStr := txt end if

Store Values in Database DGRSAddNew(rsHandle) -- Add a new record CanWeEdit := DGRSGetEditMode(rsHandle) if CanWeEdit = 2 then DGRSSetFieldValue("FirstName", FirstNameStr, rsHandle) DGRSSetFieldValue("LastName", LastNameStr, rsHandle) DGRSSetFieldValue("Address", AddressStr, rsHandle) DGRSSetFieldValue("Title", TitleStr, rsHandle) DGRSSetFieldValue("Company", CompanyStr, rsHandle) DGRSUpDate(rsHandle) end if --Adjust the count of records in the database.

Count the new number of entries DGRSMoveLast(rsHandle) HowManyEntries := DGRSGetRecordCount(rsHandle) DGRSMoveFirst(rsHandle) NoCurrentEntry := 1

Count the new number of entries DGRSMoveLast(rsHandle) HowManyEntries := DGRSGetRecordCount(rsHandle) DGRSMoveFirst(rsHandle) NoCurrentEntry := 1

Delete the current entry DGRSDelete(rsHandle) DGRSUpDate(rsHandle)

Datagrip User Manual, Version 2.0

127

August 21, 1998

Appendix E: What’s new in Datagrip 2.0 1. The capability to import bitmaps and waves from the access DB:

Now you can store your bitmaps and wave files right in the database. Then when you need them you can import them in to a cast member on the fly. This is useful for projects that have many pictures each associated with some data. An example would be an online product catalog. You could have all of the product information and a picture of that product stored in the database. If you use our competitor products you will have to store a path name in the DB and then import it off of the hard disk which is a very slow process. 2. Automatic data binding to variables:

This feature allows you to tie a variable in Director or Authorware to a specific field in a database. When the variable is tied anything you do to the database is automatically reflected in the variable. If you move to the next record, edit the record, delete the record, or cause any change the variable is automatically updated for you. This saves the programmer from having to write extra code to handle the retrieval of the data every time a change is made. An example where a developer would want to use this tracking is an ID number. You could tie the ID number of the current record to a variable and provide the user a way to move back and forth through the records. No matter where the user navigates to your variable will always hold the current ID. 3. Automatic data binding to field cast members:

The same as above except that the data is stored in a field object instead of a variable. This is useful anytime you have to display a number of data items from a record all at the same time. All you have to do is drop the fields on the stage and tie them to the database. Then any time a change is made to the database your fields automatically get updated. They will always hold the correct data and the programmer will never have to write the code to fill them! 4. Automatically fill a field or variable with the entire contents of a recordset:

This feature will take a text field or a variable and fill it with all of the data of a field in a recordset. For instance if you wanted to create a list of every name in the database so that you could display it in a field you previously had to manually retrieve the data out of each field and build a large string. With this new feature you just tell Datagrip the field in the recordset and it quickly retrieves all of the data for you. This greatly enhances performance since you make one call instead of one for every record. This will prevent the programmer from making thousands of extra calls that could slow down the application. 5. Capability to turn a field cast member or variable in to a grid:

This great feature will take a list of fields that you specify and build a multicolumn list box. You just name the fields and give them a size and Datagrip takes care of the rest. It automatically adjusts the size of the text in the database so that all of the data lines up in nice columns. Say for instance you had a database that held names and phone numbers. Of course

Datagrip User Manual, Version 2.0

128

August 21, 1998

you want this data to be displayed in a nice format so you create this grid and tell Datagrip to fill it. Datagrip goes out and gets all of the names and phone numbers and formats them for you all with 3 lines of code. If you were to use another database Xtra you would have to make an individual call for each field in each recordset and then handle the spacing for you. For a 1000 record database with 3 fields this generates over 3000 extra calls to the DB. With Datagrip's new feature you could do it in 4 calls, saving a large amount of time. 6. You no longer have to install runtime files on the user’s computer. You can run everything solely from the CD:

This is important to note because previously to use Datagrip you had to install some files on the users hard drive. Now you can run the application solely off of the CDROM without ever copying a file. 7. Datagrip now supports both Pessimistic and Optimistic locking schemes:

This will allow the developer to design more efficient multi-user applications. For instance you would probably want to use a different scheme depending on whether users would try and edit the same data or not.

Datagrip User Manual, Version 2.0

129

August 21, 1998

DATAGRIP SOFTWARE LICENSING AGREEMENT INM's one-time licensing fee enables you to use DataGrip and any additional programs, samples and tools (collectively the "Software") royalty-free, but some conditions apply. We invite you to carefully read and agree to the terms and conditions of the license below before using the software. DataGrip Xtra - License Agreement PLEASE READ THIS LICENSE AGREEMENT CAREFULLY BEFORE INSTALLING, COPYING, OR OTHERWISE USING DATAGRIP. BY USING DATAGRIP, YOU AGREE TO BECOME BOUND BY THE TERMS OF THIS LICENSE AGREEMENT. IF YOU DO NOT AGREE TO THE TERMS OF THIS LICENSE, DO NOT INSTALL, COPY OR USE THIS SOFTWARE. The Software is licensed, not sold, to you by Integration New Media, Inc. ("INM") for the purpose of using it for the development of your own product ("Product") only under the terms of this Agreement. INM and its licensors reserve any rights not expressly granted to you. You own the media on which the Software is recorded or fixed, but INM and its licensors grant you no right, title or interest in or to the Software. The Software is owned by INM and its licensors and is protected by International copyright laws and treaties. 1. LICENSE. INM grants you a non-exclusive, non-transferable, perpetual (unless terminated in accordance with this Agreement), royalty-free, worldwide license to: (a) Install one copy of the Software on a single computer. To "install" the Software means that the Software is either loaded or installed on the permanent memory of a computer (i.e., hard disk). You may only install the Software on another computer if you first remove the Software from the computer on which it was previously installed. (b) Make one copy of the Software in machine-readable form solely for backup purposes, provided the backup copy is not installed or used on any computer. As an express condition of this Agreement, you must reproduce on each copy any copyright notice or other proprietary notice that is included with the original copy of the Software supplied by INM. (c) Reproduce and distribute DGRel.X32 (the "End-User Kit") provided that: (i) you distribute the End-User Kit only in conjunction with and as part of your own Product, and (ii) own a license for the Software that contains the End-User Kit, (iii) agree to indemnify, hold harmless and defend INM and its licensors from and against any claims or lawsuits, including attorney's fees, that arise or result from the use or distribution of your Products with the End-User Kit. (d) Your license is limited to the particular version (collectively "Version") of the Software you have purchased. Therefore, use of a Version other than the one encompassed by this License Agreement requires a separate license. (e) Any third party who will use the End-User Kit in an authoring environment must purchase its own license of the Software. (f) If the Software is licensed as an upgrade or update, then you may only use the Software to replace previously validly licensed versions of the same software. You agree that the upgrade or update does not constitute the granting of a second license to the Software (i.e., you may not use the upgrade or update in addition to the software it is replacing, nor may you transfer the software which is being replaced to a third party). (g) If the Software is licensed for evaluation, you may only use it for the purpose of evaluating it with the intent of purchasing it. In any case, if the Software is not purchased within 30 days of the commencement of the evaluation, then the Software shall be destroyed. (h) If the Software is licensed for educational purposes and in a purely educational environment, you may only use it (i) to teach how to use the Software, or (ii) in non-commercial projects developed by schools, colleges or universities as part of an educational experience. Commercial projects developed by students, instructors or other members of an educational facility, whether for consideration or not, do not qualify as educational. (i) If you develop your Product using the Software for another party (the "Publisher") who (i) publishes the Product; or (ii) otherwise redistributes the Products to end-users under any label other than yours, then the Publisher is also required to be a licensee of the Software. (j) All intellectual property rights in and to the assets which may be accessed through the use of the Software are the property of the respective asset owners and may be protected by applicable copyright or other intellectual property laws and treaties. This Agreement grants you no rights to use such content. 2. RESTRICTIONS. (a) The Software contains a design-time Xtra (DGDes.X32) that may not be distributed by you in any way. (b) You may not sublease, rent, loan or lease the Software. (c) You may not transfer or assign your rights under this License to another party without INM's prior written consent. Assignment application forms can be obtained from INM's sales department. (d) The Software contains trade secrets and, to protect them, YOU MAY NOT MODIFY, ADAPT, TRANSLATE OR CREATE DERIVATIVE WORKS BASED UPON THE SOFTWARE OR ANY PART THEREOF. YOU MAY NOT TO MODIFY, ADAPT, TRANSLATE, REVERSE ENGINEER, DECOMPILE, DISASSEMBLE OR OTHERWISE REDUCE THE SOFTWARE TO ANY HUMAN PERCEIVABLE FORM. YOU MAY NOT ALTER OR CHANGE THE COPYRIGHT NOTICES AS CONTAINED IN THE SOFTWARE.

Datagrip Software Licensing Agreement

130

August 21, 1998

(f) THE SOFTWARE IS NOT INTENDED FOR USE IN THE OPERATION ENVIRONMENTS IN WHICH THE FAILURE OF THE SOFTWARE COULD LEAD TO DEATH, PERSONAL INJURY, OR PHYSICAL OR ENVIRONMENTAL DAMAGE. 3. Copyright Notices. (a) You may not alter or change INM's and its licensors' copyright notices as contained in the Software. (b) You must include a copyright notice, in direct proximity to your own copyright notice, in substantially the following form: "Portions of code are Copyright ©2000 used under license by Integration New Media, Inc. http://www.INM.com". 4. Acceptance. The Software shall be deemed accepted by you upon delivery unless you provide INM, within two (2) weeks therein, with a written description of any bona fide defects in material or workmanship. 5. Termination. This Agreement is effective until terminated. This Agreement will terminate immediately without notice from INM or judicial resolution if you fail to comply with any provision of this Agreement. Upon such termination you must destroy the Software, all accompanying written materials and all copies thereof, and Sections 7, 8, 9 and 10 will survive any termination. 6. Limited Warranty. INM warrants for a period of ninety (90) days from your date of purchase (as evidenced by a copy of your receipt) that the media on which the Software is recorded will be free from defects in materials and workmanship under normal use and the Software will perform substantially in accordance with the user manual. INM's entire liability and your sole and exclusive remedy for any breach of the foregoing limited warranty will be, at INM's option, replacement of the disk, refund of the purchase price or repair or replacement of the Software. 7. Limitation of Remedies and Damages. In no event will INM or its licensors, directors, officers, employees or affiliates of any of the foregoing be liable to you for any consequential, incidental, indirect or special damages whatsoever (including, without limitation, loss of expected savings, loss of confidential information, presence of viruses, damages for loss of profits, business interruption, loss of business information and the like), whether foreseeable or not, arising out of the use of or inability to use the Software or accompanying materials, regardless of the basis of the claim and even if INM or an INM representative has been advised of the possibility of such damage. INM's liability to you for direct damages for any cause whatsoever, and regardless of the form of the action, will be limited, at INM's option to refund of the purchase price or repair or replacement of the Software. THIS LIMITATION WILL NOT APPLY IN CASE OF PERSONAL INJURY ONLY WHERE AND TO THE EXTENT THAT APPLICABLE LAW REQUIRES SUCH LIABILITY. BECAUSE SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES, THE ABOVE LIMITATION MAY NOT APPLY TO YOU. 8. Indemnity. By using the Software, you agree to indemnify, hold harmless and defend INM and its licensors from and against any claims or lawsuits, including attorney's fees that arise or result from the use or distribution of your Product with the Software. 9. General Provisions. This Agreement will be construed under the laws of the Province of Quebec, except for that body of law dealing with conflicts of law. If any provision of this Agreement shall be held by a court of competent jurisdiction to be contrary to law, that provision will be enforced to the maximum extent permissible, and the remaining provisions of this Agreement will remain in full force and effect. 10. Language. The parties acknowledge having requested and being satisfied that this Agreement and its accessories be drawn in English. Les parties reconnaissent avoir demandé que cette entente et ses documents connexes soient rédigés en anglais et s'en déclarent satisfaits.

Datagrip Software Licensing Agreement

131

August 21, 1998