Some PragmaSQL users were notifying me about a strange but deadly serious error caused after recovering from a server connection transport-level error. To reproduce the error follow these steps:

1- Open PragmaSQL Editor and choose a connection from Saved Connections list to connect. I will assume that Default Database of the selected connection is DatabaseA 

2- In the opened script editor change database to DatabaseB

3- Open to Microsoft Sql Server Management Studio and from Management node select Activity Monitor. Find the process for DatabaseB created by PragmaSQL application and kill that process.

4- Return to PragmaSQL editor and try to execute the select statement for the selected database, that is DatabaseB.

5- You will get transport-level error after trying to execute the statement for the first time.

6- Try to execute the statement again and this time you will succeed. But the statement was executed on the initial database, that is DatabaseA not in DatabaseB (selected one ). This error may cause very serious problems if you were trying to modify data you would simply modify the data in wrong database.

The problem is actually with database change code inside PragmaSQL which is not really a bug but misusage of SqlConnection class's ChangeDatabase method. While I was coding the change database logic I did not realy realised that ChangeDatabase method does not modify the Initial Catalog property of the ConnectionString, actually there is no way to modify the ConnectionString property of the SqlConnection object once it is opened. SqlConnection object in the script editor is created and opened only after the user changes the selected server, database changes does not cause a new SqlConnection object to be created and opened, we simply change the database property of the existing SqlConnection object and that is it. I did not considered SqlConnection object's error recovery scnearios (transport-level error for example) at all. The fact is that SqlConnection class recovers from transport-level error very well but uses the ConnectionString property to reopen the connection, no matter what was the database before the error SqlConnection object always restores connection to Initial Catalog property specified in the ConnectionString.

Very ugly bug, but I am happy to resolve that one. Upcoming version will include solution for this issue.


July 22. 2009 03:05
Baume Mercier
Very interesting topic will bookmark your site to check if you write more about in the future.

Comments are closed