pbl sql server cmp, preparedStatements and null, help please

[nfrank]

Hello,

I have a very worrying problem. I am using JBOSS 3.2.1 with SQLServer 2000.

When I (or the CMP engine) perform a preparedStatement with a null parameter (I use the setNull and I guess the CMP engine also does), the database handle it as an empty value and not as a NULL... I have tried with several jdbc drivers (oracle, datadirect, ...) so I am confortable saying it doesn't come from the driver but from the database.

Does anyone met this kind of problem ? Any idea ?

This is a very serious issue for me, so thank's for helping me.

Nicolas

ex:
SELECT id from TEST where username=? AND somefield=? with first arg = TEST and second arg is NULL does not work ... (of course with IS NULL it's ok... but does not really help)

[nfrank]

It seems like adding in the preparedStatement query SET ANSI_NULLS OFF fix my problem !
As I don't want to put this in all my queries (and I don't have anyway to add it in queries generated by the CMP engine), how can I configure put this property on all my connection at cretion time (in the pool) ?

Thank's.

Nicolas