[erlang-questions] Re: Storing erlang terms in RDMBS

[Robert Raschke]

On 5/30/09, Koushik Narayanan <koushik.list@REDACTED> wrote:
> Hi,
>
> On Sat, May 30, 2009 at 10:36:18AM +1000, Matthew Palmer wrote:
>> On Fri, May 29, 2009 at 11:53:39AM +0530, Koushik Narayanan wrote:
>> > What is the right way to store erlang terms in a RDBMS accessed using
>> > the odbc application.
>>
>> binary_to_list(term_to_binary(Term)) would be my first stab at it.
>>
>
> I tried that. And this is what happens:
>
> 4> D = {a,b,1}.
>
> 5> Q = io_lib:format("INSERT INTO data(username,dbname,guid,data)
> VALUES('user1','db1',1,'~s')",[binary_to_list(term_to_binary(D))]).
> [73,78,83,69,82,84,32,73,78,84,79,32,100,97,116,97,40,117,
>  115,101,114,110,97,109,101,44,100,98,110|...]
>
> 6> odbc:sql_query(Ref,Q).
> {error,connection_closed}
>
> The way it works is this:
>
> 11> Q = io_lib:format("INSERT INTO data(username,dbname,guid,data)
> VALUES('user1','db1',1,'~p')",[term_to_binary(D)]).
>
> 12> odbc:sql_query(Ref,Q).
> {updated,1}
>
> But while fetching back,
>
> 19> {_,_,[{BinD}]} = odbc:sql_query(Ref,"SELECT data from data").
> {selected,["data"],
>           [{"<<131,104,\n
>                   3,100,0,\n
>                      1,97,100,\n
>                          0,1,98,\n
>                            97,1>>"}]}
> 20> lists:append(string:tokens(BinD,"<>\n ")).
> "131,104,3,100,0,1,97,100,0,1,98,97,1"
> 21> string:tokens(lists:append(string:tokens(BinD,"<>\n ")),",").
> ["131","104","3","100","0","1","97","100","0","1","98","97",
>  "1"]
> 22> L1 = string:tokens(lists:append(string:tokens(BinD,"<>\n ")),",").
> ["131","104","3","100","0","1","97","100","0","1","98","97",
>  "1"]
> 23> [list_to_integer(X)||X<-L1].
> [131,104,3,100,0,1,97,100,0,1,98,97,1]
> 24> binary_to_term(list_to_binary([list_to_integer(X)||X<-L1])).
> {a,b,1}
>
> There should be a better way than this right ?
>
>
> Koushik Narayanan
>

Since you don't say what your DB column types are and you are using
the odbc module which doesn't support BLOBs, I assume you are storing
the 'term' in a suitably large varchar column. In that case, you'll
always need to parse whatever you get back from the DB. It's just a
string, after all. You may want to investigate the erl_parse module
for more flexibility.

Potentially, using some DB binding lib that can deal with binary
objects, you may be able to get shorter code.

I take it, you don't want to 'just' use mnesia instead of a RDBMS.

Robby