JDBC
db4o interface to SQL databases
Contents
Purpose
Migration
Joining database files
SqlExport
SqlReImport
SqlImport
SqlImportContinue
JDBC Drivers
Databases
Migration example using Microsoft Access
SourceCode
Limitations
Support
Purpose
The sourcecode supplied in com.db4o.sql can be used to backup db4o data to SQL databases and to migrate data between db4o versions with different file formats.

In addition, classes can be generated from existing SQL databases and existing data can be imported into db4o.

All algorithms present to date are "run once and transport all data".
No security mechanisms to prevent duplicate objects or duplicate rows are implemented.

Prepared batch files are supplied in the folder db4ohome//com/db4o/sql. They need to be moved to db4ohome// to be run.

Please read this documentation once, before starting to work.


Migration
Never unzip a new version of db4o over an existing version.
Always use a new directory.
To migrate your data from one db4o version to another, you need an SQL database
and a JDBC driver for it. Follow the following routine:
- SQL database and JDBC driver should be installed.
- Backup your old db4o data file.
- Create an empty JDBC database file for migration. System tables are no problem.
- The JDBC driver should be in your CLASSPATH.
- Your old db4o.jar should be in your CLASSPATH.
- You need the name of the JDBC driver class file you intend to use, the connection String for your empty database file and the path to the db4o file, you intend to migrate.
- From the command line:
- Switch to your old db4o source directory. The classes in com.db4o.jgen, com.db4o.lib and com.db4o.sql should be present in subdirectories.
- Execute ' java com.db4o.sql.SqlExport [driver] [connectString] [dbFile]'.
- You can also use and edit the batch file SqlExport.bat for this purpose, if you move it to the root folder of your project.
- During the export run generated messages should inform you about the progress.
- After the export has successfully been completed, copy the new db4o.jar over the old version.
- If you want to reuse your old path to your db4o database file, delete (Backup!) or move the old file.
From the command line:
- Switch to the old db4o directory.
- Execute ' java com.db4o.sql.SqlReImport [driver] [connectString] [dbFile]'.
- You can also use and edit the batch file SqlReImport.bat for this purpose, if you move it to the root folder of your project.
- Use your application with the new db4o.jar to test, if all data is present.
- Cleanup old versions.

The step-by-step example for windows, using Microsoft Access could provide further help, if you work on the windows platform and this program is available for you.

Joining database files
The migration mechanism can also be used to join data from multiple db4o files. Be sure to create backup copies of your database files. Every call to SqlReImport creates the full set of objects. There is no mechanism to prevent duplicates.

SqlExport
com.db4o.sql.SqlExport exports data from db4o to JDBC databases.
Parameters:
<JDBC driver> <JDBC connection String to destination> <db4o data file used as source>

SqlReImport
com.db4o.sql.SqlReImport imports data previously exported with SqlExport.
Parameters:
<JDBC driver> <JDBC connection String to database used as source> <destination db4o data file>

SqlImport
com.db4o.sql.SqlImport imports data from a data source that is unknown to the db4o system.
Parameters:
<JDBC driver> <JDBC connection String> <destination db4o data file> <package>
For every table a .Java file will be created in the package folder specified as parameter.
The .Java files will be automatically compiled if javac is found on your system.
For every row in the database, an object will be created within db4o.

SqlImportContinue
com.db4o.sql.SqlImportContinue can be used to continue an SqlImport run, if automatic compilation could not be started on your system. In this case you should compile the newly created Java files manually before resuming the SqlImport run with SqlImportContinue.
Parameters:
<JDBC driver> <JDBC connection String> <destination db4o data file> <package>

JDBC Drivers
Our experiences with JDataConnect from NetDirect were excellent.

Databases
The migration mechanism should work on any database, providing metadata information is available over the JDBC interface.
The current implementation has been tested with a Sybase database and with Microsoft Access.
Sybase offers a free version of Adaptive Server Enterprise for Linux.

Migration example using Microsoft Access
- Backup your db4o database file.
- Download JDataConnect from NetDirect and install it.
- Start Microsoft Access
- Create a new database and store it as c:\db4o.mdb
- Exit Microsoft Access
- Start the Windows Control Panel
- Select "ODBC Data Sources"
- Select "System - DSN"
- Choose "Add"
- Select "Microsoft Access Driver"
- "Finish"
- Enter "db4oImEx" as a name for the database
- Click "Select" to choose the database file
- Choose c:\db4o.mdb
- Click "OK" to finish
- Edit SqlExport.bat in your old db4o directory.
- Remove "rem", the remark quote in front of the line below "Template for JDataConnect and db4oImEx Datasource"
- If necessary, adjust the path to the db4o database file and the version of JDataConnect.
- Apply the same changes to SqlReImport.bat.
- Open a command line window
- CD to your old db4o directory.
- Execute the SqlExport batch file
- Move the db4o database file to a safe location.
- Replace the old db4o.jar with the new version.
- Execute SqlReimport.Bat from the command line window in the old db4o directory.
- Use your application with the new db4o.jar to test, if all data is present.
- Delete c:\db4o.mdb and the ODBC data source, that you have created. You can reuse the ODBC data source for subsequent runs, but make sure you use an empty database file every time.
- Cleanup the old version of db4o.
- Copying the db4o data file to your new db4o version, completely deleting the old version and renaming the folder of the new version is the easiest way.

SourceCode
All JDBC connectivity sourcecode is provided in the folder /com/db4o/sql.
We have tried to keep all code, that could be specific to a driver or database in com.db4o.sql.Sql.Java.
If you need mappings for additional data types, necessary changes should be made in this file.

Limitations
SQL has a limit to field lengths.
The default setting of the migration interface creates all strings as VARCHAR(255).
If you encounter loss of data, you can increase the value of com.db4o.sql.Sql.STRING_LENGTH to the maximum value supported by your database. (255 for MS Access)
A mechanism to serialize BIG objects to BINARY fields will be provided in a future version.

Support
If you should encounter problems, please mail to support@db4o.com. We are trying hard, to make you happy with our product.

---