We’re having an issue or an error with an exported solution to the server. The thing is with the related tables by a primary key with db identity option.
We have the “orders” table wich is the header of other two tables. Those tables have an foreign key pointing to “orders” by “order_id”
field wich is an autoincrement value with db identity option. When we run the application with the developer it runs ok, without errors, but
we export the whole solution to the server and when we test that form we got an error, it seems servoy is trying to save the related records
first than the header one. That throws this error “Query failed: using db identity value from record that was not saved yet” and the rest of
the inserts throws an “must rollback” error because of first one.
We don’t know why it works in the developer but doesn’t work on the server. We don’t do nothing diferent, just export the solution to the
server, by the moment this is the only case we tried, but a solution for this would help if happens again.
We have tried with several Version of Servoy, always the same in Server and Developer, Version 4.1.0 -Build 651, -Build 653, -Build 655
Database: PostgreSQL 8.1
Servoy Application Server Information
Servoy version 4.1.0 i4-build 655
JDK Information
java.vm.name=Java HotSpot™ Client VM
java.vm.version=1.5.0_16-b02
java.vm.info=mixed mode, sharing
java.vm.vendor=Sun Microsystems Inc.
Operating System Information
os.name=Linux (Debian Etch)
os.version=2.6.18-6-686
IT2Be:
Otherwise I am sure he will react to this/these posts
You called?
Hi Alicelis,
Are you sure you use DB Identities with PostgreSQL ?
Also as far as I know you can’t use DB Identities with PostgreSQL. You need to use sequences instead.
So this can be database managed sequences or Servoy managed sequences.
Yes, I use sequences managed by postgreSQL, but in Servoy Developer this are showed as “db indentity”, i don’t not if this is the problem but in developer it works, but in server doesn’t work.
Db identities in Postgres are supported by Servoy since v3.5
When records are created using db identities (order) and other related records are created as well (order_detail) saving of the records have to be done in a certain order.
The order_detail record refers to the order record (order id) but the id is not known yet.
The order record has to be saved first so that the order id gets known and can be used when saving the order_detail record.
For some reason the order_detail record gets saved first resulting in the error.
Make sure that when saving records are being saved in the right order, or that all records are saved at the same time (the servoy server applies an order in that case)
You can create a case in our support system, please include a small sample if you can (or your solution if you cannot isolate the problem in a small sample)
rgansevles:
Make sure that when saving records are being saved in the right order, or that all records are saved at the same time (the servoy server applies an order in that case)
How can I make sure of that, i’m just doing databaseManager.saveData()
rgansevles:
When tables with Identity columns are created using Servoy the serial keyword is used.
select currval(‘
__seq’) is used to get the newly inserted value.
You’re right that works but only in Developer, when we export the solution to the Servoy Server is when we get the error. That is the reason of this post
rgansevles:
When tables with Identity columns are created using Servoy the serial keyword is used.
select currval(‘
__seq’) is used to get the newly inserted value.
I guess that is why it never worked for me. I create the sequences manually and I use the namingconvention ‘seq_tablename_columnname’.
Might be nice to have an option to use your own naming convention (prefix/suffix).
Another question: does Servoy create the sequences when you let Servoy create the tables? (yes I normally create them manually as well).
If not then this might be the issue for Alicelis (working in developer but not on the production server…).
rgansevles:
Make sure that when saving records are being saved in the right order, or that all records are saved at the same time (the servoy server applies an order in that case)
How can I make sure of that, i’m just doing databaseManager.saveData()
In that case Servoy sorts the records but something seems to go wrong with that.
Please create a support case.
ROCLASI:
Another question: does Servoy create the sequences when you let Servoy create the tables? (yes I normally create them manually as well).
If not then this might be the issue for Alicelis (working in developer but not on the production server…).
We create the tables manually and the sequences are created by database, not manually
We proved:
Upload the solution in Servoy Application Server conected to the very same database used in Developer
Upload the solution in Servoy Application Server conected to a new database whitout tables, letting Servoy create the tables in the import process
We use a svn repository for development, our enviroment is this:
A server with SVN + PostgreSQL + Servoy Application Server, this one has the configuration option “servoy.application_server.startRepositoryAsTeamProvider” set to False since about a month. Before that option was set to default to True and we didn’t use the .dbi files in Servoy Developer.
We watch some diferences between our .dbi files and the structure imported to the repository_server. In repository there was an old and outdated structure of the database not as in the .dbi files we manage in Developer , It seems the import process doesn’t update the structure of the database with the content of .dbi files, we don’t know if this is a right or wrong behaviour but we think the import should at least ask if you want to override or update the structure, right now it just add new things but old ones remains in the table “servoy_columninfo” in servoy_repository, that’s why we get the error because the solution imported to server was using an old structure.
This problem lead to think what’s the best way to get the structure of database sincronized, the Server and Developer ones. Because if we change things in Developer and this doesn’t update the Server at import moment, what should we do in order to this don’t happen again?
We are sincronizing Developer database structure using .dbi files because they are in svn repository. But what can we do with Server if the imported solution will use an old structure?