JSColumn.getSequencyType

[rossent]

I have a question regarding the JSColumn.getSequenceType() method. According to the documentation, it is:

Number getSequenceType() Get the sequence type of the column. The sequence type is one of:
- JSColumn.NONE
- JSColumn.SERVOY_SEQUENCE
- JSColumn.DATABASE_SEQUENCE
- JSColumn.DATABASE_IDENTITY
- JSColumn.UUID_GENERATOR

However, in the Table Column editor, the Sequence option has only the following 4 choices

- JSColumn.NONE
- JSColumn.SERVOY_SEQUENCE
- JSColumn.DATABASE_IDENTITY
- JSColumn.UUID_GENERATOR

Considering the above, what is the JSColumn.DATABASE_SEQUENCE and how can one configure the column sequence to be like that?

I am having an issue with columns which are configured as Identity columns in the database and are of type Integer. Servoy by default maps those as using a sequence type of JSColumn.DATABASE_IDENTITY but when a new record in the table is created and is not saved yet, the value of that column is something like DbIdentValue15485912 - clearly that value is not an integer. When the record is saved in the database it retrieves the correct value which the database has generated, however for that intermediate step while the record is not saved yet, the value of the integer column is not an integer and it is causing us some issues with certain validations/calculations/etc.

[patrick]

Database sequence and database identity are two different concepts and you only see the one that is available in your database.

A database sequence is a named object that counts, but independently from tables or whatever. A database identity is a column property that counts. Database identities are used by MS SQL Server for example, database sequences are used in Oracle.

[rossent]

Database sequence and database identity are two different concepts and you only see the one that is available in your database.

A database sequence is a named object that counts, but independently from tables or whatever. A database identity is a column property that counts. Database identities are used by MS SQL Server for example, database sequences are used in Oracle.

I am aware of that part - but the Servoy dbi files are supposed to "abstract" the database from the solution so you can use it against different types of back ends.

[rgansevles]

I am aware of that part - but the Servoy dbi files are supposed to "abstract" the database from the solution so you can use it against different types of back ends.

Rossen,

Servoy does not support designing using a sequence generator against a database that does not support it.
At solution import a conversion to servoy sequence is done when you have designed with db sequence of db identity and the import db does not support it.

Regarding the DbIdentValue15485912 value, this is a placeholder for the unresolved value.
Only when the record is saved we know the value and the placeholder will be updated.

Rob

[rossent]

The problem which I have is that the placeholder value is of different type (in the described case a String) and not of the type of the column (for example, DB Identity columns are Integer) . Any calculation or code which uses the value needs to check the state of the record (whether it is new and not saved yet) before it can safely use the value.

Another issue which I have is the forced dependency between the development database and the deployment database - if I develop the application using for example PostgreSQL or Oracle I will not be able to deploy it to a customer who is using MS SQL Server.

[jcompagner]

using a unstored dbident in a calculation is just a big no go.
that doesn't make any sense, we just have a a PlaceHolder object inside it so that you as a developer can set it in child records and so on before any save.
And then when you save the parent we will fill the right values in the child records before saving those. I guess in a calculation you could test the typeof of that value is it not a number you know you are in a state that you can't do anything with it (see it as a null value)

[jcompagner]

I guess if you are really targeting all kinds of databases then the best thing to do is using Servoy Sequences those will work (and those will be there when you create a new record without saving it)

[rossent]

I guess if you are really targeting all kinds of databases then the best thing to do is using Servoy Sequences those will work (and those will be there when you create a new record without saving it)

This is what I am thinking as well. Unfortunately, if records are inserted directly in the database outside of the application (not through Servoy) or by using the RawSql plugin the sequence will not be filled in...

What is the overhead of using the Servoy Sequence? Does Servoy have a separate sequence for each table that needs it or does it share one among all tables in the database? We need this automatically-generated sequence in several special cases where large amounts of records are being created in the database in batches and we need to know the order in which the records are created. Using a DateTime column does not work for us because when the insert is done in a batch, all records in that batch get the same timestamp. Will the Servoy Sequence become a bottleneck if we need to insert 10000 records in less than a second?

[rgansevles]

Rossen,

Servoy Sequence and DB Sequence are both calculated when the record is created, the overhead is a call from client to server (rmi in case of smart client); for DB Sequence also a small sql query to get the next value.

DB Identity is only calculated at insert time, the overhead is a small extra query to get the inserted value which is done at the same time of the insert, so no extra client-server (rmi) call.

Rob