I'm creating user-defined custom fields in Maria DB using the JSON Data Type. I've never had cause to use this data type before so I've been playing learning by running queries directly against a test database to get the syntax right. I thought I'd got this to the point where I can use it and imagined that there could be some issues with getting the DBI placeholders right.

But I seem to have hit a more tricky issue...

I started with this DBI query: 

$row->{'inuse'} = $dbh->selectrow_array("SELECT COUNT(*) FROM Person W
+HERE Account_idAccount = ? AND JSON_EXISTS(custom, ?)", undef, $accou
+nt, "$." . $row->{'name'});

[download]
But this didn't work. I assumed it was a placeholder issue.

However, I slowly removed the placeholders to test where things were going wrong. To the point where I arrived at this with no placeholders... 

$row->{'inuse'} = $dbh->selectrow_array("SELECT COUNT(*) FROM Person W
+HERE Account_idAccount = 35 AND JSON_EXISTS(custom, '$.test3')");

die $dbh->errstr if $dbh->err;
die $row->{'inuse'} if $row->{'inuse'};

[download]
The code doesn't die.

But, if I run the same query directly against the same database: 

SELECT COUNT(*) FROM Person WHERE Account_idAccount = 35 AND JSON_EXIS
+TS(custom, '$.test3')

[download]
I get the result 1

Is there a problem using the MariaDB JSON Data Type with DBI or have I missed something else obvious?

In reply to DBI and JSON fields by Bod

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.