Data Linker Xtra v1.2
Copyright © 2003 by Calu Software

For the latest information, please visit http://xtras.calu.us
To contact me , write to xtras.calu@calu.us

Table of Contents

Overview
End User License Agreement
System Requirement
How to use Data Linker Xtra

new( )
*registerDataLinkerXtra( )
openConnection( )
closeConnection( )
getConnectionProperties( )
getTableNames( )
getTableFieldNames( )
getFieldAttributes( )
getTableData( )
getDataBySQL( )
getRecordCount( )
getLastErrorNum( )
getDetailedError( )
isOpen( )
showDBOpenDialog( )

Contact Information
Version History
Additional Resources

Overview

There are several database xtras available to Director users. Some can connect to a particular data source such as Microsoft Access or MySQL. Other xtras use proprietary technology. Many are powerful, but extremely time consuming to learn. Most are also quite expensive to purchase. Data Linker is designed to overcome these issues. Data Linker can link to any data source, provided that the appropriate drivers are installed. By using the same methods to retrieve data from any data provider, developers can easily change and/or upgrade their database technology without significant reprogramming cost.

Table of Contents

End User License Agreement (EULA)

This End-User License Agreement("EULA") is a legal agreement between you and Calu software, please read it carefully.

YOU AGREE TO BE BOUND BY THE TERMS OF THIS EULA BY INSTALLING THIS PRODUCT. IF YOU DO NOT AGREE, DO NOT DOWNLOAD OR INSTALL THIS PRODUCT.

By install, we mean making this xtra available for use with Macromedia products such as Director or Authorware.

Calu software makes no representations concerning either the merchantability of this software or the suitability of this software for any particular purpose. It is provided "as is" without express or implied warranty of any kind. In no event is Calu software liable for any damage, including profit loss, and/or any other incidental or consequential damage. There may be errors in this software or its documentation and examples. You agree to bear all risks by agreeing to use this xtra.

You may not rent, lease, sell, modify, decompile, disassemble, reverse engineer, or transfer the licensed of the xtra, except as provided for in this agreement. Any such unauthorized use shall result in immediate and automatic termination of the rights granted in this EULA and may result in criminal and/or civil prosecution.

You are free to try Data Linker Lite with Macromedia Director in authoring mode. None of the features in Data Linker or Data Linker Lite are available in projector mode without valid registration key. A registration code represents the rights to install Data Linker Xtra on a single machine for authoring, and the rights distribute unlimited copies for use in projector mode.

Data Linker Lite have the following restrictions:

Registered versions have no restrictions.

The terms of this license are subjects of changes in future versions. All rights not expressly granted in this EULA are reserved by Calu Software. All copyrights to Data Linker Xtra are exclusively owned by the author - Calu. If you have any questions, please contact us at xtras.calu@calu.us or post questions at http://xtras.calu.us/forum

able of Contents

System Requirement

Macromedia Director 7, 8, 8.5.1 or MX.
Microsoft Windows 98, ME, NT4, 2000, XP
Microsoft MDAC 2.5 or above. They can be downloaded from www.microsoft.com/data

Data Linker Xtra is currently not shockwave safe. A shockwave version is currently being considered.

Table of Contents

How to use Data Linker Xtra

Place a copy of Data Linker Xtra into your Macromedia Director's Xtra folder. For more information on this, check Tech note ID 14888 on http://www.macromedia.com/.

To check for the functions in the Data Linker Xtra, type the following into Director's message window:
put xtra("DataLinker").interface()

The possible function/Methods are:
new object me
*registerDataLinkerXtra string regName, string regCode -- register to unlock all features
openConnection object me, string connectionString -- creates connection to your data source
closeConnection object me -- explicit call to close your existing connection
getConnectionProperties object me -- returns a list of connection properties
getTableNames object me -- returns a list of table names in the database
getTableFieldNames object me, string tableName -- returns a list of field names in a table
getFieldAttributes object me, string tableName, string fieldName -- returns the data type for the field column
getTableData object me, string tableName -- returns a 2-Dimensional List of all data in Table
getDataBySQL object me, string SQL -- returns 2-Dimensional list of query run from SQL
getRecordCount object me, string tableName -- returns integer of total number of rows in the data file
getLastErrorNum object me -- returns an error number after each call, 0 means no error
getDetailedError object me, -- returns the detailed string version of the last error call
isOpen object me, -- returns if the connection is opened
showDBOpenDialog object me, -- customized version of choose open file dialog box.

Table of Contents

new( )

Creates an instance of the Data Linker Xtra

Usage:

new (object me)

Returns:

(xtra instance)

Example:

xtra_caluData = xtra("DataLinker").new()

Notes:

Data Linker Xtra uses object instance to allow several simultaneous connections at the same time. Create an instance of the xtra for each connection you plan on making at the same time.

Introduced in version 1.0.

Table of Contents

registerDataLinkerXtra( )

This is a global movie handler. Unregistered version have certain feature restrictions. See licensing for more information.

Usage:

registerDataLinkerXtra (string regName), (string regCode)

Returns:

(integer)
1 if registration code is accepted
0 if registration code is rejected

Example:

registerDataLinkerXtra("fill me","in")

Notes:

Note that this function is a global director handler, which means that it doesn't need to be instantiated prior to usage. This is the only handler in this xtra that is in global scope, so make sure you call this before using any functions in Data Linker Xtra.

You will need to register Data Linker Xtra in both authoring mode and projector mode to unlock full functionality in that mode.

Introduced in version 1.0.

Table of Contents

openConnection( )

Creates a connection between Director and your data source

Usage:

openConnection (object me), (string connectionString)

Parameter:

connectionString: (string) is left untouched from the C++ code because it allows for maximum flexibility. With the correct connection string, you can connect to any data source, including SQL Server, Oracle, Microsoft Excel, dBASE, Microsoft Access or MySQL

Returns:

(integer)
1 = Connection opened successfully
0 = Error

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")

Notes:

To connect to an Access database, use:
_str_connectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main"&";Uid=admin;Pwd="

To connect to an Excel spreadsheet, use:
_str_connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&QUOTE&the moviepath&main.xls&QUOTE&";Extended Properties="&QUOTE&"Excel 8.0; HDR=NO;IMEX=1;"&QUOTE

To connect to an CSV file, use:
_str_connectionString = "Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="&the moviepath&";Extensions=asc,csv,tab,txt"

To connect to an dBASE file, use:
_str_connectionString = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq="&the moviepath&";"

To connect to mySQL, use:
_str_connectionString = "Driver={MySQL ODBC 3.51 Driver};Server=192.168.1.1;Database=mysql;Option=4;User=root;Password=;"

To connect to SQL Server, use:
_str_connectionString = "Driver={SQL Server};Server=xxx.xxx.xxx; Address=xxx.xxx.xxx,1433; Network=DBMSSOCN; Database=DATABASENAME; Uid=USERNAME;Pwd=PASSWORD")

** be sure to change the moviepath, filename or server IP to match your local settings

These are just some examples of various connection strings. A detailed list can be found at http://xtras.calu.us/links.php

To test if the connection was opened successfully, use getConnectionProperties() and check if the driver information is returned back.
To open a connection, you MUST supply the correct connection string. Make sure the driver you specify is installed on your target machine.

If you data file is protected, you can also pass in the user name and password in the connection string.

Introduced in version 1.0.

Table of Contents

closeConnection( )

Explicit call to close the open connection.

Usage:

closeConnection (object me)

Returns:

(integer)
1 = Connection closed successfully
0 = Error

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
xtra_caluData.closeConnection()

Notes:

Connections are automatically closed when you destroy the variable instance or when you exit Director. You can free up the memory used by closing a connection that you no longer need.

Introduced in version 1.0.

Table of Contents

getConnectionProperties( )

Obtains a list properties for the open connection

Usage:

getConnectionProperties (object me)

Returns:

(Property List)
All property values are strings. The following property names could be in the property list:
#version
#DBMS_Name
#DBMS_Version
#OLD_DB_Version
#Provider_Name
#Provider_Version
#Driver_Name
#Driver_Version
#Driver_ODBC_Version

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
put xtra_caluData.getConnectionProperties()

Notes:

