Keep Your Stored Procedures Looking Good

Introduction

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 careful 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.

ODBC

In ODBC you do this with:

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.