Response.Write("<TABLE BORDER=1>")
For Each colHeader in RS.fields
      Response.Write("<TH><B><PRE>" & colHeader.Name & "</PRE></B></TH>")
Next
Do While Not RS.EOF
      Response.Write("<TR>")
      For Each aField in RS.fields
            Response.Write("<TD>" & aField.Value & "</TD>")
      Next
      Response.Write("</TR>")
      RS.MoveNext
Loop
Response.Write("</TABLE>")

Explicit navigation with ADO

You might want to build a table that does not implicitly navigate relationships among classes, but provides a way to navigate such relations explicitly.

ADO 1.5 and later supports chaptered row sets, which provide a standard way to nest record sets. In this case the Field.Value item is a RecordSet object and you can use it to display the navigated elements.

The resulting HTML table looks like this:

Clicking on an item in the cars column explicitly navigates the Customer to Vehicle relation:

To navigate explicitly in ObjectStore Active Toolkit OLE DB, you have to write additional code to handle Field.Value. If you are using ADO 1.5 or later, ATK handles explicit navigation using the chaptered row set functionality. From the user standpoint, this means that the Field.Value is registered as type (Field.Type) 136 and that it is an ADO RecordSet object that can be used to display the navigated rows.

(To learn about specifying explicit navigation, see SQL Support in ATK OLE DB.)

Sample code ADO 2.0

This sample code provides explicit navigation:

For Each aField in RS.fields
      If aField.Type=136 Then 'Is it a Chaptered Rowset?
            set navigatedRS=aField.Value
            \QnavigatedRS is an ADO RecordSet object
      else
            Response.Write(aField.Value)
      end if
Next

An OLE DB consumer (that is, a consumer that directly accesses OLE DB, without using ADO) can detect explicit navigation by checking the type of the column.

If the column is registered as DBTYPE_IDISPATCH, the column contains a pointer to an IDispatch interface that exposes the GetCardinality, GetRecordSet and GetIRowset methods. The OLE DB consumer can then access these methods to retrieve the navigated table.

If the column is registered as DBTYPE_HCHAPTER, the column contains a handle to the chapter (HCHAPTER). Refer to the Microsoft OLE DB documentation for information on how to handle chaptered row sets.

Multimedia Object Managers in ATK OLE DB

ATK supports ObjectStore multimedia Object Managers. ATK can also access multimedia data ATK through OLE DB and ADO.

Using ADO

Using ADO to access a field that contains a multimedia Object Manager is straightforward. Suppose RS is a RecordSet, and the first column contains a multimedia Object Manager.

osmmData=RS(0).GetChunk(RS(0).ActualSize)

Then use osmmData to access an IStream or a SAFEARRAY OLE interface. For example, in ASP, use osmmData in conjunction with the BinaryWrite Response object method:

Response.BinaryWrite(osmmData)

The example WebADODemo shows how to display a collection of multimedia Object Managers using the ObjectStore Active Toolkit OLE DB provider. This HTML page displays a collection of images stored in the ObjectStore database as osmmImage objects and retrieved using the ObjectStore Active Toolkit OLE DB provider:

Using OLE DB

When you access multimedia Object Managers directly from an OLE DB consumer, rather than through the ADO object model, note that the columns containing multimedia values are registered as DBTYPE_BYTES and DBCOLUMNFLAGS_ISLONG. Follow the OLE DB specifications to retrieve the binary large object (BLOB) content. The OLE DB consumer can provide a memory buffer to be filled by ObjectStore Active Toolkit OLE DB, or to require an OLE interface such as ISequentialStream, IStream, or ILockBytes.

Accessing the ATK OLE DB Provider

Any OLE DB or ADO client can access an ObjectStore Active Toolkit OLE DB provider. The examples included with ATK show how to access ObjectStore Active Toolkit OLE DB through ADO from a Visual Basic or ASP/VBScript application. For additional information, see Chapter 1, A Visual Basic Application with ATK ActiveX Server and Chapter 2, Accessing ATK ActiveX Server from ASP Applications, in the ObjectStore Active Toolkit Tutorial.

Opening a connection

In general, to open an ObjectStore Active Toolkit OLE DB connection, provide a string in this format:

provider=ObjectStore Active Toolkit OLE DB Provider;      
      data source=< full-db-path>;

      [Join=[SQL|Inspector];]
      [Import=< import-database>;]

where

