Please make more clear how to call a Stored Procedure

Is your feature request related to a problem? Please describe. It took many iterations to figure out what was wrong in my process. And I think this could discourage others in using the tools if no clear examples are provided. Anyway, I read most of the documentation offered. I now know how to call and get results from a Store Proc.

Describe the solution you'd like statement.bind allows you to bind the required parameters for a store proc as IN , OUT etc. So we do. But turns out we apparently should never expect the OUT variable to be populated by the statement.execute(). Instead we should expect to have our result in an array. Why then we bind the OUT variable? And certainly there was no a single example with store proc returning values, just passing in all literals.

Describe alternatives you've considered Alternatives I considered was to abandon idb-pconnector and use odbc , I have not seen the examples there. I certainly enjoy more idb-pconnector. Thanks for your support here!

Additional context Not applicable.

Can you give an example of how you solved this? I am having the same issue.

Good afternoon,

//after your binding, IN OUT etc, you execute your statement, make sure you assign this to a variable

const results = await statement.execute();

//if then you console.log(result);

// you will realize this variable is an array, each output field is in there. I my case I passed just 2 variables IN and expected 1 OUT, so I did

myexpectedResult = results[0].trim();

This is what worked for me, so apparently for now, we should not expect the bind variable to be populated.

Hope it helps

On Fri, Aug 12, 2022 at 12:25 PM Collin DeSoto @.***> wrote:

Can you give an example of how you solved this? I am having the same issue.

— Reply to this email directly, view it on GitHub https://github.com/IBM/nodejs-idb-pconnector/issues/126#issuecomment-1213444031, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOMYT55QCC5SJZA7ZRR6XGTVY2QJ3ANCNFSM556PGQWQ . You are receiving this because you authored the thread.Message ID: @.***>

Good afternoon, //after your binding, IN OUT etc, you execute your statement, make sure you assign this to a variable const results = await statement.execute(); //if then you console.log(result); // you will realize this variable is an array, each output field is in there. I my case I passed just 2 variables IN and expected 1 OUT, so I did myexpectedResult = results[0].trim(); This is what worked for me, so apparently for now, we should not expect the bind variable to be populated. Hope it helps … On Fri, Aug 12, 2022 at 12:25 PM Collin DeSoto @.> wrote: Can you give an example of how you solved this? I am having the same issue. — Reply to this email directly, view it on GitHub <#126 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOMYT55QCC5SJZA7ZRR6XGTVY2QJ3ANCNFSM556PGQWQ . You are receiving this because you authored the thread.Message ID: @.>

Interesting. I have a procedure that takes 3 inputs and 1 output. It uses an output to check whether to proceed with the rest of the procedure... when that happens I receive the "FOUND" output instead of "PROCESSED" but they insist I only need to send 1 output parameter... Also, the output or results from the execute is always just the parameters I sent...

So if I sent [1,5,1," "]... I receive [1,5,1,"FOUND"]

Hello again,

I might hav more questions than answers. In my case I’v prepared the Store Procedure and I know exactly how is define and what to pass in and what to expect back. To start, I made a simple name concatenation: firstName, lastName goes in, fullName comes out. So when I see my Store Procedure using the DB perspective in RDI, and I get the definition, I get exactly that. And yes my results array only exhibit the 1 concatenated value.

Seems to me that someone else wrote that procedure for you, not sure but perhaps all variables where defined as INOUT, therefore you get them all back as well. I am guessing. As far as the expected result “PROCESSED” it is very little what I can say. Which language is that procedure written in? Can you debug it? That might give some answers so you can then proceed. Good luck!

On Aug 12, 2022, at 4:05 PM, Collin DeSoto @.***> wrote:

Good afternoon, //after your binding, IN OUT etc, you execute your statement, make sure you assign this to a variable const results = await statement.execute(); //if then you console.log(result); // you will realize this variable is an array, each output field is in there. I my case I passed just 2 variables IN and expected 1 OUT, so I did myexpectedResult = results[0].trim(); This is what worked for me, so apparently for now, we should not expect the bind variable to be populated. Hope it helps … <x-msg://1/#> On Fri, Aug 12, 2022 at 12:25 PM Collin DeSoto @.> wrote: Can you give an example of how you solved this? I am having the same issue. — Reply to this email directly, view it on GitHub <#126 (comment) https://github.com/IBM/nodejs-idb-pconnector/issues/126#issuecomment-1213444031>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOMYT55QCC5SJZA7ZRR6XGTVY2QJ3ANCNFSM556PGQWQ https://github.com/notifications/unsubscribe-auth/AOMYT55QCC5SJZA7ZRR6XGTVY2QJ3ANCNFSM556PGQWQ . You are receiving this because you authored the thread.Message ID: @.>

Interesting. I have a procedure that takes 3 inputs and 1 output. It uses an output to check whether to proceed with the rest of the procedure... when that happens I receive the "FOUND" output instead of "PROCESSED" but they insist I only need to send 1 output parameter... Also, the output or results from the execute is always just the parameters I sent...

So if I sent [1,5,1," "]... I receive [1,5,1,"FOUND"]

— Reply to this email directly, view it on GitHub https://github.com/IBM/nodejs-idb-pconnector/issues/126#issuecomment-1213579128, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOMYT56766SYTMSRZX4OJRLVY3KDBANCNFSM556PGQWQ. You are receiving this because you authored the thread.

Hello again,

I might hav more questions than answers. In my case I’v prepared the Store Procedure and I know exactly how is define and what to pass in and what to expect back. To start, I made a simple name concatenation: firstName, lastName goes in, fullName comes out. So when I see my Store Procedure using the DB perspective in RDI, and I get the definition, I get exactly that. And yes my results array only exhibit the 1 concatenated value.

Seems to me that someone else wrote that procedure for you, not sure but perhaps all variables where defined as INOUT, therefore you get them all back as well. I am guessing. As far as the expected result “PROCESSED” it is very little what I can say. Which language is that procedure written in? Can you debug it? That might give some answers so you can then proceed. Good luck!

On Aug 12, 2022, at 4:05 PM, Collin DeSoto @.***> wrote:

Good afternoon, //after your binding, IN OUT etc, you execute your statement, make sure you assign this to a variable const results = await statement.execute(); //if then you console.log(result); // you will realize this variable is an array, each output field is in there. I my case I passed just 2 variables IN and expected 1 OUT, so I did myexpectedResult = results[0].trim(); This is what worked for me, so apparently for now, we should not expect the bind variable to be populated. Hope it helps … <x-msg://1/#> On Fri, Aug 12, 2022 at 12:25 PM Collin DeSoto @.> wrote: Can you give an example of how you solved this? I am having the same issue. — Reply to this email directly, view it on GitHub <#126 (comment) https://github.com/IBM/nodejs-idb-pconnector/issues/126#issuecomment-1213444031>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOMYT55QCC5SJZA7ZRR6XGTVY2QJ3ANCNFSM556PGQWQ https://github.com/notifications/unsubscribe-auth/AOMYT55QCC5SJZA7ZRR6XGTVY2QJ3ANCNFSM556PGQWQ . You are receiving this because you authored the thread.Message ID: @.>

Interesting. I have a procedure that takes 3 inputs and 1 output. It uses an output to check whether to proceed with the rest of the procedure... when that happens I receive the "FOUND" output instead of "PROCESSED" but they insist I only need to send 1 output parameter... Also, the output or results from the execute is always just the parameters I sent...

So if I sent [1,5,1," "]... I receive [1,5,1,"FOUND"]

— Reply to this email directly, view it on GitHub https://github.com/IBM/nodejs-idb-pconnector/issues/126#issuecomment-1213579128, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOMYT56766SYTMSRZX4OJRLVY3KDBANCNFSM556PGQWQ. You are receiving this because you authored the thread.

Yes sadly it was not written by me . They state the procedure takes 3 input and 1 output. I do not see a way to mark a parameter as an input in this version of idb-pconnector.

My params are just [1, 5, 1, ""] for 3 input and 1 output. It seems his procedure actually has 2 output since it states FOUND and then PROCESSED later. I only end up receiving the FOUND and the procedure never fully processed after that.

my code cons { Connection, Statement, IN, OUT,INOUT, CHAR, NUMERIC} = require('idb-pconnector} exports.getfullName = async (req, res, next) => {

const fname = req.query.first const lname = req.query.last let fullName = "" const connection = new Connection({url: '*LOCAL'}) const statement = new Statement(connection) const sql = 'CALL mylib.user_concat_names(?,?,?) await statement.prepare(sql) await.statement.bind([ [${fname}, IN, CHAR], [${lname}, IN,CHAR], [${fullName},OUT,CHAR], ])

const results = await.statement.execute()

fulllName = results[0].trim()

res.status(200).json({firstname: fname, lastName: name, fullName: fullName}) )

On Sat, Aug 13, 2022 at 12:18 AM Collin DeSoto @.***> wrote:

Hello again,

I might hav more questions than answers. In my case I’v prepared the Store Procedure and I know exactly how is define and what to pass in and what to expect back. To start, I made a simple name concatenation: firstName, lastName goes in, fullName comes out. So when I see my Store Procedure using the DB perspective in RDI, and I get the definition, I get exactly that. And yes my results array only exhibit the 1 concatenated value.

Seems to me that someone else wrote that procedure for you, not sure but perhaps all variables where defined as INOUT, therefore you get them all back as well. I am guessing. As far as the expected result “PROCESSED” it is very little what I can say. Which language is that procedure written in? Can you debug it? That might give some answers so you can then proceed. Good luck!

On Aug 12, 2022, at 4:05 PM, Collin DeSoto @.***> wrote:

Good afternoon, //after your binding, IN OUT etc, you execute your statement, make sure you assign this to a variable const results = await statement.execute(); //if then you console.log(result); // you will realize this variable is an array, each output field is in there. I my case I passed just 2 variables IN and expected 1 OUT, so I did myexpectedResult = results[0].trim(); This is what worked for me, so apparently for now, we should not expect the bind variable to be populated. Hope it helps … x-msg://1/# On Fri, Aug 12, 2022 at 12:25 PM Collin DeSoto @.> wrote: Can you give an example of how you solved this? I am having the same issue. — Reply to this email directly, view it on GitHub <#126 https://github.com/IBM/nodejs-idb-pconnector/issues/126 (comment) #126 (comment) https://github.com/IBM/nodejs-idb-pconnector/issues/126#issuecomment-1213444031>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOMYT55QCC5SJZA7ZRR6XGTVY2QJ3ANCNFSM556PGQWQ https://github.com/notifications/unsubscribe-auth/AOMYT55QCC5SJZA7ZRR6XGTVY2QJ3ANCNFSM556PGQWQ . You are receiving this because you authored the thread.Message ID: @.>

Interesting. I have a procedure that takes 3 inputs and 1 output. It uses an output to check whether to proceed with the rest of the procedure... when that happens I receive the "FOUND" output instead of "PROCESSED" but they insist I only need to send 1 output parameter... Also, the output or results from the execute is always just the parameters I sent...

So if I sent [1,5,1," "]... I receive [1,5,1,"FOUND"]

— Reply to this email directly, view it on GitHub #126 (comment) https://github.com/IBM/nodejs-idb-pconnector/issues/126#issuecomment-1213579128, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOMYT56766SYTMSRZX4OJRLVY3KDBANCNFSM556PGQWQ . You are receiving this because you authored the thread.

Yes sadly it was not written by me . They state the procedure takes 3 input and 1 output. I do not see a way to mark a parameter as an input in this version of idb-pconnector.

My params are just [1, 5, 1, ""] for 3 input and 1 output. It seems his procedure actually has 2 output since it states FOUND and then PROCESSED later. I only end up receiving the FOUND and the procedure never fully processed after that.

— Reply to this email directly, view it on GitHub https://github.com/IBM/nodejs-idb-pconnector/issues/126#issuecomment-1213897101, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOMYT533EKQU52PHR36BAJLVY5D3ZANCNFSM556PGQWQ . You are receiving this because you authored the thread.Message ID: @.***>

:wave: Hi! This issue has been marked stale due to inactivity. If no further activity occurs, it will automatically be closed.

IBM / nodejs-idb-pconnector

Please make more clear how to call a Stored Procedure #126