SQL Miscellaneous

SQL & databases miscellaneous

Evaluate/debug sql-statement bind variables

When your sql statement contains many bind variables it can be difficult to debug which values these bind variables have when executed.
While in debugmode you will have to evaluate every bind variable manually.

The following custom function is able to evaluate the supplied Sql statement and show all current values
for each bind variable.

  Set sSqlEvaluation = PALEvaluateSqlStatement( sSqlStatement, SalContextCurrent( ) )

You can for instance use this to offer a runtime sql debugging feature.
When you enable some kind of debugmode at runtime (eg using a global boolean to indicate to debug sql's)
call this function and show the result in a message box or in a custom window.
This will give quite nice information what data is used in the sql statements just before the actual statement is executed.

A sample how to use the function and how the evaluated statement looks like:

At the left the actual sql statement is listed. At the right this statement is evaluated.
In place of the bind variable names the current values are shown.

Here you can download the sample:
DebugSqlStatementVariables.zip

Validating Sql Handles

^{(From CenturaPro April 1999)}

The SqlConnect function takes one parameter, a Receive Sql Handle.
If a call to SqlConnect is successful, it returns TRUE, and the specified Sql Handle variable passed
as a parameter contains a valid, connected Sql Handle that can be used with other Sql* functions for database programming.

When you call SqlDisconnect, the sole parameter is a passed-by-value Sql Handle, specifying the Sql Handle to
disconnect from a database. However, there’s no indication to the Sql Handle variable that the connection is severed.

In more sophisticated applications, where you might reuse Sql Handle variables, there are a couple of strategies
you can use to prevent getting tripped up on this issue.
The first strategy is to “clear out” the Sql Handle variable after the being disconnected. For example:

   If SqlDisconnect( hSql )
      Set hSql = hWndNULL

Assigning hWndNULL (a system defined, Window Handle type) to a Sql Handle may seem a bit peculiar, but
it works because in SAL all handle types are compatible.
The downside of this approach is that it requires some discipline on the part of the programmer to remember to
code this extra step every time a Sql Handle is disconnected.

When validating a Sql Handle using this approach, one simply compares the Sql Handle with hWndNULL to
ensure that it’s a valid, connected Sql Handle.

The second approach is to validate the Sql Handle by examining the SqlGetCursor value.
For example:

   Constants
      Number: BAD_CURSOR = 65535

   If SqlGetCursor( p_hSql ) = BAD_CURSOR
      ! Incorrect cursor

This second approach doesn’t require any additional work on the disconnect of a Sql Handle, but it does rely on
using an undocumented function., SqlGetCursor. Pick whichever works for you.