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