AntLR grammar for SQL INSERT statement
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Anonymous
Posted On:   Tuesday, February 24, 2009 10:32 AM

I need to parse an insert statement into JAVA object. I have the problem with 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    More>>


I need to parse an insert statement into JAVA object.
I have the problem with 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) 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.
Keywords like CURENT DATE , SYSDATE etc. are database related so it is hard to provide them all.

Is it possible to get a grammar that will parse the source string after key worlds " VALUES ( " and before last occurrence of close bracket " ) " from INSERT statement?
I have tried regular expression but I was not able to solve problem with nested functions.


Thanks in advance for any help provided.



M.    <<Less

AntLR grammar for SQL INSERT statement

Posted By:   Anonymous  
Posted On:   Tuesday, February 24, 2009 10:45 AM





I need to parse an insert statement into JAVA object.
I have the problem with 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

  1. o date: SYSDATE, CURRENT DATE

  2. o string: 'a '' b'

  3. o number: 6.2

  4. o column name: column1


    b. Labeled durations

  1. o HIREDATE + 2 MONTHS + 14 DAYS


    c. sequence-reference

  1. o sequence-name.nextval

  2. o NEXT VALUE FOR sequence-name


    d. CASE expression

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

  2. o WHEN search-condition THEN result-expression

  3. o expression WHEN expression THEN result-expression


    e. CAST expression

  1. o CAST ( expression AS datatype)


    f. function-invocation

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

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

  2. o CONCAT(col1, 'f',1)

  3. 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) 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.
    Keywords like CURENT DATE , SYSDATE etc. are database related so it is hard to provide them all.



    Is it possible to get a grammar that will parse the source string after key worlds " VALUES ( " and before last occurrence of close bracket " ) " from INSERT statement?



    I have tried regular expression but I was not able to solve problem with nested functions.




    Thanks in advance for any help provided.





    M.
  • About | Sitemap | Contact