How to insert a very large string into a table in SQL

Help Studio
, ,
1

Hello everyone

I have a very long string (json string) which need to be inserted into a table. The json string contains letters, numbers, special signs and unicode. How do I insert this into a table?

I am using a “Run query” which has established a connection to my database. This is the query I am executing…
“INSERT INTO my_table” + " VALUES (json_document) (‘" + response1.ToString + "’);"
This, however, gives me the error

“Run query: Incorrect syntax near ‘{“transactionDetail”:{“transactionID”:“rrt-xxx-a-eu-x-xxxx”,“transactionTimestamp”:"2023-07-14.’.”

It seems like UiPath thinks my json string is a part of the SQL INSERT syntax. Any idea how to fix this?

2

@nmjvk

All the special characters needs to be escaped in the sql statement

cheers

3

Dear Anil

How do I do that? Any suggestions?

4

Create a datatable with a column json_document and use response1.ToString in an Add Data Row activity. Then use the Insert activity and pass it that datatable.

5

@nmjvk

Can you please print your sql statement as is using log message and show the full statement please

That way it would be easy to understand

Mostly I believe your double quote is creating the problem

so use .Replace("""","\""")

cheers

6

hi @nmjvk

YOu would have to remove the special characters, use the below expression for it:
System.Text.RegularExpressions.Regex.Replace(yourString,“[a-z A-Z 0-9./:-]”,“”)

These are [a-z A-Z 0-9./:-+] permissible/allowed characters.

Thanks,
Happy Automation! :smiley:

7

If they remove special characters then it won’t be valid JSON any more

1 Like
8

Hello. Well, I am not allowed to remove the special characters, they need to be in the string and inserted into the table in the database :slight_smile:

9

I am not sure what you mean with “show the full statement”? This is my full INSERT SQL statement
“INSERT INTO my_table” + " VALUES (json_document) (‘ " + response1.ToString + " ’);"
I have purposefully made a space between the ’ and the ", hope it helps :slight_smile:

10

I got the process to run correctly for the first number og insert statements, afterwards, it halted and failed. It gave me this error

image

11

@nmjvk

What I meant was show the statement along with the value of response1.ToString :slight_smile:

and did you try with escaping double quotes

cheers

12

Have you tried this?

13

Due to work policy, I am not allowed to show you the full response.ToString, since it is a json file with confidential information. However, think of response1 as a json string, a very, very large json string :slight_smile:

This is my current statement which works on the first 10 INSERTS before i fails

“INSERT INTO my_table VALUES ('” + response1 + “')”

14

@nmjvk

Just to check try getting a small json string and escape the characters with \ and use it

like below and see (try static query without variables)
"INSERT INTO dnb_api_rawdata VALUES ('{\""transactionDetail\"":{\""transactionID\"":\""rrt-xxx-a-eu-x-xxxx\""}}')"

and as per statement I can see you are inserting only one value so only one column is present?

cheers

15

Hello dear Wick

I have not tried your solution. Due to the design of the project I am working on I “have” to use the “Run query” :slight_smile:

16

There are two columns. The first columt is automatic and is inserted every time, the other is the json_document.

17

That makes no sense. You should use what works best and is appropriate for the desired outcome.

18

Yes, but the people who bought the project are paying us to deliver what they specifically ask for, it’s not my job to decide the design of the project…

19

@nmjvk

Can you please try as specified above if it works then you can use the replace provided above to escape all

cheers

20

I can not do that. My json string is around 18.000 characters long, I can not manually input my json string in my INSERT statement, or are you perhaps refering to something else?

cheers