Using DESC command in Base.

[Bhujang]

Actually I am very new to database, My friend showed me in ORACLE that using DESC Table NAme was showing information about columns of table. But running this command in Base was giving error, Why? and please tell how to use this command in Base?

User DACM told me that H2 Database is good for learning SQL, but H2 is also not supporting this command. Please tell me Why one software is supporting DESC command while others are not,I think when DESC is a SQL command all softwares should support this. I will be very thankfull to users for any explanation.

[RoryOF]

Every program usually implements a subset of the master commands. One needs to read the implementation note to find out which commands are supported. Use of computers is not a mindless process - one does actually need to think!

[Villeroy]

No problem. Simply purchase your own Oracle license and get in touch with thousands of useful things that are beyond any standard and only in this particular product.

For anything else you simply have to read and understand the documentation of the respective product.
In case of HSQL 1.8 that is http://hsqldb.org/doc/guide/ch09.html#explain-section

[eremmel]

There are many differences between databases, especially when it comes to non SELECT statements like 'administrative' statements. In Base you can get information about the table structure by going to the table view, selecting a table and than choose the proper option (Edit / Change or alike).
Please start with a tutorial and/or documentation about Base see the topics at top in Base part of forum. Note that Base is not a database, but only a front end. It tries to hide the differences between databases to some extend via the GUI.

[DACM]

Why one software is supporting DESC command while others are not,I think when DESC is a SQL command all softwares should support this.

The Oracle and MySQL DESCRIBE commands are not really SQL, because they're non-standard. It's more accurate to characterize them in the realm of "procedural extensions" which Oracle dubs "PL/SQL." These extensions allow you to create your own commands -- perhaps like the DESCRIBE command -- using existing SQL commands and programming logic (such as to parse the custom command). Many RDBMS's support this functionality in one form or another. Some store the code externally as a text file and require Triggers to run the command. Others store the custom commands internally for use similar to ordinary SQL commands. H2 and HSQLDB 1.8 support external Java stored procedures through Triggers. HSQLDB 2.x adds support for internally-stored SQL procedures.

So long story short, you could theoretically create your own DESCRIBE command in Java for use with H2.

But lets get real. The H2 Console places a plethora of database administration commands at your finger-tips with the click of a mouse -- which pre-fills a GUI text editor with the corresponding SQL query. Your friend may have been showcasing Oracle's SQL*Plus tool by introducing you to the Oracle DESC command. But you actually have the more powerful command-set for such administrative tasks available through mouse-clicks in the H2 Console under the INFORMATION SCHEMA section. And in the process, you get to see and interact with ANSI-standard SQL while also being introduced to the ANSI-standard INFORMATION_SCHEMA. It's good to learn extended functions of individual database engines, such as Oracle's DESC command or H2's SHOW command, but that process should be viewed as a distraction from learning authentic SQL in the early phases.

We can examine an equivalent to the DESC command by clicking on:
INFORMATION SCHEMA > COLUMNS
and watch as the H2 Console pre-fills the necessary SQL command(s) for you...

Run the auto-generated SQL command:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
and you'll get a table with 5x the information available through the Oracle DESC command.

You can add a WHERE clause to the command to make it more exclusive, such as to display a single table or schema:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TEST'
The H2 Console even gives you a list of available options as you type, but it's not a true auto-complete function like you'll find in more advanced tools.

Then you can examine at least 20 other commands available in the same section with your mouse (producing standard, interactive SQL) that DESCRIBE your database in ways that should make your friend's head spin.

[rudolfo]

If you work with Oracle's sqlplus command line tool things are unfortunately not as clear as in other command line terminals. Here is what the manual page of the postgresql command line terminal is saying:

Anything you enter in psql that begins with an unquoted backslash is a
psql meta-command that is processed by psql itself. These commands help
make psql more useful for administration or scripting. Meta-commands
are more commonly called slash or backslash commands.

MySQL has the same backslash concept, SQLite uses the period for "meta" commands. Well, and Oracle, hm, they just use meta commands that might look as SQL commands. And yes you have guessed it DESC is a meta command (among others like CONNECT, EXIT, EXECUTE,..). Now the catch is that a meta command is terminated by the end of the line itself, while a real SQL statement needs a terminator (usually the semicolon ";" but a single slash on a line on its own achieves the same). Try
DESC dual
without a semicolon at the end and then
SELECT sysdate FROM dual
without a semicolon. The first will give you something directly, while the second will still sit there waiting on more input (a ; or a /) from you. Some folks are always using a semicolon, even after meta commands. This will work in most cases because sqlplus ignores the semicolon at the end (try "PROMPT some text;" and you see what I mean). In my opionion using the ; after meta commands justs shows the ignorance and the missing will to read the manual of the one who uses it.

Of course the SQL standard is only for SQL statements. Meta commands can do what they want.

[Villeroy]

Oracle Compatibility
Recent versions of Oracle support Standard SQL syntax for outer joins and many other operations. In addition,
HyperSQL features a setting to support Oracle syntax and semantics for the most widely used non-standard features.

HSQL has something like an "Oracle compatibility mode". Of course it can not support anything that is not part of HSQL.