ok - I have spent the last 2hrs trying to work through the correct syntax in mysql for returning a ResultSet from a procedure... turns out:
1) you use a procedure, not a function (you guys already knew this...;)
eg:
Code:
CREATE PROCEDURE testProc(in id int)
BEGIN
SELECT myid, uname FROM person;
END;
2) in your jdbc program you call by demo of example code:
Code:
String sql=new String("{CALL testProc(?)}");
cs=m_Conn.prepareCall(sql);
cs.setInt(1,10);
cs.execute();
rs=cs.getResultSet();
while(rs.next())
{
l_sPersonID=rs.getString(1);
l_sUsername=rs.getString(2);
System.out.println(l_sPersonID + "," + l_sUsername);
}
3) here is the create table SQL:
Code:
create table person (`myid` integer,`uname` varchar(30));
insert into person values(1,'bob');
insert into person values(2,'fred');
insert into person values(3,'mary');
commit;
I have NOT tried it with hibernate -- but I'd be willing to bet that if you setup the above demo... and put your call testProc in the <sql-query...
you'll get it working - please confirm.
Login
FAQ
Search
