Building the SQL API
Prev | Chapter 4. Building Berkeley DB for Windows | Next

## Building the SQL API

SQL support is built as part of the default build on Windows. For information on the build instructions, see Building Berkeley DB for Windows .

The SQL library is built as `libdb_sql51.dll` in the Release mode or `libdb_sql51d.dll` in the Debug mode. An SQL command line interpreter called `dbsql.exe` is also built.

### Binary Compatibility With SQLite

`libdb_sql51.dll` is compatible with `sqlite3.dll`. You can copy `libdb_sql51.dll` to `sqlite3.dll` and `dbsql.exe` to `sqlite3.exe`, and use these applications as a replacement for the standard SQLite binaries with the same names. However, if you want to do this, then any legacy data in use by those tools must be migrated from the standard SQLite database to a Berkeley DB SQL database before you replace the standard SQLite dll and executable with the Berkeley DB equivalent. For information on migrating data from standard SQLite databases to a Berkeley DB SQL database, see the Berkeley DB Getting Started with the SQL APIs guide.

### Warning

Rename your dlls and executables to the standard SQLite names with extreme care. Doing this will cause all existing tools to break that currently have data stored in a standard SQLite database.

For best results, rename your dlls and command line tool to use the standard SQLite names only if you know there are no other tools on your production platform that rely on standard SQLite.

### Setting Preprocessor Flags

By default, Berkeley DB SQL generates each table as a subdatabase in a single file. To generate each table in a separate file, specify BDBSQL_FILE_PER_TABLE in Preprocessor Definitions of the `db_sql` project.

When this option is enabled, the SQL database name is used as a directory name. This directory contains one file for the metadata and one file each for every table created by the SQL API. Do not add or delete files from the database directory. Adding or deleting files may corrupt the database. To backup just the metadata (schema), make a copy of the `metadata` and `table00001` files from the database directory. Make a new copy whenever the schema is changed.

### Enabling Extensions

The Berkeley DB SQL API provides extensions such as full text search and R-Tree index. To enable these extensions, do the following:

1. Open the Berkeley DB solution in Visual Studio.
2. Specify SQLITE_ENABLE_FTS3 or SQLITE_ENABLE_RTREE in Preprocessor Definitions of the `db_sql` project.
3. Re-build the `db_sql` project.

See the SQLite Documentation for more information on full text search and R-Tree.

### Building the JDBC Driver

This section describes the steps to build the JDBC driver.

1. Configure your build environment. For information on how to configure to build Java applications, see Building the Java API.
2. Build the SQL project in Debug mode.
3. Open Visual Studio.
4. Select File -> Add -> Existing Project.
5. Select `build_windows/db_sql_jdbc.vcproj` and add it to the Berkeley_DB solution. This adds the `db_sql_jdbc` Visual Studio project to the Berkeley_DB solution file.
6. Build the `db_sql_jdbc` project in Visual Studio.

You can test the build by entering the following commands from the `db\build_windows\Win32\Debug` directory:

javac -cp ".;jdbc.jar" -d . ..\..\..\sql\jdbc\test3.java
java -cp ".;jdbc.jar" test3

When building the JDBC driver, if you may see an error message: "SQLite.JDBC2x.JDBCConnection is not abstract and does not override abstract method in java.sql.Connection".

This means that your Java environment requires `JDBC2z.*` instead of `JDBC2x.*`. To resolve this problem, do the following:

- In the Solution Explorer, right-click the `db_sql_jdbc` project and select properties.
- In the Configuration Properties -> Build Events -> Pre-Build Event section, alter the command to refer to `JDBC2z` instead of `JDBC2x`.

### Using the JDBC Driver

This section describes the steps to download, build, and run sample programs using the built JDBC driver.

#### Downloading JDBC Sample Code

The download link for JDBC sample code is available on the Oracle Sun Developer Network (SDN). You can identify the link by the "JDBC programming examples from all three editions (ZIP format)" text beside it.

#### Modifying Sample Code

Before running the sample code, do the following:

1. Unzip the file containing the sample code to a new directory (for example, jdbc_ex).
2. Substitute `jdbc:sqlite:/<db-file-name>` for the generic JDBC URL that appears in the code. That is, put `jdbc:sqlite:/<db-file-name>` between the quotation marks in the line:

   `String url = "jdbc:mySubprotocol:myDataSource";`

   Note: The <db-file-name> can either be an absolute path name like `"D:\\jdbc_ex_db\\myDataSource"`, or a relative path-file-name like `"..\\jdbc_ex_db\myDataSource"`, or a file name, like `"myDataSource"`, in which the database file will be stored at the current directory.

3. Substitute `SQLite.JDBCDriver` for `myDriver.ClassName` in the line: `Class.forName("myDriver.ClassName");`
4. Substitute the username and password you use for your database in the following: `"myLogin", "myPassword"`.

   This is optional.

