empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject re: How to define and retrieve sequences for SQL Server 2005
Date Thu, 11 Feb 2010 08:43:15 GMT
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


Mime
View raw message