Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts

    ODBC Connection to MsSQL and Stored Procedures

    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!!!
    Last edited by Coyote6; 12-11-2009 at 08:55 PM.

  • #2
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    Oh here's the error I'm receiving.

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

  • #3
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    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'];
        }
    }
    ?>
    Last edited by Coyote6; 12-11-2009 at 09:02 PM.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •