Microsoft Access is not a true SQL database server; as such, it is suitable for development and testing, but not for a production database. Access does not have a JDBC driver. However, Access does support ODBC, and there is a JDBC-ODBC bridge in the Sun JDK, which enables Access to work with Enhydra.
Because Access cannot read-in files containing SQL commands, you must create tables in the Access GUI. See the Access documentation for more information. For the DiscRack example, you can also use the Access database provided in <dods_root>/examples/DiscRack/discRack.mdb.
To use DODS with Access:
Register the database as an ODBC data source:
Go to Start|Settings|Control Panel and click ODBC Data Sources.
Click the Add button in the dialog box that comes up.
Select the Microsoft Access Driver in the Create New Datasource dialog box and click Finish.
The ODBC Microsoft Access Setup dialog box appears.
Choose a name, like discRack, for the Data Source Name. Under Database, click the Select button, browse to the *.mdb file, select it, and click OK.
Set connection related parameters:
DatabaseManager.DB.<database_id>.ClassType = "Access" DatabaseManager.DB.<database_id>.JdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver" DatabaseManager.DB.<database_id>.Connection.Url = "jdbc:odbc:<dsnName>"
Set names for "objectid" table name and "next" column of "objectid" table (parameters "OidTableName" and "NextColumnName"). This will caused that DODS don't use "next" when access to "objectid" table ("next" is reserved word in "Access")
DatabaseManager.DB.<database_id>.ObjectId.OidTableName="<oidTableName>" DatabaseManager.DB.<database_id>.ObjectId.NextColumnName="<nextColumnName>"
- or you can (have a same effect) set:
DatabaseManager.DB.<database_id>.ObjectId.NextWithPrefix="true"
Disable use of "FetchSize" in DODS - this feature are not supported by MS Access odbc driver.
DatabaseManager.defaults.DefaultFetchSize=0
Disable use of "QueryTimeout" in DODS - this feature are not supported by MS Access odbc driver.
DatabaseManager.defaults.QueryTimeout=false
To avoid use of "LOWER" function in DODS created SQL statements (this function is not supported by Access) set:
DatabaseManager.defaults.CaseInsensitiveDatabase="true"
To avoid problem with inconsistency of data read/write in different transaction, use clean transaction or use every connection (transaction) only once (disable connection pool). To force DODS to disable connection pool set:
DatabaseManager.DB.<database_id>.Connection.MaxConnectionUsages = 1
You don’t have to place the JDBC driver in the application’s CLASSPATH because the ODBC/JDBC bridge is in the JDK and thus is already in the system’s CLASSPATH.
This section presents an example of an Access configuration, where <database_id> is your database identifier.
#------------------------------------------------------------------- # Database Manager Configuration #------------------------------------------------------------------- DatabaseManager.Databases[] = "<database_id>" DatabaseManager.DefaultDatabase = "<database_id>" DatabaseManager.Debug = "false" DatabaseManager.DB.<database_id>.ClassType = "Access" DatabaseManager.DB.<database_id>.JdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver" DatabaseManager.DB.<database_id>.Connection.Url = "jdbc:odbc:<data_source>" DatabaseManager.DB.<database_id>.Connection.User = "Admin" DatabaseManager.DB.<database_id>.Connection.Password = "" DatabaseManager.DB.<database_id>.Connection.MaxPreparedStatements = 10 DatabaseManager.DB.<database_id>.Connection.MaxConnectionUsages = 1 DatabaseManager.DB.<database_id>.Connection.MaxPoolSize = 30 DatabaseManager.DB.<database_id>.Connection.AllocationTimeout = 10000 DatabaseManager.DB.<database_id>.Connection.Logging = false DatabaseManager.DB.<database_id>.ObjectId.CacheSize = 20 DatabaseManager.DB.<database_id>.ObjectId.MinValue = 1 DatabaseManager.DB.<database_id>.ObjectId.OidTableName="objectid" DatabaseManager.DB.<database_id>.ObjectId.NextColumnName="next" DatabaseManager.defaults.QueryTimeout=0 DatabaseManager.defaults.DefaultFetchSize=0 DatabaseManager.defaults.CaseInsensitiveDatabase="true"
This is the link where you can find all needed information for Microsoft Access:
http://www.microsoft.com/office/access/default.asp
FOR APPLICATION DEVELOPERS (related to application code)
1. Avoid "DIFFERENCE" clausule in SQL statements: "DIFFERENCE" is unsupported.
2. Don’t use "!=" in SQL expression: "!=" is not supported - use "<>"
3. Don't use "LOWER" function in SQL statements.