kbmMW Features #4 – SQL, XML, JSON, VALUES() hand in hand

This blog post will be about the TkbmMWMemSQL component, which is a descendant of TkbmMemSQL.

I will in this post mostly take advantage of “learning by example”. So study the given examples to see a subset of all the nice capabilities available in kbmMW.

Users of kbmMemTable may recognize the component TkbmMemSQL. It is a component that presents SQL capabilities for kbmMemTable datasets.

TkbmMemSQL supports quite a big subset of the ANSI SQL syntax, including SELECT, INSERT, DELETE, UPDATE along with full DDL support like CREATE TABLE, ALTER TABLE, CREATE INDEX and so forth. The following are all valid SQL statements that can be used in both TkbmMemSQL and TkbmMWMemSQL

UPSERT table1 (fld1,fld2) VALUES ('11',2000) WHERE fld1='11'
UPSERT table1 (fld1,fld2) VALUES ('10',100) WHERE fld1='10'
UPSERT table1 (fld1,fld2) VALUES ('10',100) WHERE fld1='STR10'
SELECT * FROM Table1 ORDER BY fld1,fld2
SELECT FieldValue('fld1') as myfld1,Coalesce(FieldValue('NoField'),"ISNULL") as NoField FROM Table1
SELECT Coalesce(10) FROM Table1
SELECT Coalesce(Null) FROM Table1
SELECT Coalesce(fld7,99999) FROM Table1
SELECT Coalesce(fld7,Null,99999) FROM Table1
SELECT fld1, (SELECT Max(fld1) FROM Table1) FROM Table1
SELECT fld1 FROM Table1 WHERE fld3 IN (SELECT fld2 FROM Table1 WHERE fld2<4)
SELECT 1, MAX(fld1) FROM Table1 GROUP BY 1
SELECT 1 AS 'ID', MAX(fld1) FROM Table1 GROUP BY ID
SELECT NULL as fld1, fld2, "ABC" as fld3, 10+20 as fld4 FROM Table1
SELECT RegExp("1\d",fld2), fld2 FROM Table1
SELECT RegExp("^(\d)(\d*)",fld2,OUT $PAR),$PAR,fld2 FROM Table1
SELECT fld1 AS TIME, fld2 FROM Table1 WHERE fld1 LIKE "STR1%"
SELECT fld1, fld2 FROM Table1 WHERE fld1 LIKE "STR1%"
SELECT fld1, fld2 FROM Table1 WHERE NOT fld1 LIKE "STR1%"
SELECT fld1, fld2 FROM Table1 WHERE fld1 NOT LIKE "STR1%"
SELECT fld1, fld2 FROM Table1 WHERE fld2 LIKE "8%"
SELECT fld1, fld2, CASE WHEN fld2<100 THEN 'LOW' WHEN fld2>=100 AND fld2<200 THEN 'MEDIUM' ELSE 'HIGH' END AS FLD3 INTO table7 FROM Table1
SELECT fld1, fld2, CASE WHEN fld2<100 THEN 'LOW' WHEN fld2>=100 AND fld2<200 THEN 'MEDIUM' ELSE 'HIGH' END FROM Table1
SELECT fld1, fld2, CASE fld2 WHEN 10 THEN 99999 WHEN 20 THEN 22222 ELSE -1 END FROM Table1
SELECT Table1.fld1,Table2.fld2 FROM Table1, Table2 WHERE Table1.fld1=Table2.fld1
SELECT Table1.*,Table2.* FROM Table1, Table2 WHERE Table1.fld7=Table2.fld6
SELECT TOP 100 tb1.id,tb2.id,tb1.fld7,tb2.fld6 FROM table1 tb1, table2 tb2 WHERE tb1.fld7=tb2.fld6 ORDER BY tb1.fld7
SELECT MIN(fld1),MAX(fld1),MIN(fld2),MAX(fld2) from Table1
SELECT MIN(fld4),MAX(fld4),MIN(fld7),MAX(fld7) from Table3
SELECT [fld1] as [[fld1]]] ( "HELLO" ) FROM Table1
SELECT "ABC" FROM Table1
SELECT RecNo,RowID,fld1,fld2 FROM Table1 WHERE fld2 in (10,20,30)
SELECT Chr(876) FROM Table1 WHERE fld2 in (10,20,30)
SELECT fld1 FROM Table1 WHERE fld2 in (10,20,30)
SELECT 1-2-3 FROM Table1 LIMIT 1
SELECT fld3,fld3||$Var1 FROM Table1
SELECT LeftPad(fld3,'A',10),RightPad(fld3,'B',12),fld3||'ABC' FROM Table1
SELECT fld2+1 as fld2a FROM Table1 ORDER BY fld2a DESC
SELECT fld2+1 as fld2 FROM Table1
SELECT fld1,fld2,fld3,fld6,fld3 AS SomeField1,fld2 AS SomeField2,fld5 FROM table3 WHERE fld5 IN (5) ORDER BY fld6,SomeField2
SELECT fld2 as Field2, fld3, sum(fld5) as fld5, Sum(fld2) as SomeField1, Sum(fld3) as SomeField2 FROM table1 GROUP BY Field2, fld3
SELECT fld2 as Field2, fld3, sum(fld5) as SomeField1, Sum(fld2) as SomeField2, Sum(fld3) as SomeField3 FROM table1 GROUP BY Field2, fld3
SELECT fld5,sum(fld5) as sumoffld5,count(fld5) as countoffld5 FROM table1 GROUP BY fld5 HAVING count(fld5)>2
SELECT fld2 as somefield, fld3 FROM table1
SELECT fld5 as somefield,sum(fld5),count(fld5) FROM table1 GROUP BY somefield HAVING count(fld5)>2
SELECT count(*)+5 FROM table1
SELECT table1.* FROM table1 LIMIT 10 OFFSET 50
SELECT table1.* FROM table1 LIMIT 10
SELECT table1.* FROM table1 OFFSET 50
INSERT INTO table1 (fld1) VALUES ('Test')
UPDATE table1 SET fld5 = (fld6) + (-fld2) WHERE fld3>10
SELECT fld1,fld2,TRUE AS Visible, 2 AS RecordType FROM table1
SELECT fld2, IF(fld2>10,True,False) AS IsSomething FROM table1
SELECT SUM(fld5),SUM(fld6),SUM(fld5)+Sum(fld6) AS TotalField FROM table1
SELECT SUM(fld5)+Sum(fld6) AS TotalField FROM table1
SELECT SUM(fld5+fld6) AS TotalField FROM table1
SELECT table1.* FROM table1
SELECT fld5 as somefield,sum(fld5),count(fld5) from table1 group by somefield
SELECT tb1.fld1,tb1.fld5,SUM(tb1.fld5),COUNT(tb1.fld5) FROM table1 tb1 GROUP BY tb1.fld1,tb1.fld5
SELECT fld1,fld5,SUM(fld5),COUNT(fld5) FROM table1 tb1 GROUP BY fld1,fld5
UPDATE table1 SET fld1='UPD'
SELECT count(distinct Left(fld1,4)) from table1
SELECT table1.fld1,table1.fld3,table1.fld4 from table1 where table1.fld1="STR1" and table1.fld3=996
SELECT fld1,fld3,fld4 from table1 where fld1="STR1" and fld4<>"STR996" and fld3=996
SELECT length(fld1) from table1
SELECT fld1,fld5,sum(fld5),count(fld5) from table1 group by fld1,fld5
SELECT min(fld2),max(fld2),sum(fld5),avg(fld5),count(*) from table1 where fld5>5
SELECT min(fld2),max(fld2),sum(fld5),avg(fld5),count(*) from table1
SELECT fld5,sum(if(fld5>5,1,0)),count(fld5) from table1 group by fld5
SELECT fld5,sum(fld5),count(fld5) from table1 group by fld5
SELECT count(*) from table1
SELECT count(*) from table1 where fld2>50
SELECT fld5 from table1 group by fld5
SELECT fld3, fld3 as Field3 from table1
SELECT fld3,(fld3 mod 10)=0 as bool,fld3 / 13 as somefield from table1 order by bool desc, somefield desc
SELECT fld3,(fld3>950)=0,fld3 / 13 from table1
SELECT fld3,(fld3>950)=0,fld3 / 13, (fld3 div 11) from table1 where (fld3 mod 11)=0
SELECT fld3>5 from table1
SELECT fld3+1+2 from table1
SELECT fld1,fld3+1+2 from table1
SELECT fld1,fld3 from table1
UPDATE table1 SET fld1='UPD' WHERE fld1 in ('STR2','STR4','STR6') or fld2=10
DELETE FROM table1 WHERE fld1 in ('STR2','STR4','STR6') or fld2=10
DELETE FROM table1 WHERE fld1 in ('STR2','STR4','STR6')
DELETE FROM table1 WHERE fld1 not in ('STR2','STR4','STR6')
DELETE FROM table1 WHERE fld1='STR2'
DELETE FROM table1 WHERE id NOT IN (993)
INSERT INTO table1 (fld1,fld2,fld4) VALUES ('HEJ',1,((2+5)-2)*3-1 <>7)
INSERT INTO table1 (fld1,fld2,fld4) VALUES ('HEJ',1,2*3)
SELECT fld2+1 / 2,fld2 FROM table1 ORDER BY fld2
SELECT * FROM table1
SELECT * FROM table1 WHERE fld7 is null
SELECT * FROM table1 WHERE fld7 is not null
SELECT fld8 mod 100,IF(fld8 mod 100, fld2,fld8) FROM Table1
SELECT fld2 FROM Table1 where not (fld2 mod 10)
SELECT fld1,Min(20,30,10,40),Max(20,30,10,40),Avg(20,30,10,40),Sum(20,30,10,40) FROM Table1
SELECT fld2 FROM Table1 where fld2 xor (fld2 mod 10)
SELECT if(1 xor 1,0,1),fld2 FROM Table1
SELECT if(0 xor 1,0,1),fld2 FROM Table1
SELECT if(10 IN (10,20,30),1,0),fld2 FROM Table1
SELECT if(11 IN (10,20,30),1,0),fld2 FROM Table1
CREATE TABLE table9 (id VARCHAR(40), fld1 VARCHAR(50), fld2 VARCHAR(5) NOT NULL, fld3 BLOB NOT NULL, fld4 INTEGER, fld5 FLOAT, fld6 TIMESTAMP, PRIMARY KEY (id))
DROP TABLE table3
CREATE INDEX idx5 ON table1 (fld2 DESC)
DROP INDEX idx5 ON table1
CREATE TABLE (id INT, fld7 FLOAT DEFAULT 11.22, PRIMARY KEY (id) )
CREATE TABLE (id INT, fld7 CLOB, PRIMARY KEY (id) )
CREATE TABLE (id INT PRIMARY KEY, fld2 VARCHAR(10), fld7 CLOB ) ; CREATE INDEX idx5 (fld2 DESC) ; DROP INDEX idx5
CREATE TABLE table9 (id INT PRIMARY KEY, fld2 VARCHAR(10), fld3 FLOAT ) ; INSERT INTO table9 (id,fld2,fld3) VALUES (888,'ABC',123.456)
CREATE TABLE table9 (id INT PRIMARY KEY, fld2 VARCHAR(10), fld3 FLOAT, fld4 INT DEFAULT 10) ; INSERT INTO table9 (id,fld2,fld3) VALUES (888,'ABC',123.456)
ALTER TABLE table1 ADD COLUMN NewField VARCHAR(30)
ALTER TABLE table1 DROP COLUMN Fld2
ALTER TABLE table1 ALTER COLUMN Fld1 VARCHAR(30)
ALTER TABLE table3 MODIFY COLUMN Fld8 VARCHAR(30)
ALTER TABLE table3 MODIFY COLUMN Fld8 INTEGER
ALTER TABLE table1 ADD NewField VARCHAR(30)
ALTER TABLE table1 DROP Fld2
ALTER TABLE table1 ALTER Fld1 VARCHAR(30)
ALTER TABLE table3 MODIFY Fld8 VARCHAR(30)
ALTER TABLE table3 MODIFY Fld8 INTEGER
ALTER TABLE table1 RENAME TO table1_1
ALTER TABLE table1 RENAME COLUMN FLD1 TO FLD11
LIST TABLES
LIST INDEXES ON TABLE table1
LIST INDEXES ON table1
LIST INDEXES FOR TABLE table1
LIST INDEXES FOR table1
LIST INDEXES table1
DESC TABLE table1
DESCRIBE TABLE table1
DESCRIBE INDEX iDescend ON table1
DESCRIBE INDEX iDescend FOR table1
DESCRIBE INDEX iDescend ON TABLE table1
EXISTS TABLE table1
EXISTS INDEX iDescend ON table1
SELECT RecNo,RowID,fld1,fld2 FROM Table1 WHERE fld2 in (10,20,30) ORDER BY 3

So it is already quite capable.

What TkbmMWMemSQL brings to the table is additional advanced features like being able to query XML, CSV and JSON documents from file and inline as part of the SQL statement itself, in addition to the VALUES(..) feature where a number of comma and semicolon separated values can be delivered directly to the SELECT statement as field and row values. VALUES(..) will be available in the upcoming release of kbmMW, while the remaining features already are available in current version.

The following are examples of advanced SQL features, showing how to work with XML and JSON data and files, both as input and output.

SELECT * FROM data VALUES(10,20,30;20,30,40;100,200,300) order by FLD2 desc
SELECT fld2 as Field2, fld3, sum(fld5) as fld5, Sum(fld2) as SomeField1, Sum(fld3) as SomeField2 FROM table1 GROUP BY Field2, fld3 OUTPUT FILE(SF_JSON,'.\output.json','nodef')
SELECT fld2 as Field2, fld3, sum(fld5) as fld5, Sum(fld2) as SomeField1, Sum(fld3) as SomeField2 FROM table1 GROUP BY Field2, fld3 OUTPUT FILE(SF_CSV,'.\output.csv','nodef,onlyquotestrings')
SELECT XMLAttr(value,'//datas','name') as 'name->TEXT(20)',value FROM data TEXT(XML,'<datas name="hello"><data><value>10</value></data><data><value>20</value></data>>/datas>') SUBSET '^/datas/data/$' 
SELECT fld1,fld2 FROM data TEXT(XML,'<datas><data><fld1>10</fld1><fld2>Text10</fld2></data><data><fld1>20</fld1><fld2>Text20</fld2></data></datas>') SUBSET '^/datas/data/$' order by fld2
SELECT XMLAttr(data,'value') as 'value->TEXT(20)'  FROM data TEXT(XML,'<data><data value="10"/><data value="20"/></data>') SUBSET '^/data/data/$' order by value
SELECT XMLNode(value) as value FROM data TEXT(XML,'<datas><data><value>10</value></data><data><value>20</value></data>>/datas>') SUBSET '^/datas/data/$' order by value
SELECT value FROM data TEXT(XML,'<datas><data><value>10</value></data><data><value>20</value></data>>/datas>') SUBSET '^/datas/data/$' order by value
SELECT value FROM data TEXT(XML,'<data><value>10</value><value>20</value></data>') SUBSET '/data/value' order by value
SELECT owner as 'someowner->TEXT(3)',bid,quantity as 'dato->DATE' FROM auctions SUBSET '/alliance/auctions/.*' order by bid,owner
SELECT [species no], common_name FROM biolife FILE(SF_CSV,'.\biolife.csv') order by common_name
SELECT owner,bid,quantity FROM auctionsfile FILE(JSON,'.\auctions.json') SUBSET '/alliance/auctions/.*' order by bid,owner
SELECT value FROM data TEXT(JSON,'{"data":[{"value":10},{"value":20}]}') SUBSET '/data/.*' order by value
SELECT owner,bid,quantity FROM auctions SUBSET '/alliance/auctions/.*' order by bid,owner
SELECT owner,max(bid),sum(quantity) FROM auctions SUBSET '/neutral/auctions/.*' group by owner

Some of the examples includes a -> syntax as part of the naming of a result field. This is a way to force the resulting field to be of a particular type, rather than deducing the field type based on the results of the query statement. The datatype identifiers includes LARGEINT, BIGINT, INT64, INT, VARCHAR2, VARCHAR, CHAR, BOOL, BOOLEAN, AUTOINC, FLOAT, DOUBLE, NUMERIC, REAL, DATETIME, TIMESTAMP, DATE, TIME, BLOB, GRAPHIC, CLOB, TEXT, CURRENCY, WORD, WIDEMEMO, MEMO and GUID, and can take an optional size (n). For example VARCHAR2(100).

The SUBSET keyword can be used to select a subset of the data structure for inclusion in the query. It is in its purpose similar to XPath probably recognized by those who have worked extensively with XML document parsing. The syntax is a regular expression matching a path to interesting nodes of a XML or JSON document to be included as the query’s source. Hence /data/.* means that all nodes under the data node which is found in top of the document. Only the fantasy sets the limit for how to filter the documents to get the relevant data.