|
|
|
Configuration and vendor differences
This following sections of this article present example database configurations, including various experiences and issues with specific database vendors.
DB2
DB2 has a 'community' version, DB2 Express-C, that is available for with a license to develop, deploy, and distribute.
Downloads:
Configuration
The following configuration is known to work with IBM DB2 Driver for JDBC on DB2 9.1 fixpack 2:
/config/db/mydb = {
"class" : "com.ibm.db2.jcc.DB2SimpleDataSource",
"driverType" : 4,
"serverName" : "localhost",
"portNumber" : 50000,
"databaseName" : "MYDBNAME",
"user" : "someuser",
"password" : "<xor>Lz4sLCgwLTs="
}
For complete details about the valid properties on DB2Datasource, see the DB2 Documentation.
By default, driverType is 2, which is the native CLI wrapper. Unless you have your jars and dlls all set up correctly, it can be a pain to debug exactly why you aren't getting a Connection. By setting to 4, the pure Java driver, you'll have better success.
For more information about select the appropriate DB2DataSoure implementation, see the DB2 documentation.
If you face any issues, look below for workarounds or post to the forum.
Configure the DB2 Trace File
DB2 DataSource implementations conveniently allow the setting of the trace file on the DataSource. As such, you can configure the driver to trace file by simply adding a property in zero.config:
/config/db/mydb = {
"class" : "com.ibm.db2.jcc.DB2DataSource",
...
"password" : "${appname}/logs/db.txt"
}
In this example, ${appname} should be replaced by your application name from the application's ivy.xml. For instace, if your application is named myapp, you would use ${myapp}. Of course, the traceFile property can always be a hardcoded value C:/db/logs/db-trace, for instance. But using the variable allows for better portability.
Issues
Cannot execute a INSERT and retrieve generated keys
DB2 SQL error: SQLCODE: -517, SQLSTATE: 07005
This error was experienced on DB2 9.1 fixpack 1 and 2 using IBM DB2 Driver for JDBC and SQLJVersion 3.3 when calling the zero.data.groovy.Manager.insert() and zero.data.Manager.insert() methods. DB2 documentation has more details, but does not sufficiently explain how to resolve this error. This is a known issue for DB2 but is not scheduled to be fixed until DB2 Viper II 9.5.
The resolution is to turn DB2's eager statement preparation off. This can be done by dataSource.setDeferPrepares(false), or through zero.config:
/config/db/mydb = {
"class" : "com.ibm.db2.jcc.DB2DataSource",
...
"deferPrepares" : "false"
}
Derby
Configuration
Embedded Mode
/config/db/mydb = {
"class" : "org.apache.derby.jdbc.EmbeddedDataSource",
"databaseName" : "db/customer",
"createDatabase" : "create"
}
Note: Although it is convenient to have Derby automatically create the database file system upon first connection using the createDatabase set to create, it can get very confusing when tables don't exist because the working directory has changed and the database 'successfully' connects.
Network Mode
/config/db/mydb = {
"class" : "org.apache.derby.jdbc.ClientDataSource",
"databaseName" : "db/customer",
"user" : "someuser",
"password" : "secretpassword",
"createDatabase" : "create"
}
Note: The same 'gotcha' for Embedded Mode described above using createDatabase holds true for Network mode as well.
Issues
Derby has two 'modes': Embedded and Network. Derby always runs in a JVM, it just depends on where.
Embedded Mode
As you can probably guess, Embedded mode is running in the same JVM as Zero. This is convenient, but has some 'gotchas'. For instance, Derby locks the file system where data is persisted to disk. As such, if Zero is running and has established a connection in Embedded mode, the Embedded Derby engine is running and will prevent other Derby tools from talking to the database.
When in Embedded mode, the Derby databaseName property is the file system path to your database relative to your Zero application. So, db/mydb would be under %approot%/db/mydb.
Network Mode
Running in Network mode requires one more step, just like every other database 'server', but Network mode requires starting a Java process. This is possible with the Derby Eclipse Plugin or from the command line. The Eclipse Plugin makes it possible to start the network process by right clicking on a project. When this is done, the Derby databaseName property is the same as Embedded mode, relative to the application root.
However, when the command line option is used (%derby_home%/frameworks/NetworkServer/bin/startNetworkServer.bat), beware that the databaseName property is relative to the working directory (i.e. where you execute startNetworkServer.bat from). To simplify things, it might be wise to put the directory where this batch file lives on the system path and execute it from the application root.
Configure the Derby Trace File
Derby configures its trace file through Java System Properties. This can be accomplished by setting the properties on the command line. Derby documentation has more details.
The trace file can also be set on the DataSource by setting the following properties in zero.config:
/config/db/mydb = {
"class" : "org.apache.derby.jdbc.ClientDataSource",
...
"traceFile" : "${appname}/logs/db.txt"
}
MySQL
Configuration
The following configuration is known to work with MySQL 5.0.45 on the Connector/J 5.0 drivers:
/config/db/mydb = {
"class" : "com.mysql.jdbc.jdbc2.optional.MysqlDataSource",
"serverName" : "db.example.org",
"portNumber" : 3306,
"databaseName" : "voting",
"user" : "someuser",
"password" : "secretpasssword"
}
For more information on available MysqlDataSource properties, see the complete MySQL DataSource Documentation.
Downloads:
Issues
Java to SQL Type Matrix
Below is a matrix of SQL and Java type mappings for DB2, Derby, and MySQL. x denotes an unsupported type (i.e. DB2 supports the DECFLOAT type but MySQL does not).
|
