wood burning stoves 2.0*
The moose likes JDBC and the fly likes Error calling Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error calling Stored Procedure "SELECT in a stored procedure must have INTO "" Watch "Error calling Stored Procedure "SELECT in a stored procedure must have INTO "" New topic
Author

Error calling Stored Procedure "SELECT in a stored procedure must have INTO "

S.R.K.Vivek Raju
Ranch Hand

Joined: Sep 23, 2004
Posts: 58
Hi all

I'm writing a Web Application which uses the new MySQL 5.0.1 beta version.

I have written the following stored procedure to retrieve data from the database.

*******************************************************************************************************************************
create procedure my_proc4(in uid int(5))
Begin

insert into tmp select groups.id from groups,user
where id_user=uid and user.id=groups.id_user;
select distinct groupname,contact.firstname,contact.lastname,contact.home_phone,contact.email,contact.im
from contact,groups,tmp where contact.id_group=tmp.gid and groups.id=contact.id_group;
delete from tmp;

END
*******************************************************************************************************************************

Then I execute the Stored Procedure from my java code:

String proc_query = "call my_proc4(?)";
PreparedStatement p = con.prepareStatement(proc_query);
p.setInt(1,id_user);

and it throws the Exception: null, message from server: "SELECT in a stored
procedure must have INTO "

But when I do the call on the MySQL Command Line Client it
works fine!
I read the Bug 2658
http://bugs.mysql.com/bug.php?id=2658

and I think that is the answer but i don 't know how to
set the CLIENT_MULTI_RESULTS!!!

Can anyone help me?

Thanks in advance

With Regards
S.R.K.Vivek Raju
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

You neen to use CallableStatement in order to call stored procedure

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
S.R.K.Vivek Raju
Ranch Hand

Joined: Sep 23, 2004
Posts: 58
Hi Shailesh

Thank you for your reply, I have even tried with the callable statement and it still gives the same error , i don't think the problem is with the jdbc syntax. It is as i mentioned earlier is a bug and could you please guide me on how the set the CLIENT_MULTI_RESULTS as mentioned in the http://bugs.mysql.com/bug.php?id=2658

With Regards
S.R.K.Vivek Raju.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

It may be same problem as you said, I haven't worked on Mysql, but can you tell me does you put your java code and stack trace of exception

Shailesh
S.R.K.Vivek Raju
Ranch Hand

Joined: Sep 23, 2004
Posts: 58
Hi shailesh

I am sorry i really did not understand what you were asking me, if you wanted my stack trace exception which comes on my console, i am putting it here.

The exception on my console is as follows:---

