Error calling stored procedure with many parameters. [BUG]

[snotmare]

Describe your system

odbc Package Version: 2.4.7
ODBC Driver: IBM i Access ODBC Driver
Database Name: DB2
Database Version: Latest version + patches
Database OS: Power i
Node.js Version: 14.17.3
Node.js OS: Windows

Hello! I'm testing out odbc with our existing code and ran into an issue calling a stored procedure. Here's the call (lots of parameters)...
call WOR3070SP(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

This is the error...
Code: 30038, State: HY090, Message: [IBM][System i Access ODBC Driver]Invalid string or buffer length.

Our server admin says he does not see the stored procedure call on the database side, so I'm assuming there's something in the odbc module or the driver that's keeping this from working.

I've also attached an odbc trace.
cwbtrace-64-node.exe-28460.log

Let me know if there is any more information I can provide. Thanks again for your help!

[markirish]

Hi @snotmare ,

Could you post the snippet of code that generates the error? Just the callProcedure function call should suffice. It looks like it is failing on SQLProcedures, but there's really nothing there that should fail as all of the buffers are marked as SQL_NTS (null-terminated string).

Or, if you are using the query function instead of callProcedure, that would be helpful to know as well!

[snotmare]

Good morning @markdirish !

This is the portion of code that makes the stored procedure call...

		try {
			let callResults = new ConnectionCallResults();
			connection = await this.getConnection();

			let parameters = worker.parameters.map(parameter => parameter.value);
			let results = await connection.callProcedure(null, null, worker.sql, parameters);

			callResults.rows = results;
			callResults.parameters = results.parameters;
			
			return callResults;
		} catch(error) {

[markirish]

Ok, I created a large procedure with 80 INOUT parameters, and it seemed to behave ok. So it shouldn't be the parameter count itself. Next thing lets try, lets get a unixODBC trace (this has slightly different information than the cwb trace, which is an IBM i-specific trace). Instructions on getting a unixODBC trace can be found here: https://github.com/markdirish/node-odbc#debugging

[snotmare]

*** UPDATE ***

Please ignore the following post. It contains invalid information due to a faulty test on my part.

---

Hello!

I ran my stored procedure test today to build the trace you asked for and... it works now for some reason. Relatable meme

I don't have an explanation of why it's working now. I'm running the same code I did last Wednesday. I have installed some python packages on my pc for an unrelated project, but I don't see how that would impact anything.

Our system admin says nothing on our test lpar has changed, but, "it would have IPL'd on last Friday and likely cleaned up the SQLPKG as part of that IPL"

In case it helps, I've attached the unix trace you asked for plus a new odbc trace. I'll do some more testing over the next week or so and let you know if I run into this again.

As always, thanks for your help!

SQL.LOG
cwbtrace-64-node.exe-9800.log

[kadler]

Neither the SQL.LOG or latest cwbtrace have any relevant calls in them. It connects to the database and calls SQLGetInfo, but that's it. There's no calls to prepare, or SQLProcedures like in the original cwbtrace.

I can say that the HY090 is caused by an error when checking for wildcards in the schema / procedure names. Unfortunately, the driver doesn't trace out the parameters, but I think SQL.log should show us what was being passed.

[snotmare]

Ugh, thank you. My prior test was invalid. I have run this again with the trace you asked for and I ran into the issue as expected. Sorry for the confusion.

SQL.LOG

[mlitters]

Hi there,
i have an issues with calling an IBM i Stored Procedure which we used with the old Node.js DB Providers back in Node 6 and with idb-pconnector and maybe it is the same problem as above?!?

After testing many, many things I found out it it is the length of the output parameter.

What I do is:

CREATE or replace PROCEDURE SESEDI.SPTEST8 ( 
	IN TEST CHAR(10) CCSID 1208 , 
	OUT RVALUE VARCHAR(2000) CCSID 1208 ) 
	LANGUAGE SQL 
	SPECIFIC SESEDI.SPTEST8 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
	BEGIN 
SET RVALUE = 'Hallo Welt' ; 
RETURN ; 
END  ; 

This works fine when I call it this way:
const result = await connection.callProcedure('MYDSN', 'MYLIB','SPTEST8', ['12345', null]);

When I enhance the OUT Parameter to (for example) 16000 it doesn't work anymore.
In my original procedure I return 32000 as varchar. This is a "legacy" thing which is caused by the problem that the first DB driver for Node.js (back in 2014) didn't return a result set. So I solved it this way.
Can you enhance that value easily?
Otherwise I need to change a lot of stuff or find another way.

Many thanks and have a great weekend!
Markus

[mlitters]

odbc.log

[markirish]

Good morning @markdirish !

This is the portion of code that makes the stored procedure call...

		try {
			let callResults = new ConnectionCallResults();
			connection = await this.getConnection();

			let parameters = worker.parameters.map(parameter => parameter.value);
			let results = await connection.callProcedure(null, null, worker.sql, parameters);

			callResults.rows = results;
			callResults.parameters = results.parameters;
			
			return callResults;
		} catch(error) {

Sitting here a few weeks later, and I think I know the issue...

Is worker.sql the SQL string you are trying to call? In the .log file you sent, the call to SQLProceduresW had "call WOR3070SP(?, ? ... ,?)" as the proc name. This value should just be WOR3070SP, and then the connector will actually go and execute the procedure with the proper ODBC procedure call escape sequence.

Try replacing worker.sql with just the name of the procedure, and see what happens.

[snotmare]

Good morning!

I gave that a shot but ran into this error...

Caused By: Error: [odbc] The number of parameters the procedure expects and and the number of passed parameters is not equal

This is how I updated the code to test that out...

		try {
			let callResults = new ConnectionCallResults();
			connection = await this.getConnection();

			let parameters = worker.parameters.map(parameter => parameter.value);
			console.log(parameters);
			// let results = await connection.callProcedure(null, null, worker.sql, parameters);
			let results = await connection.callProcedure(null, null, 'WOR3070SP', parameters);

			callResults.rows = results;
			callResults.parameters = results.parameters;
			
			return callResults;
		} catch(error) {

As you can see, I also wrote the parameters to the console...

[
  'CH', 'Y', 'FFGFA', '', '',  0,   '',
  '',   '',  '',      0,  0,   '',  '',
  '',   0,   '',      0,  0,   '',  '',
  '',   '',  '',      '', '',  'N', '',
  'N',  0,   '',      '', 'N', '',  '',
  '',   0,   0,       0,  0,   '',  '',
  '',   0,   '',      0,  '',  'N', '',
  '',   '',  '',      '', '',  'N', 0,
  0,    'N', '',      '', '',  '',  '',
  '',   '',  '',      '', '',  '',  '',
  '',   '',  '',      '', ''
]

This is the call I'm expecting it to make...
call WOR3070SP('CH', 'Y', 'FFGFA', '', '', 0, '', '', '', '', 0, 0, '', '', '', 0, '', 0, 0, '', '', '', '', '', '', '', 'N', '', 'N', 0, '', '', 'N', '', '', '', 0, 0, 0, 0, '', '', '', 0, '', 0, '', 'N', '', '', '', '', '', '', 'N', 0, 0, 'N', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')

Thank you for your help!

As a side note, could the error message indicate how many parameters are expected and how many were passed in?

Ben

[markirish]

Ok, one more SQL.log with the new code and it should show me the number of params it expects. Delete your old log before running or else it will append to that file

[snotmare]

No problem. Here you go!
SQL.LOG

[markirish]

Well, I forgot that ODBC uses SQLProcedureColumns then calls SQLFetch a ton of times... so the log was less than helpful.

How about this. Run the following query, and let me know the result:

SELECT COUNT(*) FROM SYSIBM.SQLPROCEDURECOLS WHERE PROCEDURE_NAME='WOR3070SP'

If you have more than one procedure with that name, you may have to add another part of the WHERE clause and add a check against the value of PROCEDURE_SCHEM.

[snotmare]

Yep, I had to add the schema to the where clause. I see 75 columns.