Re: Declare cursor in dynamic ESQL

You do not need the declare cust cursor within the cmd string. Also, you aren't setting up the cursor correctly. To do so you need to prepare the statment, declare a cursor for it, then open and fetch from the cursor. just do: sprintf(cmd, "select cust_id, m_org_name, m_first_name, m_last_name, m_city from custinfo2 where m_org_name like \\"HUNTER\\" order by m_org_name;"); EXEC SQL prepare cmd_stmt from :cmd; EXEC SQL declare cmd_curs cursor for cmd_stmt; EXEC SQL open cmd_curs; loop EXEC SQL fetch cmd_curs INTO ...; end loop Something else you may want to think about is the use of placeholders in the statement. This way you won't have to re prepare every time the m_org_name changes. i.e. where m_org_name like ? Also, since you're not using wildcards you should consider where m_org_name = ? This will aid the optimizer. -Bill } } DOH! In my efforts to simplify the version I posted, I created } a syntax error in the SQL statement. Below is the full, unedited } version from my source code. The non-dynamic version of the command } generates no warnings. } } Obi-Wan (bvh@inetnebr.com) wrote: } } We're running Informix 7.1 on Solaris 2.5.1 using the GNU C compiler. } } No preprocessor or compiler warnings are given. } } } } Hardcoding the ESQL statement: } } } } EXEC SQL declare cust cursor for } } select cust_id, m_org_name, m_first_name, } } m_last_name, m_city } } from custinfo2 } } where m_org_name like :m_org_name and } } m_first_name like :m_first_name and } } m_last_name like :m_last_name and } } m_city like :m_city } } order by m_org_name; } } EXEC SQL open cust; } } } } works just fine. However, I now need to vary the "where" clause } } based on several conditions. I assume that using the prepare/execute } } features of dynamic ESQL is what I want. When I do: } } } } sprintf(cmd, "declare cust cursor for select cust_id, m_org_name, m_first_name, m_last_name, m_city from custinfo2 where m_org_name like \\"HUNTER\\" order by m_org_name;"); } } EXEC SQL prepare cmd_id2 from :cmd; } } EXEC SQL execute cmd_id2; } } EXEC SQL open cust; } } } } I get the following errors: } } } } after prepare, SQLSTATE = "42000" (Syntax error or access violation) } } after execute, SQLSTATE = "37000" (Syntax error or access violation in } } PREPARE or EXECUTE IMMEDIATE) } } after open, SQLSTATE = "07003" (Cursor specification cannot be executed) } } } } 'cmd' is declared inside a DECLARE SECTION. } } } } What am I doing wrong? Is there a better way to go about this? } } -- } Ben "Obi-Wan" Hollingsworth email: bvh@inetnebr.com } Vice President and System Administrator voice: (402)434-8680 } Internet Nebraska -- Nebraska's on-ramp to the information superhighway } -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net