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_sql62.dll in the Release mode
or libdb_sql62d.dll in the Debug mode.
An SQL command line interpreter called
dbsql.exe is also built.
To enable user authentication in the Berkeley DB SQL API,
specify BDBSQL_USER_AUTHENTICATION and
SQLITE_HAS_CODEC in
Preprocessor Definitions of the db_sql
and the db_sql_shell project.
To enable key-store based user authentication in the Berkeley
DB SQL API, specify BDBSQL_USER_AUTHENTICATION_KEYSTORE
and SQLITE_HAS_CODEC in Preprocessor Definitions
of the db_sql and the
db_sql_shell project.
For more information on user authentication and key-store based user authentication, see Berkeley DB Getting Started with the SQL APIs.
libdb_sql62.dll is compatible with
sqlite3.dll. You can copy
libdb_sql62.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.
To enable encryption in the Berkeley DB SQL API,
specify SQLITE_HAS_CODEC in
Preprocessor Definitions of
the db_sql project. See
SQL Encryption in the
Berkeley DB Getting Started with the SQL APIs guide for
usage details.
There are several configuration options you can add
to the Preprocessor Definitions
of the db_sql project.
For example, to enable the option
BDBSQL_FILE_PER_TABLE, add
BDBSQL_FILE_PER_TABLE=1; to the
Preprocessor Definitions of
the db_sql project.
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.
You can disable checksums in log records so as to provide a boost to database performance. However, this comes at the risk of having undetectable log file corruption that could prevent data recovery in the event of database corruption.
Note that disabling log record checksums is meant to only be used with the SQL interface. However, disabling checksums for the SQL interface also disables checksums for the non-SQL interfaces.
To disable log checksums, before you build the
library edit the
build_windows/db_config.h file,
and delete the following line:
#define HAVE_LOG_CHECKSUM 1
This section describes the steps to build the JDBC driver.
Configure your build environment. For information on how to configure to build Java applications, see Building the Java API. In Visual Studio 2010/Visual Studio 2012, you need to configure the db_sql_jdbc project.
Open Berkeley_DB.sln, Berkeley_DB_vs2010.sln, or Berkeley_DB_vs2012.sln in Visual Studio. If you are using Java 1.6, 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.
If you are using Java 1.7 or Java 1.8, 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
JDBC2z1 instead
of JDBC2x.
Go to
db\lang\sql\jdbc\SQLite,
and replace
JDBCDriver.java
with
JDBCDriver.java17,
and replace
JDBCDataSource.java
with
JDBCDataSource.java17.
To enable encryption in the Berkeley DB SQL
JDBC driver, make sure the
db_sql project is built
with encryption enabled and specify
HAVE_SQLITE3_KEY in
Preprocessor
Definitions of the
db_sql_jdbc project.
See SQL Encryption in the
Berkeley DB Getting Started with the SQL APIs guide
for usage details.
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 . ..\..\..\lang\sql\jdbc\test3.java |
| java -cp ".;jdbc.jar" test3 |
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 Technology Network (OTN) . 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
"jdbc:sqlite:/D:\\jdbc_ex_db\\myDataSource",
or a relative path-file-name like
"jdbc:sqlite:/..\\jdbc_ex_db\myDataSource",
or a file name, like
"jdbc:sqlite:/myDataSource",
in which the database file will be stored
at the current directory. Note that all
path arguments require a leading slash,
even in the case of relative paths or a
database in the local directory. Please
refer to the above examples.
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 steps to use BDB JDBC driver in the integrated development environment ADF/JDeveloper.
Build the JDBC driver. For more information, see Building the JDBC Driver .
Ensure that the Java environment used to build the JDBC driver is same as used in ADF/JDeveloper.
Include JDBC driver path in the system CLASSPATH
and LD_LIBRARY_PATH.
Create BDB JDBC Database Connection in JDeveloper. ADF accesses the database by Database Connection. As long as the database file exists and has tables, you can see all tables located by the database connection in the project application resources panel.
Select File -> New -> General -> Connections -> Database Connection.
"Create Database Connection", choose
"Generic JDBC" as "Connection Type".
"Generic JDBC Settings" section, select
"New" to register a JDBC driver.
In the registration form, enter "SQLite.JDBCDriver"
for Driver Class.
"Browse" to locate the library.
"DBSQL_JDBC" for "Library Name," and add the path of the JDBC driver
to the Class Path.
"OK" to save.
Test the connection in the panel of Create Database Connection.
jdbc:sqlite:/<db-file-name>. The
<db-file-name> should include the database file path which must be
absolute.
"Success" message is displayed.
Apply BDB JDBC Database Connection in the ADF application.
After successfully creating the database connection, you can create
and initialize ADF BC component with it. During initialization, choose
"SQL92" as SQL Platform and "Java"
as Data Type Map for the connection.
You can define the ADF BC component by adding entity objects, view objects
etc. and the database connection takes charge of accessing the database.
For more information, see the example lang/sql/adf/EX_ADF.
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.
VS8,
VS10, or
VS12.
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 project is built
with encryption enabled and specify
SQLITE_HAS_CODEC=1 and
SQLITE_ACTIVATION_KEY="password_str" in
Preprocessor Definitions of the
db_sql_odbc project. The "password_str" can
be any string you want. See SQL Encryption in the
Berkeley DB Getting Started with the SQL APIs guide
for usage details.
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 db_sql_odbc62.dll,
libdb_sql62.dll and
libdb62.dll files are now
built.
Rename db_sql_odbc62.dll
to sqlite3odbc.dll
and 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.
By default, the DSN name is SQLite3 Datasource, but
for the encryption build, the name is SQLite3 SEE Datasource.
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.
Go to the Oracle Berkeley DB download page, and download the ADO.NET package. Build the package by following the instructions included in the package.