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

ASPVB ScriptMySQL Server

Avatar of undefined
Last Comment
mike99c

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
mike99c

ASKER
I am using MariaDB, v10.3.17
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes