Dan;
You are absolutely right, this works as you explained it.
$hash->{'items'} = from_json $hash->{'items'};
Much appreciated.
Thanks;
George
On Sunday, January 14, 2018 at 2:09:19 PM UTC-5, Dan Book wrote:
>
> Hello,
> Since Mojo::mysql sets mysql_enable_utf8, you should not use encode_json
> and decode_json since the transport will already be handling UTF-8
> encoding. You should instead use to_json and from_json (as Mojo::Pg and
> Mojo::SQLite do, since their transports also handle UTF-8 encoding). If you
> use encode_json then the result is bytes, and you would need to bind it as
> a BLOB when passing the data to mysql so it does not get double encoded.
> This is further complicated that DBD::mysql 4.042 and the latest version
> have differing behavior in this regard (4.042 is the only version with
> correct behavior in all cases).
>
> -Dan
>
> On Sun, Jan 14, 2018 at 11:48 AM, George <[email protected] <javascript:>>
> wrote:
>
>> Hi;
>>
>> I'm following Advent Calendar project from Joel (Thank You Joel !) and as
>> an exercise adapted it from Sqlite to Mysql.
>> It all went rather smoothly until I started to insert data into Mysq that
>> contains utf8 chars.
>>
>> Mysql default char sert is utf8.
>>
>> Mojo:;mysql does not have an expand method as Sqlite has, so I used
>> decode_json for the json part of the query result.
>>
>> So this works perfectly well using json_decode:
>>
>> my $bytes = encode_json {title => ['Ralph Towner / John Abercrombie -
>> Five Years Later', 'Grażyna Bacewicz, Diana Ambache - Chamber Music']};
>> my $hash = decode_json($bytes);
>>
>> Once I Insert into mysql:
>>
>> insert into items (title,url,purchased, user_id) values('Ralph Towner /
>> John Abercrombie - Five Years Later','some url',0,1);
>> insert into items (title,url,purchased, user_id) values('Grażyna
>> Bacewicz, Diana Ambache - Chamber Music','some url',0,1);
>>
>> And run the following code:
>>
>> my $sql = <<' SQL';
>> SELECT
>> user_id,
>> CONCAT('[',
>> GROUP_CONCAT(JSON_OBJECT('id',
>> items.id,
>> 'title',
>> items.title,
>> 'url',
>> items.url,
>> 'purchased',
>> items.purchased)),
>> ']') AS items
>> FROM
>> items
>> WHERE
>> items.user_id = '1'
>> SQL
>>
>> $hash = $mysql->db->query($sql)->hash;
>> $hash->{'items'} = decode_json($hash->{'items'} );
>>
>> I get the following error:
>>
>> Input is not UTF-8 encoded at ./1.pl line 42.
>>
>> Same results for Mysql 5.7 and 8.
>>
>> I'm not sure if this a Mojo:JSON or Mojo:mysql bug, I'm also curious why
>> expand method is missing from Mojo::sql as it is present in Mojo:pg and
>> Mojo::sqlite.
>>
>> Thanks;
>>
>> George
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Mojolicious" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> To post to this group, send email to [email protected]
>> <javascript:>.
>> Visit this group at https://groups.google.com/group/mojolicious.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
You received this message because you are subscribed to the Google Groups
"Mojolicious" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
