Re^4: DBI and JSON fields

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.

Comment on Re^4: DBI and JSON fields Select or Download Code

Replies are listed 'Best First'.
Re^5: DBI and JSON fields by stevieb (Canon) on Mar 13, 2024 at 08:09 UTC
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]
Re^5: DBI and JSON fields by soonix (Chancellor) on Mar 13, 2024 at 07:49 UTC
… and you probably have `use constant under => undef;` [download] 😉	[reply] [d/l]
Re^6: DBI and JSON fields by stevieb (Canon) on Mar 13, 2024 at 08:16 UTC
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]
Re^6: DBI and JSON fields by Bod (Parson) on Mar 18, 2024 at 21:43 UTC
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]