RE: How to define and retrieve sequences for SQL Server 2005

[Victor Manuel Rojo Nava <vi...@asteriscogato.com>]

Thanks for the Response.
I noticed there is a table named as Sequences.
As far as I understand, I must insert on it a sequence name and the starting
value for using this feature.
The problem I have is how do I retrieve the values from it. I haven't found
on the examples how to do it.
Do you have some example about it?
I want to avoid stored procedures on my DB for doing it so.

Regards,
Victor



-----Mensaje original-----
De: Rainer Döbele [mailto:doebele@esteam.de] 
Enviado el: jueves, 11 de febrero de 2010 02:43 a.m.
Para: empire-db-user@incubator.apache.org; victor@asteriscogato.com
Asunto: re: How to define and retrieve sequences for SQL Server 2005

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