unsure.sql script need many correction
Closed this issue · 8 comments
In my case I can see queries in unsure.sql (mostly view in my case) are almost same as it is SQL Dump file. Due to this unsure.sql script has many issues and giving error. Do I need to validate each and every query in this script and correct it manually.
Sample SQL Dump file is attached "sql_server_dump_TestDB.zip". After converting this, I had to find and replace following 4 strings, then unsure.sql could work. This is just a sample dump SQL file
String: b1.C1 + '|' + b2.C1
Replaced With: concat(concat(b1.C1, '|'), b2.C1) AS C1
String: b1.C1 + '|' + ISNULL(b2.C1, '')
Replaced With: concat(concat(b1.c1, '|'), COALESCE(b2.c1::text, ''::text)) AS c1
String: CAST(T97531Id AS VARCHAR(256))+ '-' +C1
Replaced With: concat(concat(T97531.T97531Id, '-'), T97531.C1) AS c1
String: convert(NVARCHAR(15),CO11011) + ';' + convert(NVARCHAR(15),CC11011) + ';' + C11011
Replaced With: concat(concat(concat(concat("substring"(B4102.co11011::text, 1, 15), ';'), "substring"(B4102.cc11011::text, 1, 15)), ';'), B4102.c11011)
Hello @manishdwivedi02
I added some code to parse the view creation code.
This will not convert everything as full query parsing is tricky, but you can find a first iteration in #108.
Does it solve your problem?
Cordialement,
Many thanks for the fix. This had worked for sample SQL dump which I had provided. I tried on actual SQL dump, many errors are fixed but it could not work completely for that dump. First error which I got is given below
From SQL Dump - b1.C1 + '|' + CAST(ISNULL(b2.C1, '') AS VARCHAR(256))
From iteration #108 Fix - b1.C1||'|'||CAST(COALESCE(b2.C1, '') AS VARCHAR(256))
Expected (from other PostgreSQL DB) - concat(concat(b1.c1, '|'), COALESCE(b2.c1::text, ''::text)) AS c1
After correcting few errors manually, getting other errors. So I need to look into more for various new errors. Out of new error, two errors are given below.
Error1 - "(b1.C1 + N'|') + b2" - I think for this kind of aggregation + should be replaced with '||' and char N should be removed. Not sure why char N had been given in SQL
Error2 - "AND ISNULL(T4501.C1000000082,N'NA') NOT IN (SELECT ISNULL(C1000000089,N'NA') FROM T4501" - I think should be converted to "AND COALESCE(t4501.c1000000082::text, 'NA'::text) NOT IN (SELECT COALESCE(c1000000089::text, 'NA'::text) FROM T4501
Hello @manishdwivedi02
Thank you for your tests.
- First error
The migrated SQL seems to be understood by PostgreSQL.
It seeems more like a difference of vision how the code should be migrated.
Usage of "||" versus "concat()":
The difference between the two is how the null are treated. If one of the parameter is NULL, The '||' operator will return NULL. The "concat" function will ignore such parameters.
The least surprising behaviour is to return NULL on NULL input, thus use the "||" operator.
If ever you want to return an other value in case of NULL input, the "COALESCE" function is made for that purpose.
Cast to text: you expect "varcharn(X)" columns to be migrated to "text". That is a specific design. The expected design is to return "varchar(n)" columns.
If you want to change this behaviour, you need either to patch the sqlserver2pgsql.pl file or the create specific rules in the ETL.
-
Error1: I pushed a new version that removes the unicode 'N' before a SqlServer string in the columns of the view. Does it solve your case?
-
Error2: Can you give me the SqlServer dump to be sure about the part of the query you are talking about?
Thanks for your time.
Please find attached full SQLServer DB dump text file. Based on my understanding, I tired correcting errors in unsure.sql, but after correcting one getting another. So I am trying to correct error one by one, but not sure how many I will have to correct.
Hello,
Just wanted to check, if you get any generic solution for majority of issue unsure.sql for SQL dump attached.
A minor observation, in some of the view queries I see no space before FROM keyword is causing syntax error, I think this may be export SQL dump issue, but this does not look critical issue to me
I also get syntax error for some of the field in view (unsure.sql) like Limit, Offset. I think these are keyword in PostgreSQL. I have to check more on this
Hello madtibo
Did you get a change to look on the sql_server_fulldump_TestDB to convert to PostgreSQL. Thanks is advance. I am still unable to load the unsure.sql and I trying to correct queries one by one in unsure.sql but as of now no luck to correct all.
Hello
Thanks for all your help.
After doing around 9 corrections in unsure.sql, I could run the unsure.sql and completed migration of my current database. So this issue does not require more fix do be done
Just to let you know, details of some issues correct manually are given below
-
Space before FROM keyword was missing. From was in new line in generate SQL Dump text file and causing this issue.
-
Below issue in unsure.sql is caused by migration script
SQL Dump - (b1.C1 + N'|') + b2.C1
Migration script created this - (b1.C1||'|'
Expected is - (b1.C1||'|') || b2.C1
- There were some placeses in unsure.sql where + is not replaced by ||. Example string is - (b1.C3205 LIKE b2.C1803 + N' %')
Sorry, I did not get time to work on your issue!
Great to hear that you managed to migrate your database.
I will look into the issues you had to manually correct in order to solve them in the script.
Have fun using PostgreSQL :-)
Cordialement,