Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 9, 2000

Utility to Build VB Class to Match SQL Stored Procedures - Page 3

By Danny Lesandrini


Function: CreateCodeFromStoredProcs()

It's been my experience that it quickly becomes very confusing when writing code that generates other code. The full text of the code generation function is listed below and comments are interspersed to help the reader understand what's happening. Some of the key lines of code will be described in more detail below.

First, alter creating a StoredProcedure object from SQL DMO, you can loop through all procs by using a For Each loop with the Database object we created earlier. The For Each loop ensures that we will examine each stored proc of the database exactly once.

    Dim oSP As SQLDMO.StoredProcedure
    For Each oSP In oCurDB.StoredProcedures
       ' Do stuff

    Next

Next, the name of the Stored Proc is put into a variable. This variable is then examined to see if the current proc should or shouldn't be processed. Remember that the user may select one specific proc to process, or may filter out all System Stored Procs (those prefixed with "dt_" and "sp_").

If the current procedure should be processed, then it's necessary to query the StoredProcedure object to determine the names and types of Input/Output Parameters, if any exist. This is done by setting an SQLDMO QueryResults object to the return value of the EnumParameters method.

    Dim qryResults As SQLDMO.QueryResults
    Set qryResults = oSP.EnumParameters()

Now we have a SQLDMO QueryResults recordset populated with all the information we need to enumerate the parameters of the Stored Proc. If we printed the results of the recordset, it would look like this:
	name        name         length  colid   output
	---------   ---------    -----   -----   ----- 
	@TableName  varchar        75      1       0     
	@HowMany    int             4      2       0     
	@NextCount  int             4      3       1     

From this table, we can determine the Name of each parameter, its Data Type, Length and we can determine if it's an Input or Output variable. With that information in hand, we can create our VB Class method, which will ultimately take this form:
Public Function Exec_ems_GetNextCounters(ByVal strConnect As String,  _ 
        ByVal strTableName As String, ByVal lngHowMany As Long,  _ 
        ByRef lngNextCounter As Long ) As ADODB.Recordset


You will notice that I named the Function with the prefix "Exec_" and the name of the proc. This is my personal naming system. The code below can be edited to match your naming scheme. Variables are always passed ByVal and the first argument, strConnect, is always required (it's needed for the ADO Connection Object). You will notice that the remaining arguments match the parameters of the stored proc with one small difference. All VB arguments have a 3-letter prefix to indicate what data type is represented by the variable. This was done to avoid any potential problems in the VB Class where arguments are named with VB Keywords. (Since SQL Server prefixes parameters with "@" character, it is possible for users to create parameters that will cause a Keyword conflict in Visual Basic).

As we loop through each parameter and extract it's name into our local variable, we are building the various pieces of our output code. When we are all done processing parameters, the end result will look something like this:


    cnn.Open strConnect
    Set com.ActiveConnection = cnn

    With com

        .CommandText = "ems_GetNextCounters"
        .CommandType = adCmdStoredProc

        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, , 0)
        .Parameters.Append .CreateParameter("@TableName", adVarChar, adParamInput, 75, strTableName)
        .Parameters.Append .CreateParameter("@HowMany", adInteger, adParamInput, 8, lngHowMany)
        .Parameters.Append .CreateParameter("@NextCounter", adInteger, adParamOutput, 8, lngNextCounter)

        Set rsLocal = .Execute(lngRecordsAffected)
        
        ' In order to return OUTPUT params, the recordset must be closed
        ' So, first save rsLocal to an XML File to reload later
        rsLocal.Save "Temp.xml", adPersistXML
        Set rsLocal = Nothing
        
        lngNextCounter = .Parameters("@NextCounter")

        Set Exec_ems_GetNextCounters = New ADODB.Recordset
        Exec_ems_GetNextCounters.Open "Temp.xml"
        Kill "Temp.xml"

        strSQL7Error = Err.Description

    End With

    Set com.ActiveConnection = Nothing
    Set cnn = Nothing


