asked on

Return value from MySQL stored procedure using ASP Classic script not showing correct value

I'm trying to call a MySQL stored procedure from an ASP classic script. The stored procedure simply returns an integer value 99 and outputs the result. Unfortunately the result returned toggles between 0 and 67 every time you refresh the page and it should simply show 99.

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 ;

Open in new window

The database is MariaDB, v10.3.17.

Here is the ASP classic script calling the stored procedure. As value is passed in which does not get read but more importantly the return value is read and displayed.

<%@ Language=VBScript %>
<%
    Option Explicit
    Response.Buffer = True
    Dim Hostname, Username, Password, Database
    Hostname = "localhost"
    Username = "XXXXXXX"
    Password = "YYYYYYY"
    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 8.0 ANSI Driver};" & _
     "SERVER=" & Hostname & ";" & _
     "Database=" & Database & ";" & _
     "UID=" & Username & ";" & _
     "PASSWORD=" & Password & ";" & _
     "OPTION=3;"

   response.write "<p>Calling Proc_Test with only one input parameter and one output parameter</p>"
   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 "<p>myID = " & myID & "</p>"

%>
</body>
</html>

Open in new window

ASKER CERTIFIED SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tomas Helgi Johannsson

Hi,

Arnold is right. You need to retrieve the data prior closing your connection.
Also it is a good practice to initialize variables before they are used to make sure they hold desired data and nothing else.

Regards,
Tomas Helgi