trying to call Sybase stored procedure with parameters from PHP
trying to call Sybase stored procedure with parameters from PHP
(OP)
I'm trying to run a stored procedure on a Sybase 11.0.1.2596 database (Micros RES 3700) in a PHP file using PDO and dblib as the driver. I can call a procedure with no parameters with no problems using something like:
call custom.show_clocked_in_employees
This works perfectly. However, if the procedure takes parameters, I get an error. So if I have a procedure like this that takes 2 parameters:
I've tried calling it these ways:
No matter what I do, I get an error like:
this error:
or
etc.
If I run this procedure in a SQL client like RazorSQL using:
it works perfectly, so PDO/PHP/? appears to not be sending the data to the client correctly. What is the syntax to use to call a Sybase procedure with parameters using PDO?
EDIT: The exact way I'm trying to call it is like this:
call custom.show_clocked_in_employees
This works perfectly. However, if the procedure takes parameters, I get an error. So if I have a procedure like this that takes 2 parameters:
CODE -->
create procedure custom.custom_sp_R_cons_rvc_time_prd_ttls(in business_date_start timestamp,in business_date_end timestamp) result(start_business_date timestamp,end_business_date timestamp,store_number OBJ_NUM,store_id SEQ_NUM,...) begin declare@start_business_date timestamp; declare@end_business_date timestamp; ... end
I've tried calling it these ways:
CODE -->
call custom.custom_sp_R_cons_rvc_time_prd_ttls('2017-05-02','2017-05-02') call custom.custom_sp_R_cons_rvc_time_prd_ttls('2017-05-02 00:00:00.000000','2017-05-02 00:00:00.000000') exec custom.custom_sp_R_cons_rvc_time_prd_ttls('2017-05-02','2017-05-02') exec custom.custom_sp_R_cons_rvc_time_prd_ttls '2017-05-02','2017-05-02'
No matter what I do, I get an error like:
this error:
CODE -->
[0]=> HY000 [1]=>13638 [2]=> SQL Anywhere Error -188:Not enough valuesfor host variables [13638](severity 16)[(null)] [3]=>-1 [4]=>16
or
CODE -->
[2]=> SQL Anywhere Error -131: Syntax error near '2017-05-02'on line 1[102](severity 15)[(null)]
If I run this procedure in a SQL client like RazorSQL using:
CODE -->
call custom.custom_sp_R_cons_rvc_time_prd_ttls('2017-05-02','2017-05-02')
it works perfectly, so PDO/PHP/? appears to not be sending the data to the client correctly. What is the syntax to use to call a Sybase procedure with parameters using PDO?
EDIT: The exact way I'm trying to call it is like this:
CODE --> php
$dbh = new PDO('dblib:host=<host_ip>',$user,$password,null); $stmt =$dbh->prepare('call custom.custom_sp_R_cons_rvc_time_prd_ttls(:start_date, :end_date)'); $new_params = array(':start_date'=>'2017-05-02',':end_date'=>'2017-05-02'); $stmt->execute($new_params);