sql - Calling Stored Procedure in Java class -
this question has answer here:
how call stored procedure in java class? , declare string variable equal it?
genorfinalsp(name of sp)
begin declare temp_or varchar(50); call genorsp(xrco, xreceipt, @tempcount, @temporno); set xror = @temporno; while xror = (select tpay_receipt_no tbl_tax_payment tpay_receipt_no = xror) call genorsp(xrco, xreceipt, @tempcount, @temporno); end while; set xror = @temporno; set xcount = @tempcount; end for example want declare string variable test equal it.
string test = genorfinalsp(); updated: code of genorsp
begin declare var_max bigint(20); declare temp_count bigint(20); declare var_year varchar(4); declare var_tempyear varchar(4); declare var_end int(1); set temp_count = 0; set var_end = 0; set var_year = year(current_timestamp); select ( case when max(tpay_counter) null 0 else max(tpay_counter) end ), year (date(tpay_issued_date)) var_max, var_tempyear tbl_tax_payment tpay_issued_date = (select max(tpay_issued_date) tbl_tax_payment rco_code = xrco , receipt_type = xreceipt , year(date(tpay_issued_date)) = year(current_date) , tpay_counter = ( select max(tpay_counter) tbl_tax_payment rco_code = xrco , receipt_type = xreceipt , year (date(tpay_issued_date)) = year (current_date) )) , tpay_counter = ( select max(tpay_counter) tbl_tax_payment rco_code = xrco , receipt_type = xreceipt , year (date(tpay_issued_date)) = year (current_date) ); if var_year <> var_tempyear set var_end = (select if(year(current_date) = var_tempyear,0,1)); if var_end = 1 set var_max = 0; end if; end if; if var_max = 0 set temp_count = 1; set var_or = concat(xreceipt, var_year, '-', xrco, '-', lpad(temp_count, 6, '0')); else set temp_count = var_max + 1; set var_or = concat(xreceipt, var_year, '-', xrco, '-', lpad(temp_count, 6, '0')); end if; set var_count = temp_count; end
the answer generic problem , rest of cases, may benefited ;-) code work if procedure don't have arguments , won't return back.
connection connection = getyourdatabaseconnection(); callablestatement callablestatement = connection.preparecall("{ call genorfinalsp() }"); resultset resultset = callablestatement.executequery(); //or //int rowsupdated = callablestatement.executeupdate(); if interested in pass arguments procedure
callablestatement callablestatement = connection.preparecall("{call genorfinalsp(?,?)}"); callablestatement.setstring(1, firstargu); callablestatement.setstring(2, secondargu) resultset resultset = callablestatement.executequery(); and if interested in pass arguments , want return values procedure in case 2 returned values, call like
callablestatement callablestatement = connection.preparecall("{call genorfinalsp(?,?,?,?)}"); callablestatement.setstring(1, firstargu); callablestatement.setstring(2, secondargu) callablestatement.registeroutparameter(3, types.varchar); callablestatement.registeroutparameter(4, types.varchar); resultset resultset = callablestatement.executequery(); string firstreturnedresult = callablestatement.getstring(3);//getting returned result string secondreturnedresult = callablestatement.getstring(4);//getting returned result note: call various usecases demand change procedure accordingly
Comments
Post a Comment