I am looking for a small and simple query that works on Oracle and SQL Server.
It is used as a test query to check the connection.
For SQL Server we used SELECT 1, but in Oracle it would have to be SELECT 1 FROM DUAL.
What we plan to use now is SELECT COUNT(*) FROM (sometable) but any ideas for an even simpler query are appreciated.
One simple option is to add a view to SQL Server called DUAL that just returns 1, that way you can have a simple query that works the same in both environments:
SELECT 1 FROM DUAL
If returning data from relations isn't important then:
SELECT 'Hello world';
will rely on a connection as much as anything else. Your RDBMS should return 'Hello world'. Tested on SQL server and PostgreSQL (don't have access to Oracle).
As long as you write query in ANSI standard. It can be executed in all the RDMS.
May be you can try this query....
select ColumnName from TableName where 1=2
BTW, the DBProvider shld have come property to state of DB connectivity... which DB provider does ur application uses?
Does it need to be a query? You could create a simple stored procedure with the same name in both databases, that just returns a constant, and execute it from the application server.
