Strange behavior in SPL
I've tried multiple times to post this, but my browser keeps timing out.
Apologies in case it gets posted repeatedly.
This is on Informix 11.5.FC5, Solaris 10, Sun T2000.
I was always told that one of the performance benefits of stored procedures
was that several of the steps were performed at the time that the procedure
was created, thus saving that overhead at runtime. Among the things that I
thought were handled at create time were lexical scan, parsing, syntax check,
and query path evaluation / selection.
Based on this, I am a bit confused by some behavior in our environment that
makes me question what it means that procedures are 'compiled' when stored in
IDS. While it's possible that 'compiled' could simply mean 'tokenized,'
there's a certain expectation of reporting syntax errors that comes with the
concept of 'compiling.' Please consider the following table and simple stored
procedure:
CREATE TABLE my_table (my_integer INTEGER, my_date DATE);
CREATE PROCEDURE myIIUGproc()
define hv_my_date DATE;
select my_date
into hv_my_date
from my_table
where my_integer = 1;
set debug file to my.dbg;
trace hv_my_date;
end procedure;
This compiles and executes in dbaccess fine. However, changing "select
my_date" to "select my_dat" also compiles cleanly. Of course, at runtime it
generates the following execution error:
217: Column (my_dat) not found in any table in the query (or SLV is undefined).
If the procedure is being compiled before being stored, shouldnt such a
syntax error be reported?
As a follow-up test, I modified the procedure to refer to a non-existent table:
CREATE PROCEDURE myIIUGproc()
define hv_my_date DATE;
select my_date
into hv_my_date
from my_tab
where my_integer = 1;
set debug file to my.dbg;
trace hv_my_date;
end procedure;
Again, it compiled fine. Of course, at execution time we received a -206 error.
Why are these errors not caught when the procedure is created?