Error executing dynamic select query inside a stored procedure in IBM DB2

Question

What is wrong with the below code? I am trying to execute a dynamic query inside a stored procedure in DB2. My procedure is getting created

CREATE PROCEDURE  get_users( in offsetValue integer,
                              in sortCol varchar(20)            
                            )
  SPECIFIC get_users
  LANGUAGE SQL 

  Re:  BEGIN

    DECLARE SELECT_STATEMENT VARCHAR(8000);
    DECLARE SQL_STATEMENT STATEMENT;

    DECLARE c_emp CURSOR FOR SQL_STATEMENT;

    set SELECT_STATEMENT = 'select * from users;';

    PREPARE SQL_STATEMENT FROM SELECT_STATEMENT;

    EXECUTE SQL_STATEMENT; 

    open c_emp;
  END Re

But when I call the procedure using

call get_users(3, 'email_id');

I am getting the following error

SQL Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;select * from users;END-OF-STATEMENT, DRIVER=4.24.92

What might be wrong?

I have created a Cursor and later I am opening that cursor once the line is executed.. How else should I use the cursor to read the data ? — deepak asai, Nov 05 '18 at 08:24
Previously I executed the same using cursor. But it was not a dynamic query. Only when I introduce a dynamic query, I am facing this issue. — deepak asai, Nov 05 '18 at 08:37
I tried that. I gave me an Error: SQLCODE=-518. It means that there is an error while Preparing a statement. — deepak asai, Nov 05 '18 at 08:56

score 2 · Accepted Answer · answered Nov 05 '18 at 09:20

2

Too many errors... Try this:

--#SET TERMINATOR @

CREATE OR REPLACE PROCEDURE  get_users( in offsetValue integer,
                              in sortCol varchar(20)            
                            )
  SPECIFIC get_users
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL 

  Re:  BEGIN
    DECLARE SELECT_STATEMENT VARCHAR(8000);
    DECLARE SQL_STATEMENT STATEMENT;
    DECLARE c_emp CURSOR WITH RETURN FOR SQL_STATEMENT;

    set SELECT_STATEMENT = 'select * from users';
    PREPARE SQL_STATEMENT FROM SELECT_STATEMENT;
    open c_emp;
  END Re
  @

answered Nov 05 '18 at 09:20

Mark Barinstein

11,456
2
8
16

1

Depending on your code calling this SP, you may add 'WITH HOLD' clause to the cursor definition to avoid unexpected closing it if, for example, your calling code uses autocommit turned on and issues some other statements during fetching from the SP's result set... – Mark Barinstein Nov 05 '18 at 09:48
What is the print statement in db2? I have tried DBMS_OUTPUT.PUT_LINE. But it isn't working. Also, I tried SET SERVEROUTPUT ON. Even this isn't working – deepak asai Nov 05 '18 at 10:41
What tool do you use to print the results? What’s the exact sequence of calls you tried? This must work in the db2 command line processor (DB2 CLP). Other tools must call internally the corresponding SP to show you the contents of an internal buffer, where your dbms_outpt.put_line calls placed their output. – Mark Barinstein Nov 05 '18 at 11:08
how we can call the above procedure from java code?? – ashutosh Jan 21 '19 at 14:53
@ashutosh Look at the [Call the set of stored procedures implemented in SpServer.java](https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.sample.doc/doc/java_jdbc/s-SpClient-java.html) – Mark Barinstein Jan 21 '19 at 20:59

Error executing dynamic select query inside a stored procedure in IBM DB2

1 Answers1