Asp > SQL

Stored Procedure Interface Generator

Stored Procedure Interface Generator

<%@ Language=VBScript %>
<% 
    Option Explicit
    Response.Expires = 0
    
    Dim m_strProcName
    Dim m_strConnectionString
    Dim m_blnRecordset
    
    m_strConnectionString = "Provider=SQLOLEDB; Data Source=phpor-beav09-sr; Initial Catalog=PHPWorldDev; User ID=sa; Password="
    m_strProcName = "pr_webcfg_mid_get_DBConnStr"
%>
<HTML>
<HEAD></HEAD>
<BODY>
<STYLE>
TD{
    FONT-SIZE: 8pt;
}   
</STYLE>
<%
If Request.Form.Count > 0 Then
    m_strProcName = Trim(Request.Form("ProcName"))
    m_strConnectionString = Trim(Request.Form("ConnectionString"))
    If Not Trim(Request.Form("ReturnRecordset")) = "" Then
        m_blnRecordset = True
    Else
        m_blnRecordset = False
    End If
    
    Call ShowCriteriaForm
Call ShowProcInfo
Else    
    Call ShowCriteriaForm
End If
Sub ShowCriteriaForm()
%>
<FORM METHOD="POST" name=frmGenerate>
<TABLE>
    <TR>
        <TD>Connection String</TD>
        <TD><Input Type="Text" name="ConnectionString" value="<%=m_strConnectionString%>" size="60"></TD>
    </TR>
    <TR>
        <TD>Procedure Name</TD>
        <TD><Input Type="Text" name="ProcName" value="<%=m_strProcName%>" size="50" value="z_TestInterrogation"></TD>
    </TR>
    <TR>
        <TD>Return A Recordset</TD>
        <TD><Input Type="checkbox" name="ReturnRecordset" value="True"></TD>
    </TR>       
    <TR>
        <TD align="center" colspan="2"><Input Type="submit" name="btnSubmit" value="Generate"></TD>
    </TR>
</TABLE>
</FORM>
<%
End Sub
Sub ShowProcInfo()
    'Declare and create the command object
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")

'Open the connection on the command by a
' ssigning the 
'connection string to the ActiveConnecti
' on property
cmd.ActiveConnection = m_strConnectionString
cmd.CommandType = 4 'Stored Procedure Command Type

'Set the CommandText to the proc name
cmd.CommandText = m_strProcName

'Call refresh to retrieve the values    
cmd.Parameters.Refresh
%>
<TABLE width="100%" border="1">
    <TR style="background-color:lightblue;">
        <TH colspan="6">Proc Name = <%=m_strProcName%></TH>
    </TR>
    <TR style="background-color:lightblue;">
        <TH>Parameter Name</TH>
        <TH>Direction</TH>
        <TH>Type</TH>
        <TH>Precision</TH>
        <TH>Size</TH>
        <TH>Value</TH>
    </TR>
<%
    Dim blnTR1
    Dim param
    
For Each param In cmd.Parameters
        If blnTR1 Then 
            Response.Write "<TR style=""background-color:silver;"">"
        Else
            Response.Write "<TR>"
        End If
        blnTR1 = Not blnTR1
        
        Response.Write "" & _
            "<TD align=""left""> " & param.Name & " </TD>" & _
            "<TD align=""center""> " & GetParameterDirectionEnum(param.Direction) & _
                " (" & param.Direction & ") </TD>" & _
            "<TD align=""center""> " & GetDataTypeEnum(param.Type) & _
                " (" & param.Type & ") </TD>" & _
            "<TD align=""center""> " & param.Precision & " </TD>" & _
            "<TD align=""center""> " & param.Size & " </TD>" & _
            "<TD align=""center""> " & param.Value & " </TD>" & _
        "</TR>"
Next
%>
    <TR>
        <TD colspan="6">
            <TEXTAREA style="width:100%;height:100%" id=textarea1 name=textarea1>
