Servoy 2.1rc4 insisting on Servoy managed keys...

Classic Servoy
1

Hi,

I’m using mySql to store data for web pages. All primary keys are set to DB indenties.

When I imported the solution at a client site, I got a few warning messages about the primary keys, and when I open developer, the primary keys have changed to Servoy managed…

Any ideas of how to avoid this?

2

If the database at your client does not support db sequences they will be reverted to Servoy sequences

3

The database is definitvely managing the pks on its own.
The next time I see this problem I will write down the exact error messages I get.

4

Are you sure the database supports dbseqences or the dbidentity? the fallback mechanism is only towards Servoy seqences.

5

Jan…am I reading that correctly: Servoy does not support dbidentity? Does that mean you can’t use Servoy to build an interface for an existing MS SQL Server 2000 database that manages its own PKs with dbidentity?

6

Servoy does support DB identity on MS SQL, I am using those keys once in a while…

7

there are three possibilities:

  1. Servoy sequence (managed by Servoy Server)
  2. DB sequence (managed by the database)
  3. DB identity (managed by YOU)
8

What does ‘managed by you’ mean in this context?

9

JerryR:
Jan…am I reading that correctly: Servoy does not support dbidentity?

Servoy does support both dbsequence and dbidentity, but in the import we only have a fallback from dbsequence and dbidentity towards servoy seq

10

What does ‘managed by you’ mean in this context?

It means that if you have a DB Identity column, the database will not provide a primary key (that would usually be a sequence), but you have to do that. If you create a new record in a table with DB Identity turned on, you have to provide a Primary Key except the database does that by trigger or something like that.

11

Don’t mean to beat a dead horse, but I want to make sure I really get this. MS SQL Server automatically generates PKs for any column defined as an Identity. In other words, setting an MS SQL column to ‘Identity’ doesn’t just indicate that that column is to be used as the PK, it generates the PKs. I understand that the mechanism is different from that in Oracle or Sybyase, both of which support generation of sequences independent of the database column the for which the sequence value is intended as a PK. But from a Servoy perspective, does this distinction make any difference? I believe in both cases (dbidentity and dbsequence) you will want to change the Seq Setting in Servoy to 'Database Seqence", correct? Are there implications for how Servoy recognizes or treats the PK? During normal operation? During import?

12

You only set a PK column to database sequence if the database will fill the PK by itself.

There is situations, where this is not the case: if you implement “foreign data” in your solution (for example I have a table full of German banks, that comes with own PKs) that already has a primary key, the database won’t do anything in the PK column. If you want to add a record in such a table, you will have to provide a unique key yourself (that’s why I wrote “managed by you”).

In other situations you might have a table where the primary key consists of several columns. That’s not a DB sequence but a DB identity.

13

I realize I have caused a bit of confusion by not using the right vocabulary :-S

My mySQL primary keys are just a fields of type “int” which have been set to auto_increment. mySQL seems to have created an index called PRIMARY on that field.

Every time I import my project, Servoy falls back to “servoy seq”. If I create a new record I get a duplicate key error message, so I have to tell servoy I want “db identity”.

I have missed something here?

14

First of all, I have to apologize, because I did cause the confusion. I just hadn’t checked the exact vocabulary in Servoy. There you have three options:

  1. Servoy
  2. DB Identity (what I called “DB Sequence”)
  3. None (I confused with “DB Identity”)

Servoy’s terminology is clearer, as long as the column is titled “Sequence type”. I was talking about “PK type”…

To your problem: I don’t know much about mySQL, but “auto increment” sounds like a regular sequence. So in Servoy you should check “DB Identity”. This setup has worked for me (on a different DBMS…).

I did have some problems when merging repositories. Now I believe this was due to different versions of the underlaying database (one had a sequence, the other not). Actually, I would prefer if Servoy could ask me (or give me an option “Don’t touch sequence type setting” in the import dialog). I’d rather re-direct the database connection than sitting there changing sequence types…

I guess, you can call that a request :lol: