Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » problem to get stored procedure that does not return anything.
problem to get stored procedure that does not return anything. [message #963563] Mon, 29 October 2012 18:50 Go to next message
Kevin C is currently offline Kevin C
Messages: 2
Registered: October 2012
Junior Member
I had a stored procedure, it return nothing. No matter what query I use, ReadQuery/ModifyQuery, It will always give me exception from underlying vdb(teiid).

if it is read query, it will complain the stored procedure does not return resultSet.

If it is ModifyQuery, it will complain the stored procedure does not reutrn UpdateCount.

The problem is in DatabaseAccessor.basicExecuteCall(), in the code below, if there is nothing returned, it should not call executeNoSelect() since this expect a UpdateCount, it should just call (PreparedStatement)statement.execute() instead.
Also, it did not give me a chance to hook up my own code to do that. Does any one know how to get around this?
thanks
Kevin


// effectively this means that someone is executing an update type query.
if (dbCall.isNothingReturned()) {
result = executeNoSelect(dbCall, statement, session);
if (!isInBatchWritingMode(session)) {
this.writeStatementsCount++;
}
if (dbCall.isLOBLocatorNeeded()) {
// add original (insert or update) call to the LOB locator
// Bug 2804663 - LOBValueWriter is no longer a singleton
getLOBWriter().addCall(dbCall);
}
Re: problem to get stored procedure that does not return anything. [message #967207 is a reply to message #963563] Thu, 01 November 2012 11:27 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
Registered: July 2009
Senior Member
The java docs on PreparedStatement's execute and executeUpdate both say it should handle SQL that returns no results, with the excute implying that it returns "false if the first result is an update count or there is no result" and then use getUpdateCount to get the row count, while excuteUpdate states "either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing".
So executeUpdate should work if your procedure does not return any results, it is just that the count in both cases should be 0 since no rows are updated. What exactly is complaining or throwing an exception, and how are you creating your query and stored procedure call?


If you can use EclipseLink 2.5, you can have EclipseLink call execute instead of executeUpdate in native queries by calling setReturnType(5) on the DatabaseCall, but I don't know that this will help here without knowing why executeUpdate isn't working for you.

Best Regards,
Chris
Re: problem to get stored procedure that does not return anything. [message #973642 is a reply to message #967207] Tue, 06 November 2012 08:55 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Please include your stored procedure definition, what database and version you are using, and your exception stack trace.


James : Wiki : Book : Blog : Twitter
Re: problem to get stored procedure that does not return anything. [message #976668 is a reply to message #973642] Thu, 08 November 2012 14:14 Go to previous messageGo to next message
Kevin C is currently offline Kevin C
Messages: 2
Registered: October 2012
Junior Member
We are using Teiid 8.1 final as our virtaul database, the physical db is SQL SERVER.

I have defined a dummy stored procedure, it will take 2 input parameters but does nothing. the exception is from Teiid.

here is the call stack:

Internal Exception: org.teiid.jdbc.TeiidSQLException: TEIID30490 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30490 The query does not return an update count.
Error Code: 0
Call: {CALL dummySP(?, ?)}
bind => [null, null]
Query: DataModifyQuery()
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1494) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:838) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:906) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:592) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:253) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:236) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:216) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:85) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:743) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1449) [eclipselink-2.3.2.jar:2.3.2.v20111125-r10461]

Re: problem to get stored procedure that does not return anything. [message #982983 is a reply to message #976668] Tue, 13 November 2012 10:30 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I have not heard of Teiid, its JDBC driver may not work with stored procedures correctly. If you use SQL Server directly using a JDBC compliant driver it should work.
You may need to get a patch from your JDBC driver, or use another driver.

If the execute the native query using getResultList() it will not use executeUpdate().


James : Wiki : Book : Blog : Twitter

[Updated on: Tue, 13 November 2012 10:31]

Report message to a moderator

Previous Topic:Cache Coordination with WebSphere (WAS) v.8
Next Topic:How to ise dynamic fetchgroup
Goto Forum:
  


Current Time: Wed Apr 16 23:58:03 EDT 2014

Powered by FUDForum. Page generated in 0.01803 seconds