In the upcoming release of kbmMemTable, a number of improvements of the optional SQL parser is included.
Now the following syntax is also supported (see examples further down):
- CASE … WHEN … THEN … ELSE… END
- SELECT … INTO… FROM….
- CREATE TABLE
- CREATE INDEX
- DROP INDEX
- ALTER TABLE… ADD COLUMN…
- ALTER TABLE… DROP COLUMN…
- ALTER TABLE… MODIFY COLUMN…
- LIST TABLES
- LIST INDEXES ON …
- DESCRIBE TABLE …
- DESCRIBE INDEX …
Further multiple statements separated by semicolon is now also supported. All will be run in order.
The DESCRIBE functions will return SQL 2003 style metadata descriptions.
In addition kbmMemTable SQL now supports simple inner joins.
<TEASER>
Many of these new features will be utilized by next version of kbmMW, which supports extensive SQL rewriting for major databases. Hence you will be able to write most SQL in the standard kbmMemTable SQL way which is SQL 92 compatible, and kbmMW will optionally automatically rewrite it to match the actual database, making supporting different databases a breeze. More about that in another blogpost.
</TEASER>
Examples:
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 fld2
WHEN 10 THEN 99999
WHEN 20 THEN 22222
ELSE -1
END
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 CLOB, PRIMARY KEY (id) )
CREATE TABLE (
id INT PRIMARY KEY,
fld2 VARCHAR(10),
fld7 CLOB ) ; CREATE INDEX idx5 (fld2 DESC) ; DROP INDEX idx5
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
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
NB: If you like our blog posts, feel free to share then with your social network!
32,969 total views, 2 views today
Is it possible create index on two fields?
CREATE INDEX idx5 ON table1 (fld2, fld3)
I tried but error occurs “field not found ‘fld2,fld3′”
Hi,
Yes… however there is a minor bug preventing it.
Open kbmSQLMemTableAPI.pas, locate line 452 which looks like this:
a:=’,’;
and replace it with
a:=’;’;
Then rebuild.