Quoting Chris <dmagick@xxxxxxxxx>:
Rick Emery wrote:
Is anyone using PHP5 on Linux to connect to MS SQL Server 2000 on
Windows and execute stored procedures with output parameters within
a transaction? If so, how?
I'm trying to do something like this in the PHP application:
begin transaction
execute a stored procedure, receiving an identity back
if certain conditions are met
execute another stored procedure, getting an identity
if certain other conditions are met
execute another stored procedure, getting an identity
if any of the above procedures fail
rollback transaction
else
commit transaction
Can you do this in mssql (ie take php out of the picture) ?
That is - if you execute a procedure, can you get the identity back?
I have no idea about mssql but that would be my first check..
Let me first say that I got it working with php. While the dblib pdo
driver doesn't support transactions, I was able to make this work
using the mssql_* functions (though I had to get a little creative, as
not all mssql data types are supported [bigint]).
Doing this all from sql was the first thing I tried, but there's a
problem. The procedures that get called depend on the data that is
available (from the posted page). To do this in a SQL stored
procedure, where I have a fixed set of parameters, I would have a
fixed amount of data I'd have to pass (even if empty).
Without getting into specifics of the application, an example would be
that I need to add a business, with a person (or people) associated
with the business, with phone numbers associated with the business and
person/people. First, I call the stored procedure with the business
information, then call the stored procedure that adds the business
phone (multiple times if more than one phone), then call the stored
procedure to add the person (calling for each person), then the stored
procedure to add the person's/people's phone number(s). If any of
those procedure calls fails, all should roll back (according to the
design spec).
If I write a single stored procedure to call all of the others within
a transaction, that's a lot of data to pass in. Not to mention that it
might be dynamic...how many people/phone numbers should I expect to be
passed?
Doing it inside php made things a lot easier.
Thanks for the suggestion,
Rick
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
