Toad World® Forums

Apostrophes in data derails inserting of results as snapshot or table in local storage connection


#1

Hi I have a query which result I want to store as a snapshot or table in the local storage connection in TDP, however some of the data contains apostrophes and these cause errors. I guess this is because the “send-to” local connection action is done through a “create table/insert into” command in the background.

Now I know how I can escape this single ’ with a Replace() function replacing it with a double ‘’ but I don’t want to this as: a) I want to also use the generated SQL in other environments where this problem did not come up and b) there are a lot of column with text field that might trigger this problem and c) I loath to do this kind of data modifications for transactional reasons.

Is there a way around this without needing to modify the core data ?


#2

Could you save the output as a csv or spreadsheet?

What are you doing with the output once you have created it?


#3

The output is going to be used in a next query. The complete query takes hours to complete while separate components take only minutes each. We want to run the components, export them as tables or (preferably) snapshot and use those tables/snapshot in the final query and the final output.

Apart from right clicking on the the results and choosing “send-to” local storage I have also tried the same as part of an automation.


#4

Hi Arjen,

could you share with us sample of data and what provider are you using?

Thank you.

Jakub.


#5

A sample would be (in comma separated format):

55556789,null,null,AG,Stephen,O’Brien,2018,The History of America,MA

55557654,null,CT,LW,Marian,Smith,2018,Replication at 5’ ends,MS

the first row would encounter problem at the “O’Brien” part the second would hick-up on “Replication at 5’ ends”.


#6

I import your sample data into Oracle and SQL Server connection, do the query and send the results to local storage, it works fine for me with TDP 4.3. What is your TDP version and what connection are you using?


#7

I am using Toad Data Point 4.3.0.718 (64 bit). To test I created an automation that queries an Oracle database (Execute Script) and Exports to Local Storage.

8535.Capture2.png

5074.Capture3.png

Where you see “Right syntax to use near ‘UTR’, 1596…etc” The ‘UTR’ part is the last word of a dissertation title that end in the phrase " 5’ UTR".


#8

I’ve now tried it with the import/export element too and it generates the same error.


#9

Can you open a support ticket? We can help you there.


#10

From the support case we found that the issue is not supported the Prime symbol (https://en.wikipedia.org/wiki/Prime_(symbol)). This has been entered as QAT-13229 to fix.