***************************************************************************
17:19:29,296 INFO [STDOUT] Exception :null, message from server: "SELECT in a stored
procedure must have INTO"
17:19:29,296 INFO [STDOUT] java.sql.SQLException: null, message from server: "SELECT
in a stored procedure must have INTO"
17:19:29,296 INFO [STDOUT] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java
:1977)
17:19:29,296 INFO [STDOUT] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163
)
17:19:29,296 INFO [STDOUT] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1
272)
17:19:29,296 INFO [STDOUT] at com.mysql.jdbc.Connection.execSQL(Connection.java:22
36)
17:19:29,296 INFO [STDOUT] at com.mysql.jdbc.PreparedStatement.executeQuery(Prepar
edStatement.java:1555)
17:19:29,296 INFO [STDOUT] at com.dao.ContactsDAO.addContact(ContactsDAO.java:72)
17:19:29,296 INFO [STDOUT] at com.actions.ContactDetailsAction.executeSaveContact(
ContactDetailsAction.java:176)
17:19:29,296 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
17:19:29,296 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMe
thodAccessorImpl.java:39)
17:19:29,296 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Dele
gatingMethodAccessorImpl.java:25)
17:19:29,296 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:324)
17:19:29,296 INFO [STDOUT] at org.apache.struts.actions.DispatchAction.dispatchMet
hod(DispatchAction.java:276)
17:19:29,296 INFO [STDOUT] at org.apache.struts.actions.LookupDispatchAction.execu
te(LookupDispatchAction.java:162)
17:19:29,296 INFO [STDOUT] at org.apache.struts.action.RequestProcessor.processAct
ionPerform(RequestProcessor.java:421)
17:19:29,296 INFO [STDOUT] at org.apache.struts.action.RequestProcessor.process(Re
questProcessor.java:226)
17:19:29,296 INFO [STDOUT] at org.apache.struts.action.ActionServlet.process(Actio
nServlet.java:1164)
17:19:29,296 INFO [STDOUT] at org.apache.struts.action.ActionServlet.doPost(Action
Servlet.java:415)
17:19:29,296 INFO [STDOUT] at javax.servlet.http.HttpServlet.service(HttpServlet.j
ava:717)
17:19:29,296 INFO [STDOUT] at javax.servlet.http.HttpServlet.service(HttpServlet.j
ava:810)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.inte
rnalDoFilter(ApplicationFilterChain.java:237)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFi
lter(ApplicationFilterChain.java:157)
17:19:29,296 INFO [STDOUT] at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFil
ter(ReplyHeaderFilter.java:75)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.inte
rnalDoFilter(ApplicationFilterChain.java:186)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFi
lter(ApplicationFilterChain.java:157)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardWrapperValve.invoke
(StandardWrapperValve.java:214)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invoke
Next(StandardValveContext.java:104)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardPipeline.invoke(Sta
ndardPipeline.java:520)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardContextValve.invoke
Internal(StandardContextValve.java:198)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardContextValve.invoke
(StandardContextValve.java:152)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invoke
Next(StandardValveContext.java:104)
17:19:29,296 INFO [STDOUT] at org.jboss.web.tomcat.security.CustomPrincipalValve.i
nvoke(CustomPrincipalValve.java:44)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invoke
Next(StandardValveContext.java:102)
17:19:29,296 INFO [STDOUT] at org.jboss.web.tomcat.security.SecurityAssociationVal
ve.invoke(SecurityAssociationValve.java:169)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invoke
Next(StandardValveContext.java:102)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardPipeline.invoke(Sta
ndardPipeline.java:520)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardHostValve.invoke(St
andardHostValve.java:137)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invoke
Next(StandardValveContext.java:104)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.valves.ErrorReportValve.invoke(E
rrorReportValve.java:118)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invoke
Next(StandardValveContext.java:102)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardPipeline.invoke(Sta
ndardPipeline.java:520)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardEngineValve.invoke(
StandardEngineValve.java:109)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invoke
Next(StandardValveContext.java:104)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.StandardPipeline.invoke(Sta
ndardPipeline.java:520)
17:19:29,296 INFO [STDOUT] at org.apache.catalina.core.ContainerBase.invoke(Contai
nerBase.java:929)
17:19:29,296 INFO [STDOUT] at org.apache.coyote.tomcat5.CoyoteAdapter.service(Coyo
teAdapter.java:160)
17:19:29,296 INFO [STDOUT] at org.apache.coyote.http11.Http11Processor.process(Htt
p11Processor.java:799)
17:19:29,296 INFO [STDOUT] at org.apache.coyote.http11.Http11Protocol$Http11Connec
tionHandler.processConnection(Http11Protocol.java:705)
17:19:29,296 INFO [STDOUT] at org.apache.tomcat.util.net.TcpWorkerThread.runIt(Poo
lTcpEndpoint.java:577)
17:19:29,296 INFO [STDOUT] at org.apache.tomcat.util.threads.ThreadPool$ControlRun
nable.run(ThreadPool.java:683)
17:19:29,296 INFO [STDOUT] at java.lang.Thread.run(Thread.java:534)
17:19:29,328 ERROR [Engine] ApplicationDispatcher[/eeeha] Servlet.service() for servlet
jsp threw exception
java.lang.NullPointerException
at org.apache.jsp.pages.contacts.body_002dcontent_jsp._jspService(body_002dcont
ent_jsp.java:471)
***************************************************************************

Thank you once again for taking so much initiative,

With Regards
S.R.K.Vivek Raju.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081



Can you tell what is use of this query in your stored procedure.I dont see any use of this select query.
your error message is obvious you are not binding your columns to any variable,test your code after commenting select statement.

you should do something like




Shailesh
[ April 18, 2005: Message edited by: Shailesh Chandra ]
S.R.K.Vivek Raju
Ranch Hand

Joined: Sep 23, 2004
Posts: 58
Hi Shailesh

Let me describe my objective here, I have three tables namely
User_table Groups_table and Contact_table
Every user has an unique id which is refectled in the groups_table in the column named id_user in the groups table.

Similarly every group also has an unique id which is generated at the time of creating a group and which is reflected in the contact_table column as id_group.

Now when a contact is added , it is added under a particular group.
So when a user logs in , the contacts are retrieved with respect to the userid and the groupid.

Let me make it simple

user1-----user1.group1------group1.contact1
group1.contact2
user1.group2------group2.contact1,2,3
group2.contact4,5,6
user1.group3

That is the reason i needed the query
**************************************************************************
select distinct groupname,contact.firstname,contact.lastname,contact.home_phone,contact.email,contact.im from contact,groups,tmp where contact.id_group=tmp.gid and groups.id=contact.id_group;

***************************************************************************
which would pull up the groupname , first name etc for that particular contact.

But let me remind you that this query runs perfect on the mysql prompt with the exact data coming up.
It only gives the error when running through my java code.

With Regards
S.R.K.Vivek Raju
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

I mean to say how are you using the query, purpose of query is clear but I wanted to say where are you storing output of query
suppose I write query like this

select column1 into myvar from table1 where blah blah

This means when query will execute,the value of column1 would be stored in myvar.
But you are not doing anything such, this query may work on sql console (I am not sure) , did you try with commenting your query ?

Please refer some tutorial on writting stored procedure.


Shailesh
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Error calling Stored Procedure "SELECT in a stored procedure must have INTO "
 
Similar Threads
Problem at connection
Problem with inserting new data into database using hibernate
Tomcat 6 selects with alias are broken
IllegalArgumentException when closing PreparedStatement
Problem with the JBDC driver