SqlBase

From Team Developer SqlWindows Wiki
Jump to: navigation, search

SqlBase


Contents



Pointer2.png How to use OLEDB to access SQLBase from SQLServer Pointer.png

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:

Down.png SQLServer to SQLBase via OLEDB.zip


Pointer2.png How to determine the column types of a table Pointer.png

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


  select (*) FROM SYSCOLUMNS


Pointer2.png The fast way counting rows in SQLBase tables Pointer.png

(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 )