[antlr-interest] SQL grammar

[barry osullivan]

Hi,

Could anybody help me with writing a grammar that does the following:

Parsing values that are present after VALUES key world in the INSERT INTO
Table(column1,...) VALUES(value1,...);

A value1 might have 3 different form:

   1.    DEFAULT  (any string, e.g. USER, CURRENT SQLID)
   2.     NULL (e.g. INSERT INTO Table(column1,column2) VALUES(NULL,NULL);
   3.     expressions

    Operators (|| , /, + , -, *) might be used with expressions (they are
optional)

a.       scalar

o    date: SYSDATE, CURRENT DATE

o    string: 'a ' b'

o    number: 6.2

o    column name: column1

b.       Labeled durations

o    HIREDATE + 2 MONTHS + 14 DAYS

c.        sequence-reference

o    sequence-name.nextval

o    NEXT VALUE FOR sequence-name

d.       CASE expression

o    CASE searched-when-clause ELSE result-expression END

o    WHEN search-condition THEN result-expression

o    expression WHEN expression THEN result-expression

e.        CAST expression

o    CAST ( expression AS datatype)

f.        function-invocation

A function input parameter might be any argument form sections a-d or
another function

o    CONCAT('1', '1', 'd')

o    CONCAT(col1, 'f',1)

o    package.name.function(5)





I have combined the complex example with all the cases included:



INSERT INTO
TABLE(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19)
VALUES ('Don''t Pay' , 'Used to haul fuel , water'  , '' , ,, NULL , 1 ,
'1'||chr(10)||'COUNTRY=GRB' , CURRENT TIMESTAMP , CURRENT DATE +1 , HIREDATE
+ 2 MONTHS + 14 DAYS ,  CONCAT(c1 , 'x' || CURENT DATE  , 2 + 2 , f( 7 , 'a
'' b',p.n.f.g('i' ) ) ) , (2 + 8 * 16) / 2 - 3 , sequence-name.nextval ,
NEXT VALUE FOR sequence-name , case when b = '*' then 'star' when b = '+'
then 'plus' when b = '-' then 'minus' else '?''?' end ,  ( CASE WHEN
FIELDNAME IS NULL THEN DEFAULTVALUE ELSE FIELDNAME END ) , CAST ( ‘1’ AS
NUMBER ), SELECT 'i''d='||id AS ID FROM (SELECT T1.id from T1 UNION ALL
Select t2.id from T2 t2));



I expect to get:



c1 -> 'Don''t Pay'

c2 -> 'Used to haul fuel , water'

c3 -> ''

c4 ->

c5 ->

c6 -> NULL

c7 ->  1

c8 -> '1'||chr(10)||'COUNTRY=GRB'

c9 -> CURRENT TIMESTAMP

c10 -> CURRENT DATE +1

c11 -> HIREDATE + 2 MONTHS + 14 DAYS

c12 -> CONCAT(c1 , 'x' || CURENT DATE  , 2 + 2 , f( 7 , 'a ' b',p.n.f.g('i'
) ) )

c13 -> (2 + 8 * 16) / 2 - 3

c14 -> sequence-name.nextval

c15 -> NEXT VALUE FOR sequence-name

c16 -> case when b = '*' then 'star' when b = '+' then 'plus' when b = '-'
then 'minus' else '?''?' end

c17 -> ( CASE WHEN FIELDNAME IS NULL THEN DEFAULTVALUE ELSE FIELDNAME END )

c18 -> CAST ( ‘1’ AS NUMBER )

c19 -> SELECT 'i''d='||id AS ID FROM (SELECT T1.id from T1 UNION ALL Select
t2.id from T2 t2)



Mind that c4 is white space and c5 has no value at all.


Thanks in advance

Marcin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.antlr.org/pipermail/antlr-interest/attachments/20090513/3a62271d/attachment.html