MySQL stored procedure not returning result set

Question

I have a MySQL stored procedure that does a select on a table, then wrap the result using concatenated string (HTML code). I get data back when ran through Workbench, but the Java result set seems empty. Pretty much it returns one big line of concatenated string data (over 1000 lines that creates HTML table out of it).

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_html`(
    INOUT BugList varchar(30000))

    Block1: BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE LongDesc Varchar(10000) DEFAULT "";
    DECLARE BugShortList Varchar(10000) DEFAULT "";
    DECLARE sd varchar(1000) DEFAULT "";
    declare cname char(20) default "";
    DECLARE bid int DEFAULT 0;
    DECLARE cid int DEFAULT 0;
    declare prevbid int default 0;
    declare rpl varchar (64) default "";
    declare os varchar (64) default "";
    declare bs varchar (64) default "";
    declare dts date;

    DECLARE c_1 CURSOR FOR select   b.bug_id,b.short_desc,l.thetext,b.component_id,b.rep_platform,b.op_sys,b.bug_severity
    from bugs b 
    INNER JOIN longdescs l ON b.bug_id=l.bug_id 
    where component_id in (4,5,6,7,15,16,19,25,26,28,29,32,33,35) and 
    b.bug_id > 100 and
    version='1.0' and 
    resolution='fixed' 
    order by b.bug_id desc;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    set BugList= CONCAT('<table style="2", border="1"><tr><td   width=420>','<b>=================================== EXPANDED LIST ==================================</b>');

    OPEN c_1;

    repeat

    FETCH c_1 INTO bid,sd,LongDesc,cid,rpl,os,bs;

       if bid = prevbid then
           if LongDesc <> "" then 
                 set BugList=CONCAT(BugList,'<br/>',LongDesc,'<br/>');
                                   end if;
       else
           select name from components where cid=id into cname;
           set BugShortList=CONCAT(BugShortList,'<br/>','&#160;','&#160;','<a href=#',bid,'>',bid,'</a>',' - ',sd);
          -- set LongDesc = replace(LongDesc,'\n', '<br/>');
           set BugList=CONCAT(BugList,'<br/>','<tr bgcolor=lightgrey><td width=420>','&#160;','************************************************************************************************','<br/>');
           set BugList=CONCAT(BugList,
           '&#160;','* ','<a id=','#',bid,'>','<font color=red>',bid,'</font>','</a>',' - ','<b>',sd,'</b>','<br/>',
           '&#160;','* Version: 2.3.0 | Component: ',cname,' | Platform: ',rpl,' | OS: ',os,' | Severity: ',bs,'<br/>',
           '&#160;','************************************************************************************************','</td></tr><tr><td width=420>','<br/>',LongDesc,'<br/>');
           set prevbid=bid;
       end if;
       
     until done end repeat;
    CLOSE c_1;
    set BugList=CONCAT('<tr><td>','<br/>','<b>==================================== CONDENSED LIST ======================================</b>','<br/>',
                  BugShortList,'</td></tr>',BugList);
      set BugList=CONCAT(BugList,'</td></tr></table>');
    set BugList = replace(BugList,'\n', '<br/>');
END Block1

MySQL Workbench returns the string data when I make the call

set @list="";
call get_fixed_web_bugs_html(@list);
select @list 

I'm not quite sure if java accepts big string data as result set. Any tips?

My java call:

StringBuilder out = new StringBuilder("");
CallableStatement cs = conn.prepareCall("{ CALL get_html(?)}");
cs.registerOutParameter(1, Types.LONGVARBINARY);
cs.setString(1, out.toString());

ResultSet rs = cs.executeQuery();
while (rs.next()) {
    System.out.println(String.format("%s - %s", rs.getString("out")));
}

rs seems to be empty.

Answer 1

Your stored procedure should not return result set, since you're not producing result set. Instead, it returns the result in an INOUT variable.

you should be doing something like below instead

cs.registerOutParameter(1, Types.LONGVARBINARY);
..
cs.executeUpdate();
System.out.println(cs.getString(1));

It may be easier to not mess up with output parameters, and just SELECT BugList at the end of SP, and have a procedure that returns string as result set, as Akina mentioned in the comment.