ActiveCompanionSet xtras for Macromedia Director

ADO Databasing in Macromedia Director

ActiveX Data Objects (ADO) provides a universal programming way of handling databases. VbScriptXtra allows using ADO within Lingo.
ADO documentation is probably already available at your Windows\Help folder. See ADO210.CHM file. Otherwise there is MSDN.

Creating Recordset Object

Use xtra-level method CreateObject( strProgId ) to create wrapper for ADODB.Recordset object:
Vb = xtra("VbScriptXtra")
rst = vb.CreateObject( "ADODB.Recordset" )

Check resulting value to ensure that ADO is available. If function succeeded rst will be the Lingo object reference, otherwise it will be a string, describing error:
if objectP(rst) then
  put "Recordset created"
else
  put "Error:" && rst
end if

Choosing which Database to Open

ADO usually uses a connection string to specify to which database to connect or which database to open. Connection string is the string in form "PropertyName=PropertyValue;OtherPropertyName=OtherValue". Here are several samples, how the connection string may look like:

MS Access databases
connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Temp\DB.mdb; Mode=ReadWrite;Persist Security Info=True"

MS Access databases (password protected)
connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Temp\DB.mdb; Mode=ReadWrite; Jet OLEDB:Database Password=PasswordHere"

MS Access databases via ODBC driver (DSNless connection):
connectionString="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=D:\Temp\DB.mdb"

MS SQL Server:
connectionString="Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DemoDB;Data Source=SqlServerName"

Oracle databases:
connectionString="Provider=MSDAORA.1; Password=psw; User ID=admin; Data Source=srv; Persist Security Info=True"

The most important property in connection string is "Provider". Its value usually determines the type of database to work with. Other properties specify additional information that may be specific to the provider. Note that if you omit the provider property, the default will be used. Default provider for ADO is OLE DB Provider for ODBC as in the second example above.

Note that connection string may specify the type of access to data. In the first example "Mode=ReadWrite" specifies that connection to database is for reading and writing. All or almost all information specified in connection string may be adjusted directly by setting properties of the connection object. But in simple scenario you do not use Connection object directly, although ADO will create it implicitly during processing of the recordset's Open method. So, in simple scenario connection string is the only source of information about which database to open.

Opening Recordset Object

To get actual database data with ADO you have to open a recordset with specified command text over specified connection. The command text may be a SQL query or command, a table name, a stored procedure name, or other provider specific command.
To open recordset you may call the recordset's Open method:
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Temp\DB.mdb; Mode=ReadWrite;"
strSQL = "SELECT SomeFieldName, SomeOtherFieldName FROM SomeTable ORDER BY SomeFieldName"
rst.Open( strSQL, strCnn )
if rst.succeeded then
  put "Recordset state:"&&rst.State
else
  put "Error:"&&rst.lastError
end if

Be sure to always check whether call was successful if you do not use VbScriptXtra's debugging mode, since ADO often (but not always) returns useful error descriptions, if you do something incorrectly. After Open call succeeded, check the state property of the recordset. Usually if source text specifies row-returning query (like SELECT), the rst.state property will be set to adStateOpen (=1). If source text specifies command query (like INSERT), the state of recordset object will be set to adStateClosed (=0).

Getting Data from Database via Recordset

The recordset object with rst.state = adStateOpen is ready to provide access to the data. Recordset provides access to the data in record by record manner. So at any given moment you can only access the current record. Move the current record of a recordset with rst.MoveNext(), rst.MovePrevious(), rst.MoveFirst, rst.MoveLast() functions. Use rst.EOF and rst.BOF properties to determine whether recordset has reached the end or the beginning. Use rst.Fields collection to actually work with data:
repeat while not rst.eof
  put rst.fields["SomeFieldName" or SomeFieldIndex].Value
  rst.MoveNext()
end repeat

Modifying Data via Recordset

