VBscript call stored procedure from MySQL with output parameters [duplicate]

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 — user692942
– user692942, Commented Feb 18, 2022 at 9:48
@user692942 thanks I have edit the VBSCRIPT code from adParamReturnValue to adParamOutput. Same error — Edward Sheriff Curtis
– Edward Sheriff Curtis, Commented Feb 18, 2022 at 9: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
– user692942, Commented Feb 18, 2022 at 9:53

grenlow · Accepted Answer · 2022-02-18 12:24:28Z

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.

Collectives™ on Stack Overflow

VBscript call stored procedure from MySQL with output parameters [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related