Re: Stored procs and transactions using Linux/PHP and Windows/MSSQL

Quoting Chris <dmagick@gmail.com>:

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