[Objectbridge-developers] Database structures

[Kief M. <ki...@bi...>]

Hello, I've just started working with OJB, and I must say I quite
like it. I had partially developed a similar package myself last year, 
but decided that since DB code isn't a focus of my work (and it's 
a considerable time sink) I needed to find something else to use.
OJB fits the bill very neatly, thanks to those of you who've been
developing it.

I'm having a few problems however. I have a project using mySQL, 
and I haven't got the database bridge in place yet.

One problem is I'm not sure how to structure the tables which ojb
uses. I've used the information in repository.xml to create the tables,
but I'm not sure (a) how big the CHAR columns should be, or
(b) whether I need to put anything in for the CollectionDescriptors - 
foreign keys? I'll put the table definitions I'm using at the end of this 
message - I decided to make most of the CHAR fields 255 characters 
to start with, and to ignore the collections since I can't see anything
relating to table columns in the xml.

The error I'm getting at the moment may or may not be related to
the table structures. Here's the exception:

    [junit] Running com.kief.babbletest.ForumTest
    [junit] OJB Descriptor Repository: file:/C:/src/new/babble/test/conf/repository.xml
    [junit] connection.prepareStatement(connection.nativeSQL(SELECT CLASSNAME, FIELDNAME, CURRENT FROM OJB_SEQ WHERE (CLASSNAME = ?) AND (FIELDNAME = ?)), 1004, 1007)
    [junit] java.lang.AbstractMethodError
    [junit]     at ojb.broker.accesslayer.StatementsForClass.prepareStatement(StatementsForClass.java:123)
    [junit]     at ojb.broker.accesslayer.StatementsForClass.getSelectByPKkStmt(StatementsForClass.java:246)
    [junit]     at ojb.broker.accesslayer.StatementManager.getSelectByPKStatement(StatementManager.java:196)
    [junit]     at ojb.broker.accesslayer.JdbcAccess.materializeObject(JdbcAccess.java:253)
    [junit]     at ojb.broker.PersistenceBrokerImpl.getDBObject(PersistenceBrokerImpl.java:776)

The third line is output from a println I put in immediately before the error, which happens
on the line:

	result = connection.prepareStatement(nsql, TYPE, CONCUR);

So apparently the prepareStatement() method of the Connection object returned by 
my JDBC driver is considered to be abstract? I'm not sure why this would be, 
prepared statements do work on this driver in other code of mine. Any clues?
I'm using mm.mysql.jdbc-1.2c, my relevant repository.xml looks like:

   <JdbcConnectionDescriptor id="default">
      <dbms.name>BabbleTest</dbms.name>
      <driver.name>org.gjt.mm.mysql.Driver</driver.name>
      <url.protocol>jdbc</url.protocol>
      <url.subprotocol>mysql</url.subprotocol>
      <url.dbalias>//localhost/wild5</url.dbalias>
   </JdbcConnectionDescriptor>

Has anybody else reported using ojb successfully with mySQL?

Thanks for any help. 

Here are the table structures I'm using:

	-- For Class ojb.odmg.NamedRootsEntry
	CREATE TABLE IF NOT EXISTS OJB_NRM (
			NAME VARCHAR(255) NOT NULL,
			OID VARCHAR(64),
			PRIMARY KEY (NAME)
	);
   
	-- For Class ojb.odmg.collections.DListImpl 
	CREATE TABLE IF NOT EXISTS OJB_DLIST (
         ID INT NOT NULL,
         SIZE INT,
		--  CollectionDescriptor
		PRIMARY KEY (ID)
	);
   
	-- For Class ojb.odmg.collections.DListEntry 
	CREATE TABLE IF NOT EXISTS OJB_DLIST_ENTRIES (
			ID INT NOT NULL,
			DLIST_ID INT,
			POSITION INT,
			OID VARCHAR(64),
			PRIMARY KEY (ID)
	);
   
	-- For Class ojb.broker.SequenceEntry 
	CREATE TABLE IF NOT EXISTS OJB_SEQ (
		CLASSNAME VARCHAR(255) NOT NULL,
		FIELDNAME VARCHAR(64),
		CURRENT INT,
		PRIMARY KEY (CLASSNAME)
	);
   
	-- For Class ojb.odmg.collections.DBagImpl 
	CREATE TABLE IF NOT EXISTS OJB_DLIST (
		ID INT NOT NULL,
		SIZE INT,
		-- CollectionDescriptor
		PRIMARY KEY(ID)
   );
   
	-- For Class ojb.odmg.collections.DSetImpl 
	CREATE TABLE IF NOT EXISTS OJB_DSET (
		ID INT NOT NULL,
		SIZE INT,
		-- CollectionDescriptor
		PRIMARY KEY(ID)
	);
   
	-- For Class ojb.odmg.collections.DSetEntry 
	CREATE TABLE IF NOT EXISTS OJB_DSET_ENTRIES (
		ID INT NOT NULL,
		DLIST_ID INT,
		POSITION INT,
		OID VARCHAR(255),
		PRIMARY KEY(ID)
   );
   
	-- For Class ojb.odmg.collections.DMapImpl 
	CREATE TABLE IF NOT EXISTS OJB_DMAP (
		ID INT NOT NULL,
		SIZE INT,
		-- CollectionDescriptor
		PRIMARY KEY(ID)
	);
   
	-- For Class ojb.odmg.collections.DMapEntry 
	CREATE TABLE IF NOT EXISTS OJB_DMAP_ENTRIES (
		ID INT NOT NULL,
		DMAP_ID INT,
		KEY_OID VARCHAR(64),
		VALUE_OID VARCHAR(64),
		PRIMARY KEY(ID)
	);