SqlBase

SqlBase

How to use OLEDB to access SQLBase from SQLServer

Some sites running MS SQLServer may wish they had used SQLBase from the start !
They can however access the data held in a SQLBase database
directly from SQLServer via a 'Linked Server' and a Gupta OLEDB driver called SQLBaseOLEDB.dll ,
found in the SQLBase run-time folder.

The 'bitness' of both SQLServer , SQLBase and the Driver must all be the same i.e. 64 or 32 bit.
If you are runing SQLBase 12 , the minimum version you can use is 12.0.1 for this to work .
Once you have created the SQLServer 'Linked Server' , then granted rights to it , you can
Select , Update,Insert or Delete SQLBase data directly from SQLServer ( e.g. from SS Management Studio ).

The syntax of the 'Select' differs from Inserts , Updates etc.

Download the script to see a woring example running against 'ISLAND' database:
1) Create a Linked Server
2) Create login rights to SQLBase
3) Sample DML using the new Linked Server

Sample can be downloaded here:

SQLServer to SQLBase via OLEDB.zip

How to determine the column types of a table

Use this SQL statement to determine the defined columns and their types:

  select (*) FROM SYSCOLUMNS

The fast way counting rows in SQLBase tables

^{(From CenturaPro march 1998)}

For those situations where you need to know how many rows are in a table, everyone knows the standard technique:

SELECT COUNT(*) FROM <table name>

Many developers are also aware of the following optimization:
If the table has an indexed column, then some databases, including SQLBase, can do a faster row count by
counting the index entries on the indexed column:

SELECT COUNT( <indexed column name> )
FROM <table name>

But a little-known feature of SQLBase is the ROWCOUNT statement, which is optimized for returning the number of rows
in a table and significantly faster than either of the previous two techniques. The usage is simple:

ROWCOUNT <table name>

From within SAL you can use the ROWCOUNT statement as shown in the following example:

   If SqlPrepareAndExecute( hSql,"ROWCOUNT PRODUCTS" )
      Call SqlGetModifiedRows( hSql, nRowCount )