DBI and JSON fields

[Bod]

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'});

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'};

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')

I get the result 1

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

[choroba]

, undef, $account, "$." . $row->{'name'});
#                   ~~

Note that $. is a special variable in Perl and double quotes expand variables.

[Bod]

Note that $. is a special variable in Perl and double quotes expand variables

How silly do I feel now 😕

Thank you for pointing out what should have been obvious to me but clearly wasn't!

[stevieb]

Meh, easy mistake to make.

Instead of using the concat operator (ie. '.'), you can escape the dollar (note: the single quotes on your href keys are superfluous here):

"\$.$row->{name}"

...or to be even more safe, you can use a special trick to interpolate a variable inside of a string, if it gets more complex. This way, perl knows where the variable is, and the other chars are:

my $x = 'sent';
print "\$.${x}ence";

# output: $.sentence

With a hashref, this looks a lot different and is a lot more convoluted*:

"\$.${\$row->{name}}ence"

In cases like that, I much prefer sprintf(). It's always a dead reliable way to concoct strings:

sprintf("\%.%s", $row->{name});

That way, you can even do things like this:

my $row = {name => 'sent'};

my $thing = sprintf("\$.%sence", $row->{name});

# output: $.sentence

For readability, I like to write my db queries so that they're broken down logically, like this:

    my $something_query = qq{
        SELECT COUNT(*) FROM Person 
        WHERE Account_idAccount = ?
        AND JSON_EXISTS(custom, ?)
    };

Then, if the variables I need to feed into the engine are anything but single scalars in a list:

    my @array = $dbh->selectrow_array(
        $something_query, 
        undef, 
        $account, 
        sprintf(
            "\$.%s", 
            $row->{name}
        )
    );

There's no reason to feel silly for little things like this. Even the most experienced programmers bang their heads against their desks from time-to-time for seemingly simple issues. The good thing is you were humble enough to reach out, and that led to a successful conclusion. Keep doing that. Future users of your software will appreciate your willingness to ask others for help when you can't quite solve something. Much better to be humble than it is to try to broom the cockroach under the carpet. This quality will lead to your users trusting you. Trust me.

*- Because you have to reference a reference to a dereferenced reference (...and I still don't think I understand that correctly ;) )

[Corion]

When debugging SQL statements, do yourself a favour and print out the SQL statements before you run them:

my $sql = "SELECT COUNT(*) FROM Person WHERE Account_idAccount = 35 AN
+D JSON_EXISTS(custom, '$.test3')");
say "Running <<$sql>>";
my $res = $dbh->selectrow_array($sql);
...

Also, it seems to me that you are not using strict or your code would not compile at all. Please do use strict to catch errors where you erroneously don't quote strings.

Most likely you wanted code like the following:

my $sql = <<'SQL';
    SELECT COUNT(*) FROM Person WHERE Account_idAccount = 35 AND JSON_
+EXISTS(custom, '$.test3')
SQL
...

Update: While using strict is nice, it will not catch using $., as that is a valid Perl variable.

[Bod]

do yourself a favour and print out the SQL statements before you run them

Would you separate out the SQL statement into a variable in production code (without the say statement of course) or only whilst debugging?

I see some advantages in terms of clarity especially when there are lots of placeholders to include.

In case of any doubt, strict is in use...

[Corion]

Usually my code starts out as

my $results = $dbh->selectall_arrayref(<<'SQL',{ Slice => {} }, $place
+holder1, ... );
    SELECT foo
         , bar
      FROM mytable
SQL

Then, while debugging, it morphs into

my $sql = <<'SQL';
    SELECT foo
         , bar
      FROM mytable
SQL
$app->log($sql);
my $results = $dbh->selectall_arrayref($sql,{ Slice => {} }, $placehol
+der1, ... );

... and then it stays that way, because reverting the structure is only good until I need to re-debug things:

my $sql = <<'SQL';
    SELECT foo
         , bar
      FROM mytable
SQL
# $app->log($sql);
my $results = $dbh->selectall_arrayref($sql,{ Slice => {} }, $placehol
+der1, ... );

For the placeholders I agree. Sometimes I keep the placeholders in an array to mention them in the same place as the SQL. In other situations, I use named placeholders like SQLite supports nowadays, and bind those in a loop:

    while (my ($name,$value) = each %$parameter_names) {
        (my $perl_name) = ($name =~ m!(\w+)!);
        $perl_name = '$' . $perl_name;
        if( ! exists $parameters->{$perl_name}) {
            croak "Missing bind parameter '$perl_name'";
        };
        my $type = SQL_VARCHAR;

        # This is a horrible API, but so is using uplevel'ed variables
        if( my $r = ref $parameters->{$perl_name}) {
            if( $r eq 'SCALAR' ) {
                $type = SQL_INTEGER;
                # Clear out old variable binding:
                my $v = $parameters->{$perl_name};
                delete $parameters->{$perl_name};
                $parameters->{$perl_name} = $$v;
            } elsif( $r eq 'ARRAY' ) {
                $type = SQL_INTEGER;
                # Clear out old variable binding:
                my $v = $parameters->{$perl_name};
                delete $parameters->{$perl_name};
                $parameters->{$perl_name} = $v->[0];
                $type = $v->[1];
            }
        }
        $sth->bind_param($name => $parameters->{$perl_name}, $type)
    };

See Filesys::DB for a fancy but ultimately pointless idea of binding placeholder names to lexical variables in the Perl code. This was nice to use but in the end, the surrounding program does not pass SQL around but has function/method calls that do the right thing and the lexical binding does not provide anything helpful at the outside surface.