Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Dynamic SQL

15 views
Skip to first unread message

ann_lynn

unread,
Apr 8, 2004, 3:06:40 PM4/8/04
to
I have a stored procedure which dynamically creates select
statement and then executes it with a statement
execute (@select).
Everything is fine, but sometimes when I call it 2 times in
a row from application I get this error after the second
call:

com.sybase.jdbc2.jdbc.SybSQLException: Execute cursor
'jconnect_implicit_1' is declared on a procedure which
contains a non-SELECT or a SELECT with a COMPUTE clause. For
the declaration of this cursor to be legal it should have a
single SELECT statement w....

I know that it exactly the same statement returns results
the first time I run it.

If anybody has any idea, it would be great

Thanks

Ann

odbcpse

unread,
Apr 9, 2004, 12:03:12 PM4/9/04
to
Hi -

I twould hel pto post some sample code and sample ddl
so we can get a good idea of what is happening. Also -
maybe getting a tds trace.

Pv
<Ann Lynn> wrote in message news:407594b0.42...@sybase.com...

ann_lynn

unread,
Apr 15, 2004, 5:36:45 PM4/15/04
to
This is a stack trace:


com.sybase.jdbc2.jdbc.SybSQLException: Execute cursor
'jconnect_implicit_1' is declared on a procedure which
contains a non-SELECT or a SELECT with a COMPUTE clause. For
the declaration of this cursor to be legal it should have a
single SELECT statement w

at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2542)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java(Compiled
Code))
at
com.sybase.jdbc2.tds.Tds.getResultSetResult(Tds.java:2454)
at com.sybase.jdbc2.tds.TdsCursor.open(TdsCursor.java:190)
at
com.sybase.jdbc2.jdbc.SybCallableStatement.sendRpc(SybCallableStatement.java:402)
at
com.sybase.jdbc2.jdbc.SybCallableStatement.executeQuery(SybCallableStatement.java:67)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:723)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:510)
at
com.nyse.enyse.easelog.util.SQLUtilAbstract.executeQuery(SQLUtilAbstract.java:98)
at
com.nyse.enyse.easelog.web.search.SearchDAO.getSearchResults(SearchDAO.java:45)
at
com.nyse.enyse.easelog.web.search.SearchProcessor.getSearchList(SearchProcessor.java:26)
at
com.nyse.enyse.easelog.web.search.SearchProcessor.getSearchXML(SearchProcessor.java:35)
at
com.nyse.enyse.easelog.web.search.SearchEventProcessor.perform(SearchEventProcessor.java:37)
at
com.nyse.enyse.app.server.ProcessDispatcher.processEvent(ProcessDispatcher.java:44)
at
com.nyse.enyse.app.server.ServerProxyImpl.processEvent(ServerProxyImpl.java:38)
at
com.nyse.enyse.app.controller.WebClientProxyImpl.processEvent(WebClientProxyImpl.java:41)
at
com.nyse.enyse.app.controller.RequestProcessor.processRequest(RequestProcessor.java:62)
at com.nyse.enyse.app.controller.Main.process(Main.java:58)
at com.nyse.enyse.app.controller.Main.doGet(Main.java:51)
at
com.nyse.enyse.easelog.servlet.EaseLog.doGet(EaseLog.java:114)
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at
com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at
com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at
com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at
com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:948)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:530)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:176)
at
com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
at
com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:201)
at
com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at
com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)
at
com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:186)
at
com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at
com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at
com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:610)
at
com.ibm.ws.http.HttpConnection.run(HttpConnection.java:435)
at
com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)
"
SQL Error Message: Subquery returned more than 1 value.
This is illegal when the subquery follows =, !=, <, <= , >,
>=, or when the subquery is used as an expression.

SQLState: 21000
SQL Error Code: 512:Excep. message:Subquery returned more
than 1 valu"
com.sybase.jdbc2.jdbc.SybSQLException: Subquery returned
more than 1 value. This is illegal when the subquery
follows =, !=, <, <= , >, >=, or when the subquery is used
as an expression.
at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2542)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java(Compiled
Code))
at
com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at
com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204)
at
com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187)
at
com.sybase.jdbc2.jdbc.SybStatement.queryLoop(SybStatement.java:1537)
at
com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1522)
at
com.sybase.jdbc2.jdbc.SybCallableStatement.executeQuery(SybCallableStatement.java:76)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:723)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:510)
at
com.nyse.enyse.easelog.util.SQLUtilAbstract.executeQuery(SQLUtilAbstract.java:98)
at
com.nyse.enyse.easelog.web.reminders.RemindersDAO.getUserReminders(RemindersDAO.java:38)
at
com.nyse.enyse.easelog.web.worklog.WorklogFacade.getUserReminders(WorklogFacade.java:88)
at
com.nyse.enyse.easelog.web.worklog.WorklogFacade.populateWorklog(WorklogFacade.java:52)
at
com.nyse.enyse.easelog.web.worklog.WorklogEventProcessor.perform(WorklogEventProcessor.java:45)
at
com.nyse.enyse.app.server.ProcessDispatcher.processEvent(ProcessDispatcher.java:44)
at
com.nyse.enyse.app.server.ServerProxyImpl.processEvent(ServerProxyImpl.java:38)
at
com.nyse.enyse.app.controller.WebClientProxyImpl.processEvent(WebClientProxyImpl.java:41)
at
com.nyse.enyse.app.controller.RequestProcessor.processRequest(RequestProcessor.java:62)
at com.nyse.enyse.app.controller.Main.process(Main.java:58)
at com.nyse.enyse.app.controller.Main.doGet(Main.java:51)
at
com.nyse.enyse.easelog.servlet.EaseLog.doGet(EaseLog.java:114)
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at
com.ibm.ws.webcontainer.servlet.ServicingServletState.service(StrictLifecycleServlet.java:333)
at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at
com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at
com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at
com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:948)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:530)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:176)
at
com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
at
com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:201)
at
com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at
com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)
at
com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:186)
at
com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at
com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at
com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:610)
at
com.ibm.ws.http.HttpConnection.run(HttpConnection.java:435)
at
com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)
com.nyse.enyse.easelog.web.worklog.WorklogFacade:getUserReminders
Exception
"
SQL Error Message: Subquery returned more than 1 value.
This is illegal when the subquery follows =, !=, <, <= , >,
>=, or when the subquery is used as an expression.

