CODEWITHSUNDEEP
javasqlsql-serverstored-proceduresresultset
Vincent Gunawan
Vincent Gunawan

Reputation: 21

How to get result set from store procedure with parameter in jbdc

i have store procedure with 1 parameter only. I can execute the procedure like this exec proc_name param1 in microsoft SQL server management studio and show result set that i want. But when i try in java with my code like this:

String url = "jdbc:sqlserver://TOSHIBA\\SQLEXPRESS;databaseName=Perpustakaan;integratedSecurity=true";
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    conn = DriverManager.getConnection(url);

    CallableStatement statement = conn.prepareCall("{call search(?)}");
    statement.setString(1,parameter);
    ArrayList<Buku> result = new ArrayList<>();
    statement.execute();
    ResultSet rs = statement.executeQuery();
    while (rs.next()) {
        int tempId = rs.getInt("id");
        String tempJudul = rs.getString("judul");
        result.add(new Buku(tempId, tempJudul));
    }
    return result;

i get an error say 'The statement did not return a result set.' The 'parameter' in statement.setString(1,parameter) is String typed.

this is my store procedure code in case something is wrong

alter proc search
@kata varchar(255)
as
declare @tempResult table
(
    kata varchar(255)
)
declare
    @idx int,
    @katas varchar(255)

set @katas = @kata
set @idx = CHARINDEX(',',@katas,1)

while(@idx != 0)
begin
    insert into @tempResult
    select
        SUBSTRING(@katas,1,@idx-1)
    set @katas = SUBSTRING(@katas,@idx+1,100000000)
    set @idx = CHARINDEX(',',@katas,1)
end
insert into @tempResult
select
    @katas

declare @searchResult table
(
    judul varchar(255)
)
insert into @searchResult
select dbo.buku.judul
from dbo.buku cross join @tempResult
where CHARINDEX(kata, dbo.buku.judul) > 0

select 
    dbo.buku.id, sr.judul
from
    @searchResult as sr join dbo.buku
on
    sr.judul = dbo.buku.judul
group by 
    sr.judul, dbo.buku.id
order by
    COUNT(sr.judul) desc

exec search 'Games'

Upvotes: 2

Views: 958

Answers (2)

Vincent Gunawan
Vincent Gunawan

Reputation: 21

so what i do to make the statement return result set is i add SET NOCOUNT ON in the beginning of my store procedure. set nocount explanation is here.

Upvotes: 0

Veselin Davidov
Veselin Davidov

Reputation: 7071

In order to call stored procedures with callable statement you need to registerOutParameter for the expected results. Also you don't need to executeQuery after the execute becaues you don't have a query and you have already executed your statement

Try to change your code to:

boolean hasResults=statement.execute();

while (hasResults) {
    ResultSet rs = cStmt.getResultSet();
    int tempId = rs.getInt("id");
    String tempJudul = rs.getString("judul");
    result.add(new Buku(tempId, tempJudul));
    hasResults= cStmt.getMoreResults();        
}

But before that you should add the parameters you expect to the statement. Just the way you do :

 statement.setString(1,parameter);
 // Add out parameters here
 statement.registerOutParameter(1, java.sql.Types.INTEGER);
 ....

I cannot be bothered to look at the procedure call right now ;) but I guess it works if you can execute it in management studio

Upvotes: 1

Related Questions