[archive] AcuSQL cursor question

[Migrated content. Thread originally posted on 12 August 2008]

Hello,

I'm new to AcuSQL and I have a question about cursors. I'm trying to write a program that takes a query string from the user, declares a cursor for a select statement, and fetches the results returned by the query. I've managed to do this successfully.

The problem occurs when I try run a second query, using a different user-supplied string. The results returned by the second query are identical to the first set of results. The third time I try it, I get the error message "Duplicate cursor name".

I declare, open, and then close the cursor every time, in addition to preparing the statement, but it seems that the cursor (or the prepared statement) doesn't get completely closed.

My question is this: Is it possible to free the cursor in order to reuse it for a different query? Or am I just going about it the wrong way?

Here's the code I'm using:

    
      * run query using user-supplied string query-d1
           exec sql
              prepare user-query from :query-d1
           end-exec.

           exec sql
               declare cursor-q cursor for user-query
           end-exec.

           exec sql
                   open cursor-q
           end-exec.

      * fill listbox
           perform until sqlcode not = 0

               exec sql
                   fetch cursor-q into :h-record
               end-exec
    
               if sqlcode = 0
                   move h-id           to id-c1
                   move h-oms          to oms-c1
                   move h-ind          to ind-c1
                   display result-box
               end-if

           end-perform.

           exec sql 
               close cursor-q 
           end-exec.


Thanks,

Claire