Hi Jon,
OK, if I get it right, you insert records manually with your initial load
script and you then get an error once you insert the first record using
DBRecord.update()?
Looks to me that you are not adjusting the sequence value after you have
inserted your initial records.
Internally the PostgreSQL driver obtains a new user-id by calling SELECT
nextval('us_user_user_id_seq ').
If you have inserted values but did not adjust the sequence value then SELECT
nextval('us_user_user_id_seq ') will return 1 - just as if no records were in
the table.
I need to do this in Oracle too when I perform initial loads.
But I can't tell you how to adjust a sequence value in Postgre.
Regards
Rainer
> from: Jon Frias [mailto:[email protected]]
> to: [email protected]
> re: Re: empire db 2.4.1: running scripts and autoGenerated fields
>
> Hi Rainer,
>
> Thank you for your quick response. I will answer to your questions by bullets,
> if it's ok for you.
>
> 1- yes. It works fine
>
> 2- I use the DBScript instance to init the database information, that is, to
> register the information by default such as the user-by-default, the admin
> user role, guest user role, etc. And this script works fine also. No error is
> triggered here.
>
> 3- Once this is done, I receive an event from another program and I execute
> the "createUser" function. To do this, I use the DBRecord instance (as I said
> in
> my previous email). When the record is created, the "record.update(conn)"
> instruction triggers the unique_key_constraint_violation error. Not because
> of the "username" field, the origin of the error is the "user_id" field, that
> is,
> the autoGenerated one by the sequence.
>
> This happens because when I init the database information using the
> DBScript, the data table has this information:
>
> user_id | first_name | last_name | username | password |
> user_account_state
> |
>
> 1 | john | doe | admin | 1234 | true
>
> And when I create the next one using the DBRecord, the record is trying to
> register
>
> *1* | new | user | newUser | 5678 | true
>
> Instead of
>
> *2* | new | user | newUser | 5678 | true
>
> As you can see, the violation error is triggered because of the "user_id"
> field.
>
> It seems that when I execute the script to perform the INSERT instructions,
> the sequences of the auto generated fields (the ids of each data table) don't
> start correctly ("us_user_user_id_seq" in this case). So when I am creating
> new ones later, the sequence starts and generates an error because there
> are two different users with the same user_id.
>
> Thanks for your help
>
> Best regards,
>
> Jon
>
>
>
> 2013/10/8 Rainer Döbele <[email protected]>
>
> > Hi Jon,
> >
> > thanks for the info.
> > I am a bit confused in what you are trying to achieve.
> >
> > 1. To create the database schema (DDL) you correctly use:
> > //To create the database schema
> > DBSQLScript script = new DBSQLScript();
> > db.getCreateDDLScript(driver, script);
> > script.run(driver, conn, false);
> > db.commit();
> >
> > Does this work?
> >
> > 2. Then - as I understand it- you are using a new DBScript instance to
> > add one statement containing all inserts and execute it.
> > This is where you are getting your "unique key constraint is
> > violated", right?
> >
> > First you don't need a DBScript for that, you may as well simply call
> > db.executeSQL(stmt, null, conn, null) But I am not sure,
> > whether it can handle multiple inserts at a time.
> > Ideally you would execute each insert separately this way.
> >
> > Second if you get a unique key constraint is violated I assume that
> > this is because of your Unique index "index_username_unique".
> > Are you sure all usernames in your insert statements are unique?
> > (If you execute them one by one you will find out more easily)
> >
> > Finally I am missing the generation of the user-id form the sequence.
> > As I understand it there should be something like SELECT nextval('
> > us_user_user_id_seq ') somewhere.
> > But unfortunately I am not using PostgreSQL and I cannot really say
> > how this works there.
> >
> > I hope my comments help with your problem.
> > Regards
> > Rainer
> >
> >
> > > from: Jon Frias [mailto:[email protected]]
> > > to: [email protected]
> > > re: Re: empire db 2.4.1: running scripts and autoGenerated fields
> > >
> > > Hi Rainer,
> > >
> > > Thanks for your response and sorry for the missing information.
> > >
> > > 1- The database I am using is PostgreSQL and the driver is
> > > DBDatabaseDriverPostgreSQL
> > >
> > > 2- The data tables (40+-) are similars to each others, so here you
> > > have
> > an
> > > example:
> > >
> > > Data table Users: user_id | first_name | last_name | username |
> > > password | user_account_state |
> > >
> > > declared as follows in its corresponding class:
> > >
> > > public UsUser(DBDatabase db) {
> > > super("us_user", db);
> > > USER_ID = addColumn("user_id", DataType.INTEGER, 0,
> > > DataMode.AutoGenerated, "us_user_user_id_seq");
> > > FIRST_NAME = addColumn("first_name", DataType.CLOB, 0,
> > > DataMode.NotNull);
> > > LAST_NAME = addColumn("last_name", DataType.CLOB, 0,
> > > DataMode.NotNull);
> > > USERNAME = addColumn("username", DataType.CLOB, 0,
> > > DataMode.NotNull);
> > > PASSWORD = addColumn("password", DataType.CLOB, 0,
> > > DataMode.NotNull);
> > > USER_ACCOUNT_STATE = addColumn("user_account_state",
> > > DataType.BOOL, 10, DataMode.NotNull);
> > >
> > > setPrimaryKey(USER_ID);
> > >
> > > DBColumn[] uniqueFields = new DBColumn[1];
> > > uniqueFields[0] = USERNAME;
> > >
> > > addIndex("index_username_unique", true, uniqueFields); }
> > >
> > > 3- the script line to insert a new user at the initialization phase
> > > is
> > this:
> > > INSERT INTO us_user (first_name, last_name, username, password,
> > > user_account_state) VALUES ('john', 'doe', 'admin', '1234', true);
> > >
> > > 4- the code to generate the new user in the normal execution (not in
> > > the initialization phase)
> > > NOTE: userSettings is a class of "user" with getters and setters to
> > get/set the
> > > user information
> > >
> > > DBRecord record = new DBRecord();
> > > record.create(db.USUSER);
> > > record.setValue(db.USUSER.FIRST_NAME, userSettings.getFirstName());
> > > record.setValue(db.USUSER.LAST_NAME, userSettings.getLastName());
> > > record.setValue(db.USUSER.USERNAME, userSettings.getUserName());
> > > record.setValue(db.USUSER.PASSWORD, userSettings.getPassword());
> > > record.setValue(db.USUSER.USER_ACCOUNT_STATE,
> > > userSettings.getUserAccountState());
> > > record.update(conn);
> > >
> > >
> > > If you need any furhter information, just let me know.
> > >
> > >
> > > 5- In the examples you mentioned, in order to create a column in an
> > existing
> > > data table, the code is as follows:
> > >
> > > DBSQLScript script = new DBSQLScript();
> > > db.getDriver().getDDLScript(DBCmdType.CREATE, C_FOO, script);
> > >
> > > In my first email, my code to generate the script in order to
> > > initialize
> > the
> > > database was like this:
> > >
> > > DBSQLScript script = new DBSQLScript(); script.addStmt(sql); try{
> > > script.run(driver, conn, false);
> > > db.commit(conn);
> > > }
> > >
> > > Where, "sql" is a string variable containing all the INSERTS
> > > instructions (similars to 3rd bullet at this email); it may contain
> > > 20 insert
> > instructions more
> > > or less.
> > >
> > > I noticed that I haven't included the "db.getDriver().getDDLScript"
> > > code
> > line.
> > > Even if the script executes the insert instructions correctly, could
> > this be the
> > > error I have? If so, how it should be done? One Insert at a time? Or
> > > I
> > can
> > > execute all of them at the same time? And the second parameter, what
> > > should it be?
> > >
> > > Thanks a lot.
> > >
> > > Jon
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > 2013/10/8 Rainer Döbele <[email protected]>
> > >
> > > > Hello Jon,
> > > >
> > > > from your description I cannot really figure out what your problem is.
> > > > First thing you should tell us is what database i.e. DBMS you are
> > > > using and which Empire-db database driver (DBDatabaseDriver...)
> > > >
> > > > Next thing is, that we need to know what your database / table
> > > > definition looks like and what SQL is generated when you insert /
> > update
> > > records.
> > > > The latter should be in the logs or you can set a breakpoint in
> > > > DBRowSet.updateRecord() - around line 812.
> > > >
> > > > Finally our examples - which by default use HSQLDB - do the same
> > > > thing and you may want to look at what is different.
> > > >
> > > > In any case it should not have anything to do with DBSQLScript at all.
> > > >
> > > > Regards
> > > > Rainer
> > > >
> > > >
> > > > > from: Jon Frias [mailto:[email protected]]
> > > > > to: [email protected]
> > > > > re: empire db 2.4.1: running scripts and autoGenerated fields
> > > > >
> > > > > Hi all,
> > > > >
> > > > > I have the following problem and I don't know if this is normal
> > > > > or I am
> > > > doing
> > > > > something wrong. Can anybody help?
> > > > >
> > > > > Once I have a database schema created I want to initialize the
> > > > > first
> > > > registers.
> > > > > To do this, I am using an script (I load it and run it). Until
> > > > > now
> > > > everything
> > > > > works fine (the database schema and the first registers are
> > > > > created correctly).
> > > > >
> > > > > The problem comes after this.
> > > > >
> > > > > All the autoGenerated fields (id fields) are not working
> > > > > properly and
> > > > they
> > > > > don't take into account the registers created by the script, so
> > > > > every
> > > > time I
> > > > > create a new record after this using DBRecord class, etc. an
> > > > > error
> > > > message is
> > > > > triggered saying that the "unique key constraint is violated".
> > > > >
> > > > > I don't know if I am doing something wrong or if this is normal
> > > > > and I
> > > > have to
> > > > > avoid using the script. Because if I initialize the first
> > > > > registers by
> > > > code (using
> > > > > DBRecord class -- the same registers information), everything
> > > > > works
> > fine.
> > > > >
> > > > > The source code is the following:
> > > > >
> > > > > //To create the database schema
> > > > > DBSQLScript script = new DBSQLScript();
> > > > > db.getCreateDDLScript(driver, script); try{
> > > > > script.run(driver, conn, false);
> > > > > db.commit();
> > > > > }
> > > > >
> > > > > //To execute the script to initialize the database information
> > > > DBSQLScript
> > > > > script = new DBSQLScript(); script.addStmt(sql); try{
> > > > > script.run(driver, conn, false);
> > > > > db.commit(conn);
> > > > > }
> > > > >
> > > > > I have also tried the script step with this code but the result
> > > > > obtained
> > > > was the
> > > > > same
> > > > >
> > > > > driver.executeSQL(sql, null, conn, null); //driver is the
> > > > > DatabaseDriver instance
> > > > >
> > > > >
> > > > > Can anybody tell me if I am doing something wrong?
> > > > >
> > > > > Thanks a lot for your help.
> > > > > Best regards,
> > > > >
> > > > > Jon
> > > >
> >
