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

asked 19 Mar '15, 10:41

girouxlp's gravatar image

girouxlp
66127
accept rate: 0%

edited 19 Mar '15, 11:18

Volker%20Barth's gravatar image

Volker Barth
29.6k294444650

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.

(19 Mar '15, 11:17) Volker Barth

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.

(19 Mar '15, 13:21) girouxlp
Replies hidden

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

(20 Mar '15, 05:24) Volker Barth
Comment Text Removed

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.

(24 Mar '15, 09:27) girouxlp

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

(24 Mar '15, 11:17) Volker Barth

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

(24 Mar '15, 11:31) Volker Barth
showing 2 of 6 show all flat view

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.

permanent link

answered 19 Mar '15, 14:40

Breck%20Carter's gravatar image

Breck Carter
27.1k424582831
accept rate: 21%

edited 22 Mar '15, 08:49

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×194
×102
×10

question asked: 19 Mar '15, 10:41

question was seen: 1,296 times

last updated: 24 Mar '15, 11:31