Cursor not open

Question

I'm trying to create table using statements stored in table. I'm using a cursor with execute(sql) as below:

create table tab
(
  CreateSql varchar(250)
)

insert into tab values ('create table tab1 (id int )')
insert into tab values ('create table tab2 (id int )')
insert into tab values ('create table tab3 (id int )')

  declare cur cursor for
    select createsql
      from tab  

  declare @sql varchar(255)

  open cur
  fetch cur into @sql

  while @@SqlStatus = 0
    begin           
      execute(@Sql)               
      fetch cur into @sql
    end

  close cur
  deallocate cursor cur

If I run it then appears an error :

Could not execute statement. Cursor not open SQLCODE=-180, ODBC 3 State="34000"

The first table (tab1) will create, but other (tab2, tab3) tables not.

If I replace statement execute(sql) with select sql, script will work correctly.

Thanks in advance for your answer.

PX

Answer 1

IQ likes to close the cursor after the first commit.

You need to use the WITH HOLD clause (or something along those lines) to keeps the cursor open.