Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

DBI and JSON fields

by Bod (Parson)
on Mar 12, 2024 at 12:28 UTC ( [id://11158199]=perlquestion: print w/replies, xml ) Need Help??

Bod has asked for the wisdom of the Perl Monks concerning the following question:

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?

Replies are listed 'Best First'.
Re: DBI and JSON fields
by choroba (Cardinal) on Mar 12, 2024 at 12:40 UTC
    , undef, $account, "$." . $row->{'name'}); # ~~
    Note that $. is a special variable in Perl and double quotes expand variables.

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
      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!

        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 ;) )
Re: DBI and JSON fields
by Corion (Patriarch) on Mar 12, 2024 at 12:33 UTC

    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.

      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...

        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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11158199]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others examining the Monastery: (2)
As of 2024-06-20 01:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.