Question Re: execute immediate with arbitrary result set in Technology Q&A

execute immediate with arbitrary result set

Former Member — Thu, 19 Mar 2015 10:41:13 GMT

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

Re: execute immediate with arbitrary result set

VolkerBarth — Thu, 19 Mar 2015 11:17:35 GMT

The docs tell the following for SQLCODE -866 (SQLE_BAD_RESULT_SET):

Probable cause
When calling a procedure in the FROM clause of a SELECT statement, the expected schema of the result is taken from the WITH clause. If no WITH clause is given, the expected schema is taken from the system catalog. The result set returned from the call must match the expected schema or it must be possible to convert every column so that it matches.

So what does the system catalog reveal for this procedure?

select SPP.*
from sysprocparm SPP key join sysprocedure SP
where proc_name = 'tc_get_parametre_valeur_table'
   and parm_type = 1
order by parm_id


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.

Re: execute immediate with arbitrary result set

Former Member — Thu, 19 Mar 2015 13:21:37 GMT

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.

Re: execute immediate with arbitrary result set

Breck_Carter — Thu, 19 Mar 2015 14:40:10 GMT

Try turning ~~client statement~~ plan caching off altogether (you are probably interested in the Power And Glory Of SQL rather than Third-Order Performance Effects anyway 🙂

~~See max_client_statements_cached option~~

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.

Re: execute immediate with arbitrary result set

VolkerBarth — Fri, 20 Mar 2015 05:23:23 GMT

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).

Re: execute immediate with arbitrary result set

VolkerBarth — Fri, 20 Mar 2015 05:24:20 GMT

Could you show some of the generated SQL statements containing the procedure call?

Re: execute immediate with arbitrary result set

Breck_Carter — Sun, 22 Mar 2015 08:50:30 GMT

yes, indeed... corrections have been made

Re: execute immediate with arbitrary result set

Former Member — Tue, 24 Mar 2015 09:15:03 GMT

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.

Re: execute immediate with arbitrary result set

Former Member — Tue, 24 Mar 2015 09:27:34 GMT

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.

Re: execute immediate with arbitrary result set

Breck_Carter — Tue, 24 Mar 2015 09:43:29 GMT

If the code changes often then caching will not help... but turning it off might make it work.

Re: execute immediate with arbitrary result set

VolkerBarth — Tue, 24 Mar 2015 11:17:35 GMT

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!).

Re: execute immediate with arbitrary result set

VolkerBarth — Tue, 24 Mar 2015 11:31:57 GMT

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...