Hi guys,
I am trying to put in a lot of data into the SQL database, and some of them are in a table form. So, I json-encoded the table so it can be inserted into the SQL database (as a string).
Example : [“abc”, “123”, “hello”]
But my problem is that, each JSON encoded string has double quotes (") all over. I thought of using ‘string.gsub’ to replace the double quotes, but I believe there some be some better method than this.
Please advise.
Thanks
You can insert the data with quotes without problem, just use the ’ instead of " as the opening/closing marks.
example:
INSERT INTO tableName (column1) VALUES ('["abc", "123", "hello"]')or use [[and]]
Thanks guys. Using ’ (single quotes) instead of " (double quotes) worked, as the variable was already in json format.
I don’t really understand how to use the [[and]] in this case, as it is encoded in json format. I hope my coding is right.
tmp = { "abc", "123", "hello" } encodedJson = json.encode(tmp) -- This will print : ["abc","123","hello"] local query = [[REPLACE INTO]] .. tableName .. [[VALUES ( ']] .. encodedJson .. [[' ) ;]] I do it this way
local sql = [[INSERT INTO astronomers ("field1","field2","field3","field4") VALUES ("]] .. variable\_here1 .. [[","]] .. variable\_here2 .. [[","]] .. variable\_here3 .. [[","]] .. variable\_here4 .. [[");]]
hope it helps 
You use [[and]] just like you would use " and " or ’ and '. One starts a string, the other ends it. But the benefit of using [[and]] is that it can span multiple lines. It’s benefit for this is that if your strings you’re inserting into your database have apostrophes, then your current method of using single quotes won’t break:
“April O’Neil”
would break if you use single quotes.
You can insert the data with quotes without problem, just use the ’ instead of " as the opening/closing marks.
example:
INSERT INTO tableName (column1) VALUES ('["abc", "123", "hello"]')or use [[and]]
Thanks guys. Using ’ (single quotes) instead of " (double quotes) worked, as the variable was already in json format.
I don’t really understand how to use the [[and]] in this case, as it is encoded in json format. I hope my coding is right.
tmp = { "abc", "123", "hello" } encodedJson = json.encode(tmp) -- This will print : ["abc","123","hello"] local query = [[REPLACE INTO]] .. tableName .. [[VALUES ( ']] .. encodedJson .. [[' ) ;]] I do it this way
local sql = [[INSERT INTO astronomers ("field1","field2","field3","field4") VALUES ("]] .. variable\_here1 .. [[","]] .. variable\_here2 .. [[","]] .. variable\_here3 .. [[","]] .. variable\_here4 .. [[");]]
hope it helps
You use [[and]] just like you would use " and " or ’ and '. One starts a string, the other ends it. But the benefit of using [[and]] is that it can span multiple lines. It’s benefit for this is that if your strings you’re inserting into your database have apostrophes, then your current method of using single quotes won’t break:
“April O’Neil”
would break if you use single quotes.
Hello,
I have seen this thread and I have proper solution toLoad JSON String into SQL Server. To do so You have to follow some steps. Here I will mention step by step process to do and follow this step in same way.
Hello,
I have seen this thread and I have proper solution toLoad JSON String into SQL Server. To do so You have to follow some steps. Here I will mention step by step process to do and follow this step in same way.