Re: Calling MSSQL Stored Procedure

[Robert Twitty <rtwitty@xxxxxxxxxxxxxxxxx>]

On Thu, 15 Sep 2005, Alf Stockton wrote:

> Thanks for the suggestions on ADODB which, thanks to you, I have
> implemented.
>
> I like the simularity between databases as my applications are going to
> be accessing both MSSQL & Oracle.
>
> The next step is for me to use ADODB & PHP to call various MSSQL and
> Oracle Stored Procedurers so I am currently working my way throught the
> ADODB documentation.
>
> However, if one of you can come up with quick examples it would make my
> task a little quicker and, of course, simplier
>
Below is an example using the ADODB odbtp driver:

<?php

    include('adodb/adodb.inc.php');

    $DB = NewADOConnection('odbtp');

    // Database connection
    $constr = 'DRIVER={SQL
Server};SERVER=(local);UID=myuid;PWD=mypwd;DATABASE=OdbtpTest; ';
    if( !$DB->PConnect( '127.0.0.1', $constr ) ) {
        print $DB->ErrorMsg();
        die;
    }

    // SQL Query Execution
    $rs = $DB->Execute( "SELECT * FROM Employees" );
    if( !$rs ) {
        print $DB->ErrorMsg();
        die;
    }
    while (!$rs->EOF) {
        print_r($rs->fields);
        $rs->MoveNext();
    }

    // Stored Procedure Execution
    $stmt = $DB->PrepareSP( "GetTheIntsString" );
    if( !$stmt ) {
        print $DB->ErrorMsg();
        die;
    }
    if( !$DB->Parameter( $stmt, $Id, "Id" ) ) {
        print $DB->ErrorMsg();
        die;
    }
    if( !$DB->Parameter( $stmt, $TheIntsString, "TheIntsString" ) ) {
        print $DB->ErrorMsg();
        die;
    }
    $Id = 123;
    $rs = $DB->Execute( $stmt );
    if( !$rs ) {
        print $DB->ErrorMsg();
        die;
    }
    echo "String = $TheIntsString\n";

    $Id = 12;
    $rs = $DB->Execute( $stmt );
    if( !$rs ) {
        print $DB->ErrorMsg();
        die;
    }
    echo "String = $TheIntsString\n";

    $Id = 72;
    $rs = $DB->Execute( $stmt );
    if( !$rs ) {
        print $DB->ErrorMsg();
        die;
    }
    echo "String = $TheIntsString\n";
?>

Code for GetTheIntsString stored procedure:

CREATE PROCEDURE GetTheIntsString
    @Id int,
    @TheIntsString varchar(256) = NULL OUTPUT
AS
    SET NOCOUNT ON

    SET @TheIntsString =
     (SELECT 'Tiny Int = ' + CONVERT(varchar(32),TheTinyInt) + '  ' +
             'Small Int = ' + CONVERT(varchar(32),TheSmallInt) + '  ' +
             'Int = ' + CONVERT(varchar(32),TheInt) + '  ' +
             'Big Int = ' + CONVERT(varchar(32),TheBigInt)
      FROM TheInts WHERE Id = @Id)
GO

-- bob

-- 
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php