ted.byers has asked for the wisdom of the Perl Monks concerning the following question:

This is driving me insane!

I have several statements that WILL produce a record set (with one record), when executed within MySQL's default client. However, when I create a prepared statement, they produce nothing. Here is some code:

$sql = "SELECT action_id
  FROM transactions LEFT JOIN actions 
      ON transactions.transaction_id = actions.transaction_id
    LEFT JOIN merchants ON merchant_id = idmerchants
  WHERE mdf_20 = 'a4bbf326102e3064a8d9e06558c4b5e2020ee353' OR mdf_20 
+LIKE '_a4bbf326102e3064a8d9e06558c4b5e2020ee353%'
    AND action_type IN ('capture','sale')
    AND ABS(amount) = ABS('$amnt') AND success = 1 
    AND processor_settlement_date IS NULL
    ORDER BY transaction_date;";
$sth = $dbh->prepare($sql);
$sth->execute;
$aref = $sth->fetchrow_array;
print join(', ', @$aref),"\n";
$sth->finish;

$sql = "SELECT action_id,transaction_date
  FROM transactions LEFT JOIN actions 
      ON transactions.transaction_id = actions.transaction_id
    LEFT JOIN merchants ON merchant_id = idmerchants
  WHERE mdf_20 = '1442a29888840b0b9505d34da6cd8897d153976d' 
    AND action_type IN ('capture','sale')
    AND processor_settlement_date IS NULL
UNION
SELECT action_id,transaction_date FROM profitorius.actions 
  WHERE transaction_id LIKE '1442a29888840b0b9505d34da6cd8897d153976d%
+' 
    AND ABS(amount) = ABS('49.95') AND success = 1 
    AND action_type IN ('capture','sale')
    AND processor_settlement_date IS NULL
    ORDER BY transaction_date;";
$sth = $dbh->prepare($sql);
$sth->execute;
$aref = $sth->fetchrow_array;
print join(', ', @$aref),"\n";
$sth->finish;
$sql = "SELECT action_id,transaction_date
  FROM transactions LEFT JOIN actions 
      ON transactions.transaction_id = actions.transaction_id
    LEFT JOIN merchants ON merchant_id = idmerchants
  WHERE mdf_20 = 'a4bbf326102e3064a8d9e06558c4b5e2020ee353    ' 
    AND action_type IN ('capture','sale')
    AND processor_settlement_date IS NULL
UNION
SELECT action_id,transaction_date FROM profitorius.actions 
  WHERE transaction_id LIKE 'a4bbf326102e3064a8d9e06558c4b5e2020ee353 
+   %' 
    AND ABS(amount) = ABS('4.95') AND success = 1 
    AND action_type IN ('capture','sale')
    AND processor_settlement_date IS NULL
    ORDER BY transaction_date;";
$sth = $dbh->prepare($sql);
$sth->execute;
$aref = $sth->fetchrow_array;
print join(', ', @$aref),"\n";
$sth->finish;

[download]

Both of these statements produce precisely one record, when executed using mysql's client. But the above code produces no output at all. It does not even produce an error message (although none is expected since the SQL is correct). here is the mysql session:

mysql> SELECT action_id,transaction_date
    ->   FROM transactions LEFT JOIN actions 
    ->       ON transactions.transaction_id = actions.transaction_id
    ->     LEFT JOIN merchants ON merchant_id = idmerchants
    ->   WHERE mdf_20 = '1442a29888840b0b9505d34da6cd8897d153976d' 
    ->     AND action_type IN ('capture','sale')
    ->     AND processor_settlement_date IS NULL
    -> UNION
    -> SELECT action_id,transaction_date FROM profitorius.actions 
    ->   WHERE transaction_id LIKE '1442a29888840b0b9505d34da6cd8897d1
+53976d%' 
    ->     AND ABS(amount) = ABS('49.95') AND success = 1 
    ->     AND action_type IN ('capture','sale')
    ->     AND processor_settlement_date IS NULL
    ->     ORDER BY transaction_date;
+-----------+---------------------+
| action_id | transaction_date    |
+-----------+---------------------+
|   1061578 | 2014-08-05 19:36:18 |
+-----------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT action_id,transaction_date
    ->   FROM transactions LEFT JOIN actions 
    ->       ON transactions.transaction_id = actions.transaction_id
    ->     LEFT JOIN merchants ON merchant_id = idmerchants
    ->   WHERE mdf_20 = 'a4bbf326102e3064a8d9e06558c4b5e2020ee353    '
+ 
    ->     AND action_type IN ('capture','sale')
    ->     AND processor_settlement_date IS NULL
    -> UNION
    -> SELECT action_id,transaction_date FROM profitorius.actions 
    ->   WHERE transaction_id LIKE 'a4bbf326102e3064a8d9e06558c4b5e202
+0ee353    %' 
    ->     AND ABS(amount) = ABS('4.95') AND success = 1 
    ->     AND action_type IN ('capture','sale')
    ->     AND processor_settlement_date IS NULL
    ->     ORDER BY transaction_date;
+-----------+---------------------+
| action_id | transaction_date    |
+-----------+---------------------+
|   1061498 | 2014-08-05 18:50:11 |
+-----------+---------------------+
1 row in set (0.00 sec)

[download]

Now, I know for a fact that the connection to the DB is OK as microseconds before these statements are executed, the perl script executes several other statements flawlessly and produces the expected output.

I was hoping to use parameterized prepared statements, like the following to reuse, and thus make faster.

$sql = "SELECT action_id
  FROM transactions LEFT JOIN actions 
      ON transactions.transaction_id = actions.transaction_id
    LEFT JOIN merchants ON merchant_id = idmerchants
  WHERE mdf_20 = ? 
    AND action_type IN ('capture','sale')
    AND ABS(amount) = ABS(?) AND success = 1 
    AND processor_settlement_date IS NULL
    ORDER BY transaction_date;";
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, "'1442a29888840b0b9505d34da6cd8897d153976d'");
$sth->bind_param(2, 49.95);
$sth->execute;
my $aref = $sth->fetchrow_array if (defined $aref);
print join(', ', @$aref),"\n";
$sth->bind_param(1, "'a4bbf326102e3064a8d9e06558c4b5e2020ee353'");
$sth->bind_param(2, 4.95);
$sth->execute;
$aref = $sth->fetchrow_array;
print join(', ', @$aref),"\n";
$sth->finish;

my $like_cond = '';
$sql  = "SELECT action_id,transaction_date FROM profitorius.actions 
  WHERE transaction_id LIKE ? 
    AND ABS(amount) = ABS(?) AND success = 1 
    AND action_type IN ('capture','sale')
    AND processor_settlement_date IS NULL
    ORDER BY transaction_date;";
$sth = $dbh->prepare($sql);
$like_cond = "'1442a29888840b0b9505d34da6cd8897d153976d%'";
$sth->bind_param(1,$like_cond);
$sth->bind_param(2, 49.95);
$sth->execute;
$aref = $sth->fetchrow_array;
print join(', ', @$aref),"\n";

$like_cond = "'a4bbf326102e3064a8d9e06558c4b5e2020ee353%'";
$sth->bind_param(1,$like_cond);
$sth->bind_param(2, 4.95);
$sth->execute;
$aref = $sth->fetchrow_array;
print join(', ', @$aref),"\n";

[download]

Alas, these too produce no output. Worse, When using parameterized prepared queries, I know of no way to view what the resulting SQL statement would look like once bind_param has bound the parameter values to the right parameter.

Why, when I have used analogous code thousands of times without problem, would I get this problem now?

I know the usual 'advice' about providing a small, self contained example that reproduces the problem, but I fear this may not be possible. The client code is small, but the database is huge; and with my invariable success using analogous code on other tables and in other scripts, the problem may be due to peculiarities of this particular combination of database tables and this script.

Any ideas or suggestions as to what may be awry would be greatly appreciated.

Thanks

Ted

  • Comment on SQL statement produces no results when executed using DBI's prepared statement, but one record when executed directly in MySQL. WHY?
  • Select or Download Code

Replies are listed 'Best First'.
Re: SQL statement produces no results when executed using DBI's prepared statement, but one record when executed directly in MySQL. WHY?
by NetWallah (Canon) on Aug 07, 2014 at 03:33 UTC
    From the doc:

                  fetchrow_arrayref. Fetches the next row of data and returns it as a list containing the field values.

    In your assignment: 

    $aref = $sth->fetchrow_array;

    [download]
    You are assigning a list to a scalar. i.e. $aref is a SCALAR .

    The next statement, you try to dereference $aref as an array ref.

    If you had use warnings; it would say:

           Useless use of a constant (..) in void context at ...

    In any case, @$aref is empty.

    It would also be helpful to do error checking (RaiseError, or check $sth->err).

    If you are confident that the fetchrow_array returns a value, try: 

    ($aref )= $sth->fetchrow_array;
print "GOT |$aref|\n";

    [download]
    Update:Corrected doc reference from fetchrow_arrayref to fetchrow_array. Thanks McA for pointing this out.

            Profanity is the one language all programmers know best.

[reply]
[d/l]
[select]

      Thanks

      My error was to use fetchrow_array instead of fetchrow_arrayref (which is what, in fact, I'd intended). Making that change fixed all.

      Just goes to show that trying to code when you're over-tired is not conducive to being productive, allowing silly errors to creep in. Nor is it conducive to remaining sane. :-(

      Thanks

      Ted

[reply]
Re: SQL statement produces no results when executed using DBI's prepared statement, but one record when executed directly in MySQL. WHY?
by roboticus (Chancellor) on Aug 07, 2014 at 05:09 UTC

    ted.byers:

    Your SQL statements aren't actually formed properly, because you're using double-quoting and there's an unescaped '%' inside the text. So your SQL probably turned out to be something like:

    select ...
where  ...
OR mdf_20 LIKE '_a4bbf326102e3064a8d9e06558c4b5e2020ee353HASH(0x826998
+)'
                                                         ^^^^^^^^^^^^^
+^

    [download]

    Your prepared statements have a different problem: you don't need to have quote characters in your arguments. It's likely turning into something like:

    select ...
where  ...
OR mdf_20 LIKE '''_a4bbf326102e3064a8d9e06558c4b5e2020ee353HASH(0x8269
+98)'''
                ^^                                         ^^^^^^^^^^^
+^^^^^

    [download]

    So first, be sure that you don't use the @ or % character inside double-quotes without escaping them. Second, go ahead and work with the parameterized version of the code, as it'll be better in the long term, and will be no more trouble to get running than the hardcoded version. I tweaked your second listing to be:

    $sql = "SELECT action_id
  FROM transactions LEFT JOIN actions 
      ON transactions.transaction_id = actions.transaction_id
    LEFT JOIN merchants ON merchant_id = idmerchants
  WHERE mdf_20 = ? 
    AND action_type IN ('capture','sale')
    AND ABS(amount) = ABS(?) AND success = 1 
    AND processor_settlement_date IS NULL
    ORDER BY transaction_date;";
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, '1442a29888840b0b9505d34da6cd8897d153976d');
$sth->bind_param(2, 49.95);
$sth->execute;
my $aref = $sth->fetchrow_array if (defined $aref);
print join(', ', @$aref),"\n";
$sth->bind_param(1, 'a4bbf326102e3064a8d9e06558c4b5e2020ee353');
$sth->bind_param(2, 4.95);
$sth->execute;
$aref = $sth->fetchrow_array;
print join(', ', @$aref),"\n";
$sth->finish;

my $like_cond = '';
$sql  = "SELECT action_id,transaction_date FROM profitorius.actions 
  WHERE transaction_id LIKE ? 
    AND ABS(amount) = ABS(?) AND success = 1 
    AND action_type IN ('capture','sale')
    AND processor_settlement_date IS NULL
    ORDER BY transaction_date;";
$sth = $dbh->prepare($sql);
$like_cond = '1442a29888840b0b9505d34da6cd8897d153976d%';
$sth->bind_param(1,$like_cond);
$sth->bind_param(2, 49.95);
$sth->execute;
$aref = $sth->fetchrow_array;
print join(', ', @$aref),"\n";

$like_cond = 'a4bbf326102e3064a8d9e06558c4b5e2020ee353%';
$sth->bind_param(1,$like_cond);
$sth->bind_param(2, 4.95);
$sth->execute;
$aref = $sth->fetchrow_array;
print join(', ', @$aref),"\n";

    [download]

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
[select]
      Besides replying to a to-be-reaped node, your comment is mistaken. Hashes aren't interpolated in double quotes (hash references in scalar variables are).
      لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]

        choroba:

        Regarding the hash interpolation: mea culpa. But as far as the to-be-reaped node part is considered, sometimes it takes me a while to compose a node. It certainly didn't look like it was about to be reaped before I started writing.

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

[reply]

Back to Seekers of Perl Wisdom