Link to home
Start Free TrialLog in
Avatar of mike99c
mike99c

asked on

Error when calling stored procedure with out parameter in ASP classic

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

ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mike99c
mike99c

ASKER

I am using MariaDB, v10.3.17
Avatar of mike99c

ASKER

Thanks Ryan, I installed the latest 8.0 ANSI Driver:

     "DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _

This fixed the error. The only problem now is we are not getting the ID value returned that we expected but this is for another post.