<%
    Dim blnFirstParameter   'Is this is the first parameter
    Dim strDeclaration      'Function declaration
    Dim strCommandParameters    'Parameters For the command
    Dim strOutputParameters 'Retrieving of output parameters
    Dim strPrecisionParameters  'Setting of precision For Decimal and Numeric
    Dim strTempParamVarName 'The variable name For the parameter
    
    'Default the function name To the proc name
    strDeclaration = "Function " & m_strProcName & "("
    
    blnFirstParameter = True
    
    If m_blnRecordset = True Then
        strDeclaration = strDeclaration & "rst"
        blnFirstParameter = False
    End If  
    
    For Each param In cmd.Parameters        
        If Left(param.Name,1) = "@" Then
            strTempParamVarName = Mid(param.Name,2)
        Else
            strTempParamVarName = param.name
        End If
        If Not param.Direction = 4 Then     
        
            If Not blnFirstParameter = True Then 
                strDeclaration = strDeclaration & ", "
            Else
                blnFirstParameter = False
            End If
            
            strDeclaration = strDeclaration & strTempParamVarName
            
            If param.Direction = 3 Then 
                strOutputParameters = strOutputParameters & vbTab & strTempParamVarName & _
                        " = cmd.Parameters(""" & param.name & """).Value" & vbCrLf
            End If
        End If
        
        strCommandParameters = strCommandParameters & _
                    vbTab & "cmd.Parameters.Append cmd.CreateParameter(""" & param.Name _
                    & """, " & GetDataTypeEnum(param.Type) & ", " & GetParameterDirectionEnum(param.Direction) & _
                    ", " & param.Size & ", " & strTempParamVarName & ")" & vbCrLf
        
        If param.type = 14 Or param.type = 131 Then
            strPrecisionParameters = strPrecisionParameters & "cmd.Parameters(""" & _
                param.name & """).Precision = " & param.Precision & vbCrLf
        End If
        
    Next    
    
    strDeclaration = strDeclaration & ")"
    Response.Write strDeclaration & vbCrLf & vbCrLf
    Response.Write vbTab & "Dim cmd '- Command Object" & vbCrLf
    Response.Write vbTab & "Dim RETURN_VALUE'- Return Value" & vbCrLf
    Response.Write vbCrLf   
    
    Response.Write vbTab & "RETURN_VALUE = Null" & vbCrLf
    Response.Write vbTab & "Set cmd= Server.CreateObject(""ADODB.Command"")" & vbCrLf
    
    If m_blnRecordset = True Then
        Response.Write vbTab & "Set rst= Server.CreateObject(""ADODB.Recordset"")" & vbCrLf
    End If  
    
    Response.Write vbTab & "cmd.ActiveConnection = """ & m_strConnectionString & """" & vbCrLf
    Response.Write vbTab & "cmd.CommandType= 4'- Stored Procedure" & vbCrLf
    Response.Write vbTab & "cmd.CommandText= """ & m_strProcName & """" & vbCrLf & vbCrLf
    Response.Write strCommandParameters
    
    Response.Write vbCrLf & strPrecisionParameters & vbCrLf & vbCrLf
    
    If m_blnRecordset = True Then
        Response.Write vbTab & "rst.CursorLocation = 3    'adUseClient" & vbCrLf
        Response.Write vbTab & "rst.Open cmd, , 3, 1    'adOpenStatic, adLockReadOnly" & vbCrLf & vbCrLf
        Response.Write vbTab & "Set rst.ActiveConnection = Nothing" & vbCrLf
    Else
        Response.Write vbTab & "cmd.Execute" & vbCrLf & vbCrLf
    End If
    
    Response.Write strOutputParameters
    Response.Write vbTab & m_strProcName & " = cmd.Parameters(""RETURN_VALUE"").Value" & _
                                        vbCrLf & vbCrLf
    
    Response.Write vbTab & "Set cmd = Nothing" & vbCrLf & vbCrLf
    Response.Write "End Function" & vbCrLf
%>