You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@turbine.apache.org by Fabian Moerchen <fa...@mybytes.de> on 2002/01/23 19:01:16 UTC
torque: mysql, idbroker, autoincrement and foreign keys
hi
i'm using the latest torque with mysql and defaultIdMethod="native".
this generates for primary keys like
<column name="ID" type="INTEGER" required="true"
autoIncrement="true" primaryKey="true"/>
this SQL:
ID INTEGER NOT NULL AUTO_INCREMENT
ok, so far so good, but i always get this warning:
IDBroker is being used with db 'MYDB', which does not support
transactions. IDBroker attempts to use transactions to limit the
possibility of duplicate key generation. Without transactions,
duplicate key generation is possible if multiple JVMs are used or other
means are used to write to the database.
i don't really have to worry about it when using "native", do i?
can i or should i turn the IDBroker thing off?
the next problem is creating objects connected with foreign keys. e.g.
if i create a new object Artist and call save() it does not know it's
own primary key yet, since mysql decides about the value, right? because
when trying to tie it to another Website (they are connected via a m:n
relationship) and saving this i get
java.sql.SQLException: General error: Column 'ARTIST_ID' cannot be null
if i create artist and website first, retrieve them into new objects,
create the relationship and save then it works, but this is not very
convenient. especially since i search for them by one of the other
attributes (name) which is _not unique_, so it works in my small test
setup, but:
isn't there a bulletproof way to get the pk of the object just saved?
bye
fabian
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: torque: mysql, idbroker, autoincrement and foreign keys
Posted by Fabian Moerchen <fa...@mybytes.de>.
the errors came only after i added the DTD definition. a warning: please
specify a DTD so i can verify the schema would have been nice. ;)
bye
fabian
On Sun, 2002-01-27 at 00:20, John McNally wrote:
> I was going to suggest that you try defining your fk's after all the
> columns, but I assumed that since you did not mention any errors wrt
> parsing the xml, that it was handled ok.
>
> Since you are using the defaultIdMethod attribute, you should add
> idMethod="none" to tables that do not have generated keys. e.g.
> ARTIST_HAS_WEBSITE. These attributes do not affect the sql, but they do
> affect the object model.
>
> john mcnally
>
> Fabian Moerchen wrote:
> >
> > hi
> >
> > pete solved the problem after trying my code:
> >
> > you should always start the schema definition with
> >
> > <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
> > <!DOCTYPE database SYSTEM
> > "http://jakarta.apache.org/turbine/dtd/database.dtd">
> >
> > so the schema gets checked for errors.
> >
> > in my case the error was that foreign key definitions have to be after
> > the column definitions in the table element.
> >
> > bye
> > fabian
> >
> > On Thu, 2002-01-24 at 22:47, Fabian Moerchen wrote:
> > > i had a default method of native for the database tag all the time.
> > > still i tried many different combinations of defaultIdMethod and
> > > idMethod now but all produced the same, correct (?) SQL for the
> > > relation:
> > >
> > > CREATE TABLE ARTIST_HAS_WEBSITE
> > > (
> > > ARTIST_ID INTEGER NOT NULL,
> > > WEBSITE_ID INTEGER NOT NULL,
> > > PRIMARY KEY(ARTIST_ID,WEBSITE_ID),
> > > FOREIGN KEY (ARTIST_ID) REFERENCES ARTIST (ID),
> > > FOREIGN KEY (WEBSITE_ID) REFERENCES WEBSITE (ID)
> > > );
> > >
> > > and e.g. for the websites
> > >
> > > CREATE TABLE WEBSITE
> > > (
> > > ID INTEGER NOT NULL AUTO_INCREMENT,
> > > NAME VARCHAR (100) NOT NULL,
> > > URL VARCHAR (200) NOT NULL,
> > > PRIMARY KEY(ID)
> > > );
> > >
> > > still artist1.getPrimaryKey() give me always null after saving and the
> > > other problems described also persist.
> > >
> > > bye
> > > fabian
> > >
> > > On Thu, 2002-01-24 at 17:49, John McNally wrote:
> > > > I notice you are not specifying an idmethod attribute on your tables,
> > > > are you specifying a default. I suspect not as then you would probably
> > > > have the attribute on your ARTIST_HAS_WEBSITE table.
> > > >
> > > > Use idmethod="native" or idmethod="autoincrement", if native does not
> > > > work, on tables that have generated pk's. And then use idmethod="none"
> > > > on tables such as ARTIST_HAS_WEBSITE.
> > > >
> > > > john mcnally
> > > >
> > > > Fabian Moerchen wrote:
> > > > >
> > > > > here's (part of) my schema. did i do something wrong?
> > > > > could very well be since it's my first. ;)
> > > > >
> > > > > <table name="GENRE">
> > > > > <column name="ID" type="INTEGER" required="true"
> > > > > autoIncrement="true" primaryKey="true"/>
> > > > > <column name="NAME" type="VARCHAR" size="50" required="true"/>
> > > > > <unique>
> > > > > <unique-column name="NAME"/>
> > > > > </unique>
> > > > > </table>
> > > > > <table name="ARTIST">
> > > > > <column name="ID" type="INTEGER" required="true"
> > > > > autoIncrement="true" primaryKey="true"/>
> > > > > <column name="NAME" type="VARCHAR" size="100" required="true"/>
> > > > > <column name="PATTERN" type="VARCHAR" size="255"/>
> > > > > <column name="SORTNAME" type="VARCHAR" size="255"/>
> > > > > <column name="GENRE_ID" type="INTEGER"/>
> > > > > <foreign-key foreignTable="GENRE">
> > > > > <reference local="GENRE_ID" foreign="ID"/>
> > > > > </foreign-key>
> > > > > </table>
> > > > > <table name="WEBSITE">
> > > > > <column name="ID" type="INTEGER" required="true"
> > > > > autoIncrement="true" primaryKey="true"/>
> > > > > <column name="NAME" type="VARCHAR" size="100" required="true"/>
> > > > > <column name="URL" type="VARCHAR" size="200" required="true"/>
> > > > > </table>
> > > > > <table name="ARTIST_HAS_WEBSITE">
> > > > > <column name="ARTIST_ID" type="INTEGER" required="true"
> > > > > primaryKey="true"/>
> > > > > <foreign-key foreignTable="ARTIST">
> > > > > <reference local="ARTIST_ID" foreign="ID"/>
> > > > > </foreign-key>
> > > > > <column name="WEBSITE_ID" type="INTEGER" required="true"
> > > > > primaryKey="true"/>
> > > > > <foreign-key foreignTable="WEBSITE">
> > > > > <reference local="WEBSITE_ID" foreign="ID"/>
> > > > > </foreign-key>
> > > > > </table>
> > > > >
> > > > > bye
> > > > > fabian
> > > > >
> > > > > On Thu, 2002-01-24 at 07:28, John McNally wrote:
> > > > > > The first example should work. You might want to show your schema.xml,
> > > > > > to give others some idea what might be wrong. artist1.getPrimaryKey()
> > > > > > should give the id assigned after saving the object. It should not be
> > > > > > null. If it is something is wrong with your xml.
> > > > > >
> > > > > > john mcnally
> > > > > >
> > > > > > >
> > > > > > > this doesn't work:
> > > > > > >
> > > > > > > Artist artist1 = new Artist();
> > > > > > > artist1.setName("Primus");
> > > > > > > artist1.save();
> > > > > > >
> > > > > > > Website website1 = new Website();
> > > > > > > website1.setName("Primus Online");
> > > > > > > website1.setUrl("http://www.primussucks.com");
> > > > > > > website1.save();
> > > > > > >
> > > > > > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > > > > > link.setArtist(artist1);
> > > > > > > link.setWebsite(website1);
> > > > > > > link.save(); //Column 'ARTIST_ID' cannot be null
> > > > > > >
> > > > > > > this does work:
> > > > > > >
> > > > > > > Artist artist1 = new Artist();
> > > > > > > artist1.setName("Primus");
> > > > > > > artist1.save();
> > > > > > >
> > > > > > > Website website1 = new Website();
> > > > > > > website1.setName("Primus Online");
> > > > > > > website1.setUrl("http://www.primussucks.com");
> > > > > > > website1.save();
> > > > > > >
> > > > > > > Artist artist2 = ArtistFactory.findByName(artist1.getName());
> > > > > > > Website website2 = WebsiteFactory.findByUrl(website1.getUrl());
> > > > > > >
> > > > > > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > > > > > link.setArtist(artist2);
> > > > > > > link.setWebsite(website2);
> > > > > > > link.save();
> > > > > > >
> > > > > > > tried defaultIdMethod native and idBroker by now.
> > > > > > >
> > > > > > > bye
> > > > > > > fabian
> > > > > > >
> > > > > > > --
> > > > > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > > > > >
> > > > > > --
> > > > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > > > > >
> > > > > >
> > > > >
> > > > > --
> > > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > > >
> > > > --
> > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > > >
> > > >
> > >
> > >
> > > --
> > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > >
> > >
> >
> > --
> > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > For additional commands, e-mail: <ma...@jakarta.apache.org>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: torque: mysql, idbroker, autoincrement and foreign keys
Posted by John McNally <jm...@collab.net>.
I was going to suggest that you try defining your fk's after all the
columns, but I assumed that since you did not mention any errors wrt
parsing the xml, that it was handled ok.
Since you are using the defaultIdMethod attribute, you should add
idMethod="none" to tables that do not have generated keys. e.g.
ARTIST_HAS_WEBSITE. These attributes do not affect the sql, but they do
affect the object model.
john mcnally
Fabian Moerchen wrote:
>
> hi
>
> pete solved the problem after trying my code:
>
> you should always start the schema definition with
>
> <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
> <!DOCTYPE database SYSTEM
> "http://jakarta.apache.org/turbine/dtd/database.dtd">
>
> so the schema gets checked for errors.
>
> in my case the error was that foreign key definitions have to be after
> the column definitions in the table element.
>
> bye
> fabian
>
> On Thu, 2002-01-24 at 22:47, Fabian Moerchen wrote:
> > i had a default method of native for the database tag all the time.
> > still i tried many different combinations of defaultIdMethod and
> > idMethod now but all produced the same, correct (?) SQL for the
> > relation:
> >
> > CREATE TABLE ARTIST_HAS_WEBSITE
> > (
> > ARTIST_ID INTEGER NOT NULL,
> > WEBSITE_ID INTEGER NOT NULL,
> > PRIMARY KEY(ARTIST_ID,WEBSITE_ID),
> > FOREIGN KEY (ARTIST_ID) REFERENCES ARTIST (ID),
> > FOREIGN KEY (WEBSITE_ID) REFERENCES WEBSITE (ID)
> > );
> >
> > and e.g. for the websites
> >
> > CREATE TABLE WEBSITE
> > (
> > ID INTEGER NOT NULL AUTO_INCREMENT,
> > NAME VARCHAR (100) NOT NULL,
> > URL VARCHAR (200) NOT NULL,
> > PRIMARY KEY(ID)
> > );
> >
> > still artist1.getPrimaryKey() give me always null after saving and the
> > other problems described also persist.
> >
> > bye
> > fabian
> >
> > On Thu, 2002-01-24 at 17:49, John McNally wrote:
> > > I notice you are not specifying an idmethod attribute on your tables,
> > > are you specifying a default. I suspect not as then you would probably
> > > have the attribute on your ARTIST_HAS_WEBSITE table.
> > >
> > > Use idmethod="native" or idmethod="autoincrement", if native does not
> > > work, on tables that have generated pk's. And then use idmethod="none"
> > > on tables such as ARTIST_HAS_WEBSITE.
> > >
> > > john mcnally
> > >
> > > Fabian Moerchen wrote:
> > > >
> > > > here's (part of) my schema. did i do something wrong?
> > > > could very well be since it's my first. ;)
> > > >
> > > > <table name="GENRE">
> > > > <column name="ID" type="INTEGER" required="true"
> > > > autoIncrement="true" primaryKey="true"/>
> > > > <column name="NAME" type="VARCHAR" size="50" required="true"/>
> > > > <unique>
> > > > <unique-column name="NAME"/>
> > > > </unique>
> > > > </table>
> > > > <table name="ARTIST">
> > > > <column name="ID" type="INTEGER" required="true"
> > > > autoIncrement="true" primaryKey="true"/>
> > > > <column name="NAME" type="VARCHAR" size="100" required="true"/>
> > > > <column name="PATTERN" type="VARCHAR" size="255"/>
> > > > <column name="SORTNAME" type="VARCHAR" size="255"/>
> > > > <column name="GENRE_ID" type="INTEGER"/>
> > > > <foreign-key foreignTable="GENRE">
> > > > <reference local="GENRE_ID" foreign="ID"/>
> > > > </foreign-key>
> > > > </table>
> > > > <table name="WEBSITE">
> > > > <column name="ID" type="INTEGER" required="true"
> > > > autoIncrement="true" primaryKey="true"/>
> > > > <column name="NAME" type="VARCHAR" size="100" required="true"/>
> > > > <column name="URL" type="VARCHAR" size="200" required="true"/>
> > > > </table>
> > > > <table name="ARTIST_HAS_WEBSITE">
> > > > <column name="ARTIST_ID" type="INTEGER" required="true"
> > > > primaryKey="true"/>
> > > > <foreign-key foreignTable="ARTIST">
> > > > <reference local="ARTIST_ID" foreign="ID"/>
> > > > </foreign-key>
> > > > <column name="WEBSITE_ID" type="INTEGER" required="true"
> > > > primaryKey="true"/>
> > > > <foreign-key foreignTable="WEBSITE">
> > > > <reference local="WEBSITE_ID" foreign="ID"/>
> > > > </foreign-key>
> > > > </table>
> > > >
> > > > bye
> > > > fabian
> > > >
> > > > On Thu, 2002-01-24 at 07:28, John McNally wrote:
> > > > > The first example should work. You might want to show your schema.xml,
> > > > > to give others some idea what might be wrong. artist1.getPrimaryKey()
> > > > > should give the id assigned after saving the object. It should not be
> > > > > null. If it is something is wrong with your xml.
> > > > >
> > > > > john mcnally
> > > > >
> > > > > >
> > > > > > this doesn't work:
> > > > > >
> > > > > > Artist artist1 = new Artist();
> > > > > > artist1.setName("Primus");
> > > > > > artist1.save();
> > > > > >
> > > > > > Website website1 = new Website();
> > > > > > website1.setName("Primus Online");
> > > > > > website1.setUrl("http://www.primussucks.com");
> > > > > > website1.save();
> > > > > >
> > > > > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > > > > link.setArtist(artist1);
> > > > > > link.setWebsite(website1);
> > > > > > link.save(); //Column 'ARTIST_ID' cannot be null
> > > > > >
> > > > > > this does work:
> > > > > >
> > > > > > Artist artist1 = new Artist();
> > > > > > artist1.setName("Primus");
> > > > > > artist1.save();
> > > > > >
> > > > > > Website website1 = new Website();
> > > > > > website1.setName("Primus Online");
> > > > > > website1.setUrl("http://www.primussucks.com");
> > > > > > website1.save();
> > > > > >
> > > > > > Artist artist2 = ArtistFactory.findByName(artist1.getName());
> > > > > > Website website2 = WebsiteFactory.findByUrl(website1.getUrl());
> > > > > >
> > > > > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > > > > link.setArtist(artist2);
> > > > > > link.setWebsite(website2);
> > > > > > link.save();
> > > > > >
> > > > > > tried defaultIdMethod native and idBroker by now.
> > > > > >
> > > > > > bye
> > > > > > fabian
> > > > > >
> > > > > > --
> > > > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > > > >
> > > > > --
> > > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > > > >
> > > > >
> > > >
> > > > --
> > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > >
> > > --
> > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > >
> > >
> >
> >
> > --
> > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > For additional commands, e-mail: <ma...@jakarta.apache.org>
> >
> >
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: torque: mysql, idbroker, autoincrement and foreign keys
Posted by Fabian Moerchen <fa...@mybytes.de>.
hi
pete solved the problem after trying my code:
you should always start the schema definition with
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<!DOCTYPE database SYSTEM
"http://jakarta.apache.org/turbine/dtd/database.dtd">
so the schema gets checked for errors.
in my case the error was that foreign key definitions have to be after
the column definitions in the table element.
bye
fabian
On Thu, 2002-01-24 at 22:47, Fabian Moerchen wrote:
> i had a default method of native for the database tag all the time.
> still i tried many different combinations of defaultIdMethod and
> idMethod now but all produced the same, correct (?) SQL for the
> relation:
>
> CREATE TABLE ARTIST_HAS_WEBSITE
> (
> ARTIST_ID INTEGER NOT NULL,
> WEBSITE_ID INTEGER NOT NULL,
> PRIMARY KEY(ARTIST_ID,WEBSITE_ID),
> FOREIGN KEY (ARTIST_ID) REFERENCES ARTIST (ID),
> FOREIGN KEY (WEBSITE_ID) REFERENCES WEBSITE (ID)
> );
>
> and e.g. for the websites
>
> CREATE TABLE WEBSITE
> (
> ID INTEGER NOT NULL AUTO_INCREMENT,
> NAME VARCHAR (100) NOT NULL,
> URL VARCHAR (200) NOT NULL,
> PRIMARY KEY(ID)
> );
>
> still artist1.getPrimaryKey() give me always null after saving and the
> other problems described also persist.
>
> bye
> fabian
>
> On Thu, 2002-01-24 at 17:49, John McNally wrote:
> > I notice you are not specifying an idmethod attribute on your tables,
> > are you specifying a default. I suspect not as then you would probably
> > have the attribute on your ARTIST_HAS_WEBSITE table.
> >
> > Use idmethod="native" or idmethod="autoincrement", if native does not
> > work, on tables that have generated pk's. And then use idmethod="none"
> > on tables such as ARTIST_HAS_WEBSITE.
> >
> > john mcnally
> >
> > Fabian Moerchen wrote:
> > >
> > > here's (part of) my schema. did i do something wrong?
> > > could very well be since it's my first. ;)
> > >
> > > <table name="GENRE">
> > > <column name="ID" type="INTEGER" required="true"
> > > autoIncrement="true" primaryKey="true"/>
> > > <column name="NAME" type="VARCHAR" size="50" required="true"/>
> > > <unique>
> > > <unique-column name="NAME"/>
> > > </unique>
> > > </table>
> > > <table name="ARTIST">
> > > <column name="ID" type="INTEGER" required="true"
> > > autoIncrement="true" primaryKey="true"/>
> > > <column name="NAME" type="VARCHAR" size="100" required="true"/>
> > > <column name="PATTERN" type="VARCHAR" size="255"/>
> > > <column name="SORTNAME" type="VARCHAR" size="255"/>
> > > <column name="GENRE_ID" type="INTEGER"/>
> > > <foreign-key foreignTable="GENRE">
> > > <reference local="GENRE_ID" foreign="ID"/>
> > > </foreign-key>
> > > </table>
> > > <table name="WEBSITE">
> > > <column name="ID" type="INTEGER" required="true"
> > > autoIncrement="true" primaryKey="true"/>
> > > <column name="NAME" type="VARCHAR" size="100" required="true"/>
> > > <column name="URL" type="VARCHAR" size="200" required="true"/>
> > > </table>
> > > <table name="ARTIST_HAS_WEBSITE">
> > > <column name="ARTIST_ID" type="INTEGER" required="true"
> > > primaryKey="true"/>
> > > <foreign-key foreignTable="ARTIST">
> > > <reference local="ARTIST_ID" foreign="ID"/>
> > > </foreign-key>
> > > <column name="WEBSITE_ID" type="INTEGER" required="true"
> > > primaryKey="true"/>
> > > <foreign-key foreignTable="WEBSITE">
> > > <reference local="WEBSITE_ID" foreign="ID"/>
> > > </foreign-key>
> > > </table>
> > >
> > > bye
> > > fabian
> > >
> > > On Thu, 2002-01-24 at 07:28, John McNally wrote:
> > > > The first example should work. You might want to show your schema.xml,
> > > > to give others some idea what might be wrong. artist1.getPrimaryKey()
> > > > should give the id assigned after saving the object. It should not be
> > > > null. If it is something is wrong with your xml.
> > > >
> > > > john mcnally
> > > >
> > > > >
> > > > > this doesn't work:
> > > > >
> > > > > Artist artist1 = new Artist();
> > > > > artist1.setName("Primus");
> > > > > artist1.save();
> > > > >
> > > > > Website website1 = new Website();
> > > > > website1.setName("Primus Online");
> > > > > website1.setUrl("http://www.primussucks.com");
> > > > > website1.save();
> > > > >
> > > > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > > > link.setArtist(artist1);
> > > > > link.setWebsite(website1);
> > > > > link.save(); //Column 'ARTIST_ID' cannot be null
> > > > >
> > > > > this does work:
> > > > >
> > > > > Artist artist1 = new Artist();
> > > > > artist1.setName("Primus");
> > > > > artist1.save();
> > > > >
> > > > > Website website1 = new Website();
> > > > > website1.setName("Primus Online");
> > > > > website1.setUrl("http://www.primussucks.com");
> > > > > website1.save();
> > > > >
> > > > > Artist artist2 = ArtistFactory.findByName(artist1.getName());
> > > > > Website website2 = WebsiteFactory.findByUrl(website1.getUrl());
> > > > >
> > > > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > > > link.setArtist(artist2);
> > > > > link.setWebsite(website2);
> > > > > link.save();
> > > > >
> > > > > tried defaultIdMethod native and idBroker by now.
> > > > >
> > > > > bye
> > > > > fabian
> > > > >
> > > > > --
> > > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > > >
> > > > --
> > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > > >
> > > >
> > >
> > > --
> > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> >
> > --
> > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > For additional commands, e-mail: <ma...@jakarta.apache.org>
> >
> >
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: torque: mysql, idbroker, autoincrement and foreign keys
Posted by Fabian Moerchen <fa...@mybytes.de>.
i had a default method of native for the database tag all the time.
still i tried many different combinations of defaultIdMethod and
idMethod now but all produced the same, correct (?) SQL for the
relation:
CREATE TABLE ARTIST_HAS_WEBSITE
(
ARTIST_ID INTEGER NOT NULL,
WEBSITE_ID INTEGER NOT NULL,
PRIMARY KEY(ARTIST_ID,WEBSITE_ID),
FOREIGN KEY (ARTIST_ID) REFERENCES ARTIST (ID),
FOREIGN KEY (WEBSITE_ID) REFERENCES WEBSITE (ID)
);
and e.g. for the websites
CREATE TABLE WEBSITE
(
ID INTEGER NOT NULL AUTO_INCREMENT,
NAME VARCHAR (100) NOT NULL,
URL VARCHAR (200) NOT NULL,
PRIMARY KEY(ID)
);
still artist1.getPrimaryKey() give me always null after saving and the
other problems described also persist.
bye
fabian
On Thu, 2002-01-24 at 17:49, John McNally wrote:
> I notice you are not specifying an idmethod attribute on your tables,
> are you specifying a default. I suspect not as then you would probably
> have the attribute on your ARTIST_HAS_WEBSITE table.
>
> Use idmethod="native" or idmethod="autoincrement", if native does not
> work, on tables that have generated pk's. And then use idmethod="none"
> on tables such as ARTIST_HAS_WEBSITE.
>
> john mcnally
>
> Fabian Moerchen wrote:
> >
> > here's (part of) my schema. did i do something wrong?
> > could very well be since it's my first. ;)
> >
> > <table name="GENRE">
> > <column name="ID" type="INTEGER" required="true"
> > autoIncrement="true" primaryKey="true"/>
> > <column name="NAME" type="VARCHAR" size="50" required="true"/>
> > <unique>
> > <unique-column name="NAME"/>
> > </unique>
> > </table>
> > <table name="ARTIST">
> > <column name="ID" type="INTEGER" required="true"
> > autoIncrement="true" primaryKey="true"/>
> > <column name="NAME" type="VARCHAR" size="100" required="true"/>
> > <column name="PATTERN" type="VARCHAR" size="255"/>
> > <column name="SORTNAME" type="VARCHAR" size="255"/>
> > <column name="GENRE_ID" type="INTEGER"/>
> > <foreign-key foreignTable="GENRE">
> > <reference local="GENRE_ID" foreign="ID"/>
> > </foreign-key>
> > </table>
> > <table name="WEBSITE">
> > <column name="ID" type="INTEGER" required="true"
> > autoIncrement="true" primaryKey="true"/>
> > <column name="NAME" type="VARCHAR" size="100" required="true"/>
> > <column name="URL" type="VARCHAR" size="200" required="true"/>
> > </table>
> > <table name="ARTIST_HAS_WEBSITE">
> > <column name="ARTIST_ID" type="INTEGER" required="true"
> > primaryKey="true"/>
> > <foreign-key foreignTable="ARTIST">
> > <reference local="ARTIST_ID" foreign="ID"/>
> > </foreign-key>
> > <column name="WEBSITE_ID" type="INTEGER" required="true"
> > primaryKey="true"/>
> > <foreign-key foreignTable="WEBSITE">
> > <reference local="WEBSITE_ID" foreign="ID"/>
> > </foreign-key>
> > </table>
> >
> > bye
> > fabian
> >
> > On Thu, 2002-01-24 at 07:28, John McNally wrote:
> > > The first example should work. You might want to show your schema.xml,
> > > to give others some idea what might be wrong. artist1.getPrimaryKey()
> > > should give the id assigned after saving the object. It should not be
> > > null. If it is something is wrong with your xml.
> > >
> > > john mcnally
> > >
> > > >
> > > > this doesn't work:
> > > >
> > > > Artist artist1 = new Artist();
> > > > artist1.setName("Primus");
> > > > artist1.save();
> > > >
> > > > Website website1 = new Website();
> > > > website1.setName("Primus Online");
> > > > website1.setUrl("http://www.primussucks.com");
> > > > website1.save();
> > > >
> > > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > > link.setArtist(artist1);
> > > > link.setWebsite(website1);
> > > > link.save(); //Column 'ARTIST_ID' cannot be null
> > > >
> > > > this does work:
> > > >
> > > > Artist artist1 = new Artist();
> > > > artist1.setName("Primus");
> > > > artist1.save();
> > > >
> > > > Website website1 = new Website();
> > > > website1.setName("Primus Online");
> > > > website1.setUrl("http://www.primussucks.com");
> > > > website1.save();
> > > >
> > > > Artist artist2 = ArtistFactory.findByName(artist1.getName());
> > > > Website website2 = WebsiteFactory.findByUrl(website1.getUrl());
> > > >
> > > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > > link.setArtist(artist2);
> > > > link.setWebsite(website2);
> > > > link.save();
> > > >
> > > > tried defaultIdMethod native and idBroker by now.
> > > >
> > > > bye
> > > > fabian
> > > >
> > > > --
> > > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > >
> > > --
> > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> > >
> > >
> >
> > --
> > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > For additional commands, e-mail: <ma...@jakarta.apache.org>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
USER_ID as foreign key
Posted by Bruce Altner <ba...@hq.nasa.gov>.
Hi:
I'm adding a table in my project-schema.xml that references USER_ID in
TURBINE_USER as a foreign key, this way:
<foreign-key foreignTable="TURBINE_USER">
<reference local="USER_ID" foreign="USER_ID"/>
</foreign-key>
But when I try to build the classes with Torque using ant init I get the error:
[torque-om] ERROR!! Attempt to set foreign key to nonexistent table,
TURBINE_USER!
According to the ant output TURBINE_USER is already created so I don't
understand this.
To get around it I put the turbine-generated xml for TURBINE_USER from
turbine-schema.xml into the project-schema.xml file. This avoids the error
but it results in the Peer classes being generated for TURBINE_USER, as if
this were a totally new table. Can I just delete these afterwards?
Thanks,
Bruce
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: torque: mysql, idbroker, autoincrement and foreign keys
Posted by John McNally <jm...@collab.net>.
I notice you are not specifying an idmethod attribute on your tables,
are you specifying a default. I suspect not as then you would probably
have the attribute on your ARTIST_HAS_WEBSITE table.
Use idmethod="native" or idmethod="autoincrement", if native does not
work, on tables that have generated pk's. And then use idmethod="none"
on tables such as ARTIST_HAS_WEBSITE.
john mcnally
Fabian Moerchen wrote:
>
> here's (part of) my schema. did i do something wrong?
> could very well be since it's my first. ;)
>
> <table name="GENRE">
> <column name="ID" type="INTEGER" required="true"
> autoIncrement="true" primaryKey="true"/>
> <column name="NAME" type="VARCHAR" size="50" required="true"/>
> <unique>
> <unique-column name="NAME"/>
> </unique>
> </table>
> <table name="ARTIST">
> <column name="ID" type="INTEGER" required="true"
> autoIncrement="true" primaryKey="true"/>
> <column name="NAME" type="VARCHAR" size="100" required="true"/>
> <column name="PATTERN" type="VARCHAR" size="255"/>
> <column name="SORTNAME" type="VARCHAR" size="255"/>
> <column name="GENRE_ID" type="INTEGER"/>
> <foreign-key foreignTable="GENRE">
> <reference local="GENRE_ID" foreign="ID"/>
> </foreign-key>
> </table>
> <table name="WEBSITE">
> <column name="ID" type="INTEGER" required="true"
> autoIncrement="true" primaryKey="true"/>
> <column name="NAME" type="VARCHAR" size="100" required="true"/>
> <column name="URL" type="VARCHAR" size="200" required="true"/>
> </table>
> <table name="ARTIST_HAS_WEBSITE">
> <column name="ARTIST_ID" type="INTEGER" required="true"
> primaryKey="true"/>
> <foreign-key foreignTable="ARTIST">
> <reference local="ARTIST_ID" foreign="ID"/>
> </foreign-key>
> <column name="WEBSITE_ID" type="INTEGER" required="true"
> primaryKey="true"/>
> <foreign-key foreignTable="WEBSITE">
> <reference local="WEBSITE_ID" foreign="ID"/>
> </foreign-key>
> </table>
>
> bye
> fabian
>
> On Thu, 2002-01-24 at 07:28, John McNally wrote:
> > The first example should work. You might want to show your schema.xml,
> > to give others some idea what might be wrong. artist1.getPrimaryKey()
> > should give the id assigned after saving the object. It should not be
> > null. If it is something is wrong with your xml.
> >
> > john mcnally
> >
> > >
> > > this doesn't work:
> > >
> > > Artist artist1 = new Artist();
> > > artist1.setName("Primus");
> > > artist1.save();
> > >
> > > Website website1 = new Website();
> > > website1.setName("Primus Online");
> > > website1.setUrl("http://www.primussucks.com");
> > > website1.save();
> > >
> > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > link.setArtist(artist1);
> > > link.setWebsite(website1);
> > > link.save(); //Column 'ARTIST_ID' cannot be null
> > >
> > > this does work:
> > >
> > > Artist artist1 = new Artist();
> > > artist1.setName("Primus");
> > > artist1.save();
> > >
> > > Website website1 = new Website();
> > > website1.setName("Primus Online");
> > > website1.setUrl("http://www.primussucks.com");
> > > website1.save();
> > >
> > > Artist artist2 = ArtistFactory.findByName(artist1.getName());
> > > Website website2 = WebsiteFactory.findByUrl(website1.getUrl());
> > >
> > > ArtistHasWebsite link = new ArtistHasWebsite();
> > > link.setArtist(artist2);
> > > link.setWebsite(website2);
> > > link.save();
> > >
> > > tried defaultIdMethod native and idBroker by now.
> > >
> > > bye
> > > fabian
> > >
> > > --
> > > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > > For additional commands, e-mail: <ma...@jakarta.apache.org>
> >
> > --
> > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > For additional commands, e-mail: <ma...@jakarta.apache.org>
> >
> >
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: torque: mysql, idbroker, autoincrement and foreign keys
Posted by Fabian Moerchen <fa...@mybytes.de>.
here's (part of) my schema. did i do something wrong?
could very well be since it's my first. ;)
<table name="GENRE">
<column name="ID" type="INTEGER" required="true"
autoIncrement="true" primaryKey="true"/>
<column name="NAME" type="VARCHAR" size="50" required="true"/>
<unique>
<unique-column name="NAME"/>
</unique>
</table>
<table name="ARTIST">
<column name="ID" type="INTEGER" required="true"
autoIncrement="true" primaryKey="true"/>
<column name="NAME" type="VARCHAR" size="100" required="true"/>
<column name="PATTERN" type="VARCHAR" size="255"/>
<column name="SORTNAME" type="VARCHAR" size="255"/>
<column name="GENRE_ID" type="INTEGER"/>
<foreign-key foreignTable="GENRE">
<reference local="GENRE_ID" foreign="ID"/>
</foreign-key>
</table>
<table name="WEBSITE">
<column name="ID" type="INTEGER" required="true"
autoIncrement="true" primaryKey="true"/>
<column name="NAME" type="VARCHAR" size="100" required="true"/>
<column name="URL" type="VARCHAR" size="200" required="true"/>
</table>
<table name="ARTIST_HAS_WEBSITE">
<column name="ARTIST_ID" type="INTEGER" required="true"
primaryKey="true"/>
<foreign-key foreignTable="ARTIST">
<reference local="ARTIST_ID" foreign="ID"/>
</foreign-key>
<column name="WEBSITE_ID" type="INTEGER" required="true"
primaryKey="true"/>
<foreign-key foreignTable="WEBSITE">
<reference local="WEBSITE_ID" foreign="ID"/>
</foreign-key>
</table>
bye
fabian
On Thu, 2002-01-24 at 07:28, John McNally wrote:
> The first example should work. You might want to show your schema.xml,
> to give others some idea what might be wrong. artist1.getPrimaryKey()
> should give the id assigned after saving the object. It should not be
> null. If it is something is wrong with your xml.
>
> john mcnally
>
> >
> > this doesn't work:
> >
> > Artist artist1 = new Artist();
> > artist1.setName("Primus");
> > artist1.save();
> >
> > Website website1 = new Website();
> > website1.setName("Primus Online");
> > website1.setUrl("http://www.primussucks.com");
> > website1.save();
> >
> > ArtistHasWebsite link = new ArtistHasWebsite();
> > link.setArtist(artist1);
> > link.setWebsite(website1);
> > link.save(); //Column 'ARTIST_ID' cannot be null
> >
> > this does work:
> >
> > Artist artist1 = new Artist();
> > artist1.setName("Primus");
> > artist1.save();
> >
> > Website website1 = new Website();
> > website1.setName("Primus Online");
> > website1.setUrl("http://www.primussucks.com");
> > website1.save();
> >
> > Artist artist2 = ArtistFactory.findByName(artist1.getName());
> > Website website2 = WebsiteFactory.findByUrl(website1.getUrl());
> >
> > ArtistHasWebsite link = new ArtistHasWebsite();
> > link.setArtist(artist2);
> > link.setWebsite(website2);
> > link.save();
> >
> > tried defaultIdMethod native and idBroker by now.
> >
> > bye
> > fabian
> >
> > --
> > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > For additional commands, e-mail: <ma...@jakarta.apache.org>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: torque: mysql, idbroker, autoincrement and foreign keys
Posted by John McNally <jm...@collab.net>.
The first example should work. You might want to show your schema.xml,
to give others some idea what might be wrong. artist1.getPrimaryKey()
should give the id assigned after saving the object. It should not be
null. If it is something is wrong with your xml.
john mcnally
>
> this doesn't work:
>
> Artist artist1 = new Artist();
> artist1.setName("Primus");
> artist1.save();
>
> Website website1 = new Website();
> website1.setName("Primus Online");
> website1.setUrl("http://www.primussucks.com");
> website1.save();
>
> ArtistHasWebsite link = new ArtistHasWebsite();
> link.setArtist(artist1);
> link.setWebsite(website1);
> link.save(); //Column 'ARTIST_ID' cannot be null
>
> this does work:
>
> Artist artist1 = new Artist();
> artist1.setName("Primus");
> artist1.save();
>
> Website website1 = new Website();
> website1.setName("Primus Online");
> website1.setUrl("http://www.primussucks.com");
> website1.save();
>
> Artist artist2 = ArtistFactory.findByName(artist1.getName());
> Website website2 = WebsiteFactory.findByUrl(website1.getUrl());
>
> ArtistHasWebsite link = new ArtistHasWebsite();
> link.setArtist(artist2);
> link.setWebsite(website2);
> link.save();
>
> tried defaultIdMethod native and idBroker by now.
>
> bye
> fabian
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: torque: mysql, idbroker, autoincrement and foreign keys
Posted by Fabian Moerchen <fa...@mybytes.de>.
On Thu, 2002-01-24 at 06:04, Eric Dobbs wrote:
>
> On Wednesday, January 23, 2002, at 11:01 AM, Fabian Moerchen wrote:
>
> > i'm using the latest torque with mysql and defaultIdMethod="native".
> >
> > the next problem is creating objects connected with foreign keys. e.g.
> > if i create a new object Artist and call save() it does not know it's
> > own primary key yet, since mysql decides about the value, right? because
> > when trying to tie it to another Website (they are connected via a m:n
> > relationship) and saving this i get
> >
> > java.sql.SQLException: General error: Column 'ARTIST_ID' cannot be null
> >
> > if i create artist and website first, retrieve them into new objects,
> > create the relationship and save then it works, but this is not very
> > convenient. especially since i search for them by one of the other
> > attributes (name) which is _not unique_, so it works in my small test
> > setup, but:
> >
> > isn't there a bulletproof way to get the pk of the object just saved?
>
> NumberKey key = artist.save();
but save() returns void
>
> But I think what you really want to do is something like this:
>
> Artist artist = new Artist();
> Website site = new WebSite();
> //set various properties of artist and site;
> artist.setWebsite(site);
> artist.save();
>
> The save() method should connect the two records for you. And
> as an added bonus, if you use a database that supports
> transactions it'll add (or update) those records within a
> transaction.
but that's exactly what doesn't work. maybe it's because of n:m?
what i really do is the following.
this doesn't work:
Artist artist1 = new Artist();
artist1.setName("Primus");
artist1.save();
Website website1 = new Website();
website1.setName("Primus Online");
website1.setUrl("http://www.primussucks.com");
website1.save();
ArtistHasWebsite link = new ArtistHasWebsite();
link.setArtist(artist1);
link.setWebsite(website1);
link.save(); //Column 'ARTIST_ID' cannot be null
this does work:
Artist artist1 = new Artist();
artist1.setName("Primus");
artist1.save();
Website website1 = new Website();
website1.setName("Primus Online");
website1.setUrl("http://www.primussucks.com");
website1.save();
Artist artist2 = ArtistFactory.findByName(artist1.getName());
Website website2 = WebsiteFactory.findByUrl(website1.getUrl());
ArtistHasWebsite link = new ArtistHasWebsite();
link.setArtist(artist2);
link.setWebsite(website2);
link.save();
tried defaultIdMethod native and idBroker by now.
bye
fabian
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: torque: mysql, idbroker, autoincrement and foreign keys
Posted by Eric Dobbs <er...@dobbse.net>.
On Wednesday, January 23, 2002, at 11:01 AM, Fabian Moerchen wrote:
> i'm using the latest torque with mysql and defaultIdMethod="native".
>
> the next problem is creating objects connected with foreign keys. e.g.
> if i create a new object Artist and call save() it does not know it's
> own primary key yet, since mysql decides about the value, right? because
> when trying to tie it to another Website (they are connected via a m:n
> relationship) and saving this i get
>
> java.sql.SQLException: General error: Column 'ARTIST_ID' cannot be null
>
> if i create artist and website first, retrieve them into new objects,
> create the relationship and save then it works, but this is not very
> convenient. especially since i search for them by one of the other
> attributes (name) which is _not unique_, so it works in my small test
> setup, but:
>
> isn't there a bulletproof way to get the pk of the object just saved?
NumberKey key = artist.save();
But I think what you really want to do is something like this:
Artist artist = new Artist();
Website site = new WebSite();
//set various properties of artist and site;
artist.setWebsite(site);
artist.save();
The save() method should connect the two records for you. And
as an added bonus, if you use a database that supports
transactions it'll add (or update) those records within a
transaction.
-Eric
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>