CODEWITHSUNDEEP
phpsql-serverdatetimestored-proceduresodbc
Josh
Josh

Reputation: 137

odbc_prepare for procedure with datetime input parameters

Somewhat new to odbc_prepare statements and am having an issue when trying to execute a stored procedure that requires datetime input parameters.

If I am to execute using odbc without using the prepared statement as displayed below I have no issue...

$dblink = db_connect();
$query = "EXEC dbo.[ProcedureName] '" . $dateinput . "'";
odbc_exec($dblink, $query);

Using the odbc_prepare I'm getting an error (only for procedures with DateTime inputs parameters). Example below...

function execute_db($dblink, $query, $params){
   $n = sizeof($params);
   for($i=0; $i<$n; $i++){
        if($i != 0){
            $query = $query . ', ?';
        }
        else{
            $query  = $query . ' ?';
        }
    }

    $statement = odbc_prepare($dblink, $query);
    odbc_execute($statement, $params);

    return $statement;
}

$dblink = db_connect();
$query = "EXEC dbo.[ProcedureName]";
$params = array($dateinput);

$result = execute_db($dblink, $query, $params);

This returns the following error:

Warning: odbc_execute(): SQL error: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification, SQL state 22005 in SQLExecute

The date string I'm passing in is in 'mm/dd/yyyy' format and works fine without using prepare. Is there a workaround for this other than changing the stored procedure's input type?

Upvotes: 1

Views: 1458

Answers (1)

bohica
bohica

Reputation: 5990

Use the proper ODBC syntax for calling a procedure and use the proper syntax for date times and it might get you further. The call syntax is {call procname(arguments)}. The datetime syntax is {ts ''} - see Date, Time, and Timestamp Escape Sequences and ODBC Datetime Format

Upvotes: 2

Related Questions