Getting java.sql.SQLException: while calling stored procedure from an application code.

Database
Enthusiast

Getting java.sql.SQLException: while calling stored procedure from an application code.

I have the following stored procedure:-

 

REPLACE PROCEDURE SelectDataInGetData ( 
   IN name_value varchar(36)
) 
DYNAMIC RESULT SETS 1
BEGIN 
  DECLARE cur1 CURSOR WITH RETURN ONLY FOR
	select * from mytable where NAME = name_value; 
  OPEN cur1;
END;

This works fine and I am able to get the result. I am using following to call the stored proccedure:

call SelectDataInGetData('testname');

But whenever I am calling this stored proccedure fro an application like Java or Mulesoft, though I am able to get the result as expected but in the server console at the end I am getting following exception:-   

WARN  2017-05-02 02:20:05,891 [[Mule-TeraData].HTTP_Listener_Configuration.worker.01] org.mule.module.db.internal.result.statement.StatementResultIterator: Unable to determine if there are more statement results
java.sql.SQLException: [Teradata JDBC Driver] [TeraJDBC 16.00.00.28] [Error 1238] [SQLState HY000] Method only supported for ResultSet.TYPE_SCROLL_INSENSITIVE: getMoreResults(KEEP_CURRENT_RESULT)
	at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:95) ~[terajdbc4.jar:16.00.00.28]
	at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:70) ~[terajdbc4.jar:16.00.00.28]
	at com.teradata.jdbc.jdbc_4.TDStatement.getMoreResults(TDStatement.java:845) ~[terajdbc4.jar:16.00.00.28]
	at org.mule.module.db.internal.result.statement.StatementResultIterator.moveToNextResult(StatementResultIterator.java:201) ~[mule-module-db-3.8.2.jar:3.8.2]

I am using Teradata express v16 for Vmware player and connecting the DB fro application.

Please let me know if this is an issue fro my end or from the Teradata jar (terajdbc4.jar) file I a using.

 

Thanks

 

 

 

 

 


Accepted Solutions
Teradata Employee

Re: Getting java.sql.SQLException: while calling stored procedure from an application code.

The exception error message is telling the truth -- the getMoreResults(KEEP_CURRENT_RESULT) method is only supported for a Statement created with the option ResultSet.TYPE_SCROLL_INSENSITIVE.

 

When your app creates a Statement with the option ResultSet.TYPE_SCROLL_INSENSITIVE, then the Teradata JDBC Driver activates the Teradata Database feature "cursor positioning", which enables random access to the response spool. There is some overhead associated with Teradata Database cursor positioning; for example, response spools are slightly larger, so that feature is turned off be default, and your app must explicitly request it.

 

Because random access to the response spool is needed for the getMoreResults(KEEP_CURRENT_RESULT) method, that is why your app must have originally created the Statement with the option ResultSet.TYPE_SCROLL_INSENSITIVE.

 

For an app that you wrote yourself, you can change your code to specify the option ResultSet.TYPE_SCROLL_INSENSITIVE.

 

On the other hand, for a third-party app that you don't have the source code for, you will need to ask the vendor whether there is an option to use scrollable result sets, or ask whether the third-party app can be enhanced to offer that.

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Getting java.sql.SQLException: while calling stored procedure from an application code.

The exception error message is telling the truth -- the getMoreResults(KEEP_CURRENT_RESULT) method is only supported for a Statement created with the option ResultSet.TYPE_SCROLL_INSENSITIVE.

 

When your app creates a Statement with the option ResultSet.TYPE_SCROLL_INSENSITIVE, then the Teradata JDBC Driver activates the Teradata Database feature "cursor positioning", which enables random access to the response spool. There is some overhead associated with Teradata Database cursor positioning; for example, response spools are slightly larger, so that feature is turned off be default, and your app must explicitly request it.

 

Because random access to the response spool is needed for the getMoreResults(KEEP_CURRENT_RESULT) method, that is why your app must have originally created the Statement with the option ResultSet.TYPE_SCROLL_INSENSITIVE.

 

For an app that you wrote yourself, you can change your code to specify the option ResultSet.TYPE_SCROLL_INSENSITIVE.

 

On the other hand, for a third-party app that you don't have the source code for, you will need to ask the vendor whether there is an option to use scrollable result sets, or ask whether the third-party app can be enhanced to offer that.

Enthusiast

Re: Getting java.sql.SQLException: while calling stored procedure from an application code.

I have created this stored procedure in TeraData studio:

REPLACE PROCEDURE SelectDataInGetData ( 
   IN name_value varchar(36)
) 
DYNAMIC RESULT SETS 1
BEGIN 
  DECLARE cur1 CURSOR WITH RETURN ONLY FOR
	select * from mytable where NAME = name_value; 
  OPEN cur1;
END;

While I a calling this Store procedure from a Mule application:-

call SelectDataInGetData('testname');

Since I am calling this Store procedure from a database connector in the Mule application, I have no way to modify or edit the source code as the connector is pre-build.

 

Is there any way I can make a change or modify in the Store procedure to avoid this exception? 

 

Pls note: I am getting the DB result in the browser while calling the app from the browser.

But In the server console, I can see the exception.

 

Thanks for the reply

 

 

 

 

 

Teradata Employee

Re: Getting java.sql.SQLException: while calling stored procedure from an application code.

It looks like you have found an incompatibility between MuleSoft and Teradata. You can contact MuleSoft for support, or you can try executing the query directly without using the stored procedure.

Enthusiast

Re: Getting java.sql.SQLException: while calling stored procedure from an application code.

Thanks .... executing query directly doesn't seems to have any issue
Enthusiast

Re: Getting java.sql.SQLException: while calling stored procedure from an application code.

Thanks .... executing query directly doesn't seems to have any issue