Prev	Chapter 5. - Building Berkeley DB for Windows -	Chapter 5. Building Berkeley DB for Windows	Next

- 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_sql53.dll - in the Release mode or libdb_sql53d.dll in - the Debug mode. An SQL command line interpreter called - dbsql.exe is also built. -

Binary Compatibility With SQLite

- libdb_sql53.dll is compatible with - sqlite3.dll. You can copy - libdb_sql53.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. -

+ libdb_sql61.dll is compatible with + sqlite3.dll. You can copy + libdb_sql61.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. -

+ 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. - -

+ 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. +

Changing Compile Options

- 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. - -

+ 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. +

+ BDBSQL_DEFAULT_PAGE_SIZE +: + To set the default page size when you + create a database, specify the + BDBSQL_DEFAULT_PAGE_SIZE flag. The assigned + value must be 0, 512, 1024, 2048, 4096, 8192, + 16384, 32768, or 65536. The default value is + 4096. If the value is set to zero, Berkeley DB + queries the file system to determine the best + page size, and the value of + SQLITE_DEFAULT_PAGE_SIZE is used to calculate + the cache size, because the cache size is + specified as a number of pages. +
+ BDBSQL_FILE_PER_TABLE +: + To generate each table in a separate + file, rather than as subdatabases in a single + file, specify the BDBSQL_FILE_PER_TABLE flag. + 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. Note that adding or deleting + files from the database directory may corrupt + your database. To backup 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. +
+ BDBSQL_LOG_REGIONMAX +: + To configure the log region size for + the underlying storage engine, specify the + BDBSQL_LOG_REGIONMAX flag. For more + information, see DB_ENV->get_lg_regionmax(). +
+ BDBSQL_OMIT_LEAKCHECK +: + For Berkeley DB to use the default + system allocation routines rather than the + SQLite allocation routines, specify the + BDBSQL_OMIT_LEAKCHECK flag. +
+ BDBSQL_OMIT_LOG_REMOVE +: + Berkeley DB automatically removes log + files that are no longer required That is, + files that are older than the most recent + checkpoint. To disable this functionality, + specify the BDBSQL_OMIT_LOG_REMOVE flag. It is + necessary to provide this flag if you are + using replication with Berkeley DB SQL. +
+ BDBSQL_OMIT_SHARING +: + To create a private environment rather + than a shared environment, specify the + BDBSQL_OMIT_SHARING flag. That is, the cache + and other region files will be created in + memory rather than using file backed shared + memory. For more information, see the + DB_PRIVATE flag of DB_ENV->open()
+ BDBSQL_SINGLE_THREAD +: + To disable locking and thread safe + connections, specify the BDBSQL_SINGLE_THREAD + flag. If an application is going to use + Berkeley DB from a single thread and a single + process, enabling this flag can deliver + significant performance advantages. +
+ SQLITE_DEFAULT_CACHE_SIZE +: + SQLite provides an in-memory cache + which you size according to the maximum number + of database pages that you want to hold in + memory at any given time. Berkeley DB's + in-memory cache feature performs the same + function as SQLite. To specify the suggested + maximum number of pages of disk cache that + will be allocated per open database file + specify the SQLITE_DEFAULT_CACHE_SIZE flag. + Default value is 2000 pages. For more + information, see the SQLite documentation on + + PRAGMA default_cache_size. +
+ SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT +: + For SQLite, this pragma identifies the + maximum size that the journal file is allowed + to be. Berkeley DB does not have a journal + file, but it writes and uses log files. A new + log file is created when the current log file + has reached the defined maximum size. To + define this maximum size for a log file, + specify the SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT + flag. Default value is 10 MB for the Berkeley + DB SQL interface. +

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: -

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

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

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

+ 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. -

- To disable log checksums, before you build the library edit the - build_windows/db_config.h file, and delete - the following line: -

-
- Configure your build environment. For information on how to - configure to build Java applications, see - Building the Java API. -
+
+ Configure your build environment. For + information on how to configure to build Java + applications, see Building the Java API. +
-
- Build the SQL project in Debug mode. -
+
+ Build the SQL project in Debug mode. +
- Open Berkeley_DB.sln or Berkeley_DB_vs2010.sln in Visual Studio. - If you are using Java 1.6, do the following: -
+ Open Berkeley_DB.sln or + Berkeley_DB_vs2010.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 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. -
  + 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, do the following: -
+ If you are using Java 1.7, do the + following: +
- - In the Solution Explorer, right-click the - db_sql_jdbc project and select - properties. -
  + 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. Also, remove the option of - "-target 1.5". -
  + In the Configuration + Properties -> Build Events -> + Pre-Build Event section, + alter the command to refer to + JDBC2z1 instead + of JDBC2x. Also, + remove the option of "-target 1.5". +
- - Go to db\lang\sql\jdbc\SQLite, and - replace JDBCDriver.java with - JDBCDriver.java17, and replace - JDBCDataSource.java with - JDBCDataSource.java17. -
  + Go to + db\lang\sql\jdbc\SQLite, + and replace + JDBCDriver.java + with + JDBCDriver.java17, + and replace + JDBCDataSource.java + with + JDBCDataSource.java17. +
- Build the db_sql_jdbc project in Visual Studio. -

+ To enable encryption in the Berkeley DB SQL + JDBC driver, make sure the + db_dql 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: -

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

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

Modifying Sample Code

Before running the sample code, do the following:

+ Before running the sample code, do the + following: +

Unzip the file containing the sample code to a new directory (for example, jdbc_ex).
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.
Substitute SQLite.JDBCDriver for myDriver.ClassName in the line: Class.forName("myDriver.ClassName");
Substitute the username and password you use for your database in the following: "myLogin", "myPassword".
This is optional.
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.
+ Unzip the file containing the sample + code to a new directory (for example, + jdbc_ex). +
+ 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 + "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. +
+ Substitute + SQLite.JDBCDriver for + myDriver.ClassName in + the line: + Class.forName("myDriver.ClassName");
+ Substitute the username and password you + use for your database in the following: + "myLogin", + "myPassword".
This is + optional.
+ 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. +

In the db\build_windows\Win32\Debug directory, run following commands: -

+              
+                        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>
-    
-              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. 
-    +    $ java  -classpath ".;jdbc.jar" <ClassName, eg. CreateCoffees>
+              
+                        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
@@ -408,10 +656,17 @@
     CREATE TABLE COFFEES (COF_NAME varchar(32),/
     SUP_ID int, PRICE float, SALES int, TOTAL int);
     COMMIT;
-    dbsql>
-    
-              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.
+    dbsql>

+ 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. +

+
+ 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. +
  +
  +
  1. + In the panel of "Create Database Connection", choose + "Generic JDBC" as "Connection Type". +
  2. + In the "Generic JDBC Settings" section, select + "New" to register a JDBC driver. +
  +
  +
- +
  + In the registration form, enter "SQLite.JDBCDriver" + for Driver Class. +
  +
  +
  1. + Select "Browse" to locate the library. +
  2. + In the library information form, select User -> + New to create a library. +
  3. + Enter "DBSQL_JDBC" for "Library Name," and add the path of the JDBC driver + to the Class Path. +
  4. + Select "OK" to save. +
  +
  +
- +
  + Test the connection in the panel of Create Database Connection. +
  +
  +
  1. + Configure the JDBC URL with jdbc:sqlite:/<db-file-name>. The + <db-file-name> should include the database file path which must be + absolute. +
  2. + Select Test Connection. If the connection is proved to be + working, a "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. +
+

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: -

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

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

@@ -455,63 +834,134 @@

Building the Library

+ Build the SQL project in Release mode. + See Building the SQL API. +
+ Open Visual Studio. +
+ Load the Berkeley_DB solution file into + Visual Studio. +
+ Set the build target to + Release
- Build the SQL project in Release mode. See Building the SQL API. -
Open Visual Studio.
Load the Berkeley_DB solution file into Visual Studio.
Set the build target to Release
Build the solution.
- Select File -> Add -> Existing Project. -
Select build_windows.

File

Add

Existing Project

- Select the appropriate directory for your compiler: VS8 or VS10. -

build_windows

- Select 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. -
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.

VS8

VS10

+ Select + 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. +
+ 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_sql53.dll and libdb53.dll files are now built.

+ The db_sql_odbc61.dll, + libdb_sql61.dll and + libdb61.dll files are now + built. +

Testing the ODBC Install

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

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

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

Binary Compatibility With SQLite

Binary Compatibility With SQLite

Warning

Setting Preprocessor Flags -

Setting Encryption

Changing Compile Options

Enabling Extensions

Enabling Extensions

Note

Using the JDBC Driver

Using the JDBC Driver

Downloading JDBC Sample Code

Downloading JDBC Sample Code

Modifying Sample Code

Modifying Sample Code

Building and Running the JDBC Sample code

Building and Running the JDBC Sample code

Building the ODBC Driver

Using the JDBC Driver in ADF/JDeveloper

Building the ODBC Driver

Configuring Your System

Configuring Your System

Building the Library

Building the Library

Installing the Library

Installing the Library

Testing the ODBC Install

Testing the ODBC Install

Using the ADO.NET Driver -

Using the ADO.NET Driver

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