Stored Procedures in ODBC – easier maintainability

Introduction

When creating stored procedures using ODBC all indentation may get lost which makes it harder to read the procedural code subsequently when retrieving it.

The case

Depending on user input, the customer creates stored procedures. The application looks something like this:

 sqls = "Create procedure search123 () " & _ 
" begin" & _ 
... 
... 
" end"
 retcode = SQLExecDirect(hstmt, (SQLCHAR *) sqls, SQL_NTS);

This works fine, but if you look at the stored procedure definition with:

 SELECT object_name, object_type, source_definition
 FROM INFORMATION_SCHEMA.EXT_SOURCE_DEFINITION;

You find that you get…

Create procedure search123 () begin ... ... end

…where any carefully placed indentation is lost.

This is because the ODBC driver scans the SQL statement for ODBC escape sequences. While doing this it will “normalize” the SQL text in such way that multiple white space is replaced by one space. (String literals are, of course, not changed.)

In order to prevent this, you should tell the driver not to perform any scanning, in ODBC you do this with the following statement:

SQLSetConnectOption(hdbc, SQL_NOSCAN, SQL_NOSCAN_ON)

If you are writing an ODBC application you can put in a call as above before creating your routines.