Saving SQL stored procedure call

[dlscott56]

I'm executing a stored procedure on a Microsoft SQL Server with something like the following code in VB6. For error trapping I test the connection prior to this to verify that it's open. If I receive any errors or if the connection is closed I have to write the data to a file to be transferred later when the connection problem is corrected. I was wondering if I could collect the data in the cmdOverride in a way that can be just the SQL command string. Then, later, read through a file that has a list of these commands and execute them without having to rebuild the stored procedure with it's text and append all the parameters again. The file could contain command strings from other stored procedures. For example, save "call dbo.sproc_cmf_override_insert1('parameter data here')", and later loop through the list of commands.

Not sure if this is making sense?

    

On Error GoTo localErrorHandler

if g_SQL_Con.State = 1 then             
    cmdOverride.ActiveConnection = g_SQL_Con
    cmdOverride.CommandType = adCmdStoredProc
    cmdOverride.CommandText = "dbo.sproc_cmf_override_insert1"
    cmdOverride.Parameters.Append cmdOverride.CreateParameter("empId", adVarChar, adParamInput, 8, g_strEmployeeNumber)
    cmdOverride.Parameters.Append cmdOverride.CreateParameter("line", adVarChar, adParamInput, 6, g_strLineNumber)
    cmdOverride.Parameters.Append cmdOverride.CreateParameter("datetime", adVarChar, adParamInput, 19, _
                    Format(Now, "MM/DD/YYYY") & " " & Format(Now, "HH:MM:SS"))
    cmdOverride.Parameters.Append cmdOverride.CreateParameter("level", adVarChar, adParamInput, 8, g_lngAuthLevel)
    cmdOverride.Parameters.Append cmdOverride.CreateParameter("text", adVarChar, adParamInput, 256, strSend)
    cmdOverride.Parameters.Append cmdOverride.CreateParameter("so", adVarChar, adParamInput, 12, "123456000001")
    cmdOverride.Parameters.Append cmdOverride.CreateParameter("steppk", adVarChar, adParamInput, 8, "9083")
    cmdOverride.Parameters.Append cmdOverride.CreateParameter("step", adVarChar, adParamInput, 8, "9083")

    Set rsOverride = cmdOverride.Execute
Else
    'write command to local file
End If

Exit Sub

localErrorHandler:

'report message and write command to local file

[topshot]

Seems feasable as long as you don't have any security concerns. Technically, anyone would then be able to edit your file with whatever commands they wished. If it is a concern then I would save what you need to call the same routine that calls the stored procedure along with setting the variables needed as parameters. You'd have to refactor to handle the Now function.

[dlscott56]

I think I've just resolved the issue with some testing. Instead of setting the command type to adCmdStoredProc I will do something like the following:

Dim commandString as string

On Error GoTo localErrorHandler

if g_SQL_Con.State = 1 then             
    cmdOverride.ActiveConnection = g_SQL_Con
    commandString = "{ call dbo.sproc_cmf_override_insert1(" & _
        Employee_Number & "," & _
        Line_Number & "," & _
        Date_Time & "," & _
        Auth_Level & "," & _
        Text_To_Send & "," & _
        Shop_Order_Number & "," & _
        PK_Step_Number & "," & _
        Step_Number & ") }"

    cmdOverride.CommandText = commandString
    Set rsOverride = cmdOverride.Execute
Else
    'write command to local file
End If

Exit Sub

localErrorHandler:

'report message and write command to local file

Then write the variable commandString to a file for later processing if required. That way I won't have to decipher which sproc I'm calling from the routine that checks this file for data. Just open it and loop through each line, set the command string and execute. If security is an issue I can encrypt the data.

At least I think that will work ok.