cppdb WHERE JSON_EXTRACT integer bound as string

[dreaming-augustin]

Having a mysql table with a JSON field, I can easily select any integer included in the JSON object with the simple query:

"SELECT JSON_EXTRACT(json_field, '$.item_id') AS item_id FROM my_table";

And I can extract the integer in the usual fashion:

   cppdb::result    res;
    res = sql() << " SELECT JSON_EXTRACT(json_field, '$.item_id') AS item_id  " 
                  " FROM my_table"; 
   while (res.next()) {
                    long id;
                    res >> id;
   }

Now, the same does not work if I want to bind the integer to search within the JSON field:

    cppdb::result    res;
    res = sql() << " SELECT id FROM my_table "
             " WHERE JSON_EXTRACT(json_field, '$.item_id') = ? "
              << 123;

With the above statement, the integer 123 is bound as a string. As a result, the query always fails to select appropriate entries, because it is comparing integers to strings.

The workaround is to force casting the integer... back to an integer this way:

    cppdb::statement    res;
    res = sql() << " SELECT id FROM my_table "
    " WHERE JSON_EXTRACT(json_field, '$.item_id') = CONVERT(?, SIGNED INTEGER) "
              << 123;

It is obviously not ideal. I am not sure whether it is a problem with cppdb or with the mysql library that cppdb uses.

[dreaming-augustin]

Other libraries have similar problems. Here are a couple of examples, and the solutions they offer:

PDO Int placeholders getting quotes around them https://stackoverflow.com/questions/15984025/pdo-int-placeholders-getting-quotes-around-them

$stm->bindValue(2, ($request-1)*4), PDO::PARAM_INT);

Here, the PDO library offers the possibility to explicitly state the type of the parameter.

[dreaming-augustin]

JSON_EXTRACT comparison/casting issue #348 https://github.com/sidorares/node-mysql2/issues/348