ODBC databases

ODBC (Open Database Connectivity) provides the facility to use other database types, such as Oracle, within Sculptor programs.

A Sculptor program can use any database for which a suitable ODBC driver is available. An ODBC level 1 driver is normally sufficient; if the driver does not support a key feature, Sculptor generates a run time error. An ODBC driver is available for use with Sculptor keyed files – this enables embedded SQL commands to be used on a Sculptor database.

Different proprietary databases and Sculptor keyed files can be mixed in the same program.

Traditional Sculptor commands and embedded SQL commands can be used on a database that is being accessed through ODBC. Sculptor works with SQL databases by mapping the Sculptor file access commands to SQL statements and submitting these to the SQL database through ODBC. Also, the Sculptor sql command can be used to submit a general SQL query and fetch results. These access methods can be used in the same program.

Each proprietary SQL database has its own design concept and implementation. Behavioural differences between these are inevitable. Sculptor has been coded to minimise these as much as possible. If an issue arises with a particular database or with just one file, consider the possibility that a specific limitation has been encountered. For example, the field name Int4 is accepted by most SQL databases, but in MySQL it is a reserved word and generates a syntax error.

Overall performance with SQL databases is not as good as with Sculptor keyed files, but performance can be improved by making small program changes. See Optimising performance. Sculptor 5.6 introduced substantial performance and functionality improvements to Sculptor’s ODBC interface. Sculptor 5.6 programs must use SQL Server 2000 or later.

Note that in these help files the terms in bold in the table below are synonymous with the SQL terms listed alongside:

SQL

table

file

row

record

column

field

For examples of programs using ODBC see the directory $SCULPTOR/demo/odbc.