Active Objects MSSQL boolean issue

[Pepe]

I've had a number of issues with various databases using Active Objects. I was hoping AO would magically handle db abstraction but bygones...

I'm probably not taking the right approach here and can use your thoughts:

Query.select().where("MY_THING_ID = ? AND SOME_PERMISSION = TRUE", someObject.getID()));

which works for mysql and postgres but throws the following in mssql:

Database:
	- name:Microsoft SQL Server
	- version:11.00.3128
	- minor version:0
	- major version:11
Driver:
	- name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
	- version:1.2.2

java.sql.SQLException: Invalid column name 'TRUE'.

Is there a db agnostic way to use TRUE? Something like ActiveObjects.True that handles the variance in databases. Thanks

[Pepe · Accepted solution]

rather than true I needed to use Boolean.TRUE. This seems to work for all dbs.

Query.select().where("MY_THING_ID = ? AND SOME_PERMISSION IS ?", someObject.getID(), Boolean.TRUE));

[Remo Siegwart]

I feel your pain, I've had the same issues with Active Objects...

Without being able to test the code at the moment, I would try to inject the value instead of hardcoding it:

Query.select().where("MY_THING_ID = ? AND SOME_PERMISSION = ?", someObject.getID(), true));

If this still doesn't work, I would try to use "IS" instead:

Query.select().where("MY_THING_ID = ? AND SOME_PERMISSION IS ?", someObject.getID(), true));

Hope this helps.

Cheers
Remo

[Pepe]

Thanks for the input. That hasn't worked for me yet but I'll do a clean build to be sure. Interestingly using IS changes the error to "java.sql.SQLException: Incorrect syntax near '@P1'." That leads to MSSQL requires () around the arugment which leads to another error so I need to dig into it a bit.

[Remo Siegwart]

I feel your pain, I've had the same issues with Active Objects...

Without being able to test the code at the moment, I would try to inject the value instead of hardcoding it:

Query.select().where("MY_THING_ID = ? AND SOME_PERMISSION = ?", someObject.getID(), true));

If this still doesn't work, I would try to use "IS" instead:

Query.select().where("MY_THING_ID = ? AND SOME_PERMISSION IS ?", someObject.getID(), true));

Hope this helps.

Cheers
Remo