how much fetchSize(??) should i set?

[naved momin]

i am using ODBC-JDBC bridge for database connectivity with MSSQL 2005, sometimes my application greets me with a exception called connection is busy with the result of another hstm
i have done some research on that and i figured out that it is because of the fetch size , so if i set the fetchSize in Statement like

stm.setFetchSize(??)

will it solve my problem
also what should i specify in the fetchSize(???) so that i should not get any performance hit?
any suggestion are most welcome.

[Martin Vashko]

I'd say you should get rid of the ODBC-JDBC bridge first. Sun/Oracle had always suggested to use native drivers when available, and there certainly are native drivers for MS SQL. There were known issues with ODBC-JDBC last time I checked, and since virtually every database has a native driver, nobody is going to fix them.

So my suggestion is to switch to native drivers and see whether the issue persists.

Setting fetch size is a good idea anyway, I'd say a reasonable one-size-fits-all value could be around 100. Only if you read really wide tables (several dozens of columns), a lower value might be better. As always, you should test the new setting thoroughly.

[naved momin]

Martin Vajsar wrote:I'd say you should get rid of the ODBC-JDBC bridge first. Sun/Oracle had always suggested to use native drivers when available, and there certainly are native drivers for MS SQL. There were known issues with ODBC-JDBC last time I checked, and since virtually every database has a native driver, nobody is going to fix them.

So my suggestion is to switch to native drivers and see whether the issue persists.

Setting fetch size is a good idea anyway, I'd say a reasonable one-size-fits-all value could be around 100. Only if you read really wide tables (several dozens of columns), a lower value might be better. As always, you should test the new setting thoroughly.

can you name some good native drivers that works with MSSQL 2005

[Martin Vashko]

naved momin wrote:can you name some good native drivers that works with MSSQL 2005

Did you already try this one? It atually comes up first if you google up ms sql 2005 jdbc driver.

I know some people prefer the jTDS driver to connect to MS SQL Server. I don't use MS SQL Server, so I can't advice which one to choose. Maybe someone will comment further on this.

[Martin Vashko]

Naved, I'm very sorry for the confusion I've caused. By "native driver" I actually did mean the "type 4 driver". I noticed the confusion when you asked the question about native drivers.