Hi Victor,
SQL-Server does not support Sequences like e.g. Oracle does.
By Default the SQL-server driver uses identiy columns for the column type
AUTOINC for which the value is auto generated by the database.
In this case the function db.getNextSequenceValue() always returns NULL.
(You may detect this by calling driver.isSupported(DBDriverFeature.SEQUENCES) )
To insert a record we recommend using a DBRecord object like this:
// Insert an Employee
DBRecord rec = new DBRecord();
rec.create(db.EMPLOYEES);
rec.setValue(db.EMPLOYEES.FIRSTNAME, firstName);
rec.setValue(db.EMPLOYEES..., ...);
rec.update(conn);
// Return Employee ID
return rec.getInt(db.EMPLOYEES.EMPLOYEE_ID);
The DBRecord object will take care of all necessary task involved with auto
generated values.
However if you really want sequences with SQL-Server there is another option.
Empire-db allows sequences emulation by using a seqeunce table.
All you need to do is to enable the use of the seqeunce table by calling:
DBDatabaseDriverMSSQL driver = new DBDatabaseDriverMSSQL();
driver.setUseSequenceTable(true);
// Afterwards open the db
db.open(driver, conn);
The sequence table will be created when you let Empire-db create your database.
For existing data models you may just create the sequence table like this:
// optionally create the sequence table
DBTable seqTable = db.getTable(driver.getSequenceTableName());
DBSQLScript script = new DBSQLScript();
db.getDriver().getDDLScript(DBCmdType.CREATE, seqTable, script);
script.run(driver, conn, true);
Regards
Rainer
Victor Manuel Rojo Nava wrote:
> re: How to define and retrieve sequences for SQL Server 2005
>
> Hi all,
>
> I'm figuring out how to define a sequence with this amazing framework
> and
> then retrieve its values.
> I tried inserting a sequence name in table sequences and after that
> invoke
> the getNextSequenceValue methods for DataBase or DBDatabaseDriverMSSQL
> but I
> always get a null value. Am I doing something wrong?
>
> Regards,
>
> ---
> Victor
