while using some dinamyc store procedures I get in the following error:
[BEA][SQLServer JDBC Driver]Value can not be converted to requested type.
I'm using WL8.1 and Sql Server 2000.
Store procedure contains two different queries where table name is a store procedure's parameter. The first time it works great, after that I always have this error:
Reading bea doc's I found ________________________________________________________ There may be other issues related to caching prepared statements that are not listed here. If you see errors in your system related to prepared statements, you should set the prepared statement cache size to 0, which turns off prepared statement caching, to test if the problem is caused by caching prepared statements. ________________________________________________________
If I set prepared statement cache size to 0 everything works great but that does not seem the better way.
Should we expect Bea to solve this problem? Or whatever else solution?
Sounds like a bug in the connection pool. Used to see something similar in JBoss a couple of years ago.
When caching artifacts like prepared statements the connection pool has to be very careful about the relationship of that cached object to other aspects of the connection state, like the type map. If the pool implementation misses one of those dependencies, you can end up in a situation where your code appears to be doing something reasonable, but the pool is combining that with other information that was left over from previous uses of the statement.
Whenever you get a new connection, the pool is responsible for ensuring that a cached connection works the same as an uncached one, but it is easy for the pool implementor to miss things. The only way I know of to spot if this is happening is with a good debugger (e.g. in JBuilder). You run your application once with no connection pooling, and a second time with connection pooling. You use your debugger to inspect the connection state both times, and look for what is different. If you can spot what it is that the pool isn't doing to clean up the connection, then you can try to manually clean it up yourself (e.g. if the type map is wrong, you can reset the type map yourself).
All of that assumes that the bug is in the connection pool. It is also entirely possible that the bug is in the JDBC driver itself. Microsoft doesn't produce bug-free JDBC drivers; I'm constantly tripping over annoying and stupid things. You might want to experiment with switching your app over to another type of database - just temporarily - to see if the bug is still there (easier done with SQL statements, not easy if you have vendor-specific stored procedure code). If you still see the bug, either the connection pool or your code at fault. If you don't see the bug the driver is almost certainly at fault. [ May 26, 2004: Message edited by: Reid M. Pinchback ]