Search the Community

Hi - I have been battling for days trying to pass a parameter to a functioning procedure. I have a PHP app that works perfectly, which calls a Procedure in our database. As soon as I add a parameter to the Procedure, I get zero result. The objective is to be able to pass a parameter (accountID) to the called Procedure. This parameter is to be used to control the WHERE clause in the procedure Please can someone look at tell me what am I doing wrong. Thanks in advance (and I'm sorry to have copied all the code, but thought it would help) Larry The PHP (getallroutesformap.php ) Application code<?php include 'dbconnect.php'; switch ($dbType) { case DB_MYSQL: $stmt = $pdo->prepare('CALL prcGetAllRoutesForMap();'); $acID='10091'; $stmt->bindParam(':accID', $acID, PDO::PARAM_STR); // always calls the procedure /// $stmt->bindParam(':accID', $acID); /// does not work /// $stmt->bindParam(':accID', 10091); /// does not work $acID='10091'; break; case DB_POSTGRESQL: case DB_SQLITE3: } $stmt->execute(); $json = '{ "locations": ['; foreach ($stmt as $row) { $json .= $row['json']; $json .= ','; } $json = rtrim($json, ","); $json .= '] }'; header('Content-Type: application/json'); echo $json; ?> Database Procedure (getallroutesformap) that works.Note: accountID=’10091’, but I need it to be a variable/parameter BEGIN SELECT sessionId, gpsTime, CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(gpsTime, '%b %e %Y %h:%i%p'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json FROM (SELECT MAX(GPSLocationID) ID FROM gpslocations WHERE accountID = '10091' && sessionID != '0' && CHAR_LENGTH(sessionID) != 0 && gpstime != '0000-00-00 00:00:00' GROUP BY sessionID) AS MaxID JOIN gpslocations ON gpslocations.GPSLocationID = MaxID.ID ORDER BY gpsTime; END Database Procedure that gives Zero ResultsHere the Procedure has a parameter assigned to it called accID . which is setup as an IN, VARCHAR, 15 This immediately gives me a zero result, even if I change accountID= accID or leave it as it was before. BEGIN SELECT sessionId, gpsTime, CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(gpsTime, '%b %e %Y %h:%i%p'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json FROM (SELECT MAX(GPSLocationID) ID FROM gpslocations WHERE accountID = accID && sessionID != '0' && CHAR_LENGTH(sessionID) != 0 && gpstime != '0000-00-00 00:00:00' GROUP BY sessionID) AS MaxID JOIN gpslocations ON gpslocations.GPSLocationID = MaxID.ID ORDER BY gpsTime; END