in reply to DBI and JSON fields

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

[download]

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

[download]

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

Replies are listed 'Best First'.
Re^2: DBI and JSON fields
by Bod (Parson) on Mar 12, 2024 at 14:01 UTC
    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...

[reply]
[d/l]

      Usually my code starts out as

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

      [download]

      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, ... );

      [download]

      ... 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, ... );

      [download]

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

      [download]

      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.

[reply]
[d/l]
[select]
        Sometimes I keep the placeholders in an array to mention them...

        I haven't yet found a coding style for placeholders with which I'm completely happy.

        For a simple query I just keep them inline: 

        my $row = $dbh->selectrow_hashref("SELECT * FROM table WHERE idTable =
+ ?", under, $id);

        [download]
        But for more placeholders I've tried this: 
        my $row = $dbh->selectrow_array("SELECT * FROM table WHERE idTable = ?
+ AND some_column = ? AND another_column = ? AND name = ?", under,
    $table_id,
    $column_data,
    $column_info,
    $name);

        [download]
        IMHO that is too spread out. So instead I generally adopt this way if I have lots of placeholders. 
        my $row = $dbh->selectrow_array("SELECT * FROM table WHERE idTable = ?
+ AND some_column = ? AND another_column = ? AND name = ?", under,
    $table_id, $column_data, $column_info, $name);

        [download]
        Or, if there are silly numbers of placeholders, I will preload them into an array and just pass that to the DBI method.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom