ADOxtra for Macromedia Director

Initialization ADOxtra

Usually, you begin with Init method of the ADOxtra. It makes COM initialization and allows you to set default wrapper objects mode to debug mode. When debug mode is set, all ADOxtra objects output their error information to Director or projector Message window. It is highly recommended to set the debug property to true when you just play with the xtra. So, use:
bSuccess=Init(xtra"ADOxtra",true) -- Initializing xtra and setting debug mode

Creating recordset object

After successful call of Init method, you are ready to create Recordset object. Use ADOxtra function CreateObject() with parameter #Recordset to create wrapper for ADODB.Recordset object:
rst=CreateObject(xtra"ADOxtra",#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". Use BuildConnectionString ADOxtra xtra-level method to invoke a standard dialog for building connection string. 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:
connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Temp\DB.mdb; Mode=ReadWrite;"
source="SELECT SomeFieldName, SomeOtherFieldName FROM SomeTable ORDER BY SomeFieldName"
rst.Open(source,connectionString)
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 ADOxtra's debugMode, 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]
  rst.MoveNext()
end repeat

See more info about Fields collection and Field object to find out what else you can do with them. Also you may use rst.GetString method to quickly see the string representation of the recordset data. GetString method lets you specify custom strings for field delimiters, row delimiters and Null expression.

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(source,connectionString,rst.adOpenKeyset,rst.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"]=SomeNewValue
  rst.Fields["SomeOtherFieldName"]=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, be sure to void out any Lingo variable that may store a reference to the ADOxtra wrapper object, thus completely releasing it from memory.