Couple of Gotchas when working with DBsequences

Share business templates, ideas, experiences, etc with fellow Servoy developers here

Couple of Gotchas when working with DBsequences

Postby ROCLASI » Sun Oct 12, 2008 3:38 pm

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/<dbname>/ ).
By right-clicking and selecting Open With > Text Editor you see the following:

Code: Select all
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:

Code: Select all
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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Couple of Gotchas when working with DBsequences

Postby patrick » Sun Oct 12, 2008 7:38 pm

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.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Couple of Gotchas when working with DBsequences

Postby ROCLASI » Sun Oct 12, 2008 8:02 pm

Hi Patrick,
patrick wrote: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 wrote: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 wrote: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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Couple of Gotchas when working with DBsequences

Postby patrick » Mon Oct 13, 2008 10:33 am

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...
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Couple of Gotchas when working with DBsequences

Postby rgansevles » Mon Oct 13, 2008 11:46 am

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Couple of Gotchas when working with DBsequences

Postby ROCLASI » Mon Oct 13, 2008 11:56 am

Hi Rob,

rgansevles wrote: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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Couple of Gotchas when working with DBsequences

Postby rgansevles » Mon Oct 13, 2008 12:39 pm

Hi Robert,

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

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Couple of Gotchas when working with DBsequences

Postby ROCLASI » Mon Oct 13, 2008 1:08 pm

Great! :D
Thanks!
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Couple of Gotchas when working with DBsequences

Postby patrick » Mon Oct 13, 2008 1:18 pm

I think the discovery of sequences is very hard to do, so that probably will not happen...
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Couple of Gotchas when working with DBsequences

Postby ROCLASI » Mon Oct 13, 2008 1:30 pm

Hi Patrick,
patrick wrote: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:
Code: Select all
nextval('seq_tablename_pfx_id'::regclass)

or (when upgraded from PostgreSQL 7.x)
Code: Select all
nextval(('seq_tablename_pfx_id'::text)::regclass)

and older versions of PostgreSQL (<=7.x) used
Code: Select all
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 :) .
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Couple of Gotchas when working with DBsequences

Postby patrick » Mon Oct 13, 2008 3:52 pm

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?
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Couple of Gotchas when working with DBsequences

Postby ROCLASI » Mon Oct 13, 2008 4:33 pm

Hi Patrick,

patrick wrote: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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Couple of Gotchas when working with DBsequences

Postby swingman » Thu Oct 16, 2008 9:38 am

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.
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: Couple of Gotchas when working with DBsequences

Postby martinh » Wed Jun 03, 2009 2:20 pm

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.

compare.jpg
compare.jpg (82.64 KiB) Viewed 9064 times


Could it be that Servoy Developer is not creating this line, while it should create it?
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium


Return to Sharing Central

Who is online

Users browsing this forum: No registered users and 2 guests