Hi Folks
Because of problems transferring our bases system from MySQL to SQL Server we changed all the Db Identity auto increments to ServoySequence.
Now when we run a rawSQL Insert we find we cant get the row inserted because its working outside Servoy and the sequence is not being updated (the column is of course set to NotNull).
Is there a a way to make this work with ServoySequence and rawSQL?
Hi Ian,
The thing you want is not possible I guess.
If the 3rd point isn’t really an issue, I guess you should have a dedicated range of integers that you can use to insert rows via rawSQL.
Most ideal would be if this range ends at a smaller int than Servoy is using next.
Best thing would be to just use Servoy inserting rows…
Hi Ian,
Have you tried to use the databaseManager.getNextSequence() function to get the next sequence value? I am not sure if this works for ServoySequences though.
mboegem:
Hi Ian,The thing you want is not possible I guess.
- insert a row via raw sql should have the PK set as well. Basically you could determine (query before the action) which was the last inserted int, but can change quickly in a high transaction environment.
- once you succeeded in setting the PK, servoy is not aware of this. As a result, Servoy might try setting the same int again when this is still in the sequence higher than it is right now.
- rows inserted via rawSQL can’t directly being seen via Servoy. Only after restarting the solution, all records are available again.
If the 3rd point isn’t really an issue, I guess you should have a dedicated range of integers that you can use to insert rows via rawSQL.
Most ideal would be if this range ends at a smaller int than Servoy is using next.Best thing would be to just use Servoy inserting rows…
Thanks for the feedback Marc - we need rawSQL for speed and you are right it seems a bit of a conundrum - rock and a hard place! I’ll try Roberts suggestion but its not really suitable as we would need to add a parameter for the insert (there is a subselect in this insert and the param would have to go in there).
If this is the case then we are limited to losing the database agnosticism we have been trying to retain, or not using rawSQL??
How are you dealing with needing to use multiple back-ends and rawSQL - do you use db_identity or ServoySequences?
Is it possible to use both on the same table and if so how does that work-out?
HI all
Let me just outline where exactly we are at, and why we chose to go with setup we have.
We recently had a request from our client to migrate the system from MySQL to MS SQL Server as that is the only DB supported by their infrastructure.
Upon setting this all up, creating the DB’s etc, we came across an issue when importing our solution to the app server.
The solution error’d when it came to import the solution sample data with the following
Cannot insert explicit value for identity column in table 'sec_company_databases' when IDENTITY_INSERT is set to OFF.
This was repeated for every table.
From what I can gather from google’ing this parameter ‘IDENTITY_INSERT’ is normally set per session in your script to create the DB but when importing through servoy i cant do this.
So we decided to go down the route of using Servoy sequences as it made sense at the time.
However now we are at another crossroads, should we change back or was i missing something when we first crossed over to sql server.
So I guess my question is ‘Is there anyway to configure Servoy to issue IDENTITY_INSERT on solution import?’
Also has anyone else seen this issue and if so what am I missing
Regards
You should be able to do something along the lines of “SET IDENTITY_INSERT tablename ON”, which should let you run the inserts for your migration. After that (I don’t quite remember how) but there should also be a way to have MSSQL sync up it’s sequences with whatever is already in the table so that you can continue using DBIdent for your pks.
McCourt,
You could try doing the set-identity-insert on/off in the pre/post import hook using the raw sql plugin.
Alternatively, you could create the tables with servoy sequence, and create some conversion sql:
Rob
Hi Rob
We have managed to migrate our data through managements studio, thanks for the tips.
However could you clarify for me whether or not it is possible at all to handle this all through Servoy.
Is it always the case that Servoy cannot natively insert records into a database that has DB identity fields in place.
Thanks in advance
Zuke:
Is it always the case that Servoy cannot natively insert records into a database that has DB identity fields in place.
Currently, Servoy cannot import in a table with db identity fields natively, but see in my earlier post for a workaround.
Rob
rgansevles:
Zuke:
Is it always the case that Servoy cannot natively insert records into a database that has DB identity fields in place.Currently, Servoy cannot import in a table with db identity fields natively, but see in my earlier post for a workaround.
Rob
OK Rob - is this something Servoy will consider fixing ? Should we raise a case for it ?
The challenge we have is if we use Servoy Sequences we have problems with rawSQL() where the SQL does not know about the sequence and therefore fails on creating records with missing pk’s.
Ian,
It is worth filing a feature request for it, so we can investigate.
The tricky bit for us is how to make this work on most databases since the sql to fire is very db specific.
Rob
rgansevles:
Ian,It is worth filing a feature request for it, so we can investigate.
The tricky bit for us is how to make this work on most databases since the sql to fire is very db specific.Rob
Sorry Rob - case already raised - two people doing same job here
Hi,
In Servoy 6 (b2), data import will be possible in tables with db-identity columns.
Rob