Jump to content

[TOPIC: topicViewTemplate]
[GLOBAL: userSmallPhoto]
Photo

Inserting JSON string into SQL
Started by yosu Aug 10 2013 08:22 AM

- - - - -
6 replies to this topic
json sql
[TOPIC CONTROLS]
This topic has been archived. This means that you cannot reply to this topic.
[/TOPIC CONTROLS]
[modOptionsDropdown]
[/modOptionsDropdown]
[reputationFilter]
[TOPIC: post.html]
#1

yosu

[GLOBAL: userInfoPane.html]
yosu
  • Contributor

  • 520 posts
  • Corona SDK

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



[TOPIC: post.html]
#2

RedBeach

[GLOBAL: userInfoPane.html]
RedBeach
  • Corona Geek

  • 1,132 posts
  • Corona SDK

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"]')


[TOPIC: post.html]
#3

Rob Miracle

[GLOBAL: userInfoPane.html]
Rob Miracle
  • Moderator

  • 25,791 posts
  • Enterprise

or use [[ and ]]



[TOPIC: post.html]
#4

yosu

[GLOBAL: userInfoPane.html]
yosu
  • Contributor

  • 520 posts
  • Corona SDK

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 .. [[' ) ; ]]
 

 



[TOPIC: post.html]
#5

Jam Paraiso

[GLOBAL: userInfoPane.html]
Jam Paraiso
  • Enthusiast

  • 98 posts
  • Corona SDK

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 :)



[TOPIC: post.html]
#6

Rob Miracle

[GLOBAL: userInfoPane.html]
Rob Miracle
  • Moderator

  • 25,791 posts
  • Enterprise

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. 



[TOPIC: post.html]
#7

sqldaddy121

[GLOBAL: userInfoPane.html]
sqldaddy121
  • Observer

  • 1 posts
  • Corona SDK

Hello,

I have seen this thread and I have proper solution to Load 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.

 

  1.  Download and Install SSIS JSON Source Connector.  
  2. From your tool box Drag SSIS JSON Source
  3. Specify JSON File or URL from where you want to consume JSON data
  4. Click on Columns tab and select columns you want to select
  5. Drag OLEDB Destination. Select SQL Server connection and Target Table where you want to load data
  6. Map Input columns to Target SQL Server Table
  7. Run the package.    
  8. To see sample work or  snapshot of work and other related task go through this link. 
  9. http://binaryworld.net/blogs/consuming-json-data-in-sql-server-and-ssis-convert-json-toxml/



[topic_controls]
[/topic_controls]