ODBC

[James Sedlmeyer]

I’m working on an ODBC call to a SQLSERVER stored procedure. I’m sending the procedure 4 input fields and expecting 5 output fields returned to me. My script is kind of rough as I’m just trying to hard code the input values with the attempt to retrieve one row of data. So far I have not been able to retrieve a row of data.  The person writing the stored procedure stated that she did not need the key word OUTPUT included when defining the output variables that will be returned to me. I think OUTPUT needs to be included in the SP for each field that is to be returned to my tcl app. Any input would be appreciated.

Thanks

Topic

[Tom Rioux]

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

[Jim Kosloskey]

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.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[James Sedlmeyer]

Jim

Again thanks for your help. The stored procedure now has OUTPUT applied to the fields that are returned to my odbc call. It looks like I’m getting farther. My script is currently ends where I’m checking for number_of_results just after the call to the stored procedure. I’m hard coding input values to the SP that I’m told are in the database. However I’m still getting zero for number_of_results. Below is the information returned after my odbc sp call.

Connecting To The Database

SQL_SUCCESS_WITH_INFO Successful Connection To Database

SQL_SUCCESS Sql Statement Handle Allocated

Prepare works

BIND SUCCESS

BIND SUCCESS

BIND SUCCESS

BIND SUCCESS

BIND SUCCESS

BIND SUCCESS

BIND SUCCESS

BIND SUCCESS

BIND OF SQL_NUMERIC SUCCESS

storedproc = spNDCTransform ‘06310270’, ‘5’, ‘7.00’, ‘OHAC’, ”, ”, ”, ”, ”;

NDC: => Stored Proc Successful

error = SQL_SUCCESS

number of results = 0

Attached is my proc. I’m sending 4 input parms and expecting 5 output parms returned. At this point I’m just trying to get a row of data returned. I’m told the record is in the db but I do not have access to confirm. I’m hoping it is now something I have wrong. Any suggestions would help.

[Jim Kosloskey]

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.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

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… 🙄

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Author]

Replies