July 20, 2009 at 10:22 am
As part of an upgrade project, we are upgrading our intranet from a single SQL 2005 SP3 server to a mirrored pair of SQL 2008 SP1 servers. As our intranet is written in PHP, our current pages access the database via the mssql_ functions in PHP. However, now that we are moving to a mirrored pair of SQL servers, we are changing our code to use a DSN connection and the odbc_ calls in PHP. The transition has been easy with the exception of sticking point. The sticking point is as follows.
In our database classes, we use SCOPE_IDENTITY() function to return the primary key of the value we just inserted. Using the mssql_ functions, this "SELECT SCOPE_IDENTITY() FieldName" query is always sent as a T-SQL statement directly to the SQL server. However, when we perform this select using our ODBC connection, it is always sent as an RPC call. What we have found is the RPC call changes the scope of the SQL statement and causes the SCOPE_IDENTITY() function to always return NULL. What we are wondering is what do we need to do to either turn off RPC calls or fix our RPC calls so they are in the same scope as our INSERT statements?
Thanks for your help in advance.
David
July 20, 2009 at 10:52 am
Any possibility of changing to a stored procedure?
-Chuck
July 21, 2009 at 6:01 am
For reference, we are not able to use either @@IDENTITY or IDENT_CURRENT('TableName') for the following reasons.
@@IDENTITY - We have triggers on all of our tables to log all inserts, updates, and delete in an audit table. As a result, @@IDENTITY returns the primary key of the audit table instead of the table we inserted into first.
IDENT_CURRENT('TableName') - This function has too broad of a scope and could allow for the possibility that two people could simultaneously update a table and have one person receive the wrong primary key in return.
July 21, 2009 at 6:09 am
Chuck, unfortunately a stored procedure yields the same results. The insert in the stored procedure passes directly to the SQL server whereas the following SELECT statement in the stored procedure uses a RPC call. With that said, thank you for your suggestion.
If anyone else has run into this before or has any additional ideas, they would be greatly appreciated.
July 21, 2009 at 11:43 pm
Inside the remote stored procedure you capture the identity and return it as a parameter.
-Chuck
July 22, 2009 at 8:10 am
We have tried the following two approaches without any success. Each of the following approaches works when run via Query Analyzer, but not when executed via an ODBC call. We verified that the ODBC call does not return a result set or a parameter in either case.
-- Approach 1: Return results as a table
INSERT INTO PEOPLE (FirstName, LastName) VALUES ('David', 'OKeefe')
SELECT SCOPE_IDENTITY() PersonID
-- Approach 2: Return PersonID as a parameter
DECLARE @PersonID as INT
INSERT INTO PEOPLE (FirstName, LastName) VALUES ('David', 'OKeefe')
SET @PersonID = (SELECT SCOPE_IDENTITY() PersonID)
RETURN @PersonID
Thank you for the additional suggestions.
July 23, 2009 at 10:55 am
Well, this sure is an interesting debacle... Is the record data just inserted sufficiently unique that the data's ID field could be selected in a query because you have enough unique info without using that ID field to get a recordset with just 1 record in it?
I know that's a darn silly way to have to go about it, but when all else fails?
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 23, 2009 at 2:04 pm
Although we have several tables that have sufficiently unique data, the People table is a perfect example of a table where that is not the case. Thank you for your suggestion though.
July 24, 2009 at 11:58 am
What about instead of this:
SET @PersonID = (SELECT SCOPE_IDENTITY() PersonID)
do this:
SET @PersonID = SCOPE_IDENTITY()
July 24, 2009 at 12:12 pm
I don't suppose encapsulation into a transaction would help, would it?
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2009 at 10:49 am
Chuck, as you suggested, I tried the following.
SET @PersonID = SCOPE_IDENTITY()
instead of
SET @PersonID = (SELECT SCOPE_IDENTITY() PersonID)
Unfortunately, both styles produced the same results.
Steve, it appears that we are able to use encapsulation to solve our problem. Namely, we can use an OUTPUT clause to INSERT all of the newly inserted values into a temporary table that we create. Subsequently, we can select the PersonID directly back from this temporary table instead of using the SCOPE_IDENTITY() function.
Although this solves our problem with a workaround, does anyone have any other ideas about the SCOPE_IDENTITY() function? At this point I am really curious and I can't think that we are the only ones trying to use this function across an ODBC connection.
Anyone, thanks for everyone's help thus far.
July 28, 2009 at 11:08 am
You may be one of perhaps very few that use SQL Server with PHP instead of MySQL, which is significantly more common as a combination. I've heard from numerous sources that PHP is "swiss cheese" from a security perspective, so for anything other than internal website use, I'm doubtful I would choose that combination. As what I heard was from at least 6 months ago, those kinds of problems may have been resolved by now, and you may have had to "inherit" that setup. Conversion to VB.NET / ASP.NET would require a significant effort, and might well be impractical for that reason, at least in the short-term.
I wish I could offer up another alternative, but if encapsulation is working, I'd stick with it until something a lot more obvious comes along. Another line to pursue, however, would be the folks that make the PHP to SQL connectivity software (OLE DB or ODBC provider), and see if they're looking at this problem.
Steve
(aka smunson)
:-):-):-)
davido (7/28/2009)
Chuck, as you suggested, I tried the following.SET @PersonID = SCOPE_IDENTITY()
instead of
SET @PersonID = (SELECT SCOPE_IDENTITY() PersonID)
Unfortunately, both styles produced the same results.
Steve, it appears that we are able to use encapsulation to solve our problem. Namely, we can use an OUTPUT clause to INSERT all of the newly inserted values into a temporary table that we create. Subsequently, we can select the PersonID directly back from this temporary table instead of using the SCOPE_IDENTITY() function.
Although this solves our problem with a workaround, does anyone have any other ideas about the SCOPE_IDENTITY() function? At this point I am really curious and I can't think that we are the only ones trying to use this function across an ODBC connection.
Anyone, thanks for everyone's help thus far.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply