Disabling the PK requirement on external databases

[Chuck.Amata]

We are attempting to link Servoy to an existing Pervasive v11 database in order to create several custom entry screens that extend the functionality of the system. The current implementation of the database and the way the database connects to Servoy through JDBC does not provide a PK for any of the tables to Servoy and cannot be modified in any way (this would include read-only flags, null-flags, etc). Since we will only be using these tables to expose "read only" information and to populate valuelists in our new solution, it should be perfectly acceptable to allow the database to connect with flat tables (e.g. no PK). Note that the primary tables containing new data within the solution will have a PK. Yet - Servoy does not seem to allow a connection to any database table that does not have a PK.

Please advise a workaround to this limitation. Perhaps some sort of "read-only" connection mode that ignores all the PK logic.

Please advise

[lwjwillemsen]

In the Servoy table editor you can use 'Row ident' to specify which column(s) should be used for unique id / reference.

If there are no columns that lead to a unique key combination then you have a problem in Servoy...

Regards,

[Chuck.Amata]

The problem on this particular table is that the field(s) that are potential candidates for row ident have the allow NULL flag checked - this causes Row Ident to be greyed out. It is not possible to modify the source database in any way or to change the data settings - the database must be used "as-is". Servoy should have the capability to link to "flat" tables (e.g tables without PK). We agree that PK is desirable in most cases, but PK is not necessary if you are linking to data in "read only" mode or using data strictly for valuelist population. The requirement of a PK with no work around in these cases seems arbitrary and unnecessary. The pervasive database structure differs from other relational databases in that the data dictionary containing keys and relationships is maintained separately from the data itself. When a table is linked through ODBC or JDBC, these relationships are not exposed or made available (hence the key-less flat table that servo sees). Many of the the recent "BIG DATA" tables being developed and used for web analytics are also flat tables with no keys.

[ROCLASI]

Hi Chuck,

Servoy fetches table-rows by way of it's row identifier, usually the PK. This is by design.
I guess as a workaround you can use in-memory tables that you fill with a query and in that query you add a column with a sequential number. Bit of a pain but that should work.
Perhaps someone else has a better solution for this.

Hope this helps.

[Chuck.Amata]

Thanks. We'll look into this. If there was a way to un-grey the row ident option (grey do to the "allow null" checkbox being checked for the field - not sure why this is even showing checked because the database definition for the field in question does not allow NULL) we could then manually assign the row id.

[jasantana]

Not sure if it would work or what could cause but try editing the dbi file for that table. I'm not in my laptop now but if you check any other dbi file which has a row ident column you will see what to do