Solved: Error when calling stored procedure with out parameter in ASP classic

Error when calling stored procedure with out parameter in ASP classic

3 Comments

1 Solution

132 Views

Last Modified: 2020-03-19

I'm trying to call a MySQL stored procedure and read its out parameter using ASP classic. Unfortunately I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[MySQL][ODBC 3.51 Driver][mysqld-5.5.5-10.3.17-MariaDB]OUT or INOUT argument 2 for routine baseline.Proc_Test is not a variable or NEW pseudo-variable in BEFORE trigger
/functions/command_procedure.asp, line 54

The stored procedure is defined here:

DELIMITER //
CREATE PROCEDURE Proc_Test (IN  Lang_param  VARCHAR(10),
                            OUT ID_param    int)
BEGIN
   SET ID_param = 99;
END //
DELIMITER ;

CALL Proc_Test ('eS', @myID);
SELECT @myID;

Open in new window

Here is the ASP classic script calling the stored procedure:

<%@ Language=VBScript %>
<%
    Option Explicit
    Response.Buffer = True
    Dim Hostname, Username, Password, Database
    Hostname = "localhost"
    Username = "baseline-user"
    Password = "xxxxxx"
    Database = "baseline"
    
%>
<html>

<head>
<title>Baseline Connection</title>
<style type="text/css">body {font: normal 11px Arial}</style>
</head>

<body>

<%
   Const adCmdStoredProc = 4
   Const adParamInput = 1
   Const adParamOutput = 2
   Const adInteger = 3
   Const adVarChar = 200

   Dim Command
   Dim Connection
   Dim ConnectionString

   Dim myLang
   Dim myID

   Set Connection = Server.CreateObject("ADODB.Connection")
   ConnectionString = _
     "DRIVER={MySQL ODBC 3.51 Driver};" & _
     "SERVER=" & Hostname & ";" & _
     "Database=" & Database & ";" & _
     "UID=" & Username & ";" & _
     "PASSWORD=" & Password & ";" & _
     "OPTION=3;"
   Connection.Open ConnectionString

   Set Command = Server.CreateObject("ADODB.Command")
   Set Command.ActiveConnection = Connection

   Command.CommandType = adCmdStoredProc
   Command.CommandText = "Proc_Test"
   myLang = "en"
   Command.parameters.Append Command.CreateParameter("@Lang_param", adVarChar, adParamInput, 255,  myLang)
   Command.parameters.Append Command.CreateParameter("@ID_param", adInteger, adParamOutput, 4, myID )

   Command.Execute()
   Connection.Close()

   myID = Command.Parameters("@ID_param")

   Response.Write(myID)

%>
</body>
</html>

Open in new window

Error when calling stored procedure with out parameter in ASP classic

Premium Content

Premium Content

Why Experts Exchange?

Ask ANY Question

Did You Know?