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
> > >
>
