in reply to Re^2: DBI and JSON fields
in thread DBI and JSON fields

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.

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

      Separate your query from your call. See Re^3: DBI and JSON fields.

      I'll replicate here because it will translate into a much better example:

          my $thing_query = qq{
        SELECT * FROM table 
        WHERE idTable = ? 
        AND some_column = ? 
        AND another_column = ? 
        AND name = ?
    };

      [download]

      Then:

      my @ary = $dbh->selectrow_array(
    $thing_query,
    undef,
    $table_id,
    $column_data,
    $column_info,
    $name
);

      [download]

      See how you can look at the query and immediately line up the parameters to the selectrow_array() call with the supplied list of params?

[reply]
[d/l]
[select]
      … and you probably have 
      use constant under => undef;

      [download]
      😉
[reply]
[d/l]

        I admit, I laughed at this... good job!

        The reality is, is that the content quality of the question is often related directly to the quality of the answer. In this case however, this problem was pretty glaringly obvious :)

[reply]

        I really should include that constant declaration in every script just after strict and warnings as I am forever typing under instead of undef!

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom