AnySQL.net English

    In AnySQL, we could manipulate LONG/LONG RAW/BLOB/CLOB easily by uploading a client file to the database, however it's impossible in SQL*Plus:

    Create a table with LONG type column, as following:

ASQL> DESC T_LONG

NO# NAME             NULLABLE TYPE
--- ---------------- -------- ----
  1 COL1                      LONG

    Now I will insert a shell script file in to the table, you can insert or update this field by declare a CLOB (for LONG and CLOB columns) or BLOB (for LONG RAW and BLOB columns) type host variable, and define it a file name (including path) as variable value, then reference it (start with ":") in the SQL as following:

ASQL> VAR P_LONG CLOB
ASQL> define p_long=otop
ASQL> insert into t_long values (:p_long);

1 rows affected.

ASQL> commit;

    And we could directly query the long value type in AnySQL directly, maximumly 64KB will be displayed:

    I always hear people are asking how to view the content in LONG, LONG RAW, CLOB and BLOB fields. In SQL*Plus, it's really not easy, but in AnySQL I have special designed commands. This feature allow you to query LONG/LOBs contents to client side OS file (not server side as DBMS_LOB).

    LOB command, the query should return one column and one row with LONG or LOB type.

Usage:
  LOB query >> file
Note :
  >> mean export long/long raw/blob/clob to a file

    LOBEXP command, the query should return two columns, first as file name, second as LONG or LOB type column.

Usage:
  LOBEXP query
Note :
  Query should return tow column as following:
  col1 : CHAR or VARCHAR specify the filename.
  col2 : blob/clob field.

    Following is a demo table in my personal database:

ASQL> SELECT FNAME FROM T_LOB;

FNAME
-----
a.txt
otop

2 rows returned.

ASQL> desc t_LOB;

NO# NAME              NULLABLE TYPE        
--- ----------------- -------- ------------
  1 FNAME                      VARCHAR2(20)
  2 FTEXT                      CLOB

    View it with LOB command:

ASQL> LOB SELECT FTEXT FROM T_LOB WHERE FNAME='otop' >> otop.txt;

Command succeed.

    View it with LOBEXP command:

ASQL> LOBEXP SELECT FNAME, FTEXT FROM T_LOB;

Write to file: a.txt , bytes=37
Write to file: otop , bytes=483
Command succeed.

    Now check the files generated where you run AnySQL, it's so easy here.

    After some research of MySQL, Yong Huang gave me a excellent advice to add a new feature in AnySQL, to display in the form mode other than grid mode. This feature is very useful when display few rows with lots of columns. Let's get some objects for testing.

ASQL> select * from tab where rownum < 5;

TNAME      TABTYPE CLUSTERID
---------- ------- ---------
BONUS      TABLE
CLU_A      CLUSTER
CR_5043802 TABLE
DEPT       TABLE

4 rows returned.

    Then we are required to display the object information from table "USER_OBJECTS", by adding one of patterns ("/g","/G","\G","\g") to the standard select statement, the row will be displayed in form mode. As following.

ASQL> select * from user_objects where object_name='BONUS'/g;

OBJECT_NAME                   : BONUS
SUBOBJECT_NAME                : null
OBJECT_ID                     : 637961
DATA_OBJECT_ID                : 637961
OBJECT_TYPE                   : TABLE
CREATED                       : 2007-05-16 22:03:27.0
LAST_DDL_TIME                 : 2007-05-16 22:03:27.0
TIMESTAMP                     : 2007-05-16:22:03:27
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N

1 rows returned.

    Download patch if you want to enjoy it!