Re: ODBC Stored Procedures
| From: | Steven Campbell | Date: | Wed, 05 Jul 2000 14:59:18 +0000 |
| Subject: | Re: ODBC Stored Procedures | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-872@lists.php.net to get a copy of this message | ||
William Jackson wrote:
>
> I have a small problem
>
> I have a stored procedure in my SQL Server database
>
> CREATE PROCEDURE x
> @ID int,
> @RetVal int OUTPUT
> AS
>
> .......
>
> SELECT @RetVal = 1
> RETURN
>
> in my php program I want to call that stored procedure and also get hold of
> the return value
>
> so I have something like
>
> $SQL = "{ ? = CALL x ( ? ) }";
> $Values = array ($ID);
> $Query_ID = odbc_prepare($Link_ID,$SQL);
> $Query_OK = odbc_execute($Query_ID,&$Values);
>
> for a stored proc that doesnt return anything this works fine but I cant
> seem to work out the correct way of calling the above procedure and also
> getting the @RetVal Value.
>
> Anybody have any ideas?
My stored procedure looks like this:
CREATE PROCEDURE get_access
@SEQ_ID varchar(512)
AS
-- Nuke an existing global cursor if it exists.
-- If you don't do this, you'll suddenly wonder why M$ SQL Server is
using all of its available RAM and paging space...
IF CURSOR_STATUS('global', 'status_cur') <> -3
DEALLOCATE status_cur
-- Note that for simplicity I (occasionally) return the status in one
cursor, and
-- the actual results I wanted in the other. If you don't you won't know
if your query failed.
-- Then I do my happy select statement into the global cursor
DECLARE status_cur CURSOR GLOBAL FAST_FORWARD FOR
SELECT blah blah blah
RETURN
Then in PHP, I do this:
$query = "EXEC get_access $SEQ_ID ";
$query .= "OPEN status_cur ";
$query .= "FETCH NEXT FROM status_cur ";
while(odbc_fetch_row($result)) {
// The ODBC way of doing things
$bleh[] = (integer) odbc_result($result, 1);
} // end of while for results from get_access query
// Note that if you're retrieving many result sets, you should do the
following also:
// $query .= "WHILE @@FETCH_STATUS = 0 ";
// $query .= "BEGIN ";
// $query .= "FETCH NEXT FROM status_cur ";
// $query .= "END ";
// $query .= "CLOSE status_cur ";
// Note that you'll also need to retrieve the results from $result
// while(odbc_fetch_row($result)) {
// The ODBC way of doing things
// $bleh[] = (integer) odbc_result($result, 1);
// } // end of while for results from get_access query
If i've lost you, please let me know, and I'll explain further...
Steven Campbell