Calling SQL stored procedure with input parameter

Question

I need to write a little VBScript to call a SQL procedure with an input variable. I don't have to much experience with VBscript and I'm getting an error for which I can't find a solution. The error I am receiving is:

OnNewRecord (Line 14): [ODBC Firebird Driver][Firebird]Dynamic SQL Error Input parameter mismatch for procedure TEST

The VBScript I wrote is:

Const Connection = "DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=masterkey;DBNAME=C:\Users\wouter\FOR-TESTING.fdb;"
Const adParamInput = 1
Const adInteger = 3
Set myConn = CreateObject("ADODB.Connection")
myConn.Open Connection

Set spCommand = CreateObject("ADODB.Command")
spCommand.Commandtext = "TEST" 
spCommand.CommandType = 4

Set parameter = spCommand.CreateParameter(, adInteger, adParamInput, 4, 5)

Set spCommand.ActiveConnection = myConn
spCommand.Execute 
myConn.Close

This VBscript is calling the following procedure:

CREATE OR ALTER PROCEDURE TEST (ID INTEGER)
AS BEGIN
UPDATE MATERIAL_LABEL SET PRINTED = 'T', HANDLED_DATE = CURRENT_TIMESTAMP 
WHERE ID = :ID;
END

ID INTEGER should be a variable input but for testing the code I'm using the numeric value 5. I think it has something to do with the procedure requiring a different type of input but I can't find which.

You create a parameter, but you never [append](https://stackoverflow.com/a/18619736/1630171) it to your query, so the stored procedure is actually called without the parameter it expects. — Ansgar Wiechers, Jun 05 '18 at 09:27
Possible duplicate of [ADO Command Parameter Not Passing to Stored Procedure or Stored Procedure 'Ignoring' Parameter](https://stackoverflow.com/questions/25508050/ado-command-parameter-not-passing-to-stored-procedure-or-stored-procedure-ignor) — user692942, Jun 05 '18 at 14:10

Wouter · Answer 1 · 2018-06-05T12:01:01.233

I've solved the problem by using a different method to insert my numeric value:

Const Connection = "DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=masterkey;DBNAME=C:\Users\wouter\FOR-TESTING.fdb;"
Set myConn = CreateObject("ADODB.Connection")

myConn.Open Connection

SQL = "execute procedure TEST 5"
Set dbconn = CreateObject("ADODB.Connection")
dbconn.Open connection
dbconn.Execute(SQL)
myConn.Close

-- UPDATE--

Since Ansgar Wiechers and Mihai Adrian gave me the cause for my error and Geert bellekens warned me for the risk of SQL injection I've tried adding the "append line" to my VBScript and this also works perfect!

Hereby my finished VBScript:

Const Connection = "DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=masterkey;DBNAME=C:\Users\wouter\FOR-TESTING.fdb;"
Const adParamInput = 1
Const adInteger = 3
Set myConn = CreateObject("ADODB.Connection")
myConn.Open Connection

Set spCommand = CreateObject("ADODB.Command")
spCommand.Commandtext = "TEST" 
spCommand.CommandType = 4

Set parameter = spCommand.CreateParameter(, adInteger, adParamInput, 4, 5)
spCommand.Parameters.Append parameter

Set spCommand.ActiveConnection = myConn
spCommand.Execute 
myConn.Close

Thank you!

I liked your previous approach better -> no risk for SQL injection. Seems like a better idea to try to get the parameter stuff working. — Geert Bellekens, Jun 05 '18 at 11:14

score 0 · Accepted Answer · answered Jun 05 '18 at 11:34

0

As Ansgar Wiechers said, you have to pass the parameter to the stored procedure, by appending it :

spCommand.Parameters.Append parameter

answered Jun 05 '18 at 11:34

Mihai Adrian

645
6
17

Calling SQL stored procedure with input parameter

2 Answers2