TD-17641: SQLServer Update cmd. now requires SqlCommit

[UVS]

I'm connecting to a SQL Server 2008 database, with OLEDB. The attached file is in 5.2 format, which works with out needing to do a SqlCommit( hSqlHandle).

When running code in 4.0 and 5.2 everything works fine. Porting over to 6.0 SP5 all Update code stops working. No errors coming across to say it isn't working. The following code works in 4.0 and 5.2, but not in 6.0.

Code: Select all

!!CB!! 174
Call SqlConnect( hSqlCloseRequest)
Set strSelect = 'Update Test  set RQST_STATUS_FLAG = \'C\'
       where REQUEST_NMBR = ' || SalNumberToStrX( df1, 0)
Call SqlPrepareAndExecute( hSqlCloseRequest, strSelect )
Call SqlDisconnect( hSqlCloseRequest )

It has taken me two days to narrow down where the problem is. I had thought at first the problem was Unicode or that the database was set to SQL Server 2000 compatible mode. But I've found that isn't the problem.

In the attached file the Application Description has the SQL DDL code to create the table and insert one record. Under SAM_AppStartup you will need to make some minor changes to fit your environment.

The program has two buttons, the "No Commit" button runs the code without using SqlCommit and works when ran under 5.2. The "Commit" button uses SqlCommit and works in 6.0.

Was this a fix from previous versions, or is this a bug? It is going to be a most lovely time to go through all my code in all my programs and add a line after every update (and I suspect inserts too, but haven't tested that yet), just to bring everything up to 6.0 code.

[Jeff Luther]

I'm going to investigate this further, but what it looks like is that this call:
Call SqlDisconnect( hSqlCloseRequest )
is now implicitly causing a ROLLBACK. Earlier an implicit COMMIT was done. I'll reply back when I know more.

Just curious: have you checked your code behavior when connecting to MS SS via ODBC?
JEFF: No need to. Mod. test case attached next msg. prompts for ODBC conn, and with that the "No Commit" PB code works OK.

BTW, I am not fan of something changing 'magically' like this either, and I will look at this. However, my 2 cents is that relying on a router feature or the DB to do (auto) COMMITs for your DML changes can be dangerous. Is the coding style of your application to connect - make a change -- disconnect every time the code does an INSERT/UPDATE/DELETE? If so, in general that's a lot of work for the DB and router (read: a Performance 'hit') and the usual practice is to:
First connect hSqlDML at the beginning, and likely a hSqlSELECT too
do a change
commit
more dml later/commit, etc.
Then disconnect when appl. ends (unless there's an extenuating circumstance for disconnecting earlier. Maybe to switch DBs mid appl. running, etc.)

[Jeff Luther]

Update: Yes, mystery unraveled. You found a defect, all right. I have added TD/router defect TD-17641 for this issue.
Defect title:

TD with OLEDB connection to MS SS: SqlDisconnect() now causes an implicit ROLLBACK on disconnect. It was implicit COMMIT.

DETAILS:
Reason? A defect fix in v6 SP4 changed this: with an OLEDB connection an implicit COMMIT used to be done on SqlDisconnect. (That is what your code is relying on.) Change in SP4 fix changed this -- likely inadvertantly -- and an implicit ROLLBACK is now done on SqlDisconnect.

TEST CASE ATTACHED: I mod'd your test to have CREATE/DROP calls in Create/Destroy of the form, and added some code to test ODBC as well as have Error/Ok msg. boxes. This helps dev. have a self-contained test... You will need to define DB/User/PW values for ODBC connection, if you test that option.

WORKAROUND: Easiest until this is fixed is to call:
Call SqlSetParameter ( hSqlCloseRequest, DBP_AUTOCOMMIT, TRUE, '' )

after your SqlConnect call. I just tested this in your "No Commit" button code by adding it as shown (code taken from my attached mod. to your test):
If NOT SqlConnect( hSqlCloseRequest)
!
Call SqlSetParameter ( hSqlCloseRequest, DBP_AUTOCOMMIT, TRUE, '' )
!
Set sUpdatedRQST_STATUS_FLAG = 'C'
... rest of code in mod. test case

With that call in the "No Commit" code now does update the column to a 'C' and fetches back OK. Of course, if your model is that connect/change/disconnect, then adding SqlSetParameter is no easier than adding SqlCommit before the SqlDisconnect I guess.

[UVS]

I'm going to investigate this further, but what it looks like is that this call:
Call SqlDisconnect( hSqlCloseRequest )
is now implicitly causing a ROLLBACK. Earlier an implicit COMMIT was done. I'll reply back when I know more.

Thank you.

Just curious: have you checked your code behavior when connecting to MS SS via ODBC?
JEFF: No need to. Mod. test case attached next msg. prompts for ODBC conn, and with that the "No Commit" PB code works OK.

No, I stay away from ODBC ever since I could use OLEDB. It was always a pain to make sure the OS had the correct ODBC's setup. We use an INI file for a multitude of settings, the DB server and DB names are listed in that. To move the app to another server all we need to do is change the INI file.

However, my 2 cents is that relying on a router feature or the DB to do (auto) COMMITs for your DML changes can be dangerous. Is the coding style of your application to connect - make a change -- disconnect every time the code does an INSERT/UPDATE/DELETE?

Not everywhere, but this one instance it was. All apps make a connection right at the startup, using that handle for selects. Changes are usually on a separate SQL Handle, which is disconnected after running.

When you say performance hits are you meaning on the users computer? Everything seems to be very fast this way, not sure I'd notice much faster. On the server side there is usually only a few folks in the systems at one time, at the most would be twenty hitting the database at once. With dual Intel E5649 Processors (total 12 cores) and 12 gigs of memory, twenty users don't put much strain on the them. But I understand what you are saying, guess I should change 16 years of coding style.

WORKAROUND: Easiest until this is fixed is to call:
Call SqlSetParameter ( hSqlCloseRequest, DBP_AUTOCOMMIT, TRUE, '' )
With that call in the "No Commit" code now does update the column to a 'C' and fetches back OK. Of course, if your model is that connect/change/disconnect, then adding SqlSetParameter is no easier than adding SqlCommit before the SqlDisconnect I guess.

Sadly the most lovely time will begin. :/ But thank you very much for the fast response and finding that is a bug.

MartinD

[Jeff Luther]

When you say performance hits are you meaning on the users computer?

I mean the TD/router and (maybe especially) the DB. Each conn/disconn takes some work by the router and DB, that's the performance hit. I guess this is the 'computer' -- the machine you are running + wherever the DB server is. My point may be moot since you say you usually connect at start.

My comment was based on my theory' that your code every INS/UPD/DEL call was wrapped in a conn/disconn pair of calls.