| Joe Weinstein 2005-12-29, 11:23 am |
|
Christophe wrote:
> I would like to pass array ( like java.sql.Array) parameters to my SELECT
> query.
> I have found syntax for T-SQL :
>
> declare @test table( id varchar(50));
> insert into @test values ( 'Hello World!' )
> select * from @test
>
> But when I try this in JDBC it's failed :
>
> stmt.execute( "declare @test table( id varchar(50))" );
> stmt.execute( "insert into @test values ( 'coucou' )" );
> ResultSet rs = stmt.executeQuery( "select * from @test" );
>
> com.microsoft.sqlserver.jdbc.SQLServerException: Must declare the variable
> '@test'.
>
> Is there another way to pass Array parameters to a Statement ?
>
> thanks,
>
> --Christophe.
The problem is that the different execute() calls are different contexts,
so a local variable only exists for a single execute() call. Do this:
String foo = "declare @test table( id varchar(50))"
+ " insert into @test values ( 'coucou' )"
+ " select * from @test" ;
stmt.execute( foo );
Because the SQL does inserts as well as the select, it will
return an update count and then the result set. Here is
an optimal way to handle all the in-line returns of any
sort of statement:
boolean getResultSet = stmt.execute();
int updateCount = -1;
while (true) { // handle all in-line results from any SQL or procedure
if (getResultSet) {
ResultSet r = stmt.getResultSet();
while (r.next()) {
// process result set
}
r.close();
} else {
updateCount = stmt.getUpdateCount();
if (updateCount != -1) {
;// process update count
}
}
if ((!getResultSet) && (updateCount == -1)) break; // done with loop
getResultSet = stmt.getMoreResults();
}
// Get your output parameters now if any...
HTH,
Joe Weinstein at BEA Systems
|