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.
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.
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.
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.
The Berkeley DB SQL API provides extensions such as full text search and R-Tree index. To enable these extensions, do the following:
db_sql project.
db_sql project.
See the SQLite Documentation for more information on full text search and R-Tree.
This section describes the steps to build the JDBC driver.
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.
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:
db_sql_jdbc project and select properties.
JDBC2z instead of JDBC2x.
This section describes the steps to download, build, and run sample programs using the built JDBC driver.
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.
Before running the sample code, do the following:
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.
SQLite.JDBCDriver for myDriver.ClassName in the line: Class.forName("myDriver.ClassName");"myLogin", "myPassword". This is optional.
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.See Building the JDBC Driver for instructions about building JDBC driver.
To build and run the JDBC examples do the following:
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>
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>
Note: Some examples like AutoGenKeys are not yet supported by BDB JDBC driver. The SQLFeatureNotSupportedException is displayed for those unsupported examples.
This section describes the steps required to build the ODBC driver.
To configure your system prior to building the ODBC driver, do the following:
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.
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.
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.
The steps to verify that the installed driver works are as follows:
SQLite3 Datasource and click OK.CREATE TABLE t1(x); in the Statement text box and click OK.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.
SQLite-1.0.66.0-binaries.zip.
The .NET binary, System.Data.SQLite.dll, is located in the ManagedOnly directory.
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.libdb_sql51.dll to sqlite3.dll.
Place sqlite3.dll in the same directory as System.Data.SQLite.dll.bin directory.
This section describes how to run the test suite made by phxsoftware.
libdb_sql51.dll and libdb51.dll into the ManagedOnly directory.libdb_sql51.dll to sqlite3.dll.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.