Thursday, 11 November 2010

Workheet Hints

SQL Developer currently doesn't support proprietary commands of non Oracle databases like SQL Server, Sybase ... . But a new feature call Worksheet Hints allows these commands to be executed.

SQL Developers Worksheet has to parse commands to identify their start and finish and their type (query, or update ,... ).
We recognize Oracle SQL , PL/SQL and SQL*Plus , but we have yet to work on other databases syntax.

So at the moment if you running a command against MySQL, SQL Server or Sybase, it has to be fairly similar to an Oracle statement for it to be recognized and run correctly.
EX: SELECT, INSERT,UPDATE,DELETE.

Things like MySQL
use [databasename]
or
show databases
are not recognized yet.

Full MySQL recognition will not make it into SQL Developer 3.0 ,
but SQL Developer 3 EA2 does allow you to give the Worksheet parser a hint as to what type of statement it is and how to execute it.

The hint is placed in front of the command, and
the command has to be terminated with a forward slash on its own line, or a semi colon.

The two hints are
/*sqldev:query*/ if your expecting a result set to be returned
/*sqldev:stmt*/ if you are expecting nothing returned

MySQL Example:
If you connect to your MySQL connection you can perform the following
/*sqldev:query*/show databases;
/*sqldev:stmt*/use dermot;
select database();
/*sqldev:stmt*/use information_schema;
select database();

The same goes for SQL Server , Sybase ,.... commands which are not recognized by our Worksheet.

Sybase Example:
/sqldev:query*/sp_help;

Appreciate this is not an ideal solution, but until we recognize the syntax correctly this may help out.
Just to be clear, we are not supporting all MySQL/SQL Server/Sybase commands yet. But hopefully using a "hint" might be of some use.