Sender Jdbc ISSUE

[Former Member]

I am trying to execute a stored procedure in the sender JDBC. I am using EXECUTE <stored_proc name>.But it returns the error “The JDBC driver returned the following error message: 'java.sql.SQLException: [SQL0084] SQL statement not allowed” What could be issue?

AR

[bhavesh_kantilal · Accepted solution]

Hi,

Are you trying to connect to an Oracle DB?

If yes, then Sender JDBC adapters do not support Oracle Stored Procedures. Reason being that Sender JDBC adapters expect a Resultset as its output, but Oracle Stored Procedures return a Cursor.

Regards,

Bhavesh

[Former Member · Accepted solution]

No its not an Oracle DB. It’s a DB2 database. Looks like the driver cannot identify the EXECUTE statement. I guess the syntax for the SQL query is EXECUTE <stored procedure name>..Anything that I missed in other settings?

[bhavesh_kantilal · Accepted solution]

Antonio,

The best way to check if the Statement is correct is to make execute the stored procedure from your DB client.

Also, make sure DB2 returns a Resultset for its Stored Procedure and not cursors. And, make sure that there is a Stored Procedure contains exactly one Select statment.

regards,

Bhavesh

[Former Member · Accepted solution]

I am checking the stored procedure, and is it ok to use a read statement instead of a select inside the stored procedure. As all data in DB2, are stored as files. The primary condition for XI driven store procedure should be only 1 single select. Is it acceptable to have a READ statement to pick the data inside the store procedure?

[Former Member · Accepted solution]

Hi Antonio,

Not sure with READ, but you definitely can use a select statement in DB2. I am working on a scenario where I have sender as AS400(DB2) and I am able to execute SELECT statements without a problem.

Regards,

Jai Shankar.

[Former Member · Accepted solution]

Thanks..But I am assuming you are mentioning the select statements inside the store procedure ? Because it works fine for me for regular SELECT’s. It is only the store procedures that is giving fits…:)

[Former Member · Accepted solution]

Sorry.......

I was saying Select only in sender commn. channel.

BTW, why dont you use SELECT from commn. channel? Is it a problem? I just heard from AS400 team member, that read statement returns a single record. If that is what you need, you can do it using SELECT with appropriate where clause.

Regards,

Jai Shankar.

[Former Member · Accepted solution]

Actually we were having problems in selecting a limited number of records and it works fine with a select if we can specify the number of rows to be selected but at the same time we need to update after the selected rows. This is possible only when the “Where” clauses are the same. This is the reason for going for a stored procedure. If its possible by SELECT /UPDATE that would be awesome..

[Former Member · Accepted solution]

If you need to process records one by one use the "min" operator in your where clause(This is exactly what I have done). If you need some 10, 20 records try "top" operator. I am not sure about this top operator. But a experienced DB2 person will defenitely be able to give you a solution.

Regards,

Jai Shankar.

[Former Member · Accepted solution]

Hi Jai,

Could you provide me with a sample of both queries? One with 'Top' and another with 'Min' operation. I don't have a DBA here, so probably if you can give me a sample I can work out with it..I am not sold on this Stored procedure ...

[Former Member · Accepted solution]

Hi,

As I told you, Ihave not tried "top" operator. I am giving you a sample sql where I have used "min".

SELECT <Libraryname>.<table1>.field1, <library name>.<table1>.field2, <library name>.<table1>.field3, <library name>.<table2>.field1, <library name>.<table2>.field2, <library name>.<table2>.field3, from <library name>.<table1>,<library name>.<table2> where <library name>.<table1>.field1 = (select min(<library name>.<table1>.field1) from <library name>.<table1>)

Hope this helps you.

Regards,

Jai Shankar.