Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Keep Your Stored Procedures Looking Good
Categories: Programming Examples, SQL
Introduction

Recently a customer told us about a problem. Depending on user input, the customer creates stored procedures. The application looks something like this:
    Set Gconn = New ADODB.Connection
Gconn.ConnectionString = "User ID=" & uidf & ";dsn=" & dsnf & ";pwd=" & pwdf
Gconn.Open
Set cmd = New ADODB.Command
sqls = "Create procedure search123 () " & _
" begin" & _
...
...

" end"
cmd.CommandText =
cmd.CommandType = adCmdText
cmd.ActiveConnection = Gconn
cmd.Execute()
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
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.

ADO
If you are using Mimer SQL from ADO You are using the old Microsoft ODBC bridge. There is no way of getting hold of the connection handle that ADO uses.

But, we have written a small VB subroutine that can be called with 5 parameters:
dsn  datasourcename
usr username
pwd password
sql SQLSTATEMENT
RET a returncode indicating sucess/failure
If you use the no-scan module you can do something like:
    Set Gconn = New ADODB.Connection
Gconn.ConnectionString = "User ID=" & uidf & ";dsn=" & dsnf & ";pwd=" & pwdf
Gconn.Open
Set cmd = New ADODB.Command
sqls = "Create procedure search123 () " & _
" begin" & _
...
...

" end"
compilenoscan dsnf, uidf, pwdf, sqls, r
And you will get your procedure defined with proper indentation!

Last updated: 2002-09-11

 

Powered by Mimer SQL

Powered by Mimer SQL