Hi Jon, is simply recreate the sequences like this:
DROP SEQUENCE MY_SEQUENCE; CREATE SEQUENCE MY_SEQUENCE INCREMENT BY 1 START WITH ?; Where ? is the highest id+1 in the table. But there may be better ways. 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 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 > > > > > > > > > > > >
