Problem with SPL stored procedure, prepared statement, and "matches"

[Sean Baker]

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.

[Sean Baker]

In my actual stored procedure, I need to use an asterisk. The sample just illustrates the problem.

The reason I'm creating the cursor outside of the foreach is that the query is different depending on some conditions. If I try to create a cursor within the foreach, I get an error. Unless there's another way to do that?

I'll have to come up with a workaround.

Thanks,

Sean.

From: Art Kagel [mailto:***@gmail.com]
Sent: Friday, October 03, 2014 10:59 AM
To: Sean Baker
Cc: informix-***@iiug.org
Subject: Re: Problem with SPL stored procedure, prepared statement, and "matches"

Sean:

Since there are no wildcards in the tabnames fetched by the FOREACH query the operator should be "=" anyway not matches.  That said, I would agree that this is a bug.

If this is just an example, and you really do need to use matches, then the workaround would be to build the query inside the FOREACH loop:

CREATE FUNCTION spl_test()

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

  DEFINE f_tabname1 lvarchar(20);
  DEFINE f_tabname2 lvarchar(20);
  DEFINE f_matches char(20);
  DEFINE f_stmt, t_base lvarchar(500);

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

     
 LET f_base = "select tabname from systables where tabname matches '";

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

 LET f_stmt = f_base || trim(tabname1) || "'";

 PREPARE s1 FROM f_stmt;
 DECLARE c1 CURSOR FOR s1;
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
Art



Art S. Kagel, President and Principal Consultant
ASK Database Management

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.


On Fri, Oct 3, 2014 at 1:29 PM, Sean Baker <***@moneymailer.com> wrote:
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.

_______________________________________________
Informix-list mailing list
Informix-***@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[Doug Lawry]

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

[Sean Baker]

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

Thanks Doug!

Sean.

-----Original Message-----
From: informix-list-***@iiug.org [mailto:informix-list-***@iiug.org] On Behalf Of Doug Lawry
Sent: Friday, October 03, 2014 11:10 AM
To: informix-***@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