Hi All,

We're running IDS 11.50.FC6 on RHEL 5.

I'm having a problem with a prepared cursor in an SPL stored procedure that uses the "matches" keyword.  The statement is only working on the first use of the cursor, after that it fails.

I made a test procedure to illustrate:

CREATE FUNCTION spl_test()

  RETURNING
    char(20) as tabname1,
    char(20) as tabname2

  DEFINE f_tabname1 char(20);
  DEFINE f_tabname2 char(20);
  DEFINE f_matches char(20);
  DEFINE f_stmt char(500);

  SET DEBUG FILE TO "spl.out"; trace on;

  -- This one only works the first time.
  LET f_stmt = "select tabname from systables where tabname matches ?";

  -- This one works fine.
  --LET f_stmt = "select tabname from systables where tabname = ?";

  PREPARE s1 FROM f_stmt;
  DECLARE c1 CURSOR FOR s1;

  FOREACH
    select tabname
      into f_tabname1
      from systables
      where tabname matches 'syscol*'
      order by 1

    OPEN c1 USING f_tabname1;

    WHILE (1 = 1)
      FETCH c1 INTO f_tabname2;
      if sqlcode = 100 then
        exit while;
      end if

      RETURN f_tabname1, f_tabname2 with resume;
    END WHILE

    CLOSE c1;

  END FOREACH

END FUNCTION

The expected output would be:

tabname1             tabname2
syscolattribs        syscolattribs
syscolauth           syscolauth
syscoldepend         syscoldepend
syscolumns           syscolumns

But this is what I'm getting:

tabname1             tabname2
syscolattribs        syscolattribs
syscolauth           syscolattribs
syscoldepend         syscolattribs
syscolumns           syscolattribs

So on the second time using the cursor, it's not using the correct value for some reason.  But if I replace "matches" with "=", it works fine.

What am I doing wrong?  Could this be a bug?

Thanks,

Sean.

The "FREE c1;" statement is what I was missing.  Works fine now!

Thanks Doug!

Sean.

-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Doug Lawry
Sent: Friday, October 03, 2014 11:10 AM
To: informix-list@iiug.org
Subject: Re: Problem with SPL stored procedure, prepared statement, and "matches"

I have reproduced the problem with 11.50.FC9 whereas it works as intended with 11.70.FC4. A work-around is to move the DECLARE to just before the OPEN and add "FREE c1;" after "CLOSE c1;".

Regards,
Doug Lawry
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

I have reproduced the problem with 11.50.FC9 whereas it works as intended with 11.70.FC4. A work-around is to move the DECLARE to just before the OPEN and add "FREE c1;" after "CLOSE c1;".

Regards,
Doug Lawry