|
Hello, I recently updated from sybase 9 to 16, big jump! With minimal modifications, everything is working, except one stored procedure that uses execute immediate to return a table with arbitrary columns, row and types. The error I get is: "returned a result set with a different schema than expected SQLCODE=-866, ODBC 3 State="HY000"". So this is the code: /////////////////////////////////////////////////////////////
ALTER PROCEDURE TouchDB.tc_get_parametre_valeur_table(in arg_id_session char(40), in arg_id_parametre integer, in arg_valeur long varchar)
begin
declare ls_select long varchar;
declare ls_executeur long varchar;
declare ls_dynamique char(1);
select tc_parametre.executeur, tc_type_parametre.dynamique into ls_executeur, ls_dynamique
from dba.tc_parametre INNER JOIN dba.tc_type_parametre on
tc_parametre.id_tc_type_parametre = tc_type_parametre.id_tc_type_parametre
where id_tc_parametre = arg_id_parametre;
if ls_executeur IS NOT NULL AND ls_dynamique IS NOT NULL THEN
set ls_select = 'select * from ' || ls_executeur || '(' || arg_valeur || ')';
execute immediate WITH RESULT SET ON ls_select;
else
select '';
end if;
end;
/////////////////////////////////////////////////////////////
Any idea? Thanks for the help and have a nice day, Louis-Philipe |
|
Try turning
See max_plans_cached There have been a bazillion performance [cough] improvements between V9 and 16 so it's quite possible one of them is biting you. Note, in my understanding client statement caching should only have an effect here if the same client issues an identical SQL statement (i.e. including the same parameters IMHO) several times - whereas the statement with the contained procedure call seems to vary between calls... (at least that's my impression).
(20 Mar '15, 05:23)
Volker Barth
Replies hidden
yes, indeed... corrections have been made
(22 Mar '15, 08:50)
Breck Carter
Hello, Thanks Breck for the suggestion. Since the calls tend to change often, I'm not sure it can be used here, but I will do some experimenting to see the new potential.
(24 Mar '15, 09:15)
girouxlp
Replies hidden
If the code changes often then caching will not help... but turning it off might make it work.
(24 Mar '15, 09:43)
Breck Carter
|
The docs tell the following for SQLCODE -866 (SQLE_BAD_RESULT_SET):
So what does the system catalog reveal for this procedure?
How dou you use this proc inside the FROM clause when its result set will return a different schema dependent on the call?
A solution might be to dispense with the dynamic result set and to add a RESULT SET clause to the procedure's definition and to simply return no row (but the same schema) when the ELSE branch is executed.
Hello Volker, Thanks for the kick reply. I realize that some calls actually works. Weirdly, if the stored procedure called doesn't have any in parameters, the call failed, otherwise it work. Cannot explain that one...
To answer your question, this is use by a c# platform that create dynamic application on the fly using fun stuff like reflection, generic programming and late binding.
Could you show some of the generated SQL statements containing the procedure call?
Here's 3 examples:
select * from TouchDB.tc_get_employe_par_depart_data('005'); select * from TouchDB.tc_get_employe_absence_data('005','100001'); select * from TouchDB.tc_get_employe_par_compagnie_data('')The last one shouldn't have a parameter since it's not used, but the call fail otherwise. The calls are simple, but the power come from the dynamic nature of the beast.
Well, I had thought you would need to join the procedure calls with other tables (though that was just my impression, obviously, based on the error code). - If you "just" have to access the result set of a procedure call, I would recommend to call them directly with
call TouchDB.tc_get_employe_par_depart_data('005');and the like. AFAIK, that should prevent the -866 error, as the mechanism to describe result sets is somewhat different between direct calls and proc calls within a FROM clause. (I'm by no means aware of those details!).
If I read that enhanced doc topic correctly, then you could also use SELECT ... FROM proc() when adding an according WITH clause to the FROM clause dynamically - but then you would need both execute immediate in the procedure and in the select statement that uses the proc, and that would not be very useful IMHO...