Problem with EXECUTE IMMEDIATE

[Marcos Cunha Lima]

Hi everyone

We are migrating from SQLAnywhere version 7 to version 12 and are
going through a strange problem regarding EXECUTE IMMEDIATE.

I have the following procedure ( I made it simpler than it actually is
just to debug the problem ) that's called from another procedure:

CREATE PROCEDURE FOO()
DECLARE xComandoTeste LONG VARCHAR;

set xComandoTeste = 'SELECT bloqueado FROM SISTEMA';
Message xComandoTeste;
execute immediate WITH RESULT SET ON STRING ( xComandoTeste );
if @@rowcount > 0 then
raiserror 17000 'ERROR'
end if;
END;

The problem is that I always get an error when I execute the EXECUTE
IMMEDIATE statement. More strange is that it doesn't show the error
just go to the last line of the procedure (doesn't execute the if
@@rowcount...) and the application that calls this procedure freezes.
The @@error variable is set to -6.
It works fine in the ISQL and it has been working in version 7.

Does anyone have a clue about this behaviour?

Best regards

[cjd]

Post by Marcos Cunha Lima
Hi everyone
We are migrating from SQLAnywhere version 7 to version 12 and are
going through a strange problem regarding EXECUTE IMMEDIATE.
I have the following procedure ( I made it simpler than it actually is
CREATE PROCEDURE FOO()
DECLARE xComandoTeste LONG VARCHAR;
 set xComandoTeste = 'SELECT bloqueado FROM SISTEMA';
    Message xComandoTeste;
    execute immediate WITH RESULT SET ON STRING ( xComandoTeste );
      raiserror 17000 'ERROR'
    end if;
END;
The problem is that I always get an error when I execute the EXECUTE
IMMEDIATE statement. More strange is that it doesn't show the error
just go to the last line of the procedure (doesn't execute the if
@@rowcount...) and the application that calls this procedure freezes.
It works fine in the ISQL and it has been working in version 7.
Does anyone have a clue about this behaviour?
Best regards

Marcos,
Perhaps your previous isql had by default = process all result sets.
Make sure that your client ( eg dbisql , etc. ) is able ( set its
option on ) to go through all the result sets . Otherwise it will only
execute up to and including the first result set in your stored proc .
Saludos,
Carlos