Re: 9th's bug
I wrote:
> rotor wrote:
> > Hi community!
> >
> > IDS 9.40.TC6
> >
> > CREATE TABLE t1 ( f NVARCHAR( 10 ) );
> > CREATE TABLE t2 ( f NVARCHAR( 10 ) NOT NULL );
> > CREATE PROCEDURE p( p CHAR( 10 ) ) END PROCEDURE;> >
> > EXECUTE PROCEDURE p( ( SELECT f FROM t2 ) );> > -- Oops! SQL Error (-674) : Routine (p) can not be resolved.
> >
> > But if the folowed SQL will be executed first:
> > EXECUTE PROCEDURE p( ( SELECT f FROM t1 ) );> > then the problematic statement also works properly. Funny?
> >
> > Any comments awaiting...
> >
>
> I am surprised that the first 'EXECUTE' ever works at all. Page 2-217
> of the IBM Informix Guide to SQL: Syntax manual (ct1sqna) says the
> following about NULL as a default value:
>
> If you specify no default value for a column, the default is NULL unless
> you place a NOT NULL constraint on the column. In this case, no default
> exists.
>
> If you take the full definition of error -674 into account, in
> particular the portion that suggests that you may not be specifying the
> correct number of arguments, I would assume that your empty table with
> the NOT NULL constraint is returning nothing and that you are, in
> effect, calling the procedure without any arguments. I would further
> guess that your second 'EXECUTE' calls the procedure with one argument -
> NULL.
>
> I certainly don't have an answer for you, but I'd be happy to know if
> anyone can tell me just how far off I am on how I'm putting these pieces
> together. I'd also be very curious to find out why the first 'EXECUTE'
> would work after the second 'EXECUTE' was run. Something retained in
> memory, maybe? I suspect there is something off here.
I'm at work now, running the examples that rotor gave, and am trying
different data types as Ben Thompson suggests. I get the same results as
Ben when I use the same date types within the tables and procedure. (That
still doesn't explain the second execute/first execute behavior difference
that rotor initially reported, but... I suppose I can stop second-guessing
myself on the NULL versus NOT NULL point. )
--
June Hunt