Stored Procedure call that returns data

[a62754]

i am having trouble calling a stored procedure that returns rows.

from the visual stored procedure widget, when i declare the input/output variables, during the call these are treated as inputRow values and not output even if they're marked as return values.

if i manually update the access set to specify an outputRow parameter, it doesn't work

on UI i saw an edit saying "the stored procedure was defined for 'DARI' call. The output parameter row will be ignored when used for 'SQL call'"

would like some assistance here. I've gone through the samples and they don't have any return rows.

i need to do a call like -> mysp(1,'a',?,?) and this will get me the output values and also the return data (a set of rows). How do i make this call from vast?

[a62754]

for getting the output params back, we have an existing api:
executeStoredProcQuerySpec: querySpec withRow: aRow ifError: anErrorBlock
but this doesn't return data - only that the SP call was made or not

i found a way to get return values. i need to do this:
((AbtProcedureResultSets new)
openOnDatabase: aDBconnection
querySpec: querySpec
withValues: aDictionary <-- it says it can receive aRow but if i send in a abtclirow it crashes
ifError: anErrorBlock) asOrderedCollection
To make this work, i have to fix the AbtResultTable>>openOnDatabase: aDatabase querySpec: aQuerySpec withValues: aCollection method to pass the values ahead. its currently passing nil and ignoring the input arg.
i do get the results - yippie.
Has no one used this api before? no one in smalltalk is calling a SP that returns data rows? i'm using vast 7.5 - is this fixed in 8.x?

BUT, i would also want the output params. How do i get the best of both these worlds? output params as well as return data? do i have to make two calls here? or do i need to fix the method that doesn't work with abt cli row and is currently only working for dictionary?

[tc]

From VAST_Handbook_vol2_sg242219.pdf:

This will return the active users on SQL Server as strings. The ODBC stored
procedure calling convention is {call xxxx}.

Code: Select all

| activeDatabase querySpec result resultCollection |
resultCollection := OrderedCollection new.
activeDatabase := AbtDbmSystem activeDatabaseMgr databaseInUse.
querySpec := (AbtQuerySpec new) statement: '{call sp_who}'.
result := activeDatabase resultTableFromQuerySpec: querySpec.
result do: [:row | resultCollection add: (row asString)].
^resultCollection

NOTE: CLI runs on top of ODBC, so, this may work as well:

Here is an example for calling a stored procedure that passes one
parameter:

Code: Select all

| aDict querySpec ct |
aDict := Dictionary new.
ct := CompoundType new
addField: ( AbtOdbcVarCharField new
name: 'COL1';
count: ( 20 )).
aDict at: 'COL1'  put: 'CHUCKTEST'.
querySpec := (AbtQuerySpec new)
statement: '{call STOREDPROC(?)}'.
querySpec hostVarsShape: ct.
AbtDbmSystem activeDatabaseMgr
executeQuerySpec: querySpec
withValues: aDict.

--tc

[koschate]

TC, did you mean to paste the same code into both examples?

[tc]

Corrected it.

--tc

[a62754]

Thanks for the reply. but, i don't think my question has been answered.
I wanted to get the output parameters AND the results in ONE call.

Your first sample code uses resultTableFromQuerySpec which will get results of the SP but not the output params.
your second sample code uses executeQuerySpec which won't get my the results, only the output params.

The solution i've found is to do something like:
result := ( ( AbtProcedureResultSets new )
openOnDatabase: aConnection
querySpec: querySpec
withValues: values
ifError: ( an errorBlock ) ).
This gets me the output params via the "values" object and results via the "result" object.

[a62754]

anyone from Instantations wants to reply to my comments?
should we update the vast documentation examples to include this scenario?

[tc]

Hello,

I've only seen DB2 stored procedures used in the manner described here:
http://www2.instantiations.com/VAST/Docs/802/wwhelp/wwhimpl/js/html/wwhelp.htm#href=db/dbguide49.html


If I have a stored procedure that returns a result set and has two string parameters, for example, and the signature is:

Code: Select all

PROCEDURE proc_result_set (IN instr VARCHAR(4000), OUT outstr VARCHAR(4000))

. . . and the call from DB2 CLP looks like this:

Code: Select all

CALL proc_result_set ('my string', ?)

. . . then it works in DB2, both the result set and the out param are returned. In ST, the params would be defined as:

Code: Select all

inParamRow := AbtCompoundType new
   addField: (AbtDatabaseVarCharField new
      name: inParamRowName;
      procBindType: AbtOdbcConstants::Sql_Param_Input;
      count: 4000);
   addField: (AbtDatabaseVarCharField new
      name: outParamRowName;
      procBindType: AbtOdbcConstants::Sql_Param_Output;
      count: 4000).

. . . and the stored proc can be called where the result sets are returned or the out param is returned but trying to get both, results in a DB2 error with the out param.

If you have this working using AbtProcedureResultSets, where both the out param and the result set is returned then please post a more complete example and it will be added to the documentation.

Thanks.

--tc