Rob,
Sorry, I think I must have somehow missed your first email.
Firstly there are some examples of calling procedures in the t subdir of
DBD::ODBC - these should help you. Secondly, you need to look up
odbc_more_results in the DBD::ODBC pod.
ODBC does not have and does not need the sybase attributes you mention. In
general a procedure can contain inserts/updates/selects, take input
parameters, write output parameters and return a value. As the procedure runs,
each action returns a result of some form and by testing NUM_OF_FIELDS you can
tell if the next result is a select or an insert/update. You move through the
individual operations in the procedure by calling odbc_more_results. The final
procedure return value and any output parameters are not available until
odbc_more_results returns false, indicating the procedure is complete.
e.g. suppose your procedure was something like:
procedure (in a integer, out b integer)
insert into table (col1, col2) values (a,2)
set b = 2
select * from table
return 0
and you call it with { ? = call myproc(1,?)}.
When you first call the procedure, the first thing done is the insert and
NUM_OF_FIELDS will not be defined as there is no result-set. Calling
odbc_more_results moves to the select statement and NUM_OF_FIELDS will be 2.
You can now call fetch to retrieve the results until all the result-set is
retrieved. Now call odbc_more_results again and it will complete the procedure
and return false so you know you can access the return value and the output
parameter b.
Things get a little more complex for what I guess Sybase calls CS_MSG_RESULT
which I'm guessing would be what happens if you did a print in the procedure
but here again, there is an example in the t subdir.
Hope this helps.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 07-Jun-2006 Rob Biedenharn wrote:
> As it's been a couple weeks and I don't think that I've seen any
> response to this, I'd like to either refresh the question or ask if
> there's someone who could at least say "Sorry, no way to do that with
> DBD::ODBC."
>
> The use of the Sybase-specific attributes is either telling me that
> ODBC has to have the same kind of thing, or DBI just can't give you
> this information generally.
>
> -Rob
>
> On May 24, 2006, at 3:36 PM, Rob Biedenharn wrote:
>
>> I hope someone has run into the specific problem or at least knows
>> how DBD::ODBC holds the status of a stored procedure. The
>> particular database is Microsoft Sql Server.
>>
>> With Sybase and DBD::Sybase, the status from a stored procedure was
>> returned as a result of type CS_STATUS_RESULT() (i.e., 4043 from
>> the perldoc)
>>
>>> You can get the type of the current result set with
>>> $sth->{syb_result_type}. This returns a numerical value, as
>>> defined in
>>> $SYBASE/include/cspublic.h:
>>>
>>> #define CS_ROW_RESULT (CS_INT)4040
>>> #define CS_CURSOR_RESULT (CS_INT)4041
>>> #define CS_PARAM_RESULT (CS_INT)4042
>>> #define CS_STATUS_RESULT (CS_INT)4043
>>> #define CS_MSG_RESULT (CS_INT)4044
>>> #define CS_COMPUTE_RESULT (CS_INT)4045
>>>
>>> In particular, the return status of a stored procedure is
>>> returned as
>>> CS_STATUS_RESULT (4043), and is normally the last result set
>>> that is
>>> returned in a stored proc execution.
>>
>> The docs for DBD::ODBC don't seem to deal with stored procedures at
>> all. There's something that might be a hint under
>> odbc_force_rebind, but only in that a procedure might produce
>> multiple result sets and you "shouldn't have to worry about this."
>>
>> I'm thinking that a generic DBI way of obtaining this information
>> would have been mentioned in the DBD::Sybase documentation rather
>> than the syb_result_type. However, I'm holding out hope for a way
>> to determine this information.
>>
>> If you made it this far and are thinking "Too bad he didn't ask
>> about OUTPUT parameters", I'll throw that one out too: How do I
>> get the OUTPUT parameters from a stored procedure via ODBC to Sql
>> Server? (Under DBD::Sybase, I'd see $sth->{syb_result_type} ==
>> CS_PARAM_RESULT (), but no such luck with Sql Server, yet.)
>>
>> -Rob
>>
>> Rob Biedenharn http://agileconsultingllc.com
>> [EMAIL PROTECTED]
>> +1 513-295-4739
>>
>>
>
