The Sambar Server database scripting commands, and more extensive CScript manipulation language include easy-to-use RDBMS interfaces.

ODBC Datasources
You must have ODBC installed prior to enabling the Sambar Server DBMS interface. The Sambar Server will fail to start if ODBC32 is not available on your machine. In addition, all remote databases you wish to connect with via the Sambar Server DBMS scripting and CScript interfaces must have an ODBC datasource configured. The interactive ODBC utility iodbc is provided to verify that your ODBC32 datasources are properly configured.

In addition to being able to access remote databases (MySQL, Oracle, SQL Server etc.), the Sambar Server allows the creation and management of personal databases. The server uses an embedded database system (A HREF="sqlite/sqlite.htm">SQLite) which makes it very easy for users to create new databases since there is no need to first setup a database server. This is similar to dBase or MS Access databases. The internal-sqlite datasource allows you to prototype RDBMS applications or deploy small applications where there is no need for a full-featured RDBMS. SQLite supports a large subset of the SQL92 language along with many other useful features such as transactions and indices.

Enable DBMS
The first step in using the Sambar Server database interfaces is to enable the Sambar Server database library. The Enable DBMS configuration parameter (config/config.ini) instructs the Sambar Server to load the ODBC interfaces and prepare communication with the server found in the config/dbconfig.ini file.

When database usage is enabled, the Sambar Server contacts each server specified in the config/dbconfig.ini file to ensure they are available Connection information is then cached for future access to the servers.

System DSNs must be used rather than ODBC user-defined DSNs when the Sambar Server is run as a service. Services do not normally have access to user information; consequently, a System DSN must be created as opposed to a ODBC user-defined DSN. By defining an ODBC System DSN the information becomes accessible to any application on the local machine, not just the applications that the current user is aware of.

Connection Caching
The Sambar Server Database interfaces cache connections for faster execution of scripts and better scalability. A connection cache maintains a pool of available connections that the Sambar Server components can use to interact with data servers. You must configure connection caches for the specific user/database combinations used by your components. A connection cache improves performance by eliminating the overhead associated with setting up a connection when one is required. To prevent connections to remain open for prolonged periods of inactivity, the Maximum Idle Period can be configured for each cache. This period specifies the longest time (in minutes) that a connection should remain idle before being closed. By default this period is set to 10 minutes.

All database scripts must reference a database server by its cache name (config/dbconfig.ini). The minimun and maximum database connections can be configured for each cache, along with the data source/username/password for the ODBC connection.

Transactions
The database interface sets all connection handles in AUTOCOMMIT OFF mode. This means that all transaction-oriented statements (INSERT, UPDATE, DELETE) must be executed within explicit transaction statements (begin transaction/commit transaction).

dbconfig.ini Format
The config/dbconfig.ini file contains the description and configuration of each database that is available for use in the Sambar Server. Each section (delimited by brackets ( [ ] ) specifies a single database cache. A given data source can be duplicated in multiple cache definitions as long as each is cache section is named uniquely. This can be useful in debugging database activity using the Connection Status interfaces described below.

Cache names may not have spaces and must be limited to alph-numeric characters. Each cache section has a number of configuration parameters including data source, username, password, minimum and maximum connection count.

A special parameter "Single Thread" is provided for ODBC drivers that are not thread-safe. The Sambar Server is a multi-threaded server that will attempt to simultaneously query the database server on behalf of multiple users (each using a separate connection). Most ODBC-capable server libraries are thread-safe and allow this type of usage, however, if the server you require does not all for this, the "Single Thread" flag can be set to true to indicate that only a single action at a time should be executed (on the specified cache/data source).

ImportantThe current implementation of many ODBC database drivers are not thread safe. The following ODBC drivers should have the Single Thread configuration parameter set to true: dBase, FoxPro, Microsoft Access, and many Intersolve drivers.

Tf first 32-bit Oracle7 ODBC driver to support multi-threading is Version 2.0. Using Oracle's 32-bit Version 1.x drivers with the Sambar Server may cause problems with thread exceptions. Multi-threaded and thread-safe Oracle ODBC drivers (32-bit Version 2.x) only work with Oracle RDBMS Version 7.3.x or later client libraries and 7.3 or later RDBMS.

Sample Configuration
After setting up your ODBC datasource using the Control Panel using a System DNS, setup the dbconfig.ini as follows:

[mydb]
Description = My Sample Database
Datasource = MyDB
Username = admin
Password = admin
Maximum Column Length = 8192
Maximum Used Connections = 10
Minimum Used Connections = 1
Maximum Idle Period = 10
Single Thread = true
Trace SQL = false
Trace Performance = false
Administrator Only = false


The Datasource value must be the same as the DataSourceName in the ODBC32 setup. (Make sure to properly configure the username and password for the server).

Connection Status
The built-in showdb call can be used by the system administrator to derive the status of each connection cache, including:

• The database "type" if known
• How many open connections are in used or idle
• How many statements have been executed
• How many errors have been caught
• The currently configured cache parameters (data source, username etc).
• Status of the DBMS cache (enabled or disabled).
• Ability to disable or enable the DBMS cache.

ODBC Usage
With the exception of the embedded internal-sqlite datasource, the Sambar Server uses ODBC (Open Database Connectivity API) exclusively for communicating with database servers. You must configure your ODBC data source using Microsoft's ODBC Administrator, supplied with Windows 95/98/NT. This program allows you to setup data sources, install ODBC drivers, and configure various options.

ODBC Windows NT Datasources
When configuring ODBC data sources under Windows NT, you may need to configure them as "System" data sources. A system data source allows any process on the local machine to connect to it. Depending on how the Sambar Server is configured (i.e. running as a service as LocalSystem rather than as a user), it may not have access to the the "normal" ODBC data sources.

Check ODBC Traces
You can use ODBC tracing to get more information when ODBC connectivty fails (or for debugging SQL applications). To turn on tracing, run the Microsoft ODBC Admisitrator and select the "Tracing" tab. You can specify a file where traces are logged and the duration of tracing. The sequence of ODBC calles and return codes is logged to the specified trace file. Error descriptions can be found in the trace of SQLError calls.

internal-sqlite Datasources
The Sambar Server includes and personal embedded database (SQLite) that is accessible by simply defining a datasource type of internal-sqlite in the config/dbconfig.ini:

[testdb]
Description = My Sample Database
Datasource = internal-sqlite
Username = ignored
Password = ignored
Maximum Column Length = 8192
Maximum Used Connections = 10
Minimum Used Connections = 1
Maximum Idle Period = 10
Single Thread = false
Trace SQL = false
Trace Performance = false
Administrator Only = false


A SQLite DBMS will be automatically created in the dbms directory within the Sambar Server installation directory. The DBMS filename corresponds with the cache name (testdb in the above example.) The Username, Password and Single Thread parameters are ignored when the embedded RDBMS is used.

Check dbms.log & server.log
Always check for additional error messages in the log/dbms.log and log/server.log files.

Maximum Column Length
The current implementation limits the maximum database column length (converted to character string) to 8192 bytes by default. This parameter is configurable via the DBMS configuration parameters in the config/dbconfig.ini.

Maximum Rows
The maximum number of rows returned for a given datasource is unlimited by default for ODBC datasources, and limited to 1000 rows by default for the internal-sqlite datasource; using this parameter the maximum rows returned can be restricted as desired. This parameter is configurable via the DBMS configuration parameters in the config/dbconfig.ini.