comment on

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

In reply to SQL statement produces no results when executed using DBI's prepared statement, but one record when executed directly in MySQL. WHY? by ted.byers

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.