Couple of Gotchas when working with DBsequences

Classic Servoy
1

Hi all,

For those who use database sequences things changed a bit in Servoy 4. The table editor (that is the data providers tab) doesn’t allow you to type sequence names longer than 30 characters !
This is different of how things work in Servoy 3.5.x and below.
I filed issue #165822 for this.

I also noticed when you don’t provide a sequence name in the table editor it will make one up for you using the following format: seq_tablename_pkname. Which is good.
BUT it does apply that same 30 character limit on it and will clip the name somewhere in the middle.
To make matters worse when a sequence name doesn’t exist in the back-end database it will create one for you, silently :!: .
This also happens when you make a typo when entering in the sequence name yourself. This is very much new behavior in comparison with Servoy 3.5.x and below and on the whole I think it’s a nice addition that it creates one for you.
But I also think that Servoy should notify the developer that this particular database sequence doesn’t exist yet and if he/she wants to create one in the back-end yes or no. I am sure any DBA will agree ;) .
I filed feature request #165838 for this.

So to work around all of this you can do the following;
Go to the Navigator view. If you don’t have that view in your perspective than you can add it by selecting in the menubar > Window > Show View.
In the Navigator you can see all files in your local workspace. In the resources folder you find the .dbi files (in datasource// ).
By right-clicking and selecting Open With > Text Editor you see the following:

columns:[
{
allowNull:false,
autoEnterSubType:2,
autoEnterType:2,
creationOrderIndex:0,
dataType:-5,
flags:1,
name:"pfx_id"
},

etc.

Change the ‘autoEnterSubType’ value to 1 and add ‘databaseSequenceName’ like so:

columns:[
{
allowNull:false,
autoEnterSubType:1,
autoEnterType:2,
creationOrderIndex:0,
dataType:-5,
databaseSequenceName:"seq_some_longer_tablename_pfx_id",
flags:1,
name:"pfx_id"
},

etc.

Save the dbi file and you can start entering data using the correct sequence name.

Hope this helps.

2

Robert,

that Servoy creates Sequences for you is not new to Servoy 4. Servoy 3.5.x does that as well. While there have been some bugs in this it works nicely now in 3.5.7. I don’t think the creation of a sequence is a problem for a DB admin. In the worst case there is an unused object. The non existence of a sequence that a solution relies on is much worse, in my eyes. The 30 character limit is due to Oracle (at least). Any object in Oracle (unfortunately) has this 30 character limit.

3

Hi Patrick,

patrick:
that Servoy creates Sequences for you is not new to Servoy 4. Servoy 3.5.x does that as well.

Ah, I guess I missed that.

patrick:
I don’t think the creation of a sequence is a problem for a DB admin. In the worst case there is an unused object.

Very true but if you do that at a customer site some DBA’s might look at you funny (or worse) ;)

patrick:
The non existence of a sequence that a solution relies on is much worse, in my eyes. The 30 character limit is due to Oracle (at least). Any object in Oracle (unfortunately) has this 30 character limit.

I didn’t know Oracle had that limit throughout. PostgreSQL doesn’t have this limit though and sequence names longer than 30 chars are often more the rule than the exception so this is an issue for me.
I rather have Servoy yell at me for using objectnames longer than 30 characters than outright denying me to do so.

4

I rather have Servoy yell at me for using objectnames longer than 30 characters than outright denying me to do so.

Yes and it would better fit with the handling of long table or column names…

5

Servoy aims to make solutions portable across database vendors, so we will limit the editors to the least common denominator (Oracle in this case who does limit names to 30 chars).
The same for column names, if you want to add a column whose name is a keyword in one database we will disallow it, even if he name is not a keyword in the current database.

Note that Servoy will always support what is created in the database, so if you create a table or sequence with a name of more than 30 chars, it will still work but it won’t be portable to Oracle.

Rob

6

Hi Rob,

rgansevles:
Note that Servoy will always support what is created in the database, so if you create a table or sequence with a name of more than 30 chars, it will still work but it won’t be portable to Oracle.

I do indeed use an external tool to create my databases.
When I create a table with a sequence (of any length) Servoy will default to dbidentity for that table. So I need to tell Servoy that it needs to use dbsequences. When I do that I need to fill in a sequence name or else it will assign one for me (with a 30 character limit). So I need to fill in the sequence name by hand but Servoy’s table editor doesn’t let me.
So in my view something has to give here. Or Servoy needs to automatically recognise that a table uses dbsequences and which one, or Servoy has to let me be able to type in a long sequence name.

7

Hi Robert,

I have changed the table editor so that it will allow longer sequence names, it will now show a warning instead.

Rob

8

Great! :D
Thanks!

9

I think the discovery of sequences is very hard to do, so that probably will not happen…

10

Hi Patrick,

patrick:
I think the discovery of sequences is very hard to do, so that probably will not happen…

I don’t know about Oracle but in PostgreSQL it would be doable by parsing the default value of a column.
In PostgreSQL 8.x it looks like this:

nextval('seq_tablename_pfx_id'::regclass)

or (when upgraded from PostgreSQL 7.x)

nextval(('seq_tablename_pfx_id'::text)::regclass)

and older versions of PostgreSQL (<=7.x) used

nextval('seq_tablename_pfx_id')

So it pretty much consist of checking if the function ‘nextval’ is there and then parsing the name string out of it.
Should be doable :) .

11

Now that is Postgres. In Oracle you have a sequence, which is just an object as any other (for example a table). That sequence can even be used for several tables. To allow Oracle to automatically use such a sequence you have to create a trigger that queries the sequence if the PK is empty. As you can see, a completely different implementation. How would you take that apart?

12

Hi Patrick,

patrick:
Now that is Postgres. In Oracle you have a sequence, which is just an object as any other (for example a table). That sequence can even be used for several tables.

That is exactly how it works in PostgreSQL as well. Sequences are objects (special tables) and can indeed be used by multiple columns in multiple tables.

In PostgreSQL you could use a trigger for this but by default it uses the previously described method. In any case it would be an easy way to automatically let Servoy set the correct values in the dataprovider properties.
I know it’s not perfect but right now it doesn’t discover anything what dbsequences concerns so if it can discover something it would be a plus.

13

Hi, just want to say that I use DB identities in PostgreSQL in Servoy 3.5.x.
Upgraded from 3.5.5 to 3.5.7 last weekend and 4 of my tables had their DB identities mysteriously changed to DB sequences after the upgrade. This caused all sorts of problems before I discovered what was wrong :-( Have reported this before.

14

Is it possible that there is a problem between local file and remote file?

Each time when I do a synchronize I get differences, which I do not expect and the difference is always the same:

In the remote file there is in the table.dbi file the line: databaseSequenceName:“”,
and in the local file this line is missing.

[attachment=0]compare.jpg[/attachment]

Could it be that Servoy Developer is not creating this line, while it should create it?

compare.jpg
compare.jpg898×359 70.9 KB