How to convert sql return data to json

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

How to convert sql return data to json

Palanikumar Gopalakrishnan
I want to get data from mysql and convert them into json format. I tried with this

module https://sourceforge.net/projects/erlmysql/files/latest/download?source=directory


Previously i tried this modules,
https://github.com/Eonblast/Emysql

This module give support for json conversion. But It was not support for Transaction.

So I need to convert Sql return data to Json format. Please guide me to solve this challenge






--

Warm Regards,

Palanikumar Gopalakrishnan ✌
Developer



_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions
Reply | Threaded
Open this post in threaded view
|

Re: How to convert sql return data to json

Nuku Ameyibor
You can use https://github.com/talentdeficit/jsx,
https://github.com/davisp/jiffy
to convert  erlang terms to and from json .

From: [hidden email]
Sent: ‎8/‎31/‎2017 12:32 PM
To: [hidden email]
Subject: [erlang-questions] How to convert sql return data to json

I want to get data from mysql and convert them into json format. I tried with this

module https://sourceforge.net/projects/erlmysql/files/latest/download?source=directory


Previously i tried this modules,
https://github.com/Eonblast/Emysql

This module give support for json conversion. But It was not support for Transaction.

So I need to convert Sql return data to Json format. Please guide me to solve this challenge






--

Warm Regards,

Palanikumar Gopalakrishnan ✌
Developer



_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions
Reply | Threaded
Open this post in threaded view
|

Re: How to convert sql return data to json

Jesper Louis Andersen-2
In reply to this post by Palanikumar Gopalakrishnan
The way Erlang systems handle JSON data is the following:

1. In Erlang, every value passed around is an "Erlang Term".
2. A subset of these terms are representable in JSON.
3. JSON encoders such as 'jiffy' or 'jsx' are able to take the valid subset from 2 and convert it into a binary containing the equivalent JSON representation.

Your problem can be solved by taking the data you obtain from MySQL and then convert the data into a form which follows the valid JSON subset and has the structure you want. Then call something like `jsx:encode(Data)` on your `Data`.

Some background on 2 above: Erlang terms can contain tuples such as {ok, 37} or {error, not_connected}. These do not have a representation in JSON (since it lacks the equivalent of a symbol/atom and tuples). So most JSON encoders reject Erlang terms containing tuples as subterms. The details depend on the encoder though.

I can't remember if MySQL is able to return a result set directly as JSON, and if it were, you have to check that the driver you use can handle such a result as well--I can't help you much in this case, unfortunately.

Hopefully this will get you started on a solution!

On Thu, Aug 31, 2017 at 2:32 PM Palanikumar Gopalakrishnan <[hidden email]> wrote:
I want to get data from mysql and convert them into json format. I tried with this

module https://sourceforge.net/projects/erlmysql/files/latest/download?source=directory


Previously i tried this modules,
https://github.com/Eonblast/Emysql

This module give support for json conversion. But It was not support for Transaction.

So I need to convert Sql return data to Json format. Please guide me to solve this challenge






--

Warm Regards,

Palanikumar Gopalakrishnan ✌
Developer


_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions

_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions
Reply | Threaded
Open this post in threaded view
|

Re: How to convert sql return data to json

Palanikumar Gopalakrishnan
Hi guys,

               I tried with this modules
   https://github.com/talentdeficit/jsx,
   https://github.com/davisp/jiffy


But its need fieldname. My Sql driver returns data like below


for checking Sql result {{metadata,2,0,
                             {server_status,false,true,false,false,true,
                                 false,false,false,false,false,false,false},
                             [{field_metadata,"def","zurik","erlang",
                                  "erlang","name","name",33,135,253,
                                  <<0,0>>,
                                  0,[]},
                              {field_metadata,"def","zurik","erlang",
                                  "erlang","id","id",63,11,3,
                                  <<0,0>>,
                                  0,[]}],
                             []},
                         [["some",457],["some",457],["data2sosfasfme",42]]}


Please find my table structure below
+----------------+------+
| name           | id   |
+----------------+------+
| some           |  457 |
| some           |  457 |
| data2sosfasfme |   42 |
+----------------+------+



Its that idea to get field name

On 31 August 2017 at 18:43, Jesper Louis Andersen <[hidden email]> wrote:
The way Erlang systems handle JSON data is the following:

