MBS SQL Plugin; stored procedure; output parameter;

SDKs Plugins
1

I get the error “parameter … expected” for the output parameter.
The given example does not set parameter up before Cmd.execute.

Something changed for MS SQL Server or MBS?

When I set the argument, it’s not re-set by procedure…

So, some stoed procedure like this:

procedure mergeXY	
	@id varchar(32),
	@email varchar(255),	
	@nr int output
...

Any idea?

2

Well, what does your code look like?

Does the ParamCount give the right number of parameters?
What does Parameters dictionary show in debugger?

3

The parameters dictionary shows exactly what I did set the parameters to.
So, the output parameter is not changed there from procedure which always select the “nr”.

The point is, that in the MBS example, no argument for the output parameter is specified, which is complaint by SQL server as needed.
So, when I try to send only 2 arguments, omiting the output parameter, I get the error.

4

Ok, some code to show he steps done:

Jarr = JRequest.LookupArrayOfVariantOf("params") // from the webview2
Params = MSSQL.getParams(JArr)

Cmd = new SQLCommandMBS()
Cmd.Connection = Con
Cmd.CommandText = SQL
Cmd.setParams(Params) // set parameters by variant / getType
Cmd.Execute()
JParams = JResult.SetObjectOf("params") // prepare answer
Cmd.setParamsOf(JParams) // do the reverse and put Cmd.Parameters in JSON
if Cmd.isResultSet then // no resulset needed here
  Jarr = JResult.SetArrayOf("data")
  
  Cmd.setArrayOf(Jarr)
end if

Cmd.Close() 
Con.Commit()
5

So, SQLCommandMBS.Parameters is both used for input and output values.

6

one thing found: par.isOutput = false which is false… ahem…
try to find why…

7

ok → default value for output parameter prevents “value expected” error.
But no output parameter added to SQLCommandMBS… let’s see…

8

ok, if I use default argument, no parameter is added to collection.
If I simply don’t set value for parameter, result is “null”

9

I found Par.DirType… when I set this to
SQLParamMBS.kParamDirTypeOutput
I get error “Unknown parameter data type”

10

This is annoying… the only way this works is like that:

begin
  declare @no int;
  exec myProc @id = :id, @email = :email, @nr = @no output;
  select @no;
end

So, not using output parameter at all but select in block given.

11

Well, first I may ask if this is current 23.0 plugin or something older?

And I assume this is SQL Server via ODBC?

Technically we ask SQL Server via SQLProcedureColumns for the parameters needed. And ParamCount should show how many we found. The parameters dictionary is build to let you see it in the debugger, but it can only report parameters with names.

maybe you can send me later a test project, so I can debug this a bit?

12

No, it’s not ODBC; I connect via SQLConnectionMBS.kSQLServerClient.
It’s MBS 22 version → how can I get the version from plugin?

As said in MBS documentation, some servers/databases don’t give correct information about parameters.

See above: the MBS example does not use default value for parameter, but calling stored procedure without argument seems to work there; which does not work for my example.
A test with more than one argument could raise the problem, maybe?

I will use the block/select SQL.