bigbillmac
Programmer
Can anyone help?
I have an application that was originally written under PB7. I have now upgraded to PB10, which is infinitely more stable in the development environment - eg I can now actually use the debugger with confidence.
However, I have a really serious issue - transaction processing in SQL Server.
The transaction processing is carried out with the familiar statements, such as:
BEGIN TRANSACTION A
select ....
update ...
etc
COMMIT TRANSACTION A
The problem is very interment, and is different on different PCs but when you get it then it stays put and will always do it. But other PCs are getting the problem on processes where I am not.
The problem manifests itself as an error when I try to do the COMMIT TRANSACTION and I get the following conditions in SQLCA:
sqlCode = -1
sqlDbCode = 3902
sqlErrText = SQLSTATE = 25000
Microsoft OLE DB Provider for SQL Server
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
The above errors was given by the code:
EXECUTE IMMEDIATE "BEGIN TRANSACTION";
UPDATE parameters SET last_surety = last_surety + 1;
SELECT last_surety INTO :ll_ret FROM parameters;
EXECUTE IMMEDIATE "COMMIT TRANSACTION";
f_DispSqlErrors(ls_func_name + "1")
NB the function f_DispSqlErrors() displays and formats the error message after testing sqlCode.
BTW the above is not the actual code used, the code used is a series of methods in an extension of the transaction class, but essentially the above code is what it does with a very simple transaction as the one above. And it also gives exactly the same error message.
I am confident that my code is OK because it worked in PB7. Btw in PB7 I was using the direct connection interface to SQL Server (only available in Enterprise version), in PB10 you are now forced to use OLE DB. So the connection software has completely changed.
The following Stored Procedure works OK:
CREATE PROCEDURE GetNextSurety AS
SET NOCOUNT OFF;
BEGIN TRANSACTION A
UPDATE parameters
SET last_surety = last_surety + 1
SELECT last_surety
FROM parameters
COMMIT TRANSACTION A
GO
which you can see is very similar to the code that fails.
So why is it that if PB is allowed to run the statements above it either:
Closes the transaction, or
Somehow never starts the transaction.
I can here what you are thinking:
Well if it works OK in a stored procedure then write all of the code fragments that use Transaction processing in stored procedures.
Yup this can be done and I am pretty sure it will solve the problem but some of the updates involved are very complex and might involve updates on as many 10 tables. This would lead to a series of very large and complex stored procedures and would take ages to write. I am basically trying to avoid this for one thing I am not 100% sure that it will work. Not only that testing a routine that works on my machine but not someone elses is a nightmare.
The MS trace program shows that when PB is issuing the staments a whole of trace and log statements are issued - but why should these cause a problem?
Bill
I have an application that was originally written under PB7. I have now upgraded to PB10, which is infinitely more stable in the development environment - eg I can now actually use the debugger with confidence.
However, I have a really serious issue - transaction processing in SQL Server.
The transaction processing is carried out with the familiar statements, such as:
BEGIN TRANSACTION A
select ....
update ...
etc
COMMIT TRANSACTION A
The problem is very interment, and is different on different PCs but when you get it then it stays put and will always do it. But other PCs are getting the problem on processes where I am not.
The problem manifests itself as an error when I try to do the COMMIT TRANSACTION and I get the following conditions in SQLCA:
sqlCode = -1
sqlDbCode = 3902
sqlErrText = SQLSTATE = 25000
Microsoft OLE DB Provider for SQL Server
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
The above errors was given by the code:
EXECUTE IMMEDIATE "BEGIN TRANSACTION";
UPDATE parameters SET last_surety = last_surety + 1;
SELECT last_surety INTO :ll_ret FROM parameters;
EXECUTE IMMEDIATE "COMMIT TRANSACTION";
f_DispSqlErrors(ls_func_name + "1")
NB the function f_DispSqlErrors() displays and formats the error message after testing sqlCode.
BTW the above is not the actual code used, the code used is a series of methods in an extension of the transaction class, but essentially the above code is what it does with a very simple transaction as the one above. And it also gives exactly the same error message.
I am confident that my code is OK because it worked in PB7. Btw in PB7 I was using the direct connection interface to SQL Server (only available in Enterprise version), in PB10 you are now forced to use OLE DB. So the connection software has completely changed.
The following Stored Procedure works OK:
CREATE PROCEDURE GetNextSurety AS
SET NOCOUNT OFF;
BEGIN TRANSACTION A
UPDATE parameters
SET last_surety = last_surety + 1
SELECT last_surety
FROM parameters
COMMIT TRANSACTION A
GO
which you can see is very similar to the code that fails.
So why is it that if PB is allowed to run the statements above it either:
Closes the transaction, or
Somehow never starts the transaction.
I can here what you are thinking:
Well if it works OK in a stored procedure then write all of the code fragments that use Transaction processing in stored procedures.
Yup this can be done and I am pretty sure it will solve the problem but some of the updates involved are very complex and might involve updates on as many 10 tables. This would lead to a series of very large and complex stored procedures and would take ages to write. I am basically trying to avoid this for one thing I am not 100% sure that it will work. Not only that testing a routine that works on my machine but not someone elses is a nightmare.
The MS trace program shows that when PB is issuing the staments a whole of trace and log statements are issued - but why should these cause a problem?
Bill
