ODBC

What does your call to your stored procedure look like?  Do you have any catches for errors to ensure that your call to the stored procedure did not fail or that you are truly connected to the database?   If you could post some code, we could probably get a better grasp of what you are trying to do.

We do a similar thing here where we send data to via a call to a stored procedure in order to a get back a row of data.   The data sent and the data returned is in a pre-defined order by the stored procedure.   We do not require the keyword of OUTPUT returned in order to retrieve the row of data.

Hope this helps…

Tom Rioux

When invoking an Sql Server Stored Procedure, you can have 4 kinds of datum.

There can be parameters exchanged between Cloverleaf(R) (your Tcl proc) and the invoked Stored Procedure.

There can be 3 types of parameters, INPUT (to the Stored Procedure from Cloverleaf) OUTPUT (from the Stored Procedure to Cloverleaf) and INPUT/OUTPUT type parameters. The last is where you send a parameter to the Stored Procedur and it returns something (perhaps different) in the same parameter. For Sql Server I do not recall how that is defined in the Stored Procedure or in your SQL BindParameter inside your Tcl proc so I won’t discuss the last parametr tyep.

Parameters are bound using a bind action and at that time are ,among other things, defined as INPU or OUTPUT.

These are complement sets.

That is an OUTPUT parameter in Cloverleaf (the invoker) is an Input paramter in the Stored Procedure and vice-versa.

In Sql Server Stored Procedure you do not have to explicitly specify an INPUT parameter as INPUT – BUT – you do have to specifically declare an output parameter as OUTPUT.

Of course if the Stored Procedure is coded that way the fourth datum you could receive is a populated recordset.

In SQL Server ODBC, you ALWAYS get back a recordset – even if the Stored Procedure does not populate one. So assuming you are invoking a Stored Procedure to update a Table, you would not expect the Stored Procedure to populate a recordset. However there will be a recordset (empty) anyway.

So in order to get to the OUTPUT parameters (assuming they are defined correctly in the Stored Procedure) you first have to exhaust the recordset (even if it is empty).

The OUTPUT parameters are ‘hiding’ behind the recordset and you need to get through the recordset to get the OUTPUT paramerts from the Stored Procedure.

I don’t believe the Stored Procedure is returning a recordset but rather the OUTPUT parameters you bound.

BUT SQL Server hides the Output Parameters behind a result set (even if one is NOT populated by the Stored Procedure, there will be a default recordset) as described in my earlier post.

So that explains the 0 columns -and all is as expected.

But now you need to get past that recordset. To do that you need to do a SQLMoreResults >stmnt handle< until you get a return of SQL_NO_DATA_FOUND. That means you have exhausted the recordset (should only take one or two executions – I use a while just to make sure).

Once you receive SQL_NO_DATA_FOUND, the Tcl variable you bound to the Output parameters (the ones you defined as INPUT and the Stored Procedure defined as OUTPUT) will have the values the Stored Procedure provided. Just use them as you see fit.

I want to clarify a mistake I made in this discussion so that others don’t get as confused as I apparently am.

I aseem to have problems getting this straight:

When binding the parameters in Tcl those that are INPUT to the Stored Procedure should be defined as INPUT (not OUTPUT as I earlier stated).

The parameters that OUTPUT from the Stored Procedrue should be defined as OUTPUT in the Tcl bind statement rather than INPU as I earlier stated.

Sorry for the confusion… 🙄