Hi Rainer, Thanks for your help.
It is exactly what happens to me. Could you please tell me how you do it in Oracle? Then I can look for the corresponding code for PostgreSQL. Thanks a lot. Best regards, Jon 2013/10/8 Rainer Döbele <[email protected]> > 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 > > > > > > > > >
