I wonder if someone from Servoy can clarify the situation on table updates when a solution is imported into a server.
We had some issues a while ago with Identity Columns and ntext \ nvarchars etc, and it appears we have another similar issue now. Here are our queries.
When a solution is imported to a server with table differences to those previously deployed:
Does the server create the new tables using the same column types as the imported version? (We appear to be getting ntext and nvarchars instead of and varchars).
Does the server create index’s that were in place on the tables that were exported? (Looks like index’s are not being crossed over).
When tables exist in both exported versions and the current deployment, are any changes to the table updated on the server version? (as above it seems changes are not translated to the server correctly).
When the current version has a column as varchar (for example) and the exported version is changed to integer and set as an Identity column, does this get updated in the server version? (See above).
Currently we have discovered a number of columns in a long standing deployment, which has had no more than export’s imported, yet the data base is pretty well out of kilter with our development tables now. We are struggling to determine how this could have happened other than through the import not correctly updating the server table?
This is all on MSSQL Server 2008 R2 BTW. Now using 6.0.1.
Any feedback would be welcome so we can get a plan in place to manage this in future.
After some experimentation, I can conclude the following.
If I create a new column in my main Development database as varchar(whatever length), and then perform a solution update on my production server.
The columns that are being created by Servoy are nvarchar every time, the same behaviour is happening with char and text also, with Servoy creating ntext and nchar columns respectively.
This is a major pain, is there any way round this, surly we dont have to manually edit the columns Servoy has created.
Every solution update is resulting in our system failing because Servoy creates any new columns as ntext nvarchar, nchar etc. instead of the actual column types as we created the DB (varchar etc).
Is this a function of the JDBC Driver for MSSQL or is it something in Servoy.
We have discussed this before on the forum but we were advised that this would be corrected in later versions (6) though the topic was around varchar(max). That does not explain why the other data types are being incorrectly created now?
Datamodel updates and drops are never performed when you import a new release of your solution in a servoy server, it has always been like this and makes perfectly sense. If you update your datamodel in developement you need to make sure that you update the production datamodel accordingly in your server backend.
The fact that varchar(x) columns are created as nvarchar doesn’t sound right to me, I’ve been using MS SQL Server in the past (Servoy 3.5 and 4.1) and it used to work fine so probably something wrong is happening in the jdbc driver or in servoy itself, I suggest you to file a case in the support system, this is the fastest way to get support.
Hi Nicola - thanks for that feedback. You have confused me… not hard to do I admit
ngervasi:
Datamodel updates and drops are never performed when you import a new release of your solution in a servoy server, it has always been like this and makes perfectly sense. If you update your datamodel in developement you need to make sure that you update the production datamodel accordingly in your server backend.
When importing a solution to a server - the data model is updated as expected (I belivce there is a switch for it). Whetehr that process drops the tables and re-creates them I’m not sure. However where we add new tables or change a column property - in general the data model is sync’d.
ngervasi:
The fact that varchar(x) columns are created as nvarchar doesn’t sound right to me, I’ve been using MS SQL Server in the past (Servoy 3.5 and 4.1) and it used to work fine so probably something wrong is happening in the jdbc driver or in servoy itself, I suggest you to file a case in the support system, this is the fastest way to get support.
Yes this was only on varchar(MAX) in the beginning but now its on any text field. Unfortunately non of the ntext type columns can be used as triggers in MSSQL and that is now causing us a headache.
We have one production server and 2 test servers working (one cloud test and one in-house test) and it can be an entire day of corrections just for a single upgrade of a solution.
One of the biggest problems is where we have a complex index or compound ID on a table (in some very old parts of the DB structure) we cant drop and change the column type without removing the index and re-building it and with compound ID’s we cant change them at all. So all in all the structure sync is be coming a bit of a nightmare.
Hi Kahuna,
when you upload a new release with some datamodel changes (anf you check “apply datamodel changes”) servoy does the following:
it creates new tables if missing in the target db
it creates new columns if missing in the target db tables
It does NOT do the following:
drop any table that you may have removed from your development db
drop any column that you may have removed from your development db
change any column that is already in place in the target db
create any index
number 1 and 2 of the “does not” list are quite easy to understand: dropping columns or tables could cause dataloss and those table columns could be used by other applications beside servoy.
Number 3: changing column type could not be possible if the target db is different from the development db and/or it could cause data loss.
Number 4: there is no standard for creating indexes in SQL, every db server has his own dialect.
This means that: if you change existing columns or if you drop existing columns or tables you will have to do that in your deployment servers manually. Indexes should be created manually. If you add new stuff servoy will take care of updating your databse (and clone dbs) easily.