IDS Forum

Re: error 201 when using functional index

Posted By: Jonathan Leffler
Date: Friday, 27 November 2009, at 9:09 a.m.

In Response To: error 201 when using functional index (LYNKZ MIKE)

The problem is that I4GL, like ISQL, thinks that statements end at
semi-colons, and functions are created with multiple semi-colons. You can't
do CREATE FUNCTION like that in I4GL (or, indeed, in ESQL/C, which is what
I4GL uses under the covers).

The quickest workaround is probably to build the create function statement
into a string and EXECUTE IMMEDIATE it (or PREPARE and EXECUTE and FREE it).

You could try an SQL ... END SQL block, but I doubt if it will handle the
embedded semi-colons any better.

Also remember to run with WHENEVER ERROR STOP or check the result from each
SQL statement religiously.

On Thu, Nov 26, 2009 at 12:40, LYNKZ MIKE <yellr@telecom.com.co> wrote:

> Good afternoon, we are having a rare problem when we are using a functional
> index. There is a 4GL which gives error 201 when there is create a
> functional
> index.
>
> We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
>
> The 4GL application get broken if this functional index is created:
>
> create table "informix".edcve
> (
>
> cod1 decimal(8,0) not null ,
>
> cod2 decimal(4,0) not null
> );
>
> CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)
>
> WITH (NOT VARIANT);
>
> RETURN cod1 ;
>
> END FUNCTION;
>
> create index ix_edcve on edcve(f_test1(cod1));
>
> update statistics for table edcve;
>
> Debugging and using the onmode -I we have found the 4GL stops with error
> 201
> when this sql is executed:
>
> select * from edc_det where cod_emp =? and cod_pto =? and num_edc =? order
> by
> cla_ent desc
>
> When this index is not created the 4GL does not get broken.
> Before this sql statement the 4GL querys the table in the following way:
> select * from edcdev where cod1=48
>
> Both queries dont get broken if are executed in dbaccess.
>
> Anyone knows why is this happening??
>
> Thanks a lot in advanced.
>

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Ted Turner <http://www.brainyquote.com/quotes/authors/t/ted_turner.html> -
"Sports is like a war without the killing."

--000e0cd2df6c6c67b304795ad789

Messages In This Thread

• error 201 when using functional index
  LYNKZ MIKE -- Thursday, 26 November 2009, at 3:40 p.m.
  • RE: error 201 when using functional index
    Art S. Kagel -- Thursday, 26 November 2009, at 6:57 p.m.
    • Re: RE: error 201 when using functional index
      LYNKZ MIKE -- Friday, 27 November 2009, at 8:52 a.m.
      • Re: RE: error 201 when using functional index
        Jonathan Leffler -- Friday, 27 November 2009, at 9:25 a.m.
        • Re: RE: error 201 when using functional index
          LYNKZ MIKE -- Friday, 27 November 2009, at 9:51 a.m.
          • Re: RE: error 201 when using functional index
            Jonathan Leffler -- Friday, 27 November 2009, at 10:02 a.m.
            • Re: RE: error 201 when using functional index
              LYNKZ MIKE -- Friday, 27 November 2009, at 10:49 a.m.
              • Re: RE: error 201 when using functional index
                Jonathan Leffler -- Friday, 27 November 2009, at 12:40 p.m.
                • Re: RE: error 201 when using functional index
                  LYNKZ MIKE -- Friday, 27 November 2009, at 2:30 p.m.
      • Re: RE: error 201 when using functional index
        Art Kagel -- Friday, 27 November 2009, at 11:18 a.m.
        • Re: RE: error 201 when using functional index
          LYNKZ MIKE -- Friday, 27 November 2009, at 2:23 p.m.
          • Re: RE: error 201 when using functional index
            Art Kagel -- Friday, 27 November 2009, at 2:46 p.m.
  • Re: error 201 when using functional index
    Jonathan Leffler -- Friday, 27 November 2009, at 9:09 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.