SQLState: 21000
SQL Error Code: 512:Excep. message:Subquery returned more
than 1 valu"
com.sybase.jdbc2.jdbc.SybSQLException: Subquery returned
more than 1 value. This is illegal when the subquery
follows =, !=, <, <= , >, >=, or when the subquery is used
as an expression.
at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2542)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java(Compiled
Code))
at
com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at
com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204)
at
com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187)
at
com.sybase.jdbc2.jdbc.SybStatement.queryLoop(SybStatement.java:1537)
at
com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1522)
at
com.sybase.jdbc2.jdbc.SybCallableStatement.executeQuery(SybCallableStatement.java:76)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:723)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:510)
at
com.nyse.enyse.easelog.util.SQLUtilAbstract.executeQuery(SQLUtilAbstract.java:98)
at
com.nyse.enyse.easelog.web.reminders.RemindersDAO.getUserReminders(RemindersDAO.java:38)
at
com.nyse.enyse.easelog.web.reminders.RemindersProcessor.getUserReminders(RemindersProcessor.java:24)
at
com.nyse.enyse.easelog.web.reminders.RemindersEventProcessor.perform(RemindersEventProcessor.java:28)
at
com.nyse.enyse.app.server.ProcessDispatcher.processEvent(ProcessDispatcher.java:44)
at
com.nyse.enyse.app.server.ServerProxyImpl.processEvent(ServerProxyImpl.java:38)
at
com.nyse.enyse.app.controller.WebClientProxyImpl.processEvent(WebClientProxyImpl.java:41)
at
com.nyse.enyse.app.controller.RequestProcessor.processRequest(RequestProcessor.java:62)
at com.nyse.enyse.app.controller.Main.process(Main.java:58)
at com.nyse.enyse.app.controller.Main.doGet(Main.java:51)
at
com.nyse.enyse.easelog.servlet.EaseLog.doGet(EaseLog.java:114)
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at
javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at
com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at
com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at
com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at
com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:948)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:530)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:176)
at
com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
at
com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:201)
at
com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at
com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)
at
com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:186)
at
com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at
com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at
com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:610)
at
com.ibm.ws.http.HttpConnection.run(HttpConnection.java:435)
at
com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)

and this is the ddl:


create procedure up_searchItems @parameterName varchar(25),
@parametervalue varchar(255), @parameterType
varchar(255)="string"
as
declare @select1 varchar(255)
declare @select2 varchar(255)
declare @select3 varchar(255)
if (@parameterName<>"Customer")
begin
select @select1="select item_number=itemid , ssn=subjectssn,
subject_name=subjectname,firm_name=firmname,
status=StatusDesc, "
+" date_received=convert(varchar(11),receiveddate,101) from
item, lu_status where "

if @parametertype="string"
select @select2=@parameterName+" like
'"+@parameterValue+"%'"
else
select @select2=@parameterName+" =
convert(int,"+@parametervalue+")"
select @select3=" and item.statuscode=lu_status.statuscode"
execute (@select1 + @select2+@select3)

end
else
begin
select item_number=item.itemid,
ssn=subjectssn,
subject_name=subjectName,
firm_name=firmname,
status=StatusDesc,

date_received=convert(varchar(11),receiveddate,101)
from item, lu_status, itemCustomer ic, Customer c
where
c.customername like @parameterValue+"%"
and c.customerid=ic.customerid
and ic.itemid=item.itemid
and item.statuscode=lu_status.statuscode


end

J

unread,
Apr 16, 2004, 9:08:05 PM4/16/04
to
On 15 Apr 2004 13:36:45 -0800, Ann Lynn wrote:

Ann,

I don't think he wants a stack trace. I think it would be most useful
to pass the client thru Ribo (passthru tds application) to see the
actual data that is being sent back and forth. Ribo is in the
jutils-2_0 directory.

Jay

Paul

unread,
Apr 27, 2004, 5:41:41 PM4/27/04
to
Hi Ann,

I think this has something to do with some connection settings - not so on
the initial
connect but maybe some properties being set on the connection or the
statement.
If we could see some code maybe we could figure out where the problem lies.

I assume you are using a callable statement? Or perhaps you are re-issuing
the
statement with some concurrency or cursor settings?

Paul


<Ann Lynn> wrote in message news:407ef259.77...@sybase.com...

com.ibm.ws.webcontainer.servlet.ServicingServletState.service(StrictLifecycl
eServlet.java:333)
> at
>
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecyc

0 new messages