IDS Forum

Two questions about SPL behavior

Posted By: MARK COLLINS
Date: Tuesday, 27 April 2010, at 3:20 p.m.

My apologies if this appears multiple times, but each time I try to Post Message, my browser times out. I have checked, and this is not appearing in the main index, so I'm attempting again to post this.

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 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, shouldn’t such a syntax error be reported?

As a follow-up test, I modified the procedure to select from 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 compiles with no reported errors, but we receive a -206 error at runtime.

In an attempt to force such errors to be reported at compile time, having read the statement: “If you do not use the WITH LISTING IN option, the compiler does not generate a list of warnings.” I changed this test procedure as follows:

CREATE PROCEDURE myIIUGproc()

define hv_my_date DATE;

select my_dat – no such column

into hv_my_date

from my_table

where my_integer = 1;

set debug file to “my.dbg”;

trace hv_my_date;

end procedure with listing in “/my/full/path/name/my.lst”;

but storing that procedure received no listing file. Having read “with listing file is only for compiles performed after it is stored”, I then issued the statement “UPDATE STATISTICS FOR PROCEDURE myIIUGproc” but still no listing file. I then set the environment variable DBANSIWARN to 1, received a compile-time warning as follows:

Warning: Statement uses Informix extension to ANSI/ISO SQL syntax.

and still received no listing file, even when updating statistics for this procedure.

My primary question is whether there’s a way to compile stored procedures to report syntax errors such as references to a nonexistent columns. My secondary question is what I’m doing wrong trying to generate a file with a list of compile-time warnings.

Messages In This Thread

• 
  --
  • Re: Two questions about SPL behavior
    Khaled Bentebal -- Wednesday, 28 April 2010, at 12:41 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.