Storing lists on SQL

[Chris Degnen]

Hi. I'm trying to store lists on an SQL database. The lists can
contain strings, rules, lists and integers etc. I plan to store them
in a variable character field as a single string, but the first
problem is the quotes on the inner strings have to be elided, e.g./"

At the moment I'm trying a recursive routine with ToCharacterCode[] to
encode the inner strings first. I'll have to do something else for
the rules.

I'm also going to try serializing the lists with Export[] and then
storing them encoded. (That might be easiest.)

If anyone has suggestions on how I might store these lists please let
me know.

[Hans Michel]

Try

In[1]:= ExportString["Hello World", "Base64"]
Out[1]= SGVsbG8gV29ybGQ=

You can also do expressions
ExportString[{1,"2",3}, "Base64"]
MQ0KMg0KMw==

ImportString[%,"Base64"]

{{1},{2},{3}}

Try

In[4]:= Compress[{1,"2",3}]
Out[4]= 1:eJxTTMoPSmNmYGAoZgESPpnFJZmMQEYwiDDKBEkAAGveBR0=
In[5]:= Uncompress[%]
Out[5]= {1,2,3}

Not certain if it may need further escaping when inserting into SQL database
as a varchar.

Please not if you parametize your input variables before inserting, so you
are passing parameters instead of raw data depending on the Database driver,
you may not need to escape any characters as the parametization process may
take care of those issue.

Hans

"Chris Degnen" <deg...@cwgsy.net> wrote in message
news:hi1qbt$eka$1...@smc.vnet.net...

[Albert Retey]

Hi,

If you use a recent version (6 or 7) I think the simplest approach to
achieve exactly what you want is to use Compress to create a string that
should be o.k. as a variable character field with most SQL databases
(from the docs: "The string generated by Compress contains only
printable ASCII characters."). Of course you will need to Uncompress
after reading from the database. Struggling with character codes is
probably only necessary if you want to store the data as binary (e.g.
BLOB) and/or if you need to read/write with another system/language.

On the other hand, if your lists basically contain strings and integers
only, why not store them as such in the database? Rules and sublists can
probably be resolved by introducing extra tables in the database
(learning the details about normalizing relational databases might be
overkill, but the basics are not so complicated).

If that is not an option, probably because the data is too irregular,
you could also use SQLExpr (assuming you use DatabaseLink`), which I
think is exactly for the purpose you are after and basically does the
same thing as Compress/Uncompress, I believe...

hth,

albert

[David Reiss]

Are you using DatabaseLink for this? If so, perhaps simply wrap your
Mathematica expression in DatabaseLink's SQLExp function. If there
are evaluable expressions in your list then you may want to wrap it in
Hold as well in the form SQLExp[Hold[your stuff....]]

?? SQLExpr

SQLExpr[ expr] allows a Mathematica expression to be stored in a
database. >>

--David
http://scientificarts.com/worklife

[Chris Degnen]

Thanks to all who replied. SQLExpr was exactly what I was looking
for. I found if I define a text field with data length greater than
65535 a 16 million character-length field is created, which should
certainly suffice if 65535 isn't enough.

E.g. with DatabaseLink connection established:

SQLCreateTable[conn, SQLTable["Testing"],
{SQLColumn["Record", "DataTypeName" -> "INTEGER"],
SQLColumn["Data", "DataTypeName" -> "TEXT",
"DataLength" -> 65536]}];
(* check table definition *)
Print[SQLColumns[conn, "Testing"]];
data1 = {"Lots of data",
aRule -> {91, 90, 93, 92}, {"A list", "Etc."}};
SQLInsert[conn, "Testing", {"Record", "Data"}, {1, SQLExpr[data1]}];
retreivedData = SQLSelect[conn, "Testing",
{"Data"}, SQLColumn["Record"] == 1][[1, 1, 1]];
SQLDropTable[conn, "Testing"];
Print[retreivedData];

output:

{SQLColumn[{testing,Record},
DataTypeName->int,Nullable->1,DataLength->11],
SQLColumn[{testing,Data},
DataTypeName->mediumtext,Nullable->1,DataLength->16277215]}

{Lots of data,aRule->{91,90,93,92},{A list,Etc.}}