By jyg on
I have printed out the query, pasted it into my query analyzer, substituted the desired values, and I get the expected rows. The execute() call, however, returns no rows. When I remove the condition() statements, it works just fine, returning all rows.
I know that the values in the variables supplied to the condition() calls have the correct data in them, as I've debug printed them for verification. There must be something I'm not doing correctly with the $query->condition() call.
I'd really appreciate any help, thanks.
jyg
// QUERY API - , $query->execute returns 0 rows
function list_fixed_debt_series($params) {
drupal_add_http_header('Content-Type', 'application/json');
$instid = isset($params[0]) ? $params[0] : null;
$series = isset($params[1]) ? $params[1] : null;
$query = db_select('yuba_vrdn', 'v');
$query->join('yuba_debt_fixed', 'f', 'v.vrdn_id = f.vrdn_id');
$query->addField('v', 'series_name');
$query->addField('v', 'ticker');
$query->addField('v', 'amt_outstanding');
$query->addField('v', 'maturity_dt', 'maturity');
$query->addField('f', 'nxt_call_dt', 'first_call_date');
$query->addField('f', 'coupon');
$query->addField('f', 'muni_issue_yld', 'original_issue_yield');
$query->condition('v.series_name', $series, '=');
$query->condition('v.institution_id', $instid, '=');
// return $query->__toString();
$result = $query->execute();
$alldata = $result->fetchAll();
return json_encode($alldata);
}
// PREPARED STATEMENT
SELECT v.series_name AS series_name, v.ticker AS ticker, v.amt_outstanding AS amt_outstanding, v.maturity_dt AS maturity, f.nxt_call_dt AS first_call_date, f.coupon AS coupon, f.muni_issue_yld AS original_issue_yield
FROM
{yuba_vrdn} v
INNER JOIN {yuba_debt_fixed} f ON v.vrdn_id = f.vrdn_id
WHERE (v.series_name = :db_condition_placeholder_0) AND (v.institution_id = :db_condition_placeholder_1)
// AS RUN IN QUERY ANALYZER, returns 13 rows
SELECT v.series_name AS series_name, v.ticker AS ticker, v.amt_outstanding AS amt_outstanding, v.maturity_dt AS maturity, f.nxt_call_dt AS first_call_date, f.coupon AS coupon, f.muni_issue_yld AS original_issue_yield
FROM
yuba_vrdn v
INNER JOIN yuba_debt_fixed f ON v.vrdn_id = f.vrdn_id
WHERE (v.series_name = '2004B') AND (v.institution_id = 3)
Comments
I looked at that five times,
I looked at that five times, and it all looks right to me. I'm going to come back and look at it again, it's a real head scratcher. I do have a question though - how are you dumping the query containing the substituted values? I want to know how to do that :D
Edit: Are you sure that $instid and $series have values? Also, have you checked your logs to see if there are any warnings?
Contact me to contract me for D7 -> D10/11 migrations.
Thanks for checking... but...
I went and used db_query() to enter the query by hand (pasted from $query()->__toString). It helped me to realize that, as you found out, the code is 100% correct. Yes, it was the parameters. Somewhere along the way, I reversed the parameters in the Javascript that creates the URL for the AJAX call to run this web service code. I must've printed out the parameters 100 times, and and never realized that while the data was correct, the order was reversed :p
No longer a head scratcher. Its a total hair puller... Sorry to have wasted your time. Well, at least we have a good example of using SelectQuery on Drupal.org :D
Glad to have helped... and
Glad to have helped... and I'd sure appreciate it if you told me how you dumped the query with the substituted values.
Contact me to contract me for D7 -> D10/11 migrations.