Message Boards Message Boards

GROUPS:

How to Export List in SQL Format?

Posted 7 years ago
10139 Views
|
6 Replies
|
4 Total Likes
|

Hello Wolfram Forum,

I have data in list format (e.g. {{a},{b},{c}), with all entries being strings. How can I export this for SQL?

The output format looks like,

update <insert> set <insert>=<insert> where <insert>=<insert> and <insert>=<insert>;

The above {"a","b","c"} correspond to each row in the text file to be used for MySQL. "<insert>" is a variable/value, removed here to make this generic. I have the code that concatenates the variables into rows and makes it a string,

?ToString[
 ? "update tableName set fieldName= " <> ? ? ToString[variable] <> 
   ? ? " where fieldName='" <> ToString[variable] <> "' and field='" <>
    ? ? ToString[variable <> "';" ? ?]

One problem is that the list format ends the line with a comma (i.e. ,). Also, it begins the list with {{ and end encapsulates each row with { }. I haven't been able to get the "SQL" option for Export to work in Mathematica version 10 and haven't come across an example online. Maybe I should export it in some other text format?

Thanks,

Greg

POSTED BY: Greg
Answer
6 Replies

Greg, MySQL is very efficient importing delimited text. Why do you want to create a SQL file?

Posted 7 years ago

Idea is to avoid another step. I am intending to go through this update and importing process a few hundred separate times over the next few weeks.

POSTED BY: Greg
Answer
Posted 7 years ago

I'll look into it- thanks.

POSTED BY: Greg
Answer

This guide is very complete: DatabaseLinkUserGuide

Here is how I would do for your case.

Needs["DatabaseLink`"]
conn = 
  OpenSQLConnection[
    JDBC["Microsoft SQL Server(jTDS)", "myIP"]
   ,"Username" -> "myuser"
   ,"Password" -> "mypass"
  ]

list={{"a1","b1","c1"},{"a2","b2","c2"}}
sql="update tableName set fieldName=`1` where fiedName = `2` and field = `3`"
SQLExecute[conn,sql,list]
CloseSQLConnection[conn]
Posted 7 years ago

That code worked and the guide does have a lot of good info, thanks for pointing it out. Appears this is the better approach.

POSTED BY: Greg
Answer
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract