How Can You Use the Cincom® VisualWorks® EXDIs from Cincom® ObjectStudio®?

In ObjectStudio, all of the database wrappers (Oracle, ODBC, DB2 and Sybase) are built upon VisualWorks EXDIs.  However, in ObjectStudio, some EXDIs like the ones for PostgreSQL, MySQL and SQLite, do not have corresponding wrappers. Is it possible for ObjectStudio users to still use them? The answer is YES.

In this article, we’ll demonstrate how users can implement VisualWorks database connect directly from ObjectStudio and discuss the advantages of doing so.

Let’s start with four benefits of using VisualWorks EXDIs directly from ObjectStudio.

1. ObjectStudio users can handle the databases that ObjectStudio doesn’t have a wrapper for, such as PostgreSQL, MySQL and SQLite.
2. Since ObjectStudio’s database wrappers are built upon VisualWorks EXDIs, some data conversions are inevitable, and they are achieved at the cost of performance.  In other words, using VisualWorks EXDIs directly will give users better performance.
3. ObjectStudio handles database objects at database, table and cursor levels, while VisualWorks does so at connection, session and cursor levels. Users are able to choose one way or the other to do their jobs.
4. Finally, by using VisualWorks EXDIs directly, users should be able to obtain session objects more easily and reuse them to further improve performance. Doing so will avoid some of the limitations of using database wrappers in ObjectStudio.  For example, in ObjectStudio, processing an SQL statement normally requires a dedicated session. If users want to reuse it, they have to get the embedded session out and use it separately.

In the following code examples, we’ll demonstrate how the same kind of jobs can be done by using either ObjectStudio ODBC wrapper or VisualWorks ODBCEXDI from ObjectStudio environment. Of course, similarly, users are able to make use of other database connect packages from VisualWorks, especially the ones that do not have corresponding wrappers in ObjectStudio, such as PostgreSQL, MySQL and SQLite databases.

"An example using ObjectStudio ODBC wrapper."


"Connect to an SQL Server database."

db  := ODBCDatabase logOnServer: #'aSQLServerDSN' user: #'username' password: #'password'.


"Drop the test table if it existed."

db execSql: 'drop table testtable'.


"Create a test table."

res := db execSql: 'create table testtable (cid int, cname varchar(50))'.


"Insert test data."

sqlString :=  'insert into testtable values (?,?)'.

1 to: 6 do: [:i|

        res := db execSql: sqlString vars: (Array with: i with: ('test', i printString)).

].


"Retrieve the test data from server."

resultTable := db execSql: 'select * from testtable '.



"An example using VisualWorks ODBCEXDI from ObjectStudio."



"Connect to an SQL Server database."

conn := Database.ODBCConnection new.

conn username: 'username';

password: 'password';

environment: 'aSQLServerDSN'.

conn connect.


"Get an ODBCSession."

aSession := conn getSession.



"Drop the test table if it existed."

aSession prepare: 'drop table testtable'.

aSession execute.

ans := aSession answer.

aSession answer.


"Create a test table."

aSession prepare: 'create table testtable (cid int, cname varchar(50))'.

aSession execute.

ans := aSession answer.

aSession answer.


"Insert test data."

"First, prepare the SQL statement for insertion."

aSession prepare: 'insert into testtable values (?,?)'.


"Then bind rows of similar data and execute."

1 to: 6 do: [:i|

        aSession bindInput: (Array with: i with: ('test', i printString)).

        aSession execute.

        ans := aSession answer.

        aSession answer.

].


"Retrieve the test data from the server."

aSession prepare: 'select * from testtable '.

aSession execute.

ans := aSession answer.

resultDataArray := ans upToEnd.

From the code examples above, you can see that to get the same job done, the usages of ObjectStudio wrapper and VisualWorks EXDI are different.  ObjectStudio’s method seems to be simple and easy. However, since the database wrappers are built on VisualWorks EXDIs, it is less efficient in terms of performance.  VisualWorks’ method is more complicated, but more powerful and flexible since users can obtain the connection and session objects easily while taking advantage of all their features.

The code examples also illustrate how a session can prepare an SQL statement once and reuse it to handle different sets of similar data.  Certainly, users can choose other features, like array binding, to accomplish the same task.

In summary, since ObjectStudio and VisualWorks share the same foundation, and ObjectStudio database wrappers are built upon VisualWorks EXDIs, ObjectStudio users have access to not only the functionality of ObjectStudio database wrappers, but also those of VisualWorks EXDI. Therefore, users have the opportunity to choose the appropriate features from both to meet their development needs.