The function opens an ADO Connection and creates an ADO Command object. The RETURN_VALUE parameter is always included and maps to the SQL Server RETURN value (surprise!). Each of the other 3 parameters are appended to the ADO Command object, along with their direction (INPUT or OUTPUT). Next, the Command object is executed. The @@ROWCOUNT (or number of rows affected) is placed in the lngRecordsAffected variable and any error is placed in the strSQL7Error variable. These values are exposed as properties of the class.

Because of a problem with the ADO Command object, it is necessary to close the resultant recordset object prior to attempting to access a value for OUTPUT parameters. Accordingly, the recordset is persisted in a temporary XML file while the recordset is closed and the OUTPUT parameters populated. Once that operation is finished, the recordset is reloaded as the Return object of the function. (This doesn't apply if the user elected to return a Long variable from the function.)


Conclusion

As you can imagine, there are many details to the formation of the above code. As time and space do not permit me to give a detailed explanation of each line of code, I leave it to the reader to pursue the details to the extent you desire. The sample code will work for you so long as you have the SQL DMO and SQL NS libraries installed on your computer. According to Microsoft, this can be done by registering the redistributable libraries, but I've never been able to get that to work. The safest approach is to simply install SQL Server tools on your development machine. The odds are that if you are developing an application requiring SQL Server Stored Procs, you probably already have the tools installed and these libraries properly registered.

Utilities like this can be a great time saver. I thoroughly expect that some readers will immediately see ways to improve upon the above design. As I stated before, I welcome suggestions and ideas that will make this utility even more powerful and useful.

View Output

Full Source Code for CreateCodeFromStoredProcs() Function

' ****************** BEGIN CODE HERE ******************
Public Function CreateCodeFromStoredProcs() As String
On Error Resume Next

Dim oSP As SQLDMO.StoredProcedure
Dim qryResults As SQLDMO.QueryResults

Dim sUserRequestedSP As String
Dim sProcName As String
Dim fContinue As Boolean
Dim sPrefix As String

Dim sGenDeclare As String
Dim sInstrForUse As String

Dim sOpenADO As String
Dim sCloseADO As String
Dim sComment As String
Dim sDeclare As String
Dim sCreateRecordset As String
Dim sVBArguments As String
Dim sByValRef As String
Dim sAssignOUTPUTParam As String
Dim sAppendParams As String
Dim sFnReturnType As String
Dim sExecute As String
Dim sCodeText As String
Dim sReturn As String

Dim i As Integer
Const q = """"
Dim strMsg As String
Dim iLastLineContinuation As Integer

Dim sSQLParamName As String
Dim sVBVariableName As String

Dim sSQLParamType As String
Dim sVBVariableType As String
Dim sADOParamType As String

Dim sSQLParamSize As String
Dim sVBVariableSize As String
Dim sADOParamSize As String

Dim sSQLOutput As String
Dim sADOOutput As String
    
    ' Reset variables
    sComment = ""
    iProcsProcessed = 0
    fFoundProc = False
    
    sInstrForUse = "' ----- BEGIN ASP/ADO CODE USE EXAMPLE FOR THIS CLASS-----" & vbCrLf _
                 & "'" & vbCrLf _
                 & "'  This code may be tweaked and pasted into your ASP Page to instantiate" & vbCrLf _
                 & "'  the class that calls your Stored Proc." & vbCrLf _
                 & "'  You will need to change the names of the DLL, Class and functions as " & vbCrLf _
                 & "'  well as the parameters for the connection string." & vbCrLf _
                 & "'" & vbCrLf _
                 & "'" & vbCrLf _
                 & "'    Dim objDataCls" & vbCrLf _
                 & "'    Dim rstReturn" & vbCrLf _
                 & "'    Dim sConnect" & vbCrLf _
                 & "'    Dim lRecCount" & vbCrLf _
                 & "'    Dim sError" & vbCrLf & vbCrLf _
                 & "'    Set objDataClass = Server.CreateObject(" & q & "YourDLL.YourClass" & q & ")" & vbCrLf _
                 & "'    Set rstReturn = Server.CreateObject(" & q & "ADODB.Recordset" & q & ")" & vbCrLf _
                 & "'    sConnect = " & q & "Provider=SQLOLEDB.1;User ID=sa;PWD=;Initial Catalog=YourDB;Data Source=YourServer" & q & vbCrLf _
                 & "'    Set rstReturn = objDataCls.Exec_ems_MyProc(sConnect, Param_1, ... Param_n)" & vbCrLf _
                 & "'" & vbCrLf _
                 & "'    lRecCount = objDataCls.RecordsAffected" & vbCrLf _
                 & "'    sError = objDataCls.SQLError" & vbCrLf _
                 & "'" & vbCrLf _
                 & "'    If Not rstReturn.BOF Then" & vbCrLf _
                 & "'        Do Until rstReturn.EOF ..." & vbCrLf _
                 & "'" & vbCrLf _
                 & "' ------------------- END CODE SAMPLE ------------------- " & vbCrLf & vbCrLf


    ' Set the variables that will determine what is returned

    ' by the function (based on user selection)
    If chkReturnRecordset = Checked Then
        sFnReturnType = "ADODB.Recordset"
        sCreateRecordset = vbCrLf & "Dim rsLocal As ADODB.Recordset"
    Else
        sFnReturnType = "Long"
        sCreateRecordset = ""
    End If

    
    ' Declare the RecordsAffected and SQL Error variables and create
    ' Property Get functions for them (They are Read-Only)
    sGenDeclare = "Option Explicit" & vbCrLf & vbCrLf & sInstrForUse _
                & "Private lngRecordsAffected As Long" & vbCrLf _
                & "Private strSQL7Error As String" & vbCrLf & vbCrLf _
                & "Property Get RecordsAffected() As String" & vbCrLf _
                & "    RecordsAffected = lngRecordsAffected" & vbCrLf _
                & "End Property" & vbCrLf & vbCrLf _
                & "Property Get SQLError() As String" & vbCrLf _
                & "    SQLError = strSQL7Error" & vbCrLf _
                & "End Property" & vbCrLf & vbCrLf

    ' Declare and Open ADO Connection and Command Objects

    sOpenADO = "On Error Resume Next" & vbCrLf & vbCrLf _
             & "Dim cnn As New ADODB.Connection" & vbCrLf _
             & "Dim com As New ADODB.Command" & sCreateRecordset & vbCrLf & vbCrLf _
             & "    cnn.Open strConnect" & vbCrLf _
             & "    Set com.ActiveConnection = cnn" & vbCrLf & vbCrLf _
             & "    With com" & vbCrLf & vbCrLf

    ' Close the ADO Connection and Command Objects

    sCloseADO = "        strSQL7Error = Err.Description" & vbCrLf & vbCrLf _
              & "    End With" & vbCrLf & vbCrLf _
              & "    Set com.ActiveConnection = Nothing" & vbCrLf _
              & "    Set cnn = Nothing" & vbCrLf & vbCrLf _
              & "End Function" & vbCrLf & vbCrLf
              
   
    ' Check to see if user has entered name of specific

    ' Stored Proc to process.  If so, save to variable
    If chkProcessOnlyOneSP = Checked Then
        sUserRequestedSP = txtStoredProcToProcess
    End If
    
    ' Loop through all Stored Procs, processing only
    ' those matching user defined filter.
    For Each oSP In oCurDB.StoredProcedures
        ' Reset flag   Assume Success
        fContinue = True
        
        ' Capture name of Current Stored Procedure
        sProcName = oSP.Name
        
        ' If the user has not entered the name of a specific
        ' Proc to process, AND has asked that System Procs be
        ' excluded, then test the Proc Name for Sys Prefix.

        If sUserRequestedSP = "" And chkFilter = Checked Then
            ' Capture the Proc 3-letter prefix
            sPrefix = Left(sProcName, 3)
            
            ' Toggle the Continue Flag OFF for Sys Prefix
            If sPrefix = "dt_" Or sPrefix = "sp_" Then fContinue = False
        End If
        
        ' Now toggle Continue Flag OFF is user selected specific proc ...
        If Len(sUserRequestedSP) Then
            ' ... but this one isn't it!
            If LCase(sUserRequestedSP) <> LCase(sProcName) Then fContinue = False
        End If
        
        If fContinue Then
            ' Set counter to indicate that number of procs
            ' processed.  (Will determine finish message.)
            fFoundProc = True
            iProcsProcessed = iProcsProcessed + 1
            lGetTickCountEnd = GetTickCount
            lTics = (lGetTickCountEnd - lGetTickCountStart) / 1000

            lblMsg.Caption = "Processing Proc Number " & iProcsProcessed & "  (Total of " & lTics & " seconds)"
            Me.Refresh
            
            sComment = "'//////////// Code for:  " & sProcName & " ////////////" & vbCrLf
            sComment = sComment & "'  Created on " & Date & vbCrLf
            sComment = sComment & "'  Created by Danny Lesandrini" & vbCrLf
            sComment = sComment & "'  Created for Dean Evans and Associates, Inc." & vbCrLf & "'" & vbCrLf
            sComment = sComment & "'  Parameters expected by this Stored Proc" & vbCrLf & "'  " & String(40, "-") & vbCrLf
            
            sDeclare = "Public Function Exec_" & sProcName & "(ByVal strConnect As String, "
                     
            sCodeText = "        .CommandText = " & q & sProcName & q & vbCrLf _
                      & "        .CommandType = adCmdStoredProc" & vbCrLf & vbCrLf _
                      & "        .Parameters.Append .CreateParameter(" & q & "RETURN_VALUE" & q & ", adInteger, adParamReturnValue, , 0)" & vbCrLf


            
            Set qryResults = oSP.EnumParameters()
            With qryResults
                For i = 1 To qryResults.Rows
                    ' Get Parameter and Variable Name

                    sSQLParamName = .GetColumnString(i, 1)
                    sSQLParamType = " " & .GetColumnString(i, 2)
                    sVBVariableName = GetVBPrefix(Trim(sSQLParamType)) & Mid(sSQLParamName, 2)
                    
                    ' Get Parameter and Variable (VB and ADO) Type
                    sVBVariableType = GetVBVariableType(sSQLParamType)
                    sADOParamType = GetADOParamType(sSQLParamType)
                    
                    ' Get Parameter and Variable (VB and ADO) Size
                    sSQLParamSize = ""
                    sVBVariableSize = .GetColumnString(i, 3)
                    If InStr(1, sSQLParamType, "char") Then sSQLParamSize = "(" & sVBVariableSize & ")"
                    sADOParamSize = GetADOParamSize(sSQLParamType, sVBVariableSize)
                    
                    ' Initialize Variables effected by INPUT/OUTPUT status
                    sSQLOutput = ""
                    sByValRef = "ByVal "
                    sADOOutput = ", adParamInput"
                    
                    ' Determine if Param Type is OUTPUT and reset variables accordingly

                    If .GetColumnString(i, 5) = 1 Then
                        sSQLOutput = " OUTPUT"
                        sADOOutput = ", adParamOutput"
                        sByValRef = "ByRef "
                        sAssignOUTPUTParam = sAssignOUTPUTParam & Space(8) & sVBVariableName & " = .Parameters(" & q & sSQLParamName & q & ")" & vbCrLf

                    End If
                    
                    sComment = sComment & "'    " & sSQLParamName & sSQLParamType & sSQLParamSize & sSQLOutput & vbCrLf
                    
                    ' To avoid the need to scroll right to read the entire Function Declaration

                    ' and Argument List, line continuations will be added ever 60 characters
                    iLastLineContinuation = LastInStr(sVBArguments, "_")
                    If Len(Mid(sVBArguments, iLastLineContinuation)) > 60 Then sVBArguments = sVBArguments & " _ " & vbCrLf & Space(8)
                    
                    sVBArguments = sVBArguments & sByValRef & sVBVariableName & sVBVariableType
                    sAppendParams = sAppendParams & "        .Parameters.Append .CreateParameter(" & q _
                                          & sSQLParamName & q & sADOParamType & sADOOutput _
                                          & sADOParamSize & sVBVariableName & ")" & vbCrLf
                Next
            End With
            
            sDeclare = sDeclare & sVBArguments
            sDeclare = Left(sDeclare, (Len(sDeclare) - 2)) & " ) As " & sFnReturnType & vbCrLf & vbCrLf
            

            ' There is a bug with ADO that requires a recordset be closed prior to
            ' attempting to read OUTPUT parameters.  Since it's not known if the SP
            ' contains a populated recordset, simply persist rsLocal as XML, read the
            ' OUTPUT params, and then reload the XML file into the Return Recordset
            
            If chkReturnRecordset = Checked Then
                sExecute = Space(8) & "Set rsLocal = .Execute(lngRecordsAffected)" & vbCrLf _
                         & Space(8) & "' In order to return OUTPUT params, the recordset must be closed" & vbCrLf _
                         & Space(8) & "' So, first save rsLocal to an XML File to reload later" & vbCrLf _
                         & Space(8) & "rsLocal.Save " & q & "Temp.xml" & q & ", adPersistXML" & vbCrLf _
                         & Space(8) & "Set rsLocal = Nothing" & vbCrLf & vbCrLf _
                         & Space(8) & "Set Exec_" & sProcName & " = New ADODB.Recordset" & vbCrLf & vbCrLf _
                         & Space(8) & "Exec_" & sProcName & ".Open " & q & "Temp.xml" & q & vbCrLf _
                         & Space(8) & "Kill " & q & "Temp.xml" & q & vbCrLf & vbCrLf
            Else
            ' If Return Type is not Recordset, then there are no problems accessing OUTPUT Params

                sExecute = "        .Execute(lngRecordsAffected)" & vbCrLf _
                         & "        Exec_" & sProcName & " = lngRecordsAffected" & vbCrLf
            End If
            
            ' Prepare strings for concatonation
            sCodeText = sCodeText & sAppendParams & vbCrLf & sExecute
            sComment = sComment & "'  " & String(40, "-") & vbCrLf & vbCrLf
            sAssignOUTPUTParam = sAssignOUTPUTParam & vbCrLf
            
            ' Return neatly formatted code text

            sReturn = sReturn & vbCrLf & sComment & sDeclare & sOpenADO & sCodeText & sAssignOUTPUTParam & sCloseADO
            
            ' Reset Variables
            sComment = ""
            sDeclare = ""
            sCodeText = ""
            sAppendParams = ""
            sExecute = ""
            sVBArguments = ""
            sAssignOUTPUTParam = vbCrLf
        End If
    Next
    
    Set oSP = Nothing
    
    Select Case iProcsProcessed
        Case 0
            strMsg = "Error.  No Procs Processed"
        Case 1
            strMsg = "Finished!  Procedure " & sUserRequestedSP & " was processed."
        Case Else
            strMsg = "Finished!  All " & iProcsProcessed & " have been processed."
    End Select
    
    lblMsg.Caption = strMsg
    Me.Refresh

    sReturn = sGenDeclare & sReturn
    CreateCodeFromStoredProcs = sReturn

End Function

' ****************** END CODE HERE ******************

Page 4: Utility Output for SP ems_GetNextCounters




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date