provider is the registered OLE DB source you want to open.
ObjectStore Active Toolkit OLE DB Provider is the name used by ObjectStore Active Toolkit OLE DB.
data source is the name of the ObjectStore database you want to open. It can specify any ObjectStore DB path, such as c:\odi\mydb.db (a file database) or myHost:/dbs/my.db (a remote database) or myHost::/dbs/my.db (an ObjectStore rawfs database).
Join specifies whether you want to obtain SQL-like or Inspector-like navigations/joins from this connection. Use this field to override the default behavior of the ObjectStore Active Toolkit OLE DB provider.

Inspector relationships

If you specify Inspector, ATK expands one-to-many relationships in a tree-like way. For example, suppose you have two classes named Customer and Vehicle; a Customer might have several Vehicles. ATK provides output in this form:


Name	Make	Model
John, Smith	Ford	Escort
	Cadillac	DeVille
Bob, Dylan	Ford	Ranger
	Mazda	626
	Dodge	Spirit

SQL relationships

If you specify Join=SQL, ATK expands one-to-many relationships to show all the possible values that meet the conditions of the query (as an SQL join would). The output takes this form:


Name	Make	Model
John, Smith	Cadillac	Deville
Bob, Dylan	Ford	Ranger
Bob, Dylan	Mazda	626
Bob, Dylan	Mazda	626
Bob, Dylan	Dodge	Spirit

Choosing between the two

Usually a tree-like view is more readable, but an SQL-oriented tool such as a report generator cannot easily use it. However, the SQL-like view is less readable and, if you navigate multiple one-to-many relationships in a row, the number of generated rows is much greater than in the tree-like view.

For example, using ASP and ADO you can create and open an ObjectStore Active Toolkit OLE DB connection:

