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