We help IT Professionals succeed at work.

Error when calling stored procedure with out parameter in ASP classic

mike99c
mike99c asked
on
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

Comment
Watch Question

View Solution Only

Software Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT
Commented:
what is your MySQL version that you connecting to?

I have encountered that same error, and managed to resolve it by using a newer MySQL connector.

so, try to download and install the newer MySQL connector and update your code at:

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

https://downloads.mysql.com/archives/c-odbc/
mike99c

Author

Commented:
I am using MariaDB, v10.3.17
mike99c

Author

Commented:
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.