New ODBC Wrapper Via ODBCEXDI for Cincom® ObjectStudio®
Why we do it?
The following thoughts are behind the new ODBC Wrapper Via ODBCEXDI:
- To create a simpler ODBC Wrapper.
Most of the ObjectStudio users use the APIs at the database level, currently in the existing ODBC Wrapper. ObjectStudio also has its own connection- and statement-level APIs that talk to ODBC libraries directly. This makes the ODBC Wrapper more complicated and hard to use. In the new ODBC Wrapper, the ObjectStudio version of connection- and statement-level APIs are removed, and the database-level APIs will go through VisualWorks’ ODBCEXDI to get their jobs done.
In the new ODBC Wrapper Via ODBCEXDI, the following ObjectStudio classes and related APIs no longer exist:
ODBCBlob, ODBCColumn, ODBCConnection, ODBCDataSource, ODBCDatatype, ODBCDemoController, ODBCDriver, ODBCStatement, ODBCThreadedConnection, SAGSqlColumnDescriptor, SAGSqlConnection, SAGSqlError, SAGSqlStatement.
Therefore, the new ODBC Wrapper Via ODBCEXDI is much simpler and easier to use.
This change also eliminates the need for double implementation for both ObjectStudio and Cincom® VisualWorks® every time we need to add new features. If we decide to add any new ODBC features to the VisualWorks side, we can take advantage of it easily from the ObjectStudio side.
- To achieve architectural consistency across all database wrappers.
In previous ObjectStudio versions, all database wrappers except the ODBC Wrapper go through VisualWorks EXDIs to interact with the database client libraries. This new ODBC Wrapper Via ODBCEXDI is based upon the same architecture.
- To make it easier to use APIs from both ObjectStudio and VisualWorks.
In the existing ODBC Wrapper, it is not easy to combine the usage of the ODBC-related APIs from both the ObjectStudio and VisualWorks sides, and sometimes this combined usage is necessary in order to get better performance. The new ODBC Wrapper Via ODBCEXDI makes it possible and easy to do so. For example, from an ObjectStudio database instance, you can easily get access to its VisualWorks ODBCConnection object and create new VisualWorks ODBCSession objects from the connection. Then, the VisualWorks connection- and session-level APIs become handy to you.
Who should use this new ODBC Wrapper Via ODBCEXDI?
Users only using the database-level APIs (e.g., ODBCDatabase) are the right people to switch to this new ODBC Wrapper Via ODBCEXDI. If you are also using the connection- and statement-level APIs (e.g., ODBCConnection and ODBCStatement) and you are not willing to make changes, you have to stick to the existing ODBC Wrapper.
What are our plans for the existing ODBC Wrapper and the new ODBC Wrapper Via ODBCEXDI?
From ObjectStudio 8.5, both the existing ODBC Wrapper and the new ODBC Wrapper Via ODBCEXDI will become loadable applications, but they can’t co-exist in the same image; you have to use one of them, not both, at the same time.
We are planning to support both for a while, and gradually the existing ODBC Wrapper supporting both database- and connection-level APIs will be phased out. Therefore, users of it are encouraged to switch to the new ODBC Wrapper Via ODBCEXDI if possible.
An example of using APIs from both ObjectStudio and VisualWorks
The following example demonstrates the mixed usage of APIs from both ObjectStudio and VisualWorks. From the example, you can see how a VisualWorks ODBCSession can be prepared once and executed multiple times to achieve better performance.
“Logon to the database server.”
ODBCDatabase logOnServer: #ODBCDSN user: #username password: #password.
“Get access to the database needed.”
db := ODBCDatabase accessName: #ODBCDSN.
“Drop the test table if it existed.”
db execSql: 'DROP TABLE TESTTABLE'.
“Create a test table.”
db execSql: 'CREATE TABLE TESTTABLE(cid int ,name varchar(100))'."Set the number of records being inserted."loopCount := 10.
“The SQL used to insert test data.”
sql := 'INSERT INTO TESTTABLE VALUES (?, ?)'.
“Insert: some test data.”
1 to: loopCount do: [ :i|db execSql: sql vars: (Array with: i with: ('test' + i asString)).].
“Verify the inserted data.”
res := db execSql: 'SELECT * FROM TESTTABLE'.
“The following code shows how to re-use a VisualWorks ODBCSession to improve performance.”
“Get the VisualWorks ODBCConnection in the database instance.”
conn := db activeConnection.
“Get a VisualWorks ODBCSession.”
aSession := conn getSession.
“SQL statement used for retrieval”
sqlString := 'SELECT * FROM TESTTABLE WHERE cid = ?'.
“Prepare the SQL statement once”
aSession prepare: sqlString.
“Bind different values to get different records. This will save the preparing time.”
“Create an execution block using the prepared ODBCSession.”
execBlock := [ :val |"Bind the argument and retrieve the record."aSession bindInput: (Array with: val).aSession execute.ans := aSession answer.res := ans upToEnd inspect.aSession answer.].
“Bind the first value and inspect the retrieved record.”
execBlock value: 1.
“Bind the second value and inspect the retrieved record.”
execBlock value: 2.
“Bind the second value and inspect the retrieved record.”
execBlock value: 3.