DBD::Sybase and $sth->{syb_result_type} v. DBD::ODBC (Sql Server)

--Apple-Mail-64-540962185
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

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
Rob@AgileConsultingLLC.com
+1 513-295-4739



--Apple-Mail-64-540962185--

Re: DBD::Sybase and $sth->{syb_result_type} v. DBD::ODBC (Sql Server)

--Apple-Mail-11--392404029
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

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
> Rob@AgileConsultingLLC.com
> +1 513-295-4739
>
>


--Apple-Mail-11--392404029--

Re: DBD::Sybase and $sth->{syb_result_type} v. DBD::ODBC (Sql Server)

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
>> Rob@AgileConsultingLLC.com
>> +1 513-295-4739
>>
>>
>