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
>
