D. Elp. D. Elp. - 6 months ago 29
SQL Question

Proper way to (get from /insert into) table using Erlang Mysql Driver

I am trying to get

erlang-mysql-driver
working, I managed to set it up and make queries but there are two things I cannot do.(https://code.google.com/archive/p/erlang-mysql-driver/issues)

(BTW, I am new to Erlang)

So Here is my code to connect
MySQL
.

<erl>
out(Arg) ->
mysql:start_link(p1, "127.0.0.1", "root", "azzkikr", "MyDB"),
{data, Result} = mysql:fetch(p1, "SELECT * FROM messages").
</erl>


1. I cannot get data from table.



mysql.erl
doesn't contain any specific information on how to get table datas but this is the farthest I could go.

{A,B} = mysql:get_result_rows(Result),
B.


And the result was this:

ERROR erlang code threw an uncaught exception:
File: /Users/{username}/Sites/Yaws/index.yaws:1
Class: error
Exception: {badmatch,[[4,0,<<"This is done baby!">>,19238],
[5,0,<<"Success">>,19238],
[6,0,<<"Hello">>,19238]]}
Req: {http_request,'GET',{abs_path,"/"},{1,1}}
Stack: [{m181,out,1,
[{file,"/Users/{username}/.yaws/yaws/default/m181.erl"},
{line,18}]},
{yaws_server,deliver_dyn_part,8,
[{file,"yaws_server.erl"},{line,2818}]},
{yaws_server,aloop,4,[{file,"yaws_server.erl"},{line,1232}]},
{yaws_server,acceptor0,2,[{file,"yaws_server.erl"},{line,1068}]},
{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]


I understand that somehow I need to get second element and use foreach to get each data but strings are returned in different format like queried string is
Success
but returned string is
<<"Success">>
.

{badmatch,[[4,0,<<"This is done baby!">>,19238],
[5,0,<<"Success">>,19238],
[6,0,<<"Hello">>,19238]]}


First Question is: How do I get datas from table?

2. How to insert values into table using variables?



I can insert data into table using this method:

Msg = "Hello World",
mysql:prepare(add_message,<<"INSERT INTO messages (`message`) VALUES (?)">>),
mysql:execute(p1, add_message, [Msg]).


But there are two things I am having trouble,
1. I am inserting data without
<<
and
>>
operators, because When I do
Msg = << ++ "Hello World" >>,
erlang throws out an exception (I think I am doing something wrong), i don't know wether they are required but without them I am able to insert data into table except this error bothers me after execution:

yaws code at /Users/{username}/Yaws/index.yaws:1 crashed or ret bad val:{updated,
{mysql_result,
[],
[],
1,
[]}}
Req: {http_request,'GET',{abs_path,"/"},{1,1}}


returned atom is
updated
while I commanded to insert data.

Question 2 is: How do I insert data into table in a proper way?

Answer

Error:

{badmatch,[[4,0,<<"This is done baby!">>,19238],
                      [5,0,<<"Success">>,19238],
                      [6,0,<<"Hello">>,19238]]}

Tells you that returned values is:

[[4,0,<<"This is done baby!">>,19238],
 [5,0,<<"Success">>,19238],
 [6,0,<<"Hello">>,19238]]

Which obviously can't match with either {data, Data} nor {A, B}. You can obtain your data as:

<erl>
out(Arg) -> 
            mysql:start_link(p1, "127.0.0.1", "root", "azzkikr", "MyDB"),
            {ehtml, [{table, [{border, "1"}],
               [ {tr, [], [ {td, [], case Val of
                           _ when is_binary(Val) -> yaws_api:htmlize(Val);
                           _ when is_integer(val) -> integer_to_binary(Val)
                          end}
                   || Val <- Row]
                 }
               || Row <- mysql:fetch(p1, "SELECT * FROM messages")]
            }]}.
</erl>