Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| Dev |
|
| QA |
|
| Priority | Minor |
| Complexity | Unknown |
| Component | Tools - Export Tool |
| Version | 16.0 |
Export tool for PostgreSQL is not escaping "\" in strings.
See ExportThread : Line 461. Last parameter in ETLUtil.generateINSERTStatement() is not being passed, which should probably be ConnectionProperties.requiresEscapedBackslash().
|
|
91 KB
|
|
47 KB
|
|
94 KB
|
|
89 KB
|
|
93 KB
|
|
55 KB
|
|
74 KB
|
12 KB
I have made the needed changes and create a patch for tom to review the code.
mySQL, nCluser, Netezza, Paraccel & Redshift should always escape the back slash on exporting data with slashes. Postgres versions 9 and below should also always escape the backslash. Postgres 9.1+ and Greenplum should escape the backslash when the database property standard_conforming_strings is set to OFF. You can discover the databases setting with the following query:
To test you may create a simple table with a text field: create table a (a int, b varchar(255))
mySQL, nCluser, Netezza, Paraccel & Redshift should always escape the back slash on exporting data with slashes. Postgres versions 9 and below should also always escape the backslash. Postgres 9.1+ and Greenplum should escape the backslash when the database property standard_conforming_strings is set to OFF. You can discover the databases setting with the following query:
To test you may create a simple table with a text field: create table a (a int, b varchar(255))
Verified in ADS v16.0.9-6.
Works correctly in customers PostgreSQL version for Tools -> Export with FORMAT = INSERT statements.
Try the below cases as well and make sure Export with FORMAT = INSERT displays the correct back slashes. You need to select Export Data and under Options tab, Save to Destination = Preview Export and Format = INSERT statements.
Verified in ADS v16.0.9-6.
Works correctly in customers PostgreSQL version for Tools -> Export with FORMAT = INSERT statements.
Try the below cases as well and make sure Export with FORMAT = INSERT displays the correct back slashes. You need to select Export Data and under Options tab, Save to Destination = Preview Export and Format = INSERT statements.
1 more test case:
1 more test case:
QA: 1 more place to test would be Schema Script Generator and then under options enable Include INSERT statements for Table data.
QA: 1 more place to test would be Schema Script Generator and then under options enable Include INSERT statements for Table data.
Verified in ADS 16.0.9-6, ADS 17.0.0-rc-30 and ADS 18.0.0-dev-13.
Steps to Verify the issue :
1. Connect to PostgreSQL Server 9.1 and above versions
(For PostgreSQL 9.0 and below versions we don't have to set below Property)
2. Right Click on Server and Select "Server Properties" option.
3. In Server Properties Window go to Scripts tab. Enter below Script here
SET standard_conforming_strings = OFF;
4.Click on Save Button.
5. Connect to Server and Create below table in any schema
CREATE TABLE "public"."a" ( "a" int4 NULL, "b" varchar(255) NULL )
GO
6. Insert Data in create Table "a"
INSERT INTO public.a(a, b)
VALUES('7', 'c:\\program\\files\\path\\file''s name.txt')
GO
7. Now Right click on this table and Select "Tools -> Export Data".
8. In Export data window, in General tab table a is selected. Click on Next Button.
9. In Options Tab, Select "Preview Export" from 'Destination' drop down and Select "INSERT Statements" from 'Format' drop down. Click on Next Button.
10. In 'Preview' tab of 'Export Data window' below script is generated :
INSERT INTO "public"."a"("a", "b")
VALUES(7, 'c:\\program\\files\\path\\file''s name.txt')
GO
Data displayed is what we have inserted actually.
Please refer attached screenshots "Scenrio1_ADS16.0.9-6.png", "Scenario1_ADS17-rc-30.png" and "Scenario1_ADS18.0.0-dev-13.png".
Also checked this issue in "Schema script generator" and "Table Data Editor", it working fine.
Please refer attached screenshot "Schema Script Generator.png" and "Table_Data_Editor,png"
Verified in ADS 16.0.9-6, ADS 17.0.0-rc-30 and ADS 18.0.0-dev-13.
Steps to Verify the issue :
1. Connect to PostgreSQL Server 9.1 and above versions
(For PostgreSQL 9.0 and below versions we don't have to set below Property)
2. Right Click on Server and Select "Server Properties" option.
3. In Server Properties Window go to Scripts tab. Enter below Script here
SET standard_conforming_strings = OFF;
4.Click on Save Button.
5. Connect to Server and Create below table in any schema
CREATE TABLE "public"."a" ( "a" int4 NULL, "b" varchar(255) NULL )
GO
6. Insert Data in create Table "a"
INSERT INTO public.a(a, b)
VALUES('7', 'c:\\program\\files\\path\\file''s name.txt')
GO
7. Now Right click on this table and Select "Tools -> Export Data".
8. In Export data window, in General tab table a is selected. Click on Next Button.
9. In Options Tab, Select "Preview Export" from 'Destination' drop down and Select "INSERT Statements" from 'Format' drop down. Click on Next Button.
10. In 'Preview' tab of 'Export Data window' below script is generated :
INSERT INTO "public"."a"("a", "b")
VALUES(7, 'c:\\program\\files\\path\\file''s name.txt')
GO
Data displayed is what we have inserted actually.
Please refer attached screenshots "Scenrio1_ADS16.0.9-6.png", "Scenario1_ADS17-rc-30.png" and "Scenario1_ADS18.0.0-dev-13.png".
Also checked this issue in "Schema script generator" and "Table Data Editor", it working fine.
Please refer attached screenshot "Schema Script Generator.png" and "Table_Data_Editor,png"
For some more test scenarios please refer attached "Test Scenarios.xlsx" sheet.
For some more test scenarios please refer attached "Test Scenarios.xlsx" sheet.
Issue #13810 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| Fixed Build v16.0.9-6, v17.0.0-rc-28, v18.0.0-dev-13 |
| No time estimate |
1 issue link |
relates to #13085
Issue #13085Table Data Editor on PostgreSQL should check "show standard_conforming_strings" in ConnectionProperties.requiresEscapeBackslash() |
I have made the needed changes and create a patch for tom to review the code.