Hi there,

I'm looking for a simple and quick SQL query with no side effect, that
runs on most databases (at least Oracle, MySQL and PostgreSQL). I need
that to test my database connection.

I tried "select 1" but it does not work under Oracle. Any other idea ?

--
Hugo

Hugo wrote:
select 1;

should fail on ALL databases, since there is no FROM clause as required
by the SQL Standard. You would need a table that exists in any
database, but such a table does not exist AFAIK.

There is a dummy one row table that exists in any ORACLE database, so
for ORACLE you would use
select 1 from dual;
and it will return
1
----------
1

What table to use on others DBMS products, i do not know.

Ed Prochak wrote :

Thanks for the tips, but I already tried that too, and I guess "dual"
table it's not ANSI SQL since it does not work for PostgreSQL (but does
for MySQL though).

I will be quite surprised if SQL specification does not contain a dummy
request (like an identity function, or a "nop" instruction).

Maybe I just need to RTFM after all

--
Hugo

hugo.lafayette (AT) gmail (DOT) com wrote:
*Surprise!* ;-)

There is no provision in ANSI SQL for a thing like "dual" in Oracle.
Every query expression must query data from a table. I suppose the
rationale is that if you are just querying constant values, why do you
need to use SQL? Just assign a constant value to a variable in your
application programming language.

Of course, this reasoning I'm inferring from the SQL standard seems to
ignore the many types of expressions available as extensions within
individual implementations of SQL. SELECT VERSION(); SELECT
USERNAME(); SELECT CURDATE(); SELECT LAST_INSERT_ID(); etc. So I'm
not saying that it isn't a useful notion to have a query with no FROM
clause, it's just not part of ANSI SQL.

Anyway, you could simulate Oracle's DUAL table in RDBMS products that
don't offer that extension (e.g. PostgreSQL, MS SQL Server), by doing this:

CREATE TABLE DUAL (DUMMY VARCHAR(1));
INSERT INTO DUAL VALUES ('X'); // insert exactly one row!

Then queries "FROM DUAL" will not be errors.

Regards,
Bill K.

hugo.lafayette (AT) gmail (DOT) com wrote:
ANSI does not specify or require that any tables exist. DUAL was just
convenient for Oracle, and I guess MySQL copied them. Even the
information schema must be accessible as tables/views, but what tables
or views are allowed to be determined by the vendors.

Actually it is not surprising. AFAICT, they assumed you know which DBMS
product you are connecting to, so once connected, you do not need a WHO
AM I feature.

For what you are asking to do, there is no manual that will help AFAIK.
Good luck. Keep us posted on you progress.
Ed

Ed Prochak wrote :

I'm not asking for a "who am i" feature, but just a simple SQL query
that allow me to check that I'm actually connecting to something real.

Actually I'm using Hibernate to connect on several different DBMS. So I
did post a similar question on hibernate forums, but still waiting for
an answer.

--
Hugo

On 07.09.2006 15:55 Hugo wrote:
I don't think there is one universal statement that you can use. You
will have to either create one dummy table in all of your tables (as
others have already suggested) or use a different statement for each DBMS

Thomas


--
It's not a RootKit - it's a Sony

On Thu, 07 Sep 2006 15:55:28 +0200, Hugo wrote:

Seems to me you have only a few variations:

select expression from dual; # potentialy undefined table
select expression; # non-standard SQL

Last time I checked, most languages permit exception handling. So there
are at least two variations I can see:
1) insist that the target database as a 'dual' table to use your s/w;
2) use a try/catch block, potentially up front to set a global for method.



BTW: I have found that, while much individual SQL code is portable
between rdbms implmentations, multi-statement transactions are generally
not portable and cause a lot of performance tuning grief. I'd be
interested in following a discussion around that.

--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.

Bill Karwin wrote :

This is mainly true, but from a philosophical point of view, since SQL
allows arithmetics operations, one could want to use his RDMS to compute
a multiplication ("select 6 * 7"). And what about a substring "select
substring('foobar' FROM 1 FOR 3)" ?

Thanks for that one. I already thought to create a test table, but I'd
rather do otherwise if possible. Maybe I just can't...

--
Hugo

On Thu, 07 Sep 2006 14:43:02 +0000, HansF wrote:


I've also found that the view 'tab' is fairly consistent across the
rdbms I've used. Thus 'select expression from tab' might do the trick.

--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.