Migrating solution from PGSQL to MS SQL Server UUID PK issue

Questions
1

Hi,
I am trying to migrate solution I created using PostgreSQL as back end to a server using MS SQL Server as database server.
I use UUID’s as my PK’s in every table.

id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,

[attachment=1]PGSQLTable.png[/attachment]
I thought that by exporting my solution with all tables & data included would allow me to import this on to new server and Servoy would automatically take care of creating the correct columns in the new database ‘language’ and all would work nicely.
I installed the latest MS JDBC driver for SQL Server, but on solution import, I get this error

[error] com.microsoft.sqlserver.jdbc.SQLServerException: Identity column 'id' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, unencrypted, and constrained to be nonnullable.

If I manually create a new database in Servoy Developer on that computer, it correctly sets it to type ‘uniqueidentifier’ & nonnullable, but this doesn’t seem to happen when migrating.
[attachment=0]MSSQLTable.png[/attachment]
My database URL is like this

jdbc:sqlserver://localhost:1433;DatabaseName=<database_name>;SelectMethod=direct;sendTimeAsDateTime=false;encrypt=false;

Is there some parameter or something else I should be setting to make sure it creates the tables correctly??
Thanks
Rafi

MSSQLTable.png
MSSQLTable.png1224×428 53.6 KB

PGSQLTable.png
PGSQLTable.png1088×1012 85.8 KB

2

Hi Rafi,

It looks like the migration fails because the column is marked as dbidentity.

How did you create the table in postgres?
From Servoy developer creating a dbidentity uuid column is not working/supported, but if you create one manually it will use it.
If you did something like: id uuid PRIMARY KEY DEFAULT uuid_generate_v4() manually for the table we would see that as a dbidenity column.

I guess if you create the table upfront in sqerver with column ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY it may work.

Rob

3

Hi Rob,
you are correct, I created the databases manually with PostgreSQL & then pointed Servoy at them, and as you say, Servoy shows the PK’s as ‘dbidentity’.

In future, I will create each table & just it’s PK (id) in Servoy and then do the rest using SQL (as it’s easier for me to copy and paste lots of the same columns I use in each table in a text editor than typing them in to Servoy by hand ;-)

In the end I used this tool

to migrate the tables as it converted the PK’s correctly.

Thanks

Rafi