Booleans in MySQL, Perl DBI, and JSON

Question

Consider a simple mysql table with id (integer) and flag (boolean) columns. The user interface, written in JavaScript, communicates with the CGI server via JSON, both for receiving data from the table and for updating it.

Now, sending the data {id: 5, flag: true} to the server, of course I use from_json to get the perl (ref) hash. Then

 my $sth = $dbh->prepare('UPDATE my_table SET flag = ? WHERE id = ?); $sth->execute($data->{flag}, $data->{id}); 

always leads to the introduction of a false value. I believe this is due to the magical behavior of JSON :: true and JSON :: false, which build true and false, respectively (but will be numbered 1 and 0). Apparently, the execute statement provides a string context, not a numeric context, so the mysql server gets a string containing no digits, which then calls the value 0. At the moment, I fix this by putting ? 1 : 0 ? 1 : 0 after $data->{flag} .

The opposite happens in the other direction: according to the DBI document, “Most of the data is returned to the Perl script as strings.”, So when I click on the result of the SELECT query with to_json , the values ​​are accepted by the UI as JavaScript strings “0” and “1”, which are true in javascript. So now I do $_->{flag} += 0 foreach (@result) before applying to_json .

Question: At what point (s) in the chain should I insert these "hacks"? At the end of JavaScript, I could, for example, be sure to send the values ​​1 and 0 instead of true and false and re-read the flags as numbers. Is there something I can tell DBI to return the numeric columns in the form (scalars that JSON would take into account)?

Answer 1

Was and did it. I initially added 0 to the numbers in JSON that we decrypted in Perl, but it was tedious and took a lot of time because I had to iterate over a large structure, adding 0 to all the numbers. Then I helped Tim Bunce add sql_type_cast to the DBI so that a few minor changes to DBD :: Oracle could use it to return numbers instead of strings (they are still Perl scalars, but with some internal mastering - see below).

The problem was that I used JSON :: XS and looked at the scalars in encode_json to make sure pv or iv is configured to choose whether to produce "0" or 0 in encoded JSON. The DBI sql_type_cast and the new DBIstcf_DISCARD_STRING attribute means that I now bind the numeric columns like $ s-> bind_col (1, \ my $ col, {TYPE => SQL_INTEGER, DiscardString => 1}), and sql_type_cast removes the scalar pv if it looks number. The problem is that the DBD needs to be changed to use sql_type_cast. DBD :: Oracle is changed, and I just changed DBD :: ODBC to 1.31 to do this.

Perhaps you could modify DBD :: mysql in the same way or convince someone else (I am not using mysql).

See here for a long discussion of DiscardString implementation, etc.

You can find a test in DBD :: ODBC called sql_type_cast.t that checks the operation of DiscardString without using JSON :: whatever.

UPDATED: another link to a discussion on dbi-dev