5. If your JDK version is above 1.5, change the variable name `enum` in `OutputApplet.java` to some other variable name because, as of JDK release 5 `enum` is a keyword and can not be used as an identifier.

#### Building and Running the JDBC Sample code

See Building the JDBC Driver for instructions about building JDBC driver.

To build and run the JDBC examples do the following:

1. In the `db\build_windows\Win32\Debug` directory, run following commands:

           $ javac -classpath ".;jdbc.jar" -d . \path\to\jdbc_ex\*.java
           $ java  -classpath ".;jdbc.jar" <ClassName, eg. CreateCoffees>

2. After you run the CreateCoffees example, use the `dbsql` executable to open the `myDataSource` database file and check if the table `COFFEES` has been successfully created in the database.

           $ dbsql myDataSourcedbsql> .tables
           COFFEES
           dbsql> .dump
           PRAGMA foreign_keys=OFF;
           BEGIN TRANSACTION;
           CREATE TABLE COFFEES (COF_NAME varchar(32),/
           SUP_ID int, PRICE float, SALES int, TOTAL int);
           COMMIT;
           dbsql>

3. Repeat step 2 to run other examples.

   Note: Some examples like AutoGenKeys are not yet supported by BDB JDBC driver. The `SQLFeatureNotSupportedException` is displayed for those unsupported examples.

### Building the ODBC Driver

This section describes the steps required to build the ODBC driver.

#### Configuring Your System

To configure your system prior to building the ODBC driver, do the following:

1. Download and install the latest SQLite ODBC driver Windows installer package for 32 bit Windows or 64 bit Windows.
2. Download and install the latest Microsoft Data Access Components (MDAC) SDK. The MDAC SDK is only required for testing the installation.

#### Building the Library

1. Build the SQL project in Release mode. See Building the SQL API.
2. Open Visual Studio.
3. Load the Berkeley_DB solution file into Visual Studio.
4. Set the build target to Release
5. Build the solution.
6. Select File -> Add -> Existing Project.
7. Select `build_windows/db_sql_odbc.vcproj` and add it to the Berkeley_DB solution. This adds the `db_sql_odbc` Visual Studio project to the Berkeley_DB solution file.
8. Build the `db_sql_odbc` project. This can be done by right-clicking the `db_sql_odbc` project in the project explorer panel, and selecting `build`.

The `sqlite3odbc.dll`, `libdb_sql51.dll` and `libdb51.dll` files are now built.

#### Installing the Library

Copy the dll files built in the Building the Library section to the Windows system folder.

The Windows system folder is different on different systems, but is often `C:\WINDOWS\System32`.

#### Testing the ODBC Install

The steps to verify that the installed driver works are as follows:

1. Open the Unicode ODBCTest application. On Windows XP: Windows start -> Microsoft Data Access SDK 2.8 -> ODBCTest (Unicode, x86).
2. Select the Conn -> Full Connect... menu item.
3. Select `SQLite3 Datasource` and click `OK`.
4. Select the Stmt -> SQLExecDirect... menu item.
5. Enter `CREATE TABLE t1(x);` in the `Statement` text box and click `OK`.
6. Verify that no error messages were output to the error window.

### Using the ADO.NET Driver

This section describes the steps to use the ADO.NET driver made by phxsoftware. The Berkeley DB development team has tested version 1.0.66.0 of the ADO.NET driver.

1. Download and unzip `SQLite-1.0.66.0-binaries.zip`. The .NET binary, `System.Data.SQLite.dll`, is located in the `ManagedOnly` directory.
2. Build the Berkeley DB SQL API. For more information, see Building the SQL API. If you have installed the Windows binaries, you can ignore this step.
3. Copy `System.Data.SQLite.dll and libdb51.dll` into a directory. If you have installed the Windows binaries, copy `System.Data.SQLite.dll` into the `bin` directory of the installation.
4. Copy `libdb_sql51.dll` to `sqlite3.dll`. Place `sqlite3.dll` in the same directory as `System.Data.SQLite.dll`.
5. Add this directory to the environment variable PATH. Alternatively, you can copy the dlls into the directory from which the applications is run. If you have installed binaries, the environment variable PATH is already set to include the installation's `bin` directory.

#### Running the ADO.NET Test Suite

This section describes how to run the test suite made by phxsoftware.

1. Download the driver and build Berkeley DB as described above.
2. Unzip and copy `libdb_sql51.dll` and `libdb51.dll` into the `ManagedOnly` directory.
3. Copy `libdb_sql51.dll` to `sqlite3.dll`.
4. Run `test.exe` from the `ManagedOnly` directory.

When running the test suite, exclude `LockTest` and `MultithreadingTest` due to differences between SQLite locking and Berkeley DB locking. For more information, see the Berkeley DB SQL Guide. You should also exclude `FunctionWithCollation` and `FunctionWithCollation2` tests because they use extension functions which are not a part of the default SQLite distribution. For more information, see http://www.sqlite.org/contrib.

Prev | Up | Next
Building the C# API | Home | Building the Tcl API