1. In Erlang, every value passed around is an "Erlang Term".
2. A subset of these terms are representable in JSON.
3. JSON encoders such as 'jiffy' or 'jsx' are able to take the valid subset from 2 and convert it into a binary containing the equivalent JSON representation.

Your problem can be solved by taking the data you obtain from MySQL and then convert the data into a form which follows the valid JSON subset and has the structure you want. Then call something like `jsx:encode(Data)` on your `Data`.

Some background on 2 above: Erlang terms can contain tuples such as {ok, 37} or {error, not_connected}. These do not have a representation in JSON (since it lacks the equivalent of a symbol/atom and tuples). So most JSON encoders reject Erlang terms containing tuples as subterms. The details depend on the encoder though.

I can't remember if MySQL is able to return a result set directly as JSON, and if it were, you have to check that the driver you use can handle such a result as well--I can't help you much in this case, unfortunately.

Hopefully this will get you started on a solution!

On Thu, Aug 31, 2017 at 2:32 PM Palanikumar Gopalakrishnan <[hidden email]> wrote:
I want to get data from mysql and convert them into json format. I tried with this

module https://sourceforge.net/projects/erlmysql/files/latest/download?source=directory


Previously i tried this modules,
https://github.com/Eonblast/Emysql

This module give support for json conversion. But It was not support for Transaction.

So I need to convert Sql return data to Json format. Please guide me to solve this challenge






--

Warm Regards,

Palanikumar Gopalakrishnan ✌
Developer


_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions



--

Warm Regards,

Palanikumar Gopalakrishnan ✌
Developer



_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions
Reply | Threaded
Open this post in threaded view
|

Re: How to convert sql return data to json

Kenneth Lakin
On 09/01/2017 05:36 AM, Palanikumar Gopalakrishnan wrote:

> *for checking Sql result {{metadata,2,0,
> {server_status,false,true,false,false,true,
> false,false,false,false,false,false,false},
> [{field_metadata,"def","zurik","erlang",
> "erlang","name","name",33,135,253,
> <<0,0>>,0,[]},
> {field_metadata,"def","zurik","erlang",
> "erlang","id","id",63,11,3,
> <<0,0>>,0,[]}],[]},
> [["some",457],["some",457],["data2sosfasfme",42]]}*
So, it *looks* like your driver is returning a tuple containing:
* A 'metadata' record which contains (among other things) a
server_status record and a list of field_metadata records.
* A list containing the data that is the response to your query.

The "Getting Started" section of the erlmysql documentation [0] seems to
say that the record definitions that you need are contained in
"client_records.hrl". Loading that file into your Erlang shell with the
rr/1 shell command and re-running the query should make the SQL driver's
return value make more sense to you.

In order to load those record definitions into your client code, you'll
probably need to do something like

-include_lib("erlmysql/include/client_records.hrl").

near the top of the file that needs to interact with the SQL driver.

[0] http://erlmysql.sourceforge.net/


_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: How to convert sql return data to json

Palanikumar Gopalakrishnan
Hi Kenneth,

                    Thanks for your reply, I already include the -include_lib("erlmysql/include/client_records.hrl").  in my source

On 5 September 2017 at 02:29, Kenneth Lakin <[hidden email]> wrote:
On 09/01/2017 05:36 AM, Palanikumar Gopalakrishnan wrote:
> *for checking Sql result {{metadata,2,0,
> {server_status,false,true,false,false,true,
> false,false,false,false,false,false,false},
> [{field_metadata,"def","zurik","erlang",
> "erlang","name","name",33,135,253,
> <<0,0>>,0,[]},
> {field_metadata,"def","zurik","erlang",
> "erlang","id","id",63,11,3,
> <<0,0>>,0,[]}],[]},
> [["some",457],["some",457],["data2sosfasfme",42]]}*

So, it *looks* like your driver is returning a tuple containing:
* A 'metadata' record which contains (among other things) a
server_status record and a list of field_metadata records.
* A list containing the data that is the response to your query.

The "Getting Started" section of the erlmysql documentation [0] seems to
say that the record definitions that you need are contained in
"client_records.hrl". Loading that file into your Erlang shell with the
rr/1 shell command and re-running the query should make the SQL driver's
return value make more sense to you.

In order to load those record definitions into your client code, you'll
probably need to do something like

-include_lib("erlmysql/include/client_records.hrl").

near the top of the file that needs to interact with the SQL driver.

[0] http://erlmysql.sourceforge.net/




--

Warm Regards,

Palanikumar Gopalakrishnan ✌
Developer



_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions