kbmMemTable SQL new features coming

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!