CODEWITHSUNDEEP
sqlsql-serverdatabaseoracle-database
AsconX
AsconX

Reputation: 171

Simple SQL query that works for Oracle and SQL Server

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.

Upvotes: 1

Views: 382

Answers (4)

ninesided
ninesided

Reputation: 23273

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

Upvotes: 2

ninesided
ninesided

Reputation: 23273

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.

Upvotes: 0

Aravinth Kannan
Aravinth Kannan

Reputation: 535

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?

Upvotes: 0

cms_mgr
cms_mgr

Reputation: 2017

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).

Upvotes: 0

Related Questions