Chapter 6. SQL Syntax

Index:

  1. Select
  2. Insert
  3. Update
  4. Delete
  5. CREATE CATALOG
  6. CREATE DATABASE
  7. DROP DATABASE
  8. FLUSH DATABASE
  9. CREATE TABLE
  10. DROP TABLE
  11. ALTER TABLE
  12. TRUNCATE TABLE
  13. PACK TABLE
  14. LOCK TABLE
  15. UNLOCK TABLE
  16. CREATE VIEW
  17. DROP VIEW
  18. CREATE SEQUENCE
  19. DROP SEQUENCE
  20. ALTER SEQUENCE
  21. SET TRANSACTION
  22. START TRANSACTION
  23. COMMIT
  24. ROLLBACK
  25. SAVEPOINT
  26. RELEASE SAVEPOINT
  27. Call Procedure
  28. EXPLAIN
  29. Pseudo Tables
  30. Pseudo Columns
  31. Pseudo Variables
  32. Split Multivalue Column Into Rows
  33. Declare Variable
  34. SET Variable
  35. Comment Syntax
  36. SQL States

Use ";" to separate multi sql statements. "reserved word" or {v 'reserved word'} is used to quote a column with reserved word name in SQL statement. The HXTT Word supports using DATE, TIME, TIMESTAMP, GROUP, ORDER, KEY, DESC, SEQUENCE, INCREMENT, MINVALUE, MAXVALUE, CACHE, CHECK, CYCLE, OTHER, SET, INT, UNIQUE, LEVEL, RELEASE, INDEX, TOP, PACK, CALL, CONNECT, START, PRIMARY, COMMENT, TRANSACTION, REFERENCES, and UPDATE directly in SQL, although they're reserved words too.

SELECT [ALL | DISTINCT [ ON ( expression [, ...] ) ] ] | DISTINCTROW [TOP n [PERCENT]] select_list [INTO variable [, ...] | INTO OUTFILE 'outfileName' ] FROM table_reference_list [WHERE condition_expression] [[START WITH initial-condition] CONNECT BY [NOCYCLE] recurse-condition] [group_by_clause] [HAVING condition_expression] [union_clause] [order_by_clause] [LIMIT [offset,] [limit] ] [FOR UPDATE]

select_list: { expression [ [AS] columnAlias] | table.* | * } [,...]

table_reference_list: {table_reference | table_join} [,...]

table_reference: { { table_name | subquery | (table_join) | (VALUES expression[, ...] ) AS tableName(columnName[,...])} [ [AS] tableAlias] } [pivot_clause] [unpivot_clause]

table_name: { [catalog.]tableName} | {UNC path}

table_join: table_reference join_clause [join_clause,...]

join_clause: [NATURAL] { INNER | { [ LEFT | RIGHT | FULL] [OUTER] } } JOIN table_reference [ ON condition_expression | USING(column1,column2,...) ]

condition_expression: an expression which should return a boolean value.

pivot_clause: PIVOT ( aggregate_function(value_column) FOR pivot_expr IN (column_list) ) [AS] tableAlias

unpivot_clause: UNPIVOT ( value_column FOR pivot_column IN (column_list) ) [AS] tableAlias

group_by_clause: GROUP BY expression [,...] [WITH ROLLUP | CUBE]

union_clause: { UNION | INTERSECT | EXCEPT | MINUS } [ ALL ] select_statement [ union_clause ...]

order_by_clause: ORDER BY expression [ASC|DESC] [,...]

DISTINCT specifies that duplicate rows are discarded. A duplicate row is when each corresponding select_list column has the same value. DISTINCT has no effect on constant value, and pseudo columns(_rowid_).

expression: a complicated expression which can include parentheses, logical operator(NOT, AND, OR), positives/minus sign(+, -), arithmetical operator(+,-,*,/,%), string operator(|| (left string concat right string), +(left string concat right string), -(trim left string then concat rightstring), $(check whether left string is contained in right string), comparison operator(>, >=, =, ==, <=, <, !=, <>), bitwise logical operator(&, |, ~, ^, <<, >>), [NOT] LIKE pattern {escape 'escape_character'},[NOT] ILIKE pattern {escape 'escape_character'}, IS [NOT] NULL, BETWEEN ... AND ..., [NOT] IN, [NOT] EXISTS, [ALL|ANY|SOME] (subquery), [NOT] CASE WHEN expr THEN result [WHEN expr THEN result ...] [ELSE expr] END, CASE expr WHEN compare_expr THEN result [WHEN compare_expr THEN result ...] [ELSE result] END, SQL Escape Syntax({d 'yyyy-mm-dd'}, {t 'hh:mm:ss'}, {ts 'yyyy-mm-dd hh:mm:ss.f...'},{v 'reserved_word'}, {fn functionExpression}, {escape 'oneEcapeCharacter'}, {"varbinary" 'string'}), function(more than 200), aggregate function(MAX, MIN, AVG, COUNT, SUM, STD, STDDEV), constant(null, true, false, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, date, time, timestamp, number, string), column, parameter(?), subquery(single-row subquery, multirow subquery, multiple-column subquery, inline views, correlated subquery) and so on.

"SELECT select_list" can be used to get some calculated values through an one-row ResultSet. Column can be used in all sql except for "SELECT select_list". Parameter(?) can only be used in PreparedStatement.

SELECT sql is used to query MS Word objects (table, paragraph, comment, picture, comment, header, footer, hyperlink, and so on).

For instance:

select val('123.222')
SELECT top 8 percent * FROM sz9010;
select *,children from testword.pictures;
select *,children from testdoc.vtable1;
select *,children from testdoc.vtable2;
select *,children from testdoc.celltable1;
select *,children from testdoc.celltable1 where rowid=1146756269056;
select *,children from testdoc.table1;
select *,children from testdoc.vtable1;
select *,children from testdoc.htable1;
select *,children from testdoc.celltable1;
select * from testdoc.properties;
select *,children,objectNo,objectType from testdoc.tables;
select *,children from testdoc.vtable1;
select *,children from testdoc.htable2;
select *,children from testdoc.celltable3;
select *,children from testdoc;
select *,children from testdoc.paragraphs;
select *,children from testdoc.paragraph1;
select *,children from testdoc.headers;
select *,children from testdoc.footers;
select *,children from testdoc.comments;
select *,children from testdoc.tables;
select *,children from testdoc.pictures;
select *,children from testdoc.hyperlinks;
select * from testdoc.comments;

INSERT INTO table_name [ [AS] tableAlias] [ ( column_identifier [,...] ) ] { VALUES ( expression [, ...] ) | VALUES expression [, ...] | VALUES ( expression [, ...] ),... | SELECT query | ? }

column_identifier = columnName | "reserved_word" | {v 'reserved_word'}

INSERT sql is used to insert text or MS Word objects(table, paragraph, comment, picture, and so on). You can assign font size and text format through variable(CURRENT_Paragraph,CURRENT_TextFormat).

For instance:

insert into testword.paragraph1 (text) values('A demo line');
insert into testword.paragraph1 (text) values('New demo line for paragraph1'+RAND());
insert into testword.paragraph2 (text) values('demo line for paragraph2');
insert into testword (text) values('demo new paragraph');
insert into he.table1 (child1,child2) select children as child1,'Another demo' as child2 from testdoc.tables where objectno=1;'/*Warning: Clone a table into a cell*/
insert into he.table2 (child1,child2) select children as child1,'Another demo' as child2 from testdoc.table1';/*Warning: Clone a table row into a cell*/
create table if not exists he.paragraphs select 'Warning: Clone a table into a cell: insert into he.table1 (child1,child2) select children as child1,''A nother demo'' as child2 from testdoc.tables where objectno=1;'; insert into he.table1 (child1,child2) select children as child1,'Another demo' as child2 from testdoc.tables where objectno=1;' create table if not exists he.paragraphs select 'Warning: Clone a table row into a cell: insert into he.table2 (child1,child2) select children as child1,''A nother demo'' as child2 from testdoc.table1'; insert into he.table2 (child1,child2) select children as child1,'Another demo' as child2 from testdoc.table1' insert into testdoc.comments (rowid,text) select rowid, 'A demo comment ' from testword.paragraph2;/* will append a comment for paragraph2 */ insert into testdoc.comments (author,text) select 'Tom Cat' , 'HXTT Word demo comment' ;/* will append a comment after the end */ insert into _memory_.testword.paragraphs (text) values('A demo line');
insert into _memory_.testword.paragraph2 (text) values(' append some demo text for paragraph2');
insert into _memory_.testword.paragraph3 (text) values(' paragraph3');
create table if not exists testdoc.hyperlink1 (url varchar default 'http://www.hxtt.com/',text varchar default 'A demo text');/* will create a hyperlink if there's no hyperlink */ insert into testdoc.hyperlinks (rowid,url,text) select rowid, 'http://www.hxtt.com/','A demo text' from testword.paragraph2;/* will append a hyperlink for paragraph2 */
insert into testdoc.hyperlinks (url,text)select 'http://www.hxtt.com/word.html' , 'HXTT Word' ;/* will append a hyperlink after the end */

UPDATE table_name [ [AS] tableAlias] SET [column_identifier | (column_identifier,...) ] = expression [,...] [WHERE condition_expression]
UPDATE table_reference_list SET [column_identifier | (column_identifier,...) ] = expression [,...] [WHERE condition_expression]

UPDATE sql is used to set text or MS Word objects(table, paragraph, comment, picture, and so on).

For instance:

update testword.paragraphs set self->'comment'='A comment' where recno()=2;
update testword.paragraph1 set self->'color'='FF00FF';
update _memory_.testword.paragraphs set text=replace(text,'demo','hello') where recno()=2;
update testword.paragraphs set text=replace(text,'demo','hello') where recno()=2;
update testword.paragraphs set text=replace(text,'demo','hello') where recno()=5;
create table if not exists testword.header1 (text varchar default 'A header demo');
update testword.header1 set text ='A header demo2';
create table if not exists testword.footer1 (text varchar );
update testword.footer1 set text ='A footer demo2';
update testdoc.comments set text='A demo comment' where recno()=3;

DELETE FROM table_name [ [AS] tableAlias] [WHERE condition_expression]

Removes rows in a table according to condition_expression.
DELETE sql is used to set text or MS Word objects(table, paragraph, comment, picture, and so on).

For instance:

delete from testword.paragraph3; /* become a blank paragraph */ delete from testword.vtable1; /* become a blank table but leave the first line */

CREATE CATALOG [IF NOT EXISTS] catalogName

Create a subdirectory to contain database files.

For instance:

create catalog if not exists data222;

CREATE DATABASE [IF NOT EXISTS] databaseName

Create a new MS Word document, the URL format must be assigned as a directory, for example,"jdbc:word:////usr/data"

create database if not exists testword;
create database if not exists _memory_.testword;

DROP DATABASE [IF EXISTS] compressed-file-name

The DROP DATABASE statement is used to delete a database. IF that database doesn't exist without using IF EXIST, an SQLException will be thrown.

FLUSH DATABASE [IF EXISTS] databaseName

Flush all cached modications to database file.

For instance:

flush DATABASE db1;

CREATE TABLE [IF NOT EXISTS] table_name [(column_identifier data_type [constraint] [,...] [, constraint_clause [,...] ] )] [ [AS] SELECT query | ? ]

data_type: CHAR(n) | CHARACTER(n) | VARCHAR[(n)] | BINARY (n) | VARBINARY (n) | NUMERIC(n1[,n2]) | DEC[IMAL](n1[,n2]) | INT[EGER] [ AUTO_INCREMENT] | SMALLINT | FLOAT [(n)] | REAL | DOUBLE | BIT | BOOLEAN | DATE [(dateFormat)] | TIME [(dateFormat)] | TIMESTAMP [(dateFormat)] | LONGVARCHAR [(n)] | LONGVARBINARY [(n)] | JAVA_OBJECT [(n)] | CLOB | BLOB| OTHER(type_name [,n]) | NUMBER[(n1[,n2])]

n, n1,n2: positive integer, n2 can be 0

constraint: [NULL| NOT NULL] [UNIQUE] [DEFAULT expression] [PRIMARY KEY] [COMMENT 'string']

constraint_clause: [ CONSTRAINT constraint_name ] PRIMARY KEY (column1, column2, . column_n) | CONSTRAINT constraint_name FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column1, column2, ... column_n) [ON DELETE CASCADE] [ON UPDATE CASCADE] | [ CONSTRAINT constraint_name ] UNIQUE (column1, column2, . column_n) | CONSTRAINT constraint_name CHECK (column_name condition)

Temporary table is only visible in the current connection.

Table name is using to create MS Word object.

For instance:

Table name is using to create MS Word object.

To create a MS Word document or insert MS Word objects from another MS Word document.
        create table if not exists aDemoFile select children from testdoc;/* copy all objects from testdoc file*/
        create table if not exists he select children from testdoc.comments;/*copy only comments*/
        create table if not exists he select children from testdoc.headers;/* copy header */
        create table if not exists he select children from testdoc.hyperlinks;/* copy all hyperlinks from testdoc file*/

To create a paragraph.
        create table if not exists he.paragraphs select 'That text content will be append as a new paragraph';
        create table if not exists testword.paragraph1 (nothing int);
        create table if not exists _memory_.testword.paragraph1 (text longvarchar) select 'abcefg';
        create table if not exists testword.paragraph1 (size varchar(2) default 'A5' );
        create table if not exists testword.paragraph2 select 'A demo line for create paragraph2' as text;
        create table if not exists testword.paragraph3 (text longvarchar) select 'A demo line for create paragraph3';
        create table if not exists testword.paragraph4 (text longvarchar) select 'A demo line for create paragraph4';

For MS Word table, you can use tables or tableN format to create table or insert table rows. vable means vertical table, htable means horizontal table, and celltable means every cell as a data row.
        create table if not exists he.celltable1 select children from testdoc.celltable1;
        create table if not exists he.htable1 select children from testdoc.htable1;
        create table if not exists he.vtable2 select children from testdoc.vtable1;
        create table if not exists he.celltable3 select children from testdoc.celltable1;
        create table if not exists he.vtable1 select children from he2.vtable1;/*It will create the first table in he.docx file, append all data rows, except the header row, from the first vertical table */
        create table if not exists testword.vtable1 (size varchar(5) default '4x3', alignment varchar default 'center',name varchar(5),phone int,age int);/* table size rows,cols: nxn or n,n *//* table alignment: center,left,right,both *//* table verticalAlignment: center,left,right,both */

        create table if not exists testword.htable2 (size varchar(5) default '3x6', name varchar(5),phone int,age int);
        create table if not exists testword.celltable3 (name varchar(5) default 'Tom Cat',phone int,age int,sex boolean);         create table if not exists testword.table1 (size varchar default '3x5');         create table if not exists testword.table2 (product varchar, amount int);         create table if not exists he.vtables select children from testdoc.vtable1;/* Clone a vertical table*/
        create table if not exists he.celltables select children from testdoc.celltable1;/*'Clone a cell table*/
        create table if not exists he.vtable2 select children from testdoc.vtable1;/*Clone or append text into vertical table2*/
        create table if not exists he.htables select children from testdoc.htable1;/*Clone a horizontal table*/
        create table if not exists he.vtables select children from testdoc.htable1;/*Rotate text to create a vertical table*/
        create table if not exists he.htable3 select children from testdoc.vtable1;/*Clone or append rotated text into a horizontal table 3*/
        create table if not exists he.tables select date() as child1,children as child2 from testdoc.tables where objectno=1;/*Warning: Clone a table into a cell*/
        create table if not exists he.tables select date() as child1,children as child2 from testdoc.table1;/*Warning: Clone a table row into a cell*/
        create table if not exists he.tables select date() as child1,children as child2 from testdoc.table1;/* complicate sqyl: to create a table, which will contain two column, but the second cell will contain another table from testdoc file*/
        create table if not exists testword.celltable1 (size varchar(5) default '3x6', name varchar(5),phone int,age int);/*Create a table 3(row)x6(column), use name,'',phone,'',age,'' to set the first row*/
create table if not exists testword.celltable1 (name varchar(5),phone int,age int);

CREATE TABLe sql can create header, footer, picture, or hyperlink too.
        create table if not exists testword.header1 (text varchar default 'A header demo');
        create table if not exists testword.footer1 (text varchar );
/* picture size: nxn width,height */         create table if not exists testword.picture1 (fileName varchar default 'icon.gif', size varchar(5) default '128x128');
        create table if not exists testdoc.hyperlink1 (url varchar default 'http://www.hxtt.com/',text varchar default 'A demo text');/* will create a hyperlink if there's no hyperlink */

DROP TABLE [IF EXISTS] table_name

Removes a table, and its indexes from the database. IF that table doesn't exist without using IF EXIST, an SQLException will be thrown.

It can remove all kinds of MS Word object.

For instance:

drop table if exists testword.paragraph4;

ALTER TABLE [IF EXISTS] table_name alter_specification [,...]

alter_specification: {{ADD|MODIFY} column_identifier data_type [constraint]}| DROP column_identifier | RENAME column_identifier 1 TO column_identifier 2 | RENAME TO table_name2 | ADD constraint_clause

It can be used to modify MS Word object's properties.

For instance:

alter table testword modify size varchar default 'A4'; alter table _memory_.testword.paragraph3 modify alignment varchar default 'CENTER'; alter table testword.paragraph3 modify size varchar default 'A4';

