ODBC Connection to MsSQL and Stored Procedures

[Coyote6]

Hi I was wondering if someone could help me out on this one. I am trying to run a stored procedure on an odbc_connection to an MsSQL server. I can get the procedure to work like so without the prepare statement by adding the parameter directly into the string.

PHP Code:

<?php
error_reporting(E_ALL); 
ini_set('display_errors', 1);

include 'dbc.php';

$q = "exec stored_procedure 'paramenter_1'";
$r = odbc_exec($dbc, $q);

if ($r) {
    while ($row = odbc_fetch_array($r)) {
        echo $row['id'];
    }
}
?>

But when I try to use the odbc_prepare statement to insert the parameters as the examples show on the php website, I get errors.

PHP Code:

<?php
error_reporting(E_ALL); 
ini_set('display_errors', 1);

include 'dbc.php';

$q = "exec stored_procedure ?";
$p = array ('parameter_1');
$sp = odbc_prepare ($dbc, $q);
$r = odbc_exec($sp, $p);

if ($r) {
    while ($row = odbc_fetch_array($r)) {
        echo $row['id'];
    }
}
?>

It may have to do with the stored procedure syntax of the stored procedure in MsSQL. I don't really know because I normally use MySQL for my databases. Also using the mssql_connection or any of the other mssql functions is not an option as the server does not have them installed. Thanks for any help.

Stupid Microsoft Server GRRRRR!!!

[Coyote6]

Oh here's the error I'm receiving.

PHP Warning:  odbc_exec(): supplied resource is not a valid ODBC-Link resource in C:\Sites\sp_test.php on line 10

[Coyote6]

Okay now I feel like an idiot!

There are two different execute statements in odbc:

PHP Code:

odbc_exec($dbc, $q);
odbc_execute($sp, $p); 


The latter is the one to use with the prepare statement.

PHP Code:

$sp = odbc_prepare($dbc, $q);
odbc_execute($sp, $p); 


So here is the working code.

PHP Code:

<?php
error_reporting(E_ALL); 
ini_set('display_errors', 1);

include 'dbc.php';

$q = "exec stored_procedure @param_1=?";
$p = array ('parameter_1');
$sp = odbc_prepare ($dbc, $q);
$r = odbc_execute($sp, $p);
$r;
if ($r) {
    while ($row = odbc_fetch_array($sp)) {
        echo $row['id'];
    }
}
?>