Insert a full JSON response into MySql 8 db field - tried json, text

Wappler General Need Help
1

Hi all,

Getting an error when trying to output JSON from an API directly into a MySql 8 field.

I’ve tried the field as ‘text’ and ‘JSON’ type, and also outputting the API response directly, or using a Set Value Array and outputting that, all throw out the following error:

stack: "Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', experience = ‘[object Object]’, ‘[object Object]’, education = ‘[object Ob’ at line 1↵

2

Hi @mgaussie,
Have you tried using a formatter to convert to a string before inserting?

3

I just set up a test and it clearly doesn’t cope with an array or object as a parameter. There doesn’t appear to be a json encode/decode/stringify option anywhere I can see. Sorry I can’t help.

4

Hey @mgaussie,
here you go:

You can achieve this with a simple custom formatter.

  1. In dmxConnectLib/lib/formatters create a file, call it something like customformatters.php

  2. Add the following code:
    <?php namespace lib\core; function formatter_stringify($val){ $retval = json_encode($val); return $retval; } ?>

  3. Save and upload the custom formatter to the server

  4. Use the formatter in your insert statement:

    image
    image1588×482 21.4 KB
    (where q_json represents the API/DB query response)

Simples… :grin:

1 Like
5

Thank you so much for this - unfortunately (my bad I should have said) I have a node project, so can’t use php :frowning:

I see so often on the forum people recommending storing the entire JSON response in the DB, so just assumed it was possible! I’ll try to find another work around - but again, thank you so much for going to all that trouble!!

1 Like
6

It was no problem; I have learnt some really useful stuff looking at custom formatters. I would imagine that for Node there will be a way of making custom formatters - you could create something using JSON.strigify(). Being JS, it will probably be more like the client-side ones (instructions here Creating Custom Formatters (PHP and client-side) ). Maybe @patrick or @Teodor could help detail the difference for Node?

7

Thanks Ben.

I took some of your guidance however and tried to use a formatter on a Set Value to convert to the JSON to String and input that into a newly created ‘text’ field. It ‘worked’ as in it didn’t throw any errors, but the output in the field in the DB is just [object] [Object]. Plus, I’m assuming if converted to a string, it won’t be as useable as a normalized JSON output? @Teodor any guidance here on if this is possible?

8

Converting the object to a string will not work, you’ll need to use the stringify function. If you can get stringify working as a custom formatter, you can use JSON.parse to go the other way and convert a string to a JSON when retrieving from the DB.

1 Like
9

hi @bpj!! how you sugest to do this on node.js?

10

Depending on your database setup, Wappler often picks the field up automatically as a JSON type. If the value returned from an SC API call is a string it is usually because the DB field might have changed from MEDIUMTEXT or similar to JSON type since Wappler got the schema. It can be a bit fiddly to get this recognised in Wappler so that the data is converted correctly, in which case you can convert it client-side by adding these to your formatters:

dmx.Formatter('object', 'stringify', function stingfy(val) {
    return JSON.stringify(val);
})
dmx.Formatter('array', 'stringify', function stingfy(val) {
    return JSON.stringify(val);
})
dmx.Formatter('string', 'unstringify', function unstingfy(val) {
    return JSON.parse(val);
})