The ODBC Open Server Plug-in
============================

Registration
------------

The ODBC Open Server plug-in requires a plug-in specific registration key for use with a registered Open Server.

System requirements 

The ODBC plug-in requires that an ODBC driver manager be installed along with any ODBC drivers that you may want to use.

Unix requirements:

For use on Unix platforms, the iODBC driver manager must be installed.

Limitations
-----------

The Open Server supports data access only, it doesn't directly support data definition. You can work around this by using the command 'dbms query("execute", "<your statement>")' to send the statements directly to the ODBC server with out processing.

The ODBC Open Server plug-in will not convert PrimeBase outer join syntax into ODBC outer join syntax.

ODBC Specific considerations

When a client opens a session with an Open Server running the ODBC Open Server plug-in, the session is initialized but no connection is made to an ODBC data source. A connection to an ODBC data source is made when a database is opened. When opening a database the database name will be interpreted as an ODBC DSN (Data Source Name) and used in the call to the ODBC driver manager to load the correct ODBC driver and connect to the data source.

Because of the differences between ODBC drivers and the data sources that they support there is no clear set of standards that can be used to ensure that a PrimeBase client will work with any given ODBC data source. For the best results, limit your SQL statements to those directly supported by the ODBC core level syntax and do as much work on the client side as possible.  Avoid data conversion on the server side for example doing a comparison to a string where the string is to be interpreted as a date.


Custom plug-in commands
-----------------------

The ODBC Plug-in supports the following custom commands that you can use to work around some of the differences between PrimeBase syntax and ODBC syntax.

DBMS QUERY("execute", "<ODBC command>");

Executes the string "<ODBC command>" in the ODBC data source. The string is not parsed by the PrimeBase Virtual Machine so it can contain ODBC specific syntax. The command returns no data.

Example: DBMS QUERY("execute", "DROP TABLE  foo");

---

DBMS QUERY("select", "<ODBC select>") [INTO <cursor> [FOR EXTRACT]];

Executes the string "<ODBC select>" in the ODBC data source. The string is not parsed by the PrimeBase Virtual Machine so it can contain ODBC specific syntax. The command returns cursor as if a normal select had been executed.

Example: DBMS QUERY("execute", "select * from foo where UPPER(foo.name) = 'PRIMEBASE'");

---

DBMS QUERY("update", "<ODBC update or insert>") ;

Executes the string "<Oracle update or insert>" in the ODBC data source. The string is not parsed by the PrimeBase Virtual Machine so it can contain ODBC specific syntax. The command sets the $rowseffected flag.

Example: DBMS QUERY("execute", "update foo set quality = "VeryGood" where UPPER(foo.name) = 'PRIMEBASE'");

---

SET SCHEMA("<name>");

By default the schema name is assumed to be the same as the user's login name when not specified in a query. You can use this command to set the default schema name to something other than the user's login name. For example if you logged in as 'bob' and you executed the following select: "select * from foo" it would be the equivalent of executing: "select * from bob.foo". If you execute the command "SET SCHEMA('Sally');" then "select * from foo" would default to "select * from Sally.foo".


DEBUG SETTINGS
--------------

The following can be used to turn on/off tracing on the open server. When tracing is turned on the statements sent to the open server to be executed will be displayed on the Open Server's console.

set tracing_on()

set tracing_off()