Migrating solution from PGSQL to MS SQL Server UUID PK issue

[rafig]

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.

Code: Select all

    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,

PGSQLTable

PGSQLTable.png (139.9 KiB) Viewed 1355 times

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

Code: Select all

[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.

MSSQLExample

MSSQLTable.png (76.68 KiB) Viewed 1355 times

My database URL is like this

Code: Select all

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

[rgansevles]

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

[rafig]

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
https://www.convert-in.com/pgskit.htm
to migrate the tables as it converted the PK's correctly.

Thanks

Rafi