Debugging BlackfishSQL Stored Procedures

I’ve been spending a bit of time with BlackfishSQL over the last couple of days, and in particular have been developing stored procedures for it. Initially I had a couple of problems when attempting to debug these stored procedures in the IDE. I thought I’d blog about them here, along with how I got around them, in the hopes that others may find my trials and tribulations helpful.

The first problem I encountered was that the assembly containing the stored procedures would be locked by the BlackfishSQL server process, so when I tried to compile it after making changes, the IDE couldn’t create the dll due to file locking issues. To solve this, I use John Moshakis’ excellent custom MSBuild task to allow me to easily stop the BllackfishSQL server, deploy the dll, and restart it again, all by invoking a single command from the Project Manager.

The second problem I faced was that attempts to use the integrated Data Explorer to debug my stored procedures would result in the IDE locking. This is because you get a deadlock as the integrated Data Explorer is waiting for a response from the server that’s stopped on a breakpoint on the IDE. The solution to this problem is to use the stand-alone version of Data Explorer (DataExplore.exe), which should reside in the RAD Studio bin directory.

The actual invocation of the stored procedure can either be achieved by using a SQL window to issue a ‘call MyProc()’ statement, or by using the ‘View Parameters’ option available when right-clicking on the relevant procedure node sub-node located under the ‘Procedures’ node for the database connection in Data Explorer. From there, you can specify your input parameters, and use the ‘Execute’ button to execute the stored procedure. Any results returned will be shown in a grid at the bottom of the tab page.

Share This | Email this page to a friend

Posted by David Clegg on October 9th, 2007 under .NET, Database, Delphi |