Re: [xmlblaster] MySQL

[Peter Bennett <bencom at bencom.co.nz>]

Greetings Michele

Can you make and test the following changes to
based on the notes that follow also...
not null needs to be mapped...

JdbcManagerCommonTable.java

req = "CREATE TABLE " + this.nodesTableName.toUpperCase() + " (nodeId " 
+ this.stringTxt + " not null, PRIMARY KEY (nodeId))";

req = "CREATE TABLE " + this.queuesTableName.toUpperCase() +
		  "(queueName " + this.stringTxt +
                  " not null, nodeId " + this.stringTxt +
                  " not null, numOfBytes " + this.longintTxt +
                  ", numOfEntries " + this.longintTxt +
                  ", PRIMARY KEY (queueName, nodeId)" +
                  ", FOREIGN KEY (nodeId) REFERENCES " + 					 
this.nodesTableName + " ON DELETE CASCADE)";

req = "CREATE TABLE " + this.entriesTableName.toUpperCase() + " (dataId 
" + this.longintTxt +
                  " not null, nodeId " + this.stringTxt +
                  ", queueName " + this.stringTxt +
                  " not null, prio " + this.intTxt +
                  ", flag " + this.stringTxt +
                  ", durable " + this.booleanTxt +
                  ", byteSize " + this.longintTxt +
                  ", ablob " + this.blobTxt +
                  ", PRIMARY KEY (dataId, queueName)" +
                  ", FOREIGN KEY (queueName, nodeId) REFERENCES " + 
this.queuesTableName + " ON DELETE CASCADE)";

Tables created in MySQL are not allowed to have fields that are
defined as keys with null values therefor they must have the not null
qualifier... varchar(128) is the correct mapping for string as you
suggested. Thanks.

I don't think you will be creating records with null values in your key
fields...

Maybe it needs a mapping... Don't map or Map to "" for db's that don't
care.... That way config files don't need changing just the mapping
added for MySQL. :)

The blob field name has to be changed for MySQL...
Note I have had similar problems with other key words... Namely "user".

You have an error in your SQL syntax near 'blob blob, PRIMARY KEY 
(dataId, queueName), FOREIGN KEY (queueName, nodeId) REFE' at line 1"

If I change the field name to ablob it works fine...
Obviously code using that field name will need changing as well.
Line #712 or there abouts req = .... I could not find anything else
quickly...
You know it better than me...

It all needs testing under postgres and the other dbs... I will test
here with postgres and MySQL but I am running out of time today... Gotta
have dinner then go out and watch the All Black rugby team thrash the
Frogs (French) on Sky. We love to hate them... Rainbow Warrior lives on.

Back into it all day tomorrow...

Regards