Use this handler to examine your connection properties and to determine if your connection was opened successfully. Not all properties are available to all data providers. For example, Microsoft Excel using a Jet provider will not have the #Driver_Name property.

If the connection string to open the connection is invalid or if the driver is not installed, a empty list is returned.

Introduced in version 1.0.

Table of Contents

getTableNames( )

Obtains a list of tables from the active connection

Usage:

getTableNames (object me)

Returns:

( list of strings ) - a list of tables names in the active connection

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
put xtra_caluData.getTableNames()

Notes:

Microsoft Excel don't deal with tables, instead a list of spreadsheets are returned.
In many cases, getTableName will also return the system table names. System tables are usually used by the data provider itself for self maintenance.
This function will return an empty list for those data formats that don't support multiple data sets, such as a Comma Delimited File(csv).

Introduced in version 1.0.

Table of Contents

getTableFieldNames( )

Obtains a list of fields (column heading) from the active connection

Usage:

getTableFieldNames (object me), (string tableName)

Parameter:

tableName (string) - the name of the table in the data file. Use getTableNames() to obtain the list of tables available.

Returns:

( list of strings ) - a list of column field names for a particular table

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
put xtra_caluData.getTableFieldNames("MSysAccessObjects")

Notes:

Not all data sources will have field names. For example, csv files may not contain field names. In cases when the data source don't support retrieving field names, a empty list is returned

Introduced in version 1.0.

Table of Contents

getFieldAttributes( )

Retrieve the data type of a column from the active connection

Usage:

getFieldAttributes (object me), (string tableName), (string fieldName)

Parameter:

tableName - (string) the name of the table in the data file. Use getTableNames() to obtain the list of tables available.
fieldName - (string) the name of the column in the data file. Use getTableFieldNames() to obtain the list of column names in a table.

Returns:

list with a single string value - return values can be either of the following:
adBigInt, adBinary, adBoolean, adBSTR, adChapter, adChar, adCurrency, adDate, adDBDate, adDBTime, adDBTimeStamp,
adDecimal, adDouble, adEmpty, adError, adFileTime, adGUID, adIDispatch, adInteger, adIUnknown, adLongVarBinary
adLongVarWChar, adNumeric, adPropVariant, adSingle, adSmallInt, adTinyInt, adUnsignedBigInt, adUnsignedInt,
adUnsignedSmallInt, adUnsignedTinyInt, adUserDefined, adVarBinary, adVarChar, adVariant, adVarNumeric, adVarWChar
adWChar, *UNKNOWN*

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
myAttribute = xtra_caluData.getFieldAttributes("MSysAccessObjects","ID")

Notes:

While getFieldAttributes() can obtain useful field type information, however Director doesn't support most of these data types.

Introduced in version 1.0.

Table of Contents

getTableData( )

Retrieve the entire table data into an two dimensional director list.

Usage:

getTableData (object me), (string tableName)

Parameter:

tableName - (string) the name of the table in the data file. Use getTableNames() to obtain the list of tables available in your data file.

Returns:

(2 Dimensional list), with the inner list storing column data for each row, and the outer list storing the lists for each row

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
myData = xtra_caluData.getTableData("MSysAccessObjects")

Notes:

Use this method to quickly obtain the entire table data back into director. This function is only limited by the available memory available
on the target machine. If you believe the data set is larger than needed, consider using getDataBySQL()

Introduced in version 1.0.

Table of Contents

getDataBySQL( )

Selectively filter the data to retrieve from the active connection by using SQL.

Usage:

getDataBySQL (object me), (string SQL)

Parameter:

SQL (string) - use standard SQL (Structured Query Language) to selectively filter data to retrieve. See site for more information:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

Returns:

(2 Dimensional list), with the inner list storing column data for each row, and the outer list storing the lists for each row

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
myData = xtra_caluData.getDataBySQL("select [id] from [MSysAccessObjects]")

Notes:

Use this method to quickly return a selective set of data back into Director.
You can also use SQL to update data values in the data file.
It is worth noting that not all SQL strings are created equally. Here are some examples of a simple query on different data providers

Provider	SQL String
mySQL ODBC 3.51 Driver	select * from myTable
Microsoft.Jet.OLEDB.4.0	select * from 'myTable'
MSDASQL	select * from [myTable]

Introduced in version 1.0.

Table of Contents

getRecordCount( )

Retrieves the record count( row count) for a table

Usage:

getRecordCount (object me), (string tableName)

Parameter:

tableName - (string) the name of the table in the data file. Use getTableNames() to obtain the list of tables available in your data file.

Returns:

(integer), total row count in the table

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
rowCount = xtra_caluData.getRecordCount("MSysAccessObjects")

Notes:

It may be useful to obtain the record count before calling getTableData() to ensure that you have adequate memory before create a large list.

Introduced in version 1.1.

Table of Contents

getLastErrorNum( )

After calling each handler, you can quickly call getLastErrorNum() to query if there was an error

Usage:

getLastErrorNum (object me)

Returns:

(integer)
0: no error
-201: database connection not open
-210: could not execute SQL
-211: could not get access to Row information
-212: could not read recordset returned
-299: unknown error

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
myData = xtra_caluData.getTableData("MSysAccessObjects")
if xtra_caluData.getLastErrorNum() > 0 then alert "Problem reading data"

Notes:

Negative numbers denote errors in your last operation. It may be more helpful to the developer by getting the description of the error by calling getDetailedError().

Introduced in version 1.0.

Table of Contents

getDetailedError( )

Retrieves a string message for more details on the last error

Usage:

getDetailedError (object me)

Returns:

(string)
Detailed error description provided by the data provider.

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
myData = xtra_caluData.getTableData("MSysAccessObjects")
if xtra_caluData.getLastErrorNum() < 0 then alert xtra_caluData.getDetailedError()

Notes:

The detailed error is provided by the data provider, and provides insights into why the last operation failed. Not all failures are the result problems with the data provider, therefore use getLastErrorNum( ) to test if there is an error before calling getDetailedError( ).

Introduced in version 1.2.

Table of Contents

isOpen( )

checks if the data connection is open

Usage:

isOpen (object me)

Returns:

(boolean)
0: connection is closed
1: connection is open

Example:

xtra_caluData = xtra("DataLinker").new()
xtra_caluData.openConnection("Provider=MSDASQL;ExtendedAnsiSQL=2;DRIVER={Microsoft Access Driver (*.mdb)};Dbq="&the moviepath&"main")
myData = xtra_caluData.isOpen( )

Notes:

Calling the xtra function getConnectionProperties( ) takes a bit more processing power, thus it can be more efficient to check if your connection has been opened by calling isOpen( ).

Introduced in version 1.2

Table of Contents

showDBOpenDialog( )

Display a fully customized modal file open dialog box for choosing database files

Usage:

showDBOpenDialog (object me)

Returns:

(string)
file path to the file selected

Example:

xtra_caluData = xtra("DataLinker").new()
str_pathFILE = xtra_caluData.showDBOpenDialog()

Notes:

This function was included to facilitate the sample movie. Prior to v1.2, the sample movie used fileXtra 3, which confused some users.

Introduced in version 1.2

Table of Contents

Contact Information

If you have any questions, please contact me at xtras.calu@calu.us or visit http://xtras.calu.us/forums
The latest version can be found online at http://xtras.calu.us

Please send any issues, problems, concerns and/or bug to xtras.calu@calu.us.  Please provide your system specifications and as much details on recreating the problem as possible, and I'll try to resolve it as soon as possible

Table of Contents

Version History

v1.2 Release - November 9, 2003. Major update with the following changes

1. String allocation previously limited to 255 characters, now this restriction has been fixed.
2. Improved xtra's memory allocation and deallocation scheme
3. Improved stability and added additional error checking code internally
4. Added 3 new functions: getDetailedError(), isOpen(), showDBOpenDialog()
5. Changed all VOID returns into director string "*NULL*"
6. Changed licensing scheme
7. Updated documentation to reflect new changes

Table of Contents

Additional Resources:

For more information on SQL or connection string, please visit http://xtras.calu.us/links.php

Table of Contents

This document last edited on December 27, 2007

Macromedia, Director, and Lingo are registered trademarks of Macromedia.
Microsoft Access, Excel, SQL Server registered trademarks of Microsoft.