Set adoConnection = Server.CreateObject("ADODB.Connection")
Call adoConnection.Open("
      provider=ObjectStore Active Toolkit OLE DB Provider;
      data source=c:\odi\ATK6.0\Examples\demodbs\carsdemo.db")

Then you can open a RecordSet:

Set RS = Server.CreateObject("ADODB.RecordSet")
Call RS.Open("myDataView", adoConnection)

where myDataView is the name of a data view in the ATK metaknowledge of the current database, or a SQL query following the syntax accepted by ObjectStore Active Toolkit OLE DB. (For more information, see SQL Support in ATK OLE DB.)

Providing user and password values

ObjectStore Active Toolkit OLE DB can also obtain information about the user and password values to open the ObjectStore database. You can provide this kind of information in two ways:

Specifying the user and password when you open the OLE DB connection:

Call adoConnection.Open("
      provider=ObjectStore Active Toolkit OLE DB Provider;
      data source=c:\odi\ATK6.0\Examples\demodbs\carsdemo.db",
      "userName",
      "password")

Allowing ObjectStore Active Toolkit OLE DB to prompt the user each time it requires a user name and password.

Through the ADO interface, set the ADO connection Prompt variable adoConnection.Properties("Prompt") to a value different from adPromptNever.

Through the OLE DB interface, set the DBPROP_INIT_PROMPT property in OLE DB to a value different from DBPROMPT_NOPROMPT.

If ATK cannot get the user name and password information it requires (because it is not specified when the connection is opened and ATK is not enabled to prompt the user), an error condition is generated.

Accessing the ATK OLE DB Source Through ODBC

OLE DB providers and ODBC drivers provide similar services, making it possible to build a bridge between any OLE DB provider and ODBC.

Use ISG Navigator/Bridge

ISG Navigator/Bridge includes an ODBC driver for OLE DB data sources. It consumes OLE DB providers and allows ODBC applications to access any OLE DB data source. ISG Navigator/Bridge is distributed by ISG International Software Group at http://www.isgsoft.com, and by Microsoft Corporation at http://www.microsoft.com.

Download and install ISG Navigator/Bridge. Then you can configure it to access ObjectStore Active Toolkit OLE DB through any ODBC client. Refer to Chapter 5, Using Crystal Reports with ATK, in the ObjectStore Active Toolkit Tutorial for an example of configuring ISG Navigator/Bridge.

SQL Support in ATK OLE DB

ATK supports a subset of the SQL syntax. This SQL support lets you dynamically modify data views defined in Inspector by customizing the instance format and the filter, and by ordering definitions. ATK supports these statements:

SELECT
FROM
WHERE
INSERT INTO
UPDATE
DELETE FROM
ORDER BY DESC/ASC
ALIAS

Examples of supported syntax

Suppose you are using a data view called my_data_view, based on a collection of Customer instances:

Customer is a class containing name and address data members, as well as a relationship to the Vehicle class, implemented by the cars data member.
Vehicle is a class containing make and model data members and a reverse relationship to the class Customer called owner.

ATK accepts SQL statements such as these:


SQL Statement	Description
*SELECT FROM my_data_view**	This statement maintains the default settings of my_data_view.
SELECT name, address FROM my_data_view	This statement modifies the instance format used in my_data_view to include only the name and address of each customer.
*SELECT name, address FROM my_data_view WHERE name like `john'**	This statement modifies the instance format and filter defined in my_data_view. The resulting table contains only those customers whose names begin with john and also displays the customers' names and addresses. ATK translates the filter defined in the SQL statement into an ObjectStore query. This allows you to perform efficient ObjectStore queries through an SQL statement.
SELECT name, address FROM my_data_view ORDER BY name DESC	This SQL statement modifies the instance format and order defined in my_data_view. The resulting table shows the customers' names and addresses displayed alphabetically in descending name order.
SELECT name, cars#make FROM my_data_view	ATK maps the SQL column names to data member names. To navigate implicitly from one class to another, you can specify a column name containing the concatenation of the navigated data members separated by the # character. This statement lets you build a table based on the persistent collection used to define my_data_view. The table adopts the same filtering and ordering settings as the data view, but it contains a column that shows the customer's name and the makes of the cars the customer owns. In general, you can concatenate any number of relationships. For example, cars#owner#cars#model is a valid column name. This column identifies the models of the cars by navigating from car to owner and to customer.
SELECT name, cars#make, cars#model FROM my_data_view WHERE cars#make='Ford'	This SQL statement uses the navigated data members to build a new filter in my_data_view. As the filter is translated into a native ObjectStore query expression, the ObjectStore query executed by the above SQL statement iscars[: !strcmp(make,"Ford") :] This query is satisfied by any customer who owns at least one Ford car. This result is different from what you could expect reading the SQL expression.
SELECT name, {SELECT make, model FROM cars} FROM my_data_view	To build a table containing explicitly navigated relationships in the OLE DB provider, ObjectStore Active Toolkit OLE DB accepts nested SQL commands. (The ATK ActiveX server does not accept this syntax.) This query creates a table that contains the customer name column and the car column. For each name, the car column contains an OLE DB table, which contains the cars that the customer owns. That table contains two columns, the make and model of the cars.
SELECT Employee::salary FROM person_data_view	This SQL statement builds a table containing columns that are associated with data members which are defined in classes derived from the one associated with the dataview. Here, the person_data_view contains instances of the Person class, and the SQL command displays a column filled with the field salary defined in the Employee class which derives from Person.
INSERT INTO person_data_view (name, surname) VALUES ("Owen", "Foster")	This SQL statement inserts a row in the person_data_view table, creating the corresponding persistent object(s) in the process. In order to run this command, a create user-defined method (Person::oledb_create) must have been registered in Inspector, and the name and surname columns must be part of the arguments handled by the registered method.
UPDATE person_data_view SET name="Charles" WHERE name="Carlo"	This SQL statement updates all the rows (and hence, the corresponding persistent objects) whose name column contains the value "Carlo". In order to run this command, an update user-defined method (Person::oledb_update) must have been registered in Inspector, and the name column must be part of the arguments handled by the registered method.
DELETE FROM person_data_view WHERE name="Carlo"	To delete all the rows (and hence, the corresponding persistent objects) whose "name" column contains the value "Carlo". In order to run this command, a Person::oledb_delete "delete" user-defined method must have been registered from Inspector.

CRUD Operations in ATK OLE DB

You can use the Active Toolkit OLE DB interface to perform CRUD operations (create, read, update, and delete) on objects in an ObjectStore database. This section describes the ways you can implement CRUD operations using ATK.

Editing Objects and Relationships

Editing objects

ADO clients are written against OLE DB tables; an OLE DB table corresponds to a collection of objects in an ObjectStore database. Consider the following classes, CAuthor and CBook:

To create, update, or delete persistent instances of CAuthor from an ADO client, you would first retrieve a table of authors by execute a statement like this one:

 adoRS.OPEN(
      "SELECT m_FirstName, m_Books#m_ISBN FROM CAuthor")

The resulting table would look like this:

Next, the client would edit the table, for example, adding a new author:

 adoRS.AddNew "m_FirstName" , "Ugo"

The AddNew command calls a C++ user-defined method registered in Inspector, CAuthor::oledb_create, to create a new instance of CAuthor. You can learn more about the details of this method in Creating Objects.

Editing relationships

You can also edit relationships themselves. Note that the CAuthor and CBook classes are related, in ObjectStore, by the m_Books and m_Authors data members.

To edit the relationship between two objects - and not the objects themselves, as seen earlier - you would first execute a statement like the following in the ADO client:

 adoRS.OPEN(
      "SELECT m_FirstName, {SELECT m_ISBN FROM m_Books}
      FROM CAuthor")

Here is the resulting table:

Notice that the SELECT statement creates chaptered rowsets, that is, tables within tables. It is against these chaptered rowsets that you executing statements in order to edit a relationship.

Next, you locate the chaptered rowset for a given instance, in this case the first instance, for the author Owen.

 adoRS.MoveFirst

Next, you retrieve the chaptered rowset for the author:

SET owenBooks= adoRS("m_Books")

Finally, you execute the desired CRUD statement, such as adding a new book for that author:

owenBooks.AddNew "m_ISBN", "1-879239-06-06"

The result here is that a new book (identified by its ISBN) is added to the relationship identified by the selected author, in this case, Owen. As before, the AddNew command calls a C++ user-defined method registered in Inspector. This method requires a different signature because it is used to edit a relationship, not an object: CAuthor::oledb_create_relationship

Navigating relationships

If you are using ADO RecordSet methods to implicitly navigate relationships in order to perform create or update operations, you must specify the complete C++ path using # as the separation character. For example consider a relationship between Customer, Order, and Warehouse classes. You would represent the navigation between the Customer and Warehouse classes as Customer#Order#Warehouse.

Creating Objects

Method signature

You need to define and register in Inspector a create user-defined method for each class you want to persistently instantiate. The create method must have the following signature:

static PersistentClass* PersistentClass::oledb_create(

      os_database* aDB,
      const os_Dictionary<char*, void*>& args)

Using ADO

To create a row in an OLE DB table using ADO, use the RecordSet::AddNew method. Open a RecordSet based on the class for which you want to create a new instance.

The fields you specify in the AddNew method must be the same as (or a subset of) the data members exposed through the instance format of the data view corresponding to the RecordSet and the arguments specified in the registered oledb_create user-defined method.

Using SQL

To create a row in an OLE DB table using SQL, use the INSERT INTO command. Using SQL enables you to create a new object without explicitly opening a RecordSet. For example, to create a new instance of CCustomer, you could specify the following SQL command:

INSERT INTO CCustomer (m_FirstName, m_LastName)
VALUES ('Hank', 'Foster')

Interpreting class names

Active Toolkit is able to interpret class names as Inspector data views - even if no data view has been formally defined for that class in Inspector. It does this by implicitly creating a data view based on the class, using the default instance format representation for that class.

Reading Objects

Reading information about objects using a user-defined method does not require any special code regardless of whether you use ADO or SQL. The only requirements are that the user-defined method is registered in Inspector and that it is part of the instance format for the data view corresponding to the RecordSet.

Updating Objects

Method signature

You need to define and register in Inspector an update user-defined method for each class of any persistent instance you want to modify. The update method must have the following signature:

int PersistentClass::oledb_update(
      const os_Dictionary<char*, void*>& args)

Using ADO

To update a row in an OLE DB table using ADO, position on the row you want to modify and then use the RecordSet::Update method. The RecordSet you open must be based on a data view that contains the objects of a persistent class for which an
oledb_update user-defined method has been registered.

The fields you modify must be the same as (or a subset of) the data members exposed through the instance format of the data view corresponding to the RecordSet and the arguments specified in the registered oledb_update user-defined method.

Using SQL

To update a row in an OLE DB table using SQL, use the UPDATE command. Using SQL enables you to update an object without explicitly opening a RecordSet. For example, to update an instance of CCustomer, you could specify the following SQL command:

UPDATE CCustomer SET m_FirstName='Henry' 
WHERE m_FirstName='Hank' AND m_LastName='Foster'

Updating relationships

In order to update a relationship, you need to define and register an update user-defined method with the following signature:

int PersistentClass::oledb_create_relationship(
      const os_Dictionary<char*, void*>& args)

This method must be able to update a relationship of the PersistentClass on which it is defined. This method always receives an argument, oledb_relationship_name, which identifies the relationship that is to be updated. The other arguments received by oledb_create_relationship are the parameters specified by the user in the ADO command that invoked the method. These parameters must be sufficient to uniquely identify the other object participating in the relationship.

From ADO you use chaptered rowsets to represent relationships in OLE DB tables. You can invoke the oledb_create_relationship by calling the RecordSet::AddNew method on a RecordSet obtained by an OLE DB chaptered rowset.

For example, if you open a RecordSet with the SQL command

SELECT m_FirstName, {SELECT m_OrderID, m_OrderDescription
      FROM m_Orders}
FROM CCustomer

the second column identifies a chaptered rowset. This means that the fields of the RecordSet corresponding to the second column are RecordSet objects themselves; each contains a description of all the orders made by a customer.

If you call RecordSet::AddNew on the navigated RecordSet that contains the order information, ATK OLE DB tries to invoke CCustomer::oledb_create_relationship, specifying m_Orders in the oledb_relationship_name argument and passing the other arguments specified in the RecordSet::AddNew method.

Deleting Objects

Method signature

You need to define and register in Inspector a delete user-defined method for each class from which you want to delete a persistent instance. The delete method must have the following signature:

static int PersistentClass::oledb_delete(
      void* anObject)

Using ADO

To delete a row in an OLE DB table using ADO, position on the row you want to delete and then use the RecordSet::Delete method. The RecordSet you open must be based on a data view that contains the objects of a persistent class for which an
oledb_delete user-defined method has been registered.

Using SQL

To delete a row in an OLE DB table using SQL, use the DELETE command. Using SQL enables you to delete an object without explicitly opening a RecordSet. For example, to delete an instance of CCustomer, you could specify the following SQL command:

DELETE FROM CCustomer WHERE m_FirstName='Henry' 
AND m_LastName='Foster'

Deleting relationships

In order to delete a relationship, you need to define and register a delete user-defined method with the following signature:

int PersistentClass::oledb_delete_relationship(
      const os_Dictionary<char*, void*>& args)

This method must be able to delete a relationship of the PersistentClass on which it is defined. This method always receives an argument, oledb_relationship_name, which identifies the relationship that is to be deleted. The other arguments received by oledb_delete_relationship are provided by ATK - they are the intersection of all the chaptered rowset columns and the arguments registered in the oledb_delete_relationship method.

From ADO you use chaptered rowsets to represent relationships in OLE DB tables. You can invoke the oledb_delete_relationship by calling the RecordSet::Delete method on a RecordSet obtained by an OLE DB chaptered rowset.

For example, if you open a RecordSet with the SQL command

SELECT m_FirstName, {SELECT m_OrderID, m_OrderDescription
      FROM m_Orders}
FROM CCustomer

the second column identifies the chaptered rowset. This means that the fields of the RecordSet corresponding to the second column are RecordSet objects themselves; each contains a description of all the orders made by a customer.

If you position on order number 23, the myOrderedBook row of the navigated RecordSet, and then call RecordSet::Delete, ATK OLE DB tries to invoke CCustomer::oledb_delete_relationship, specifying m_Orders in the oledb_relationship_name argument, 23 in the m_OrderID argument, and myOrderedBook in the m_OrderDescription argument.

Retrieving Error Information

Using ADO

When an ADO method that accesses the ObjectStore Active Toolkit OLE DB provider causes an error, ADO returns a standard ADO error code or generates the error code #80041000. ObjectStore Active Toolkit OLE DB uses the error code to identify its internal error conditions. In either case, you can retrieve extended information about the generated error.

For example, this code attempts to open a database in an Active Server Page:

On Error Resume Next
Set adoConnection = Server.CreateObject("ADODB.Connection")
Call adoConnection.Open("provider=ObjectStore OLE DB Provider;data source=mydb.db","","")
if Err.Number <> 0 then
      Response.Write("Error# " & Hex(Err.Number) & "<BR>")
      Response.Write("Generated by: " & Err.Source & "<BR>")
      Response.Write("Description: " & Err.Description & "<BR>")
      Exit Sub
End If

If the database is not available, the Active Server Page generates this output:

Error# 80041000
Generated by: ObjectStore OLE DB Provider
Description: Could not open in MVCC mode the database 'mydb.db'!

You might find it useful to scan the Connection::Errors collection to retrieve multiple descriptions for the error. (Refer to Microsoft's ADO documentation for further information about the Connection::Errors collection.)

Using OLE DB

If you are accessing OLE DB directly (that is, without using the ADO layer), you can retrieve information about the errors generated by the ObjectStore Active Toolkit OLE DB provider. Refer to Microsoft's OLE DB documentation for details.

[previous] [next]

Updated: 05/11/99 11:35:03