Using DESC command in Base.

Creating tables and queries

Using DESC command in Base.

Postby Bhujang » Tue Feb 14, 2012 5:36 pm

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.
OpenOffice 3.3 on Windows Vista SP2
Bhujang
 
Posts: 6
Joined: Sat Feb 11, 2012 4:21 pm

Re: Using DESC command in Base.

Postby RoryOF » Tue Feb 14, 2012 5:56 pm

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!
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31565
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Using DESC command in Base.

Postby Villeroy » Tue Feb 14, 2012 6:03 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28674
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using DESC command in Base.

Postby eremmel » Tue Feb 14, 2012 6:19 pm

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W10 (build 18362), LO 7.0.0.3(x64)
eremmel
 
Posts: 1044
Joined: Tue Dec 30, 2008 1:15 am

Re: Using DESC command in Base.

Postby DACM » Tue Feb 14, 2012 9:42 pm

Bhujang wrote: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. :shock:
Last edited by DACM on Tue Apr 10, 2012 3:02 pm, edited 5 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Using DESC command in Base.

Postby rudolfo » Tue Feb 14, 2012 9:48 pm

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:
man psql wrote: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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Using DESC command in Base.

Postby Villeroy » Tue Feb 14, 2012 10:07 pm

HSQLDB 2.2 documentation wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28674
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest