Hi Andrew, true we really need to update the javadoc on the website. The documentation in the distribution is more accurate.
I am a bit surprised about the case problem as I did not expect HSQL to be case sensitive at this point. I must admit that I am more familiar with Oracle and SQL Server. But I agree that the driver should deal with this problem to make the client code as portable as possible. The casing of a database object should not matter. The quoting of the names usually depends on key-words but can be overridden in detectQuoteName(String name) from the driver. If you find a good way to do it and you want to share your findings with us we will publish it with the next release. If you need any more help, just let us know. Regards Rainer [email protected] wrote: > Re: Retrieving auto-generated keys after insert > > Hi, > > Thanks for the detailed reply. I have now *almost* got this working > and suspect the remaining issues are bugs in my own code. Your reply > was very helpful, as was looking again at the examples (particularly > the advanced example, which I had not paid attention to earlier). > > Anyway, I am replying now (before I have everything working) partly > because I didn't want to stay silent for so long before saying > thank-you, and also because I have hit a couple of issues that you > might want to look at: > > 1 - it seems that the API documentation on the web site isn't the same > as the documentation in the download (for example, the > org.apache.empire.data.beans package is missing). > > 2 - it seems that some of the SQL related code is inconsistent about > quoting names. I initially used lower case names for everything, but > then had an error because they are not quoted during database creation > (so get changed to upper case) BUT the sequence for for HSQL does > quote the sequence name (which is then not found because it is lower > case). > > Now I just need to fix a couple more bugs in my code and I think > things will work... > > Thanks again, > Andrew > > > PS Here is the case-related error (note the quotation of the sequence > name): > org.acooke.uykfd.db.Schema$: No records found for query SELECT NEXT > VALUE FOR canonicals_seq FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES > WHERE SEQUENCE_NAME='canonicals_seq' > at > org.apache.empire.commons.ErrorObject.internalSetError(ErrorObject.java > :255) > at > org.apache.empire.commons.ErrorObject.error(ErrorObject.java:314) > at > org.apache.empire.commons.ErrorObject.error(ErrorObject.java:336) > at > org.apache.empire.db.DBDatabase.querySingleValue(DBDatabase.java:655) > at > org.apache.empire.db.hsql.DBDatabaseDriverHSql.getNextSequenceValue(DBD > atabaseDriverHSql.java:236) > at > org.apache.empire.db.DBDatabase.getNextSequenceValue(DBDatabase.java:61 > 9) > at > org.apache.empire.db.DBTableColumn.getRecordDefaultValue(DBTableColumn. > java:136) > at > org.apache.empire.db.DBRowSet.updateRecord(DBRowSet.java:659) > at org.apache.empire.db.DBRecord.update(DBRecord.java:719) > > > And here is the schema when I used lower case (as I said, this all > works if I use upper case names): > -- creating sequence for column tracks.id -- > CREATE SEQUENCE track_id_seq START WITH 1; > > -- creating sequence for column artist_tags.id -- > CREATE SEQUENCE artist_tags_seq START WITH 1; > > -- creating sequence for column canonicals.id -- > CREATE SEQUENCE canonicals_seq START WITH 1; > > -- creating sequence for column album_tags.id -- > CREATE SEQUENCE album_tags_seq START WITH 1; > > -- creating sequence for column song_title_tags.id -- > CREATE SEQUENCE song_title_tags_seq START WITH 1; > > -- creating table tracks -- > CREATE TABLE tracks ( > id BIGINT NOT NULL, > artist_tag BIGINT NOT NULL, > album_tag BIGINT NOT NULL, > song_title_tag BIGINT NOT NULL, > track_number_tag BIGINT NOT NULL, > path VARCHAR(100) NOT NULL, > CONSTRAINT tracks_PK PRIMARY KEY (id)); > > -- creating table artist_tags -- > CREATE TABLE artist_tags ( > id BIGINT NOT NULL, > value VARCHAR(100) NOT NULL, > canonical BIGINT NOT NULL, > CONSTRAINT artist_tags_PK PRIMARY KEY (id)); > > -- creating table canonicals -- > CREATE TABLE canonicals ( > id BIGINT NOT NULL, > value VARCHAR(100) NOT NULL, > CONSTRAINT canonicals_PK PRIMARY KEY (id)); > > -- creating table album_tags -- > CREATE TABLE album_tags ( > id BIGINT NOT NULL, > value VARCHAR(100) NOT NULL, > canonical BIGINT NOT NULL, > CONSTRAINT album_tags_PK PRIMARY KEY (id)); > > -- creating table song_title_tags -- > CREATE TABLE song_title_tags ( > id BIGINT NOT NULL, > value VARCHAR(100) NOT NULL, > canonical BIGINT NOT NULL, > CONSTRAINT song_title_tags_PK PRIMARY KEY (id)); > > -- creating foreign key constraint artist_tags_canonical_FK -- > ALTER TABLE artist_tags ADD CONSTRAINT artist_tags_canonical_FK > FOREIGN KEY (canonical) REFERENCES canonicals (id); > > -- creating foreign key constraint tracks_artist_tag_FK -- > ALTER TABLE tracks ADD CONSTRAINT tracks_artist_tag_FK FOREIGN KEY > (artist_tag) REFERENCES artist_tags (id); > > -- creating foreign key constraint album_tags_canonical_FK -- > ALTER TABLE album_tags ADD CONSTRAINT album_tags_canonical_FK FOREIGN > KEY (canonical) REFERENCES canonicals (id); > > -- creating foreign key constraint tracks_album_tag_FK -- > ALTER TABLE tracks ADD CONSTRAINT tracks_album_tag_FK FOREIGN KEY > (album_tag) REFERENCES album_tags (id); > > -- creating foreign key constraint song_title_tag_canonical_FK -- > ALTER TABLE song_title_tags ADD CONSTRAINT song_title_tag_canonical_FK > FOREIGN KEY (canonical) REFERENCES canonicals (id); > > -- creating foreign key constraint tracks_song_title_t_FK -- > ALTER TABLE tracks ADD CONSTRAINT tracks_song_title_t_FK FOREIGN KEY > (song_title_tag) REFERENCES song_title_tags (id);