TRUNCATE TABLE [IF EXISTS] table_name

Remove all table rows.

It will clear all child objects in a MS Word object.

For instance:

truncate table _memory_.testword.paragraph2;/* become a blank paragraph */ truncate table _memory_.testword.paragraphs;/*clear all paragraphs*/

PACK TABLE [IF EXISTS] table_name

It can be used to remove an empty MS Word object.
pack table testword.paragraphs;/*remove all blank paragraphs*/

LOCK TABLE table_name

lock the table. Returns 1 if sucess, 0 if failed to lock a table.

For instance:

lock table table2;

UNLOCK TABLE table_name

unlock the table. Returns 1 if sucess, 0 if failed to unlock a table.

For instance:

unlock table table2;

CREATE [OR REPLACE] [ TEMP | TEMPORARY ] VIEW table [ ( column_identifier [,...] ) ] [AS ] SELECT query

Creates a virtual table whose contents (columns and rows) are defined by a query. Temporary view is only visible in the current connection.

For instance:

create or replace temporary view abcv (aaa) select FEC_ANAL FROM ANALISIS;

DROP VIEW [IF EXISTS] table

Drop a virtual table.

For instance:

drop view if exists abcv;

CREATE SEQUENCE [IF NOT EXISTS] sequence_name [AS {INT|SMALLINT|TINYINT|BIGINT}] [START [WITH] n] [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE] [ MAXVALUE n | NO MAXVALUE ] [ CACHE n | NO CACHE] [ [ NO ] CYCLE ]

sequence_name: [catalog.]sequenceName

The optional clause START WITH n allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones. The optional clause INCREMENT BY n specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1. The optional clause MINVALUE n determines the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE is specified, then defaults will be used. The defaults are 1 and -128(-32768,0x80000000,0x8000000000000000L) for ascending and descending sequences, respectively. The optional clause MAXVALUE n determines the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used. The defaults are 127(32767,0x7fffffff,0x7fffffffffffffffL)and -1 for ascending and descending sequences, respectively. The optional clause CACHE cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default. The maximum value for cache is 65535. The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively. If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value will throw an exception. If neither CYCLE or NO CYCLE are specified, NO CYCLE is the default.

For instance:

create sequence if not exists userID start WITH 100 increment by 2 maxvalue 2000 cache 5 cycle;

DROP SEQUENCE [IF EXISTS] sequence_name

Removes a sequence from the database. IF that sequence doesn't exist without using IF EXIST, an SQLException will be thrown.

For instance:

drop sequence if exists userID;

ALTER SEQUENCE sequence_name [AS {INT|SMALLINT|TINYINT|BIGINT}] [RESTART [WITH] n] [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE] [ MAXVALUE n | NO MAXVALUE ] [ CACHE n | NO CACHE] [ [ NO ] CYCLE ]

ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameter not specifically set in the ALTER SEQUENCE command retains its prior setting.

For instance:

alter sequence userID restart WITH 100 increment by 1 maxvalue 5000;

SET TRANSACTION transaction_mode [, ...]

transaction_mode: { ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } | { READ WRITE | READ ONLY }

Sets the transaction characteristics of the current transaction. It effects any subsequent transactions in the same connection. java.sql.Connection.setTransactionIsolation(int level) and java.sql.Connection.setReadOnly(boolean readOnly) can do the same task.

For instance:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION [ transaction_mode [, ...] ]

Begins a new transaction block. java.sql.Connection.setAutoCommit(false), java.sql.Connection.setTransactionIsolation(int level), and java.sql.Connection.setReadOnly(boolean readOnly) can do the same task.

For instance:

START TRANSACTION;

COMMIT [WORK]

Terminates the current transaction and makes all changes under the transaction persistent. It commits the changes to the database. java.sql.Connection.commit() can do the same task.

For instance:

commit;

ROLLBACK [WORK] [ TO [ SAVEPOINT ] savepoint_name]

Without savepoint_name, terminates the current transaction and rescinds all changes made under the transaction. It rolls back the changes to the database. With savepoint_name, rolls back all commands that were executed after the savepoint was established. java.sql.Connection.rollback() can do the same task of ROLLBACK [WORK] sql.

For instance:

rollback;

SAVEPOINT savepoint_name

SAVEPOINT establishes a new savepoint within the current transaction. java.sql.Connection.setSavepoint(String name) and java.sql.Connection.setSavepoint() can do the same task.

For instance:

savepoint t1;

RELEASE SAVEPOINT savepoint_name

Destroys a savepoint previously defined in the current transaction. java.sql.Connection.releaseSavepoint(Savepoint savepoint) can do the same task.

For instance:

release savepoint t1;

{ [ ? = ] call procedure_name [ ( ? [, ? [ , ... ]] ) ] }

java.sql.CallableStatement can be used to call stored procedure.

For instance:

{call myview(?)};

EXPLAIN [SQL] anySQL

EXPLAIN SQL anySQL provides SQL syntax analysis feature like INFORMATION_PARSER.sql table

For instance:

explain sql select RECNO(),vrrdnr,Vrrdvnr,STR(vrrdnr),STR(Vrrdvnr,2),STR(vrrdnr)+STR(Vrrdvnr,2),* from Voorraad where STR(vrrdnr)+STR(Vrrdvnr,2)='4567812';

EXPLAIN anySQL shows how the tables involved in the statement will be scanned by index scanned or sequential scan.

For instance:

explain select RECNO(),vrrdnr,Vrrdvnr,STR(vrrdnr),STR(Vrrdvnr,2),STR(vrrdnr)+STR(Vrrdvnr,2),* from Voorraad where STR(vrrdnr)+STR(Vrrdvnr,2)=' 4567812';

Pseudo Tables

MS Word file name can be used to access all paragraphs, tables, headers, and footers.
nameS format is using to access the serial of same objects. For instance, paragraphs, tables, header, footers, comments, pictures, and hyperlinks.
nameN(n is an int object number from 1 to n) is a pseudo table name to access MS Word object. For instance,
For table obejct, you can use tables(child1, child2,... as column name), vtables(vertical table, first row as column names), htable(horizontal table, first column as column names),celltable( cell table, every cell become a data row).

You can use tables(child1, child2,... as column name), vtables(vertical table, first row as column names), htable(horizontal table, first column as column names),celltable( cell table, every cell become a data row). "select *,text from testpdf.vtable1;" will return all text content information in the first table file.
You can use
update testpdf.vtable1 set child1=?,child2= where child1=? and recno()=?; to set any content
or
create table if not exist testdoc.vtable1 select children from anotherfile.vtable1;
to a table from another document.

properties: used to show document property (creator,title,keywords,subect,decription, and so on).
body: used to access all paragraphs and tables.

The INFORMATION_SCHEMA implementation include SCHEMATA, TABLES, COLUMNS, and DATABASES. For instance,

SELECT TABLE_SCHEM FROM INFORMATION_SCHEMA.SCHEMATA;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE' AND TABLE_SCHEMA='SCHEMA-NAME';
SELECT column_name,data_type,column_default,is_nullable FROM information_schema.tables AS t JOIN information_schema.columns AS c ON t.table_catalog=c.table_catalog AND t.table_schem=c.table_schem AND t.table_name=c.table_name WHERE t.table_name='TABLE-NAME';
SELECT column_name, data_type, character_maximum_length, numeric_precision, column_default, is_nullable FROM information_schema.tables as t JOIN information_schema.columns AS c ON t.table_catalog=c.table_catalog AND t.table_schem=c.table_schem AND t.table_name=c.table_name WHERE c.table_schema='TABLE-SCHEMA' AND c.table_name='TABLE-NAME';

The INFORMATION_PARSER.sql provides SQL syntax analysis feature. For instance,

Note: Use "EXPLAIN SQL aSqlStatement" can get the same result.
            sql =  "SELECT * FROM INFORMATION_PARSER.sql WHERE SQL_COMMAND = ? and OBJECT_TYPE='TABLE'";
            sql =  "SELECT SQL_TYPE,OBJECT_TYPE,OBJECT_NAME FROM  INFORMATION_PARSER.SQL WHERE SQL_COMMAND= ?";


            pstmt = con.prepareStatement(sql);

             pstmt.setString(1, "select * from a.test out; SELECT efg,3+2 as dd FROM ABC;update a set a=3;");
      //      pstmt.setString(1, "select EFG from a.test, test2 where test.b=test2.c");
//            pstmt.setString(1, "select EFG from (select * from abc);select EFG from (select * from abc) as o;");
    //        pstmt.setString(1, "select EFG from a.test left join test2 where test.b=test2.c; SELECT A FROM B UNION SELECT C FROM D;");
            ResultSet rs = pstmt.executeQuery();


Pseudo Columns

text: used to get the main text content of MS Word object
children: used to quote all children of a MS Word object for import/export
chileN(n is an int object number from 1 to n): is a pseudo column name to access a child of a MS Word object.
self: used to quote ta MS Word object.
rowId: used to quote a speicial row in an object table.
objectNo: the object number in an object table, which can be same for different object type.
objectType: the object type of a MS Word object
columNo, rowNo: is useful only for celltable object.
url: is for hyperlink object
author: is for comment object
fileName: is for picture object
For instance, update testword.paragraph1 set self->'color'='FF00FF';
select rowId,text,array_ndims(children),children,children[1]->'color',children[1]#>'text' from testword.paragraph1 where array_ndims(children)>0;

_rowid_, is a pseudo column as primary key. A _rowid_ identifies a row in a table.

_values_, is a pseudo read-only column for all values in a data row.

level is a pseudo column that can be used in hierarchical queries (start with .. connect by). For records that appear in the root, level is 1, for their (direct) children, level is 2 and so on.

Pseudo Variable

CURRENT_DATE is a pseudo variable which returns the current date.

CURRENT_TIME is a pseudo variable which returns the current time.

CURRENT_TIMESTAMP is a pseudo variable which returns the current timestamp.

_CURRENT_ is a pseudo catalog(or schema name) which returns the current catalog (or schema).

CURRENT_Page is a pseudo variable which used to set page size.
Size: A0, A1, A2, A3, A4, A5, A6, Legal, Letter, nxn (for instance, 8.5x1.4 means 8.5" x 14")
Rotate integer (Optional; inheritable) The number of degrees by which the page shall be rotated clockwise when displayed or printed. The value shall be a multiple of 90. Default value: 0.
head,foot,leftMargin,rightMargin: use to define page margin

select CURRENT_Page;
DECLARE _Page_Demo '{"size":"A4"}'; select _Page_Demo,_Page_Demo->'size'; set _Page_Demo->'size'='A2'; select _Page_Demo; set CURRENT_Page=_Page_Demo; select CURRENT_Page; /* head,foot,leftMargin,rightMargin */ set CURRENT_Page->'header'=32; DECLARE CURRENT_Page '{"size":"A4","rotate":0}'; /*size: A0, A1, A2, A3, A4, A5, A6, Legal, Letter, nxn (for instance, 8.5x1.4 means 8.5" x 14")*/ /* Rotate integer (Optional; inheritable) The number of degrees by which the paragraph shall be rotated clockwise when displayed or printed. The value shall be a multiple of 90. Default value: 0.*/ select CURRENT_Page; set CURRENT_Page->'size'='A6'; select CURRENT_Page;

CURRENT_Font is a pseudo variable which used to set font information for current text operation
Name: Courier, Courier-Bold, Courier-BoldOblique, Courier-Oblique, Helvetica, Helvetica-Bold, Helvetica-BoldOblique, Helvetica-Oblique,Times-Roman, Times-Bold, Times-BoldItalic, Times-Italic, and so on

DECLARE CURRENT_Font '{"name":"Courier"}';
DECLARE _Font_Demo '{"name":"Times-Roman"}';
set CURRENT_Font=_Font_Demo;

CURRENT_TextFormat is a pseudo variable which used to set text format information for current text operation

DECLARE CURRENT_TextFormat '{"fontSize":12,"color":"BED4F1","italic":false,"bold":false,"shadow":false,"strike":false,"underline":"dash"}';

CURRENT_Paragraph is a pseudo variable which used to set text format information for current parapgrah operation

set CURRENT_Paragraph->'verticalAlignment'='center';/* table verticalAlignment: center,left,right,both */
set CURRENT_Paragraph->'alignment'='left';/*alignment: BOTH, CENTER, DISTRIBUTE, HIGH_KASHIDA, LEFT, LOW_KASHIDA, MEDIUM_KASHIDA, NUM_TAB, RIGHT, THAI_DISTRIBUTE */ set CURRENT_Paragraph->'wordwrap'=true;
set CURRENT_Paragraph->'indentationFirstLine'=480;/* indentationFirstLine: uset to indent the First Line of Every Paragraph*/
/* color: in the hex form "RRGGBB" */
/*underline: DASH, DASH_DOT_DOT_HEAVY, DASH_DOT_HEAVY, DASH_LONG, DASH_LONG_HEAVY, DASHED_HEAVY, DOT_DASH, DOT_DOT_DASH, DOTTED, DOTTED_HEAVY, DOUBLE, NONE, SINGLE, THICK, WAVE, WAVY_DOUBLE, WAVY_HEAVY, WORDS */

Split Multivalue Column Into Rows

If each row has multiple multi value columns, a special subquery table can be used in special join sql. For instance,

select User,Role from aTable,(select split(aTable.Roles,',') as Role) AS bTable;

select User,Role,Year from aTable,(select split(aTable.Roles,',') as Role,split(aTable.Years,',') as Year) AS bTable;

DECLARE variable_name[,...] type [DEFAULT expression]
DECLARE var_name[,...] 'jsonText'

Variable is visiable only in the same connection.

For instance:

DECLARE abc CHAR(20) DEFAULT 'Hello';
DECLARE x, y INT;
DECLARE _Page_Demo '{"size":"A4","rotate":0}';
select _Page_Demo; select _Page_Demo->'size';

SET variable_name[.propertyName] = expression [,...]

expression can be a complicated expresion. BTW, INTO variable[,...] clause of SELECT syntax can set selected columns directly into variables.

For instance:

SET x = 1+int(55.5),y=2;
SELECT name,id INTO x,y FROM table1 WHERE id=33;
SELECT date(),pi() INTO x,y;
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
set _Page_Demo->'size'='A2';

Comment Syntax

#one-line comment
--one-line comment
/*multiline comment*/

For instance:

select * /* column list */ from test;#This is a select sql.

SQL States

SQL State
Description
01001
Cursor operation conflict
01427
single-row subquery returns more than one row
01428
single-column subquery returns more than one column
01429
subquery returns mismatch column number
01436
CONNECT BY loop in user data
01430
single-row subquery returns none row
07006
Restricted data type attribute violation
08000
Connection exception
08003
Connection not open
08007
Connection failure during transaction
08S01
Remote database access failure
0A000
Feature not supported
0A001
Multiple server transactions
21S01
Insert value list does not match column list
22000
Data exception
22019
Invalid escape character
22023
Invalid parameter value
23000
Integrity constraint violation
24000
Invalid cursor state
25000
Invalid transaction state
26000
Invalid SQL statement name
28000
Access denied error
2A000
Direct SQL syntax error or access rule violation
2D000
Invalid transaction termination
2E000
Invalid connection name
34000
Invalid cursor name
34102
Invalid variable name
34103
Invalid funciton name
34104
Invalid index file name
3C000
Duplicate cursor name
3D000
Invalid catalog name
3F000
Invalid schema name
40000
Transaction rollback
42000
Syntax error or access violation
42001
Syntax error
42002
Access violation
42003
Statement has been closed
60000
System errors
99999
Catch all others
C0100
Unknown CodePageID
C0101
Unknown File Format
C0102
Unknown Table Version
C0103
Unknown Index Version
C0104
Corrupt Index File
C0105
Invalid Record Number
C0106
Convert dirty data into null value
S0001
Base table or view already exists
S0021
Index already exists
S0022
Column not found
S1002
Invalid column number
S1009
Invalid Argument value
S1T00
Timeout expired
HY008
Operation canceled

 

Copyright © 2003-2019 Heng Xing Tian Tai Lab | All Rights Reserved. |