problem with derby and question about mysql

From: Ivica Puljic (ivicapulji..mail.com)
Date: Tue Jul 10 2007 - 12:10:31 EDT

  • Next message: Andrus Adamchik: "Re: problems with Oracle stored procedure"

    Hi all,

    I am starting with Cayenne (using version 1.2.3), and I am trying tutorial
    example.

    First I have a problem with derby. I successfully created database schema's
    and Java classes form Cayenne modeler. Here is code from "Generate DB
    Schema":
    ===================================================
    CREATE TABLE GALLERY (ID INTEGER NOT NULL, NAME VARCHAR (200), PRIMARY KEY
    (ID));

    CREATE TABLE ARTIST (ARTIST_ID INTEGER NOT NULL, DATE_OF_BIRTH DATE, NAME
    VARCHAR (200), PRIMARY KEY (ARTIST_ID));

    CREATE TABLE PAINTING (ID INTEGER NOT NULL, NAME VARCHAR (200), PRIMARY KEY
    (ID));

    ALTER TABLE PAINTING ADD FOREIGN KEY (ID) REFERENCES ARTIST (ARTIST_ID);

    ALTER TABLE PAINTING ADD FOREIGN KEY (ID) REFERENCES GALLERY (ID);

    DROP TABLE AUTO_PK_SUPPORT;

    CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL, NEXT_ID
    INTEGER NOT NULL, PRIMARY KEY(TABLE_NAME));

    DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('ARTIST', 'GALLERY');

    INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('ARTIST', 200);

    INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('GALLERY', 200)
    ====================================================

    Seems everything is OK. Then switched to Eclipse and tried to generate object
    and then commite it to database.

      DataContext context = DataContext.createDataContext();
      Artist picasso = (Artist) context.newObject(Artist.class);
      picasso.setName("Pablo Picasso");
      picasso.setDateOfBirthString("18811025");
      context.commitChanges();

    I've got next error:

    ================================================
    INFO QueryLogger: Created connection pool: jdbc:derby:testdb;create=true
            Driver class: org.apache.derby.jdbc.EmbeddedDriver
            Min. connections in the pool: 1
            Max. connections in the pool: 1
    INFO QueryLogger: Opening connection: jdbc:derby:testdb;create=true
            Login: null
            Password: *******
    INFO QueryLogger: +++ Connecting: SUCCESS.
    INFO QueryLogger: Detected and installed adapter:
    org.objectstyle.cayenne.dba.derby.DerbyAdapter
    INFO QueryLogger: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = ?
    FOR UPDATE [bind: 'ARTIST']
    INFO QueryLogger: --- transaction started.
    INFO QueryLogger: Opening connection: jdbc:derby:testdb;create=true
            Login: null
            Password: *******
    INFO QueryLogger: +++ Connecting: SUCCESS.
    Exception in thread "main" org.objectstyle.cayenne.CayenneRuntimeException:
    [v.1.2.3 May 6 2007] Commit Exception
            at
    org.objectstyle.cayenne.access.DataContext.flushToParent(DataContext.java:1290)
            at
    org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1166)
            at cayenne.tutorial.Main.main(Main.java:30)
    Caused by: java.sql.SQLSyntaxErrorException: Table/View 'AUTO_PK_SUPPORT' does
    not exist.
    ================================================

    Seems like AUTO_PK_SUPPORT table doesn't exist. From Eclipse Database explorer
    I connected to my local derby testdb an I can verify that all tables exists.
    Then I tried to change PK Generation strategy from Default
    to "Database-Generated" and recreate database and then I have next error:

    ================================================
    ...
    INFO QueryLogger: +++ Connecting: SUCCESS.
    INFO QueryLogger: *** error.
    java.sql.SQLSyntaxErrorException: Table/View 'ARTIST' does not exist.
    ================================================

    So it seems that all tables exists, I successfully connect to derby database
    but I can't query any table.

    Then I tried to switch from derby to mysql 5.0 database. Everything went ok I
    succsesfully created and commited objects to database. When Cayenne Modeler
    generates DB Schema, I select "Create FK Support" but Modeler dosn't generate
    sql code for foreign keys. MySQL 5 have support for foreign keys, do Modeler
    support this?

    I am using:
    JDK 1.6.0
    Cayenne 1.2.3
    Derby 10.2.2.0
    MySQL connector 5.0.6
    MySQL 5.0

    Regards,
    Ivica



    This archive was generated by hypermail 2.0.0 : Tue Jul 10 2007 - 12:11:26 EDT