VBscript call stored procedure from MySQL with output parameters

Question

[ EDIT 20220219 ]

Resolved using VBSCRIPT CODE below

   SQL = " CALL NewCheckData(@pOld); "
   cn.execute(SQL)   
   
   SQL = " SELECT @pOld; "
   Set RS = cn.execute(SQL)
   
   pOld = cInt(RS("@pOld"))

[ EDIT 20220219 ]

[EDIT]

I have a Stored Procedure on a MySQL DB.

Which simply takes the COUNT ROWS of a Parameter and returns the Value of that Parameter.

I would like to call this Stored Procedure to assign value to variable in my VBscript code.

This is MySql routine (stored procedure) tried and worked.

CREATE DEFINER=`user`@`%` PROCEDURE `NewCheckData`(OUT pOld INT (11))
BEGIN

SELECT
    COUNT(*) tOld INTO pOld
FROM
    `DoTable` 
WHERE
    DATE( myDATE ) = CURRENT_DATE;

END

VBSCRIPT CODE is as below

   On Error Resume Next
   
   Const adCmdStoredProc = 4
   Const adInteger = 3
   Const adVarWChar = 202
   Const adParamInput = &H0001
   Const adParamOutput = &H0002
   Const adParamReturnValue = &H0004

   Set cn = CreateObject("ADODB.Connection")
   cn.Open "DRIVER={MySQL ODBC 5.1 Driver};SERVER=XXX;PORT=3306;DATABASE=XXX;USER=XXX;PASSWORD=XXX;OPTION=3;"  
   cn.CommandTimeout = 10000
   
   Set cmd = CreateObject("ADODB.Command")
   
   With cmd
      Set .ActiveConnection = cn
          .CommandText = "NewCheckData"
          .CommandType = adCmdStoredProc
          .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue )
          .Parameters.Append .CreateParameter("@pOld", adInteger, adParamOutput, 11)
          .Execute
          parmval = .Parameters(0).Value
   End With
   
   cn.Close()
   Set cn = Nothing

   If Err.Number <> 0 Then
     WScript.Echo "Error in : " & Err.Description
     Err.Clear
   End If
   On Error GoTo 0
Error or messagebox

Error or messagebox

Any suggestion, please.

[OLD QUESTION]

I am working with VBSCRIPT and using stored procedure MySQL.

I have to get the value of stored procedure out parameter.

This is MySql routine (stored procedure) tried and worked

CREATE DEFINER=`user`@`%` PROCEDURE `CheckData`(OUT pOld INT (11))
BEGIN

SELECT
    COUNT(*) tOld INTO pOld
FROM
    `DoTable` 
WHERE
    DATE( myDATE ) = CURRENT_DATE;

END

VBSCRIPT CODE is as below

   Set cn = CreateObject("ADODB.Connection")
   cn.Open "DRIVER={MySQL ODBC 5.1 Driver};SERVER=XXX;PORT=3306;DATABASE=XXX;USER=XXX;PASSWORD=XXX;OPTION=3;"  
   cn.CommandTimeout = 1000 

   Set objCommandSec = CreateObject("ADODB.Command")
   objCommandSec.ActiveConnection = cn
   objCommandSec.CommandType = 4
   objCommandSec.CommandText = "CheckData"

   objCommandSec.Parameters.Refresh

   objCommandSec.Parameters.append objCommandSec.createParameter("@pOld", adInteger, adParamReturnValue) <<< error line
   objCommandSec.execute , , adExecuteNoRecords
   pOld = objCommandSec.Parameters("@pOld").value

   MsgBox(pOld) 
   
   cn.Close()
   Set cn = Nothing

Error or messagebox line 15

Error 'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another'

Any suggestion, please.

You have the wrong `ParameterType` set, it should be `adParamOutput` not `adParamReturnValue`. See [`ParameterDirectionEnum`](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/parameterdirectionenum?view=sql-server-ver15) — user692942, Feb 18 '22 at 09:48
@user692942 thanks I have edit the `VBSCRIPT code` from `adParamReturnValue` to `adParamOutput`. Same error — Edward Sheriff Curtis, Feb 18 '22 at 09:50
@EdwardSheriffCurtis Think you need to specify the `Size` as well on an `adParamOutput` parameter. Also, have you defined the "ADO Constants", `adInteger`, `adParamOutput` etc? You should have `Const adInteger = 3` for example, as VBScript doesn't know about the ADO named constants automatically. — user692942, Feb 18 '22 at 09:53
BEGIN-END (and DELIMITER re-assigning) is excess in single-statement stored procedure. — Akina, Feb 18 '22 at 10:10
Does this answer your question? [Calling SQL Stored Procedure with Output Parameter in VBScript](https://stackoverflow.com/a/10017933) — user692942, Feb 18 '22 at 10:13
@user692942 Your suggested link resolve the problem of input parameters not of the out parameters — Edward Sheriff Curtis, Feb 18 '22 at 10:13
Also relevant - [Return a value and a result set from stored procedure classic asp](https://stackoverflow.com/a/42070162) (It says Classic ASP, but applies to VBScript also). — user692942, Feb 18 '22 at 10:15

grenlow · Answer 1 · 2022-02-18T12:24:28.977

Edit: I failed to consider and mention that the below code example is accessing a MS-SQL DB. The behavior could therfor be different.

I don't use .VBS much anymore, but as I believe you are using the "Windows Script Host" environment I don't think it will make much difference. In the past I have done essentially the same thing as you demonstrate above many times with WSH & .JS. I also always ran into problems when I explicitly added the parameter definitions. I have since learned that for me the .refresh() is completely sufficient. I therefore leave .createParameter out now and simply give the named parameters the needed values as such:

var jsADO = {}; 
jsADO.objConn = new ActiveXObject("ADODB.Connection");
jsADO.objConn.Open("Provider=SQLOLEDB.1;...");
jsADO.cmd_insertShare = new ActiveXObject("ADODB.Command");
var cmd = jsADO.cmd_insertShare;
cmd.ActiveConnection = jsADO.objConn;
cmd.CommandType = adCmdStoredProc; // 4
cmd.CommandText = "usp_insertShare";
cmd.Prepared = true;
cmd.NamedParameters = true;
cmd.Parameters.Refresh()

...

var sqlRec;
var cmd = jsADO.cmd_insertShare;
cmd.Parameters("@p_Server")      = "myServer";
cmd.Parameters("@p_Name")        = "myShare";
cmd.Parameters("@p_Description") = "myShare Desc";
cmd.Parameters("@p_LocalPath")   = "sharePath";
sqlRec = cmd.Execute(null, null, 0);

The syntax is indeed different, but I hope the gist is clear.

In summary, I think you've got it, just try leaving the .createParameter function out and only setting the named parameter values.

VBscript call stored procedure from MySQL with output parameters

1 Answers1