ADOxtra is implemented as scripting xtra that provides several types of Lingo objects. Each object is used as "wrapper" of the corresponding ADO object. Each type of ADOxtra Lingo object implements its own set of properties and functions. Also all ADOxtra object wrappers have a set of common properties that provide error handling mechanism etc. You may create and use any number of these objects (limited only by system resources).
ADOxtra implements several xtra-level functions that provide COM initialization, general xtra information, initial creation of wrappers for ADO objects and date/time conversion functions.
ADO objects use a large amount of constant values (enumeration constants) as
values of properties and function parameters. For example, Recordset.LockType
property may accept several numeric values that indicate the type of locking
to be applied to the recordset. This numeric values have their own names (like
adLockReadOnly which equals 1). You may use numeric values directly
or make ADOxtra to convert constant name to the corresponding value. Every ADOxtra
object wrapper has this capability. Just use the name of constant value as usual
object's property:
rst.CursorType=rst.adOpenKeyset
It is the same as:
rst.CursorType=1
Also, many ADO parameters or properties may accept or return a bitmask of constant values. Use Lingo bitwise operations to set or get neccessary information (bitOr(arg1,arg2), bitAnd(arg1,arg2)).
Several ADO objects' methods may use optional arguments. To skip optional argument
at the end of argument list you may just omit it. To skip optional argument
in the middle of the argument list use VOID instead. For example:
rst.Open("SomeTable",void,void,void,rst.adCmdTable)
ADO objects use several collections. Collection is just a list of
items. For example, Recordset object contains a collection of Field
objects. In Visual Basic you may use several approaches to get the certain item
in collection. The most common way is Item(index) method of the collection.
ADOxtra does not support Item() method due to some syntax limitations in Director
7. Instead, ADOxtra offers square brackets [] operator to access an item of
the collection. For example, use:
put rst.Fields["Name"] -- getting the default Value propertyto access collection's items. Another collection sipported by ADOxtra
is
put rst.Fields["Name"].Type -- getting the type property of Field
object
Properties collection of Connection and Recordset
objects. Also ADOxtra has limited support for Connection.Errors
collection
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
Debug mode may be set explicitly to every ADOxtra object wrapper using common
property DebugMode.
ADO (ActiveX Data Objects) are a set of objects that provides the high level interface to OLE DB database providers. Database providers may perform actual database accessing or may serve as lower level interface to other databasing components. MDAC (Microsoft Data Access Components) which contain ADO as one of its components, contain several OLE DB providers for Access, MS SQL, Oracle, ODBC and several others. Also MDAC contain Microsoft Remoting provider. It is used to provide remote (over Internet) connection to the database. MS Remote provider is used on the client side and one of the other providers is used on the server side.
So, the one of the basic points of using ADO is specifying correct connection
parameters which have to define the provider, the data source and other information
specific to the provider. ADO provides the Connection object that
is responsible for the establishing connection to some provider. Also Connection
object is responsible for the general connection operations like transactions
management.
One of the most important objects which ADO consists of is the Recordset
object. Recordset object serves as an interface to the actual database
data. Recordset can access the data one record at a time. The current
record is represented via collection of Field objects, where each
Field object corresponds to the field of a table or data query.
Recordset object provides browsing capabilities to move through
the set of records. Some of Recordset's capabilities depends on
the provider, others depends on the settings of certain properties.
In general, there are several ways how to get connected to the database. The
simple scenario includes: creating a recordset object, invoking
Recordset's Open method with parameters providing
minimum amount of information. The advanced scenario usually includes more steps:
creating connection object, specifying multiple connection options,
opening connection, creating recordset object, binding recordset
to the opened connection, adjusting recordset properties and at
least opening recordset. In most cases it is enough to use simple scenario since
the default ADO behaviour often is what you need.
After successfull 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
Now you may just call the recordset's Open
method to execute SQL command or query, run stored proc, or get table rows.
The minimum required information is the connection string (activeConnection)
and command text (source). Connection string is the
string in form "PropertyName=PropertyValue;OtherPropertyName=OtherValue"
that specify information necessary to connect to the database. Use BuildConnectionString
xtra level method to invoke a dialog for building connection string. Here is
several samples, how the connection string may look like:
connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Password=psw;
Data Source=D:\Temp\DB.mdb; Mode=ReadWrite;Persist Security Info=True"connectionString="DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=D:\Temp\DB.mdb"connectionString="Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DemoDB;Data Source=SqlServerName"connectionString="Provider=MSDAORA.1; Password=psw; User ID=admin;
Data Source=srv; Persist Security Info=True"connectionString="Provider=MS Remote; Data Source=D:\Temp\DB.mdb;
Mode=Read; Remote Server=http://YourWebServer; Remote Provider=Microsoft.Jet.OLEDB.4.0;
Internet Timeout=300000"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 process of the recordset's
Open method. So, in simple scenario connection string is the only
source of information about which database to open.
Then, you have to specify the command text (the source parameter
of Open method). It may be whatever SQL query or command, or table
name, or stored procedure call, or other provider specific command.
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 successfull
if you do not use ADOxtra's debugMode,
since ADO often (but not always) returns usefull 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).
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 beggining.
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.
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 behaviour 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"]=SomeNewValueThe actual data modification is ocurred on
rst.Fields["SomeOtherFieldName"]=SomeOtherNewValue
rst.Update()
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.
ADOxtra performs neccessary type casting operations to make database values available in Lingo and vice versa. See more details about type casting issues below.
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.
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 transaction. The other example is retrieving database schema information.
Use ADOxtra function CreateObject()
with parameter #Connection to create wrapper for ADODB.Connection object:
cnn=CreateObject(xtra"ADOxtra",#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:"&&db
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. It is definitely good idea do not specify the same kind of information
twice, since different versions of ADO may behave differently, deciding which
information to take into account. For example, if you set cnn.Provider
property and set alternative provider info in the cnn.ConnectionString
property, different versions of ADO may try to use different providers, probably
generating an error in one of the cases.
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]
end repeat
See more details about property object here.
You may adjust some dynamic properties:
cnn.Properties["SomePropertyName"]=SomeNewPropertyValue
After all of the adjustments of the Connection object you have
to open it with cnn.Open
method:
cnn.Open(void,userName,password)
You may use opened connection to start transactions. Use cnn.BeginTrans
to start transaction. Use cnn.CommitTrans
method to save changes or cnn.RollbackTrans
method to cancel the changes being made inside it.
Use cnn.Execute
method to run SQL queries or commands.
Use cnn.OpenSchema
method to get all the information about database structure.
The same way you may create and adjust recordset
object. Then you have to bind recordset to the connection. Use rst.ActiveConnection
property to set the connection over which recordset
will access data.
ADOxtra performs neccessary type casting operations to make database values available in Lingo and vice versa. Typecasting operations are implicitly performed by ADOxtra whenever possible while reading or writing the most of ADO objects properties, translating parameters values, etc. Note that ADO supports the large amount of types of data and Lingo has its own Director specific data types. So, ADOxtra cannot find suitable conversion in all cases, although it provides conversion in the most common cases.
| ADO type | Lingo type | Value (if any) |
| EMPTY | String |
"" |
| NULL | Symbol |
#Null |
| Integer (signed/unsigned), 1,2,4 bytes | Integer |
|
| Float 4,8 bytes | Float |
|
| Date | Float* |
|
| String | String |
|
| Boolean | Integer |
1 or 0 |
| Currency | Float |
| Lingo type | Lingo Value (if any) | ADO type |
Symbol |
#Null | NULL |
Symbol |
other symbols | String |
Integer |
signed integer 4 bytes | |
Float |
Float 8 bytes | |
String |
String | |
Date |
since date(1901,12,14) to date(2038,1,19) | Float* |
DateTimeListToFloat
and FloatToDateTimeList
functions to convert date/time property lists to float value back and forth.If ADOxtra does not know how to convert the value it will report an error. Also note, database provider may perform its own data type conversion. For example, if you assign a value to the Date/Time field, you may assign a string value. In this case ADOxtra converts it to the ADO string value. Then ADO itself will try to convert string value to the date/time value. If you specify a correct date/time string representation (as specified in system's regional settings) ADO will succeed in conversion this string to the date/time value. If you do not specify correct date/time value, ADO will say an error.
Use cnn.Error property
to access Errors
collection of the ADODB.Connection object. This collection contains
error objects describing errors generated by database provider. These errors
may appear for example when you try to modify database that is opened in read
only mode, etc.
Every wrapper object has internal last error flag and error description. The
last error flag is cleared before executing every ADO object method and accessing
ADo object property . If ADO method failed or another error happen, this flag
is raised. So you may detect whether last call completed successfully. Use object.failed
or object.succeeded
properties to check whether the last call was successfull. If an error happened
you may see its description using object.lastError
property. Also you may adjust ADOxtra wrapper objects to output its lastError
directly to the Message window every time error happens. Set object.DebugMode
to true (1) to do this.
ADOxtra may be used to access databases over Internet right from usual Director movie or Shockwave movie. Note, ADOxtra is limited to remote only databasing while it is used from Shockwave movie. This limitation is done intentionally to conform Shockwave safety requirements.
MDAC (Microsoft Data Access Components) contain MS Remoting Provider (Provider=MS Remote) which is capable to retrieve or send recordset data over Internet via http or https protocols. MDAC have to be installed (usually it is already installed) on both client and server sides. Remoting provider is used on the client side. It connects to the server part via http (https) protocol. The server side has to be running IIS web site. IIS provides ISAPI extensions mechanism which allows MS Remoting Provider to call its counter part on the server. IIS web site and server itself have to be properly configured to allow remote databasing. Also there are several server security issues to take into account.
At first, web server must contain virtual directory MSADC with "execute
programs" enabled. This virtual directory has to contain msadcs.dll
file. Copy this file from C:\Program Files\Common Files\System\msadc.
Server security may need to be adjusted to allow remote databasing. By default,
ADO is installed with a "safe" server configuration. The registry
file handsafe.reg (C:\Program Files\Common Files\System\msadc)
has been provided to set up the handler registry entries for a safe configuration.
To run in safe mode, run handsafe.reg. The registry file handunsf.reg
has been provided to set up the handler registry entries for an unrestricted
configuration. To run in unrestricted mode, run handunsf.reg. In
safe configuration Msdfmap.ini file is installed in the Windows
directory. You must configure this file according to your needs, before using
remote databasing. By default this file denies all connections to databases.
Msdfmap.ini file may specify valid data sources that are allowed
to access remotely, named sql queries, access rights etc. See msdn
library to get more information about this file or look at the file itself
since it contains necessary information and samples.
To get remote access to the database you have to be able to access database locally on the web server's computer. Note that IIS may execute server part of RDS under IIS's Internet user account (IUSR_ComputerName). This may be important when connecting to MS SQL Server or databases over LAN.
At client side (it is your usual Director movie or Shockwave movie somewhere
in the world, that is running on the machine connected to the Internet) you
have to specify the correct connection parameters. You have to specify that
you are going to use MS Remote provider to connect to your web site via http
and your web server will use the data source name specified in server's Msdfmap.ini
or some other provider to access some database. See examples below. Also you
may set Internet Timeout property. It defines how long client will wait for
the answer in milliseconds. Default setting is 5 minutes.
connectionString="Provider=MS Remote; Data Source=D:\DemoDB;
Mode=Read; Remote Server=http://YourWebServer; Remote Provider=Microsoft.Jet.OLEDB.4.0;
Internet Timeout=300000"connectionString="Provider=MS Remote; Data Source=SqlServerName;
Initial Catalog=DemoDB; Remote Server=http://YourWebServer; Remote Provider=SQLOLEDB.1;
Internet Timeout=300000"connectionString="Provider=MS Remote; Data Source=YourSystemDSN;
Remote Server=http://YourWebServer; Remote Provider=MSDASQL.1; Internet Timeout=300000"Note, the above samples will probably work only if server is configured to
"unsafe" mode. In "safe" mode the Data Source parameter
is usually corresponds to the entry in Msdfmap.ini file which defines
the actual connection string. Typical connection string for safe server mode
specifies the minimum of information:
connectionString="Provider=MS Remote; Data Source=YourDataSourceName;
Remote Server=http://YourWebServer; Internet Timeout=300000"In this case the actual connection string is contained in [connect YourDataSourceName]
section of the Msdfmap.ini file.
After setting correct connection string you may use Connection or Recordset objects as usual. Note that real data transfer occures when you open recordset or update it.
ADO is capable to perform lengthy operations asynchronousely. It is expecially
usefull for accessing databases remotely, since sending data over Internet may
take a noticable amount of time. Synchronous operation will freeze the movie
until either data exchange is completed or a timeout elapsed. Asynchronous operation
will return control to the movie as soon as possible, so the movie may show
a progress bar or make anything else while data is being retrieved. To open
recordset asynchronousely use following:
rst.Properties["Background Fetch Size"]=10
rst.Properties["Initial Fetch Size"]=1
rst.open("DemoTable",void,void,void,rst.adAsyncFetch+rst.adCmdTable)
repeat while bitand(rst.state,rst.adStateFetching)=rst.adStateFetching
put "Asynchronous operation"
end repeat
Note: It is important to use command type specifier (rst.adCmdTable
or rst.adCmdText etc.) together with rst.adAsyncFetch.
Otherwise ADO may not execute operation asynchronousely. Use rst.state
to determine when asynchrous operation is completed. See rst.Open
method for more details about its parameters.
To use ADOxtra from Shockwave several extra steps are required. At first you
have to place the ADOxtra package (certified by Verisign) on your web site.
Otherwise you may use package available at http://download.adoxtra.com/package/.
Then you have to modify xtrainfo.txt file located in you Director installation
folder. This file provides the master list of xtras known to Director. Add folowing
lines to the end of this file:
[#nameW32:"ADOxtra.x32", Replace the #package values to the location on your web server where
package file resides. Note: file and folder names in url may be case
sensitive. After modifying this file you may run Director, open the movie you
are going to use from Shockwave and then invoke the
#info:"http://www.adoxtra.com/",
#package:"http://download.adoxtra.com/package/ADOxtra"]
[#nameW32:"ADOxtraLite.x32",
#info:"http://www.adoxtra.com/",
#package:"http://download.adoxtra.com/package/ADOxtraLite"]
Modify/Movie/Xtras
dialog. Find (or add) the ADOxtra (ADOxtraLite) entries and place the check
mark near "Download if needed" and "Include in projector".
Director will check whether package file is available at this moment. Then you
may save the movie as Shockwave and try it.
Take a look on this Shockwave sample to see how it works. The original sources of this movie is included in downloading demo package.
| ©2001 Eugene Shoustrov | www.adoxtra.com | Built on April 14, 2001 |