Stored Procedure Interface Generator

Stored Procedure Interface Generator

--------------------------------------------------------------------------------

Description : Generate the ASP code to interface with a stored procedure.

<%@ 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
%>