By default, recordset's Open method will open read only forward only recordset. It means such recordset will not be able to modify data and will not be able to move the current record backward. This behavior is determined by other parameters of rst.Open method. See the description of cursorType and lockType parameters of rst.Open method. In general, lockType parameter determines the type of locking to be applied to the data. The default value is adLockReadOnly, which allows only read access to the data. The cursorType defines the capabilities of the recordset in relation to data changes made by others. The default value is adOpenForwardOpen, which defines a static copy of a set of records with forward only movement capability. Usually, in case you are going to modify data in database you may set the lockType parameter to adLockPessimistic and the cursorType parameter to adOpenKeyset:
rst.Open( strSQL, strCnn, #adOpenKeyset, #adLockPessimistic )
if rst.succeeded then
  put "Recordset state:" && rst.State
else
  put "Error:" && rst.lastError
end if

Now you are able to make modifications to data:
rst.Fields["SomeFieldName"].Value = SomeNewValue
rst.Fields["SomeOtherFieldName"].Value = SomeOtherNewValue
rst.Update()

The actual data modification is occurred on Update method. Always check whether call was succeeded, since data provider may deny attempt to modify data if data violates database integrity or other database rules.

Closing Recordset

After you finish using particular recordset you may reopen it with other parameters. Use rst.Close method to release system resources associated with open recordset. Then you may reopen it with other parameters. If you do not need it any more, make sure to void out any Lingo variable that may store a reference to the VbScriptXtra wrapper object, thus completely releasing it from memory.

Connection object

In certain cases you may need to use alternative approach to perform required task. For example, you have to create connection object before opening recordset to open recordset inside a transaction. The other example is retrieving database schema information.
Use xtra-level method CreateObject( strProgId ) to create wrapper for ADODB.Connection object:
Vb = xtra("VbScriptXtra")
cnn = vb.CreateObject( "ADODB.Connection" )

Check resulting value to ensure that ADO is available. If function succeeded cnn will be the Lingo object reference, otherwise it will be a string, describing error. Use cnn.Version property to determine ADO version:
if objectP( cnn ) then
  put "ADO version:" && cnn.Version
else
  put "Error:" && cnn
end if

Then you have to adjust connection parameters using Connection object's properties. See cnn.ConnectionString, cnn.Provider and other properties of the Connection object. Otherwise you may specify connection information as parameters of cnn.Open method.

Object's Dynamic Properties

Connection object contains the collection of dynamic properties cnn.Properties. This collection contains multiple properties specific to the provider. You may access this collection after you specify which provider to use. If you do not specify any, the OLE DB provider for ODBC will be used. Once you set the provider of the connection object you cannot change it for this particular instance. After you specify provider you may look at dynamic properties it supports:
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
repeat with i = 0 to cnn.Properties.Count - 1
  put cnn.Properties[i].Name && "=" && cnn.Properties[i].Value
end repeat

You may adjust some dynamic properties:

cnn.Properties["SomePropertyName"] = SomeNewPropertyValue

The recordset object contains its own provider specific collection of the dynamic properties. They may be accessed in the same way.

Using Transactions

You may use opened connection to start transaction. Use cnn.BeginTrans to start transaction. Use cnn.CommitTrans method to save changes or cnn.RollbackTrans method to cancel the changes being made inside the current transaction.

Sample OpenRecordset handler

-- Handler creates a new recordset object
-- connects it to the MS Access database dbPath
-- sets access rights to read or read/write depending on bReadWrite parameter
-- executes SQL query and returns resulting recordset object if successful or
-- string with error description otherwise
on OpenRecordset dbPath, sql, bReadWrite
  if voidP(bReadWrite) then bReadWrite=false
  
  if voidP(sql) then return "OpenRecordset: Required parameter is missing: "&sql
  if voidP(dbPath) then return "OpenRecordset: Required parameter is missing: "&dbPath
  
  -- Creating recordset object
  rst=createObject(xtra "VbScriptXtra","ADODB.Recordset")
  if not objectP(rst) then return rst
  
  -- Building connection string
  cnnStr="Provider=Microsoft.Jet.OLEDB.4.0;" -- Microsoft Jet provider for MS Access databases
  cnnStr=cnnStr&"Data Source="&dbPath&";"
  if bReadWrite then
    cnnStr=cnnStr&"Mode=Read|Write;"
  else
    cnnStr=cnnStr&"Mode=Read;"
  end if
  
  rst.ActiveConnection=cnnStr
  if rst.failed then return rst.lastError
  
  if bReadWrite then
    rst.lockType=#adLockPessimistic
    rst.CursorType=#adOpenKeyset
  else
    rst.lockType=#adLockReadOnly
    rst.CursorType=#adOpenStatic
  end if
  
  rst.Open(sql)
  if rst.failed then return rst.lastError
  
  return rst
end

Using ADO events with VbScriptXtra

Sample demonstrates using events with ADODB.Connection object.

-- *******************************************************
-- Here is the code for EventsHandler parent script
on new me
  return me
end

on IncomingEvent me, event, args
  put event, args
end

on ConnectComplete me, args
  pError = args[1]
  adStatus = args[2]
  pConnection = args[3]
  put "ConnectComplete"
  if ( adStatus 1 ) then alert pError.Description
end

on Disconnect me, args
  adStatus = args[1]
  pConnection = args[2]
  put "Disconnect"
end

on WillConnect me, args
  ConnectionString = args[1]
  UserID = args[2]
  Password = args[3]
  Options = args[4]
  adStatus = args[5]
  pConnection = args[6]
  put "WillConnect"

  -- Creating new connection string
  -- Microsoft Jet provider for MS Access databases
  cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
  cnnStr = cnnStr & "Data Source=D:TempTestDB.mdb;"
  cnnStr = cnnStr & "Mode=Read|Write;"

  -- return it to the connection object via referenced parameter
  args[1] = cnnStr
end

-- End of the code for EventsHandler parent script
-- *******************************************************

Name this script as "ConnectionEvents". Then try to execute following lines right in Director's messages window.

Vb = xtra("VbScriptXtra")

-- Setting debug mode to true
vb.Init(true)

-- Creating an instance of the ADODB.Connection object
cnn = vb.CreateObject("ADODB.Connection")

-- Creating an instance of the events handler parent script
evnts = new( script("ConnectionEvents") )

-- Attaching handler to a wrapper
cnn.EventsHandler = evnts

-- Opening connection without explicitly specifying connection params
-- Connection string should be set by the events handler
cnn.Open()

put cnn.State

-- Closing connection
cnn.Close()

Where to find more info about ADO?

ADO is fully documented at msdn.microsoft.com.

VbScriptXtra provides autodocumentation feature allowing you to see what you can do with particular object instance. Just create a recordset object and type in Messages window:
interface(rst)
interface(rst.fields)
interface(rst.fields(0))

ObjectBrowser xtra - autodocumentation companion for VbScriptXtra - will show all available ADO interfaces, methods, properties and enumerations.

ObjectBrowser xtra is a part of ActiveCompanionSet. It is available to Download.

Sample Director movie is available to download. It shows basing ADO operations with VbScriptXtra.