Caigen DBF SQL Syntax
Index:
Use ";" to separate multi sql statements. For instance, "insert into test (int1) values(1);insert into test (int1) values(2);". "reserved word", [reserved word] or {v 'reserved word'} is used to quote a column with reserved word name in SQL statement, for instance, 'select {v 'RIGHT'},'other' from states where {v 'RIGHT'}=32. The Caigen DBF 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, and UPDATE directly in SQL, although they're reserved words too.
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_column 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] [,...]
_rowid_, is a virtual column as primary key.
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, and _rowid_. For instance, "select distinct 'First Name',name,age from users". 'First Name' will be ignored since it's a constant.
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), condition 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.
For instance:
select val('123.222')
select CONVERT('123',SQL_INTEGER) as a,TTOC({d '1999-10-10'},1) as b, IFNULL(1,33) as c, 123 in(456,123,789,'abc') as d, EXTRACT(DECADE FROM '2001-02-16 20:38:40'), '88'+IIF(3<6,'1','0')
select encode('adsdfsdf');
select decode(encode('adsdfsdf'))+'';
SELECT top 8 percent * FROM data.sz9010;
select distinct top 10 * from test where not deleted() order by int1,char1 desc;
select int1,float1 from test where int1>0 group by int1,double1;
select distinct on (int1) int1,double1 from test;
select sum(int1),max(dec1),min(double1) from test;
SELECT SUM(apmast.fnamount), SUM(glcshi.fnadjamt),SUM(glcshi.fncashamt),SUM(glcshi.fndiscount) FROM apmast, glcshi WHERE apmast.fcinvoice +apmast.fvendno = glcshi.fcinvoice + glcshi.fcnameid AND apmast.fduedate between {d '1999-01-01'} AND {d '1999-11-30'} AND apmast.finvdate <= {d '1999-11-30'};
SELECT cellID, columnID, reference, function, parameter FROM repLayout WHERE reportID = '1' AND rowID = 0 ORDER BY columnID;
select distinct int1,double1 from test group by int1,double1,float1;
select distinct * from test where int1>0
select distinct int1,count(*),sum(int1) from brain.user group by int1
SELECT date1,time1,int1 FROM test where TIMESTAMPdIFF(SQL_TSI_YEAR,time1,{ts '3999-03-24 00:59:23.22222'})<-2000 and date1>{d '1900-01-01'} and date1>{d '1960-01-01'} and date1<{d '2000-01-02'}+20;
select int1 as a,c+23 as b,a+b as c from test where a=1;
SELECT INT1,FLOAT1,A.* FROM TEST A WHERE {fn abs(-TEST.INT1)}>0 or a.float1<0 order by int1 asc,currency1,double1*5+int1 desc;
select int1,count(*),sum(int1+count(*)),sum(int1)+int1 from test group by int1 having int1>10;
SELECT SCHOOLNUM, STULINK,CHGNUMBER, {v 'ABSEN$0101'}, {v 'ABSEN$0102'}, USERSTAMP, DATESTAMP, {v 'TIMESTAMP'},SEQUENCE FROM AATD2019 where {v 'ABSEN$0101'}='1234' ORDER BY SCHOOLNUM, STULINK, SEQUENCE;
select char1,char1 like 'Z%',char1 in('ZZAA','Z'),char1 between 'A' and 'ZZZ',char1 in('ZZAA','Z') or char1 between 'A' and 'Z',* from test where char1='Z';
select int1 from test where int1=(select distinct top 1 int1 from test where int1>0);
select int1 from test where int1 in(select int1 from test where not deleted());
select recno(),int1 from test where (recno(),int1) in(select top 2 recno(),int1 from test where int1>0);
select subquery.int1,recno('subquery') from (select top 2 recno(),int1 from test where int1>0) as subquery;
select subquery.int1,recno('test'),test.int1,recno('subquery') from (select top 2 recno(),int1 from test where int1>0) as subquery, test where test.int1=subquery.int1;
ELECT INT1 FROM test as a WHERE EXISTS(SELECT 1 FROM test WHERE int1 >0);
SELECT INT1 FROM test as a WHERE int1>=all(SELECT int1 FROM test);
SELECT INT1 FROM test as a WHERE int1>=any(SELECT int1 FROM test);
SELECT INT1 FROM test as a WHERE int1>=some(SELECT int1 FROM test);
select int1,recno() from test where (int1,recno())>(3,5);
select int1,recno() from test where (recno(),int1)=(6,222);
SELECT * FROM (SELECT * FROM test WHERE int1 = 222 ) as a WHERE EXISTS(SELECT 1 FROM test WHERE int1 >0);
select recno('a'),recno('b'),a.int1,a.char1,b.int1,b.char1 from test a, test as b where recno('a')=recno('b');
select a.int1,a.char1,b.int1,b.char1 from test a inner join test as b on a.int1=b.int1;
select a.int1,a.char1,b.int1,b.char1 from test a NATURAL inner join test as b on a.int1=b.int1;
select recno('a'),recno('b'),a.int1,a.char1,b.int1,b.char1 from test a left join test as b on a.int1=b.int1;
select a.int1,a.char1,b.int1,b.char1 from test a right join test as b on a.int1=b.int1;
select a.int1,a.char1,b.int1,b.char1 from test a full join test as b on a.int1=b.int1;
select recno('a'),recno('b'),a.int1,a.char1,b.int1,b.char1 from test a full join test as b on a.int1==b.int1 and recno('a')!=recno('b');
SELECT * FROM test a LEFT JOIN (test b JOIN test c ON (b.int1 = c.int1)) as d ON (a.int1 = d.int1);
SELECT * FROM test a,test b,test c WHERE a.int1 = b.int1 AND b.int1 = c.int1;
SELECT * FROM test a NATURAL CROSS JOIN test b CROSS JOIN test c WHERE a.int1 = b.int1 AND b.int1 = c.int1;
SELECT * FROM test a LEFT JOIN (test b JOIN test c ON (b.int1 = c.int1)) on recno('a')=recno(2);
SELECT int1 FROM test where int1>0 UNION ALL select int1 from test where int1>3000 order by int1 desc
SELECT int1,* FROM test where int1>0 UNION select int1,* from test where int1>3000 order by int1
ELECT int1,* FROM test where int1>0 INTERSECT all select int1,* from test where int1>3000 order by int1;
SELECT int1,* FROM test where int1>0 EXCEPT select int1,* from test where int1>3000 order by int1 descl
SELECT int1,* FROM test where int1>0 MINUS select int1,* from test where int1>3000 order by int1,double1 desc;
select double1,sum(double1),int1 from test where int1>0 group by int1 having sum(double1)>0 and double1>0;
select distinct 1,a.int1,sum(a.int1) from test as a,test as b group by a.int1,B.int1
select a.int1,a.char1,b.int1,b.char1 from test a NATURAL inner join test as b
select * from (select 'ab5' as a) where a like '%[a-c][^ac][12345]'
column_identifier = columnName | "reserved_word" | {v 'reserved_word'}
Adds one or more new rows of data into a table. SQL does't permit that table1 is the same table as table2 when INSERT INTO table1 select * from table2, but the Caigen DBF support such an unadvisable operation, for example, INSERT INTO test (INT1,DATE1) select distinct int1,date1 from test.
For instance:
INSERT INTO test (INT1,dec1,time1) VALUES(-1999,-222.33333,{ts '1333-11-30 22:22:22.999999999'});
INSERT INTO test ("INT1","DATE1") VALUES(1999.0111,{d '1996-10-21'});
INSERT INTO test ("INT1","DATE1") VALUES(1999.0111,{d '1996-10-21'}),(333,{d '2006-10-21'});
INSERT INTO test ("INT1") VALUES 1999.0111,333;
insert into ecode values('Maciej', 'Kowalski');
insert into test values (reccount()+1,'abc',date(),{ts '2003-12-18 19:42:17.88'});
INSERT INTO AATD2019 ({v 'ABSEN$0101'}) values('1234');
insert into test select * from test order by int1 asc;
insert into test select * from test order by int1 asc;
"update tableName set _DeletedFlag=false[, column_identifier = {expression
| NULL}]... [WHERE condition_expression]" will unmarks records marked for
deletion in the selected table. For instance, you can use "update tableName
set _DeletedFlag=false" to restore all deleted roecords. "update tableName set
_DeletedFlag=false where recno() between 50 and 100" will restore part deleted
records.
For instance:
update order set buyer=(SELECT name FROM user WHERE user.id = order.buyerid);
update test set int1=null where SequenceID=26;
update test set INT1=323232,DEC1=-DEC1 where FLOAT1=3.00 and INT1=222 and DEC1=3.00 and DOUBLE1=34.0 and TIME1 is NULL and CHAR1='ZZAA' and CURRENCY1=0 and BOOLEAN1 is NULL
update AATD2019 set {v 'ABSEN$0101'}='1234' where SequenceID=1;
update test set int1=3333555 where exists(SELECT 1 FROM test WHERE int1 = 222 ) and SequenceID=3;
DELETE FROM table_name [ [AS] tableAlias] [WHERE condition_expression]
Removes rows in a table according to condition_expression.
For instance:
delete from test where SequenceID=4;
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] compressed-file-name ?
CREATE DATABASE [IF NOT EXISTS] compressed-file-name ?" sql can be used to create a database from any compressed java.io.InputStream object. Sample is here.
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.
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])
n, n1,n2: positive integer, n2 can be 0
constraint: [NULL| NOT NULL] [UNIQUE] [DEFAULT expression] [PRIMARY KEY]
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)
The Caigen DBF will ignore DEFAULT expression and (dateFormat) for Xbase's compatibility. Column can store null values in FoxPro,FoxBase or VFP, and constraint NULL is ignored for other Xbase.
SQL Type |
xBase Data Type |
Size in table
|
SQL Syntax |
Java Type |
---|---|---|---|---|
CHARACTER |
Unavailing |
1~2
|
CHAR[ACTER] |
char |
CHAR |
Character field of width n ('C') |
1~254
|
CHAR[ACTER] (n) |
String |
VARCHAR |
Character field of width n ('V' for VFP 9.0, DBF supports , but won't create 'V' type in CREATE TABLE) |
1~254
|
VARCHAR (n) |
String |
LONGVARCHAR |
Character field of width n ('V' for SIx Driver, DBF supports , but won't create 'V' type in CREATE TABLE) |
1~65534
|
LONGVARCHAR (n) |
String, char[], java.sql.CLOB |
NUMERIC |
Numeric field of width n with d decimal places ('N') |
1~255
|
NUMERIC [(n[,d])] |
java.math.BigDecimal |
DECIMAL |
Floating numeric field of width n with d decimal places ('F') |
1~255
|
DEC[IMAL] [(n[,d])] |
java.math.BigDecimal |
BIT |
Logical ('L') |
1
|
BIT |
boolean |
TINYINT |
Unavailing, map TINYINT into INT |
|
TINYINT |
byte |
SMALLINT |
Unavailing, map SMALLINT into INT |
|
SMALLINT |
short |
INTEGER |
Integer ('I','+' for DB2K's Autoincrement) |
4
|
INT[EGER] |
int |
BIGINT |
Unavailing, map BIGINT into NUMERIC(20,0) |
|
bigint |
long |
REAL |
Unavailing, map REAL into FLOAT |
|
REAL |
float |
FLOAT |
Double ('B') |
1~255
|
FLOAT [(d)] |
double |
DOUBLE |
Double ('O') |
8
|
DOUBLE |
double |
BINARY |
Incompatible extended ('2') |
n
|
BINARY (n) |
byte[] |
VARBINARY |
Incompatible extended ('1') |
n+(1~2)
|
VARBINARY (n) |
byte[] |
VARBINARY |
VFP9.0's Varbinary ('Q') |
1~255
|
VARBINARY (n) |
byte[] |
LONGVARBINARY |
BINARY ('0', 'B' for DB2K) |
LONGVARBINARY |
byte[], java.sql.BLOB |
|
DATE |
Date ('D') |
8
|
DATE |
java.sql.Date |
TIME |
Unavailing |
|
|
java.sql.Time |
TIMESTAMP |
DateTime ('T','@') |
8
|
TIMESTAMP |
java.sql.Timestamp |
BOOLEAN |
Logical ('L') |
1
|
BOOLEAN |
boolean |
BLOB |
General ('G'), VFP9.0's BLOB ('W') |
BLOB |
byte[], java.sql.BLOB, Object |
|
CLOB |
Memo ('M') |
CLOB |
String, char[], java.sql.CLOB |
|
OTHER |
Currency ('Y') Picture ('P') |
8
|
OTHER (Currency) OTHER (Graphics) |
java.math.BigDecimal byte[] |
JAVA_OBJECT |
General ('G') |
JAVA_OBJECT |
byte[] or Object |
For instance:
create table if not exists test (int1 int, float1 float,double1 numeric(12,3),boolean1 boolean, currency1 OTHER (Currency),dec1 DECIMAL(10,5), mem1 clob,char1 varchar(25),date1 date, time1 timestamp);
create table if not exists product1 (MODEL_NO varchar(20), MODEL_NM varchar(30), DIGIT8_CD int, COUNTRY_CD varchar(10), CATE_CD varchar(10), GENRE_CD varchar(20), FACTORY_CD varchar(20), UOM_CD varchar(20), RELEASE_DT date, JAPAN_SRP int, APPROVE_NO varchar(20), BAN boolean, DISCONT boolean, BEST boolean, PKG_REMARK varchar(20), REMARK1 varchar(30), REMARK2 varchar(30), INTERFACE boolean, currency1 OTHER (Currency),mem1 blob);
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.
For instance:
drop table if exists states;
DECLARE variable_name[,...] type [DEFAULT expression]
Variable is visiable only in the same connection.
For instance:
DECLARE abc CHAR(20) DEFAULT 'Hello';
DECLARE x, y INT;
SET variable_name = expression [,...]
expression can be a complicated expresion. BTW, INTO variable[,...] clause of SELECT syntax can set selected columns directly into variables. SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
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;
#one-line comment
--one-line comment
/*multiline comment*/
For instance:
select * /* column list */ from test;#This is a select sql.
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 |
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 |
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 |