[jsr-221-eg] SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Douglas Surber]

The proposal is that text between ‘{\’ and ‘\}’ is not processed to recognize and expand JDBC specific syntax. In particular this means JDBC escape sequences and ‘?’ parameter markers. ‘{\’ and ‘\}’ are not recognized inside comments, literals or double quoted identifiers. Occurrences of ‘\’ between ‘{\’ and ‘\}’ other than inside comments, literals, or double quoted identifiers are doubled. It is vendor dependent whether use of ‘{\’ and ‘\}’ is required. If an implementation can distinguish  JDBC tokens from SQL tokens without ‘{\’ and ‘\}’ they may be omitted. An implementation must support their use even if not required.

Examples:

	String literal							character sequence
	“{\\ foo \\}”							« foo »
	“{\\ {ts '1999-01-09 20:11:11.123455’} \\}		« {ts '1999-01-09 20:11:11.123455’} »
	“{\\ ? \\}”								« ? »
	“{\\ \\\\ \\}”								« \ »
	“{\\ /* \\} */ \\}”							« /* \} */ »
	“{\\ // \\} \n \\}”							« // \} ↵ »
	“{\\ ‘\\}’ \\}”								« ‘\}’ »
	“{\\ q’{\\ foo \\}’ \\}						« q’{\ foo \}’ »
	“{\\ \”\\}\” \\}”							« “\}” »
	“{\\ \\ \\}”								SQLException

I have discussed this with some folks from Oracle’s SQL standards team. They think it is reasonable to have the SQL standard somehow reserve this syntax in the SQL spec for use by preprocessors. I think there is a good chance that the JDBC EG and the SQL Standard Committee can agree on a syntax and reserve it in SQL. We should be able to solve this problem once and for all.

There are more additions coming to SQL that use ‘?’, ‘{‘, and ‘}’. This problem will just get worse. 

There is at least one open issue. Should the JDBC spec say anything about how ‘?' would be handled by implementations where the underlying database uses ‘?’ as a parameter marker? It might be reasonable to say that any ‘?’ that is a parameter marker must not be escaped. Whether or not a ‘?’ that is not a parameter marker must be escaped is vendor dependent.

Consider a database that uses ‘?’ as a parameter marker and supports MATCH RECOGNIZE. The SQL syntax is such that the database can distinguish between a parameter marker and a quantifier. This requires a full SQL parser. By including the above restrictions a driver can scan the SQL for parameter markers without implementing a full SQL parser or being tripped up by a ‘?’ used as a quantifier. The JDBC should say something, at least that it is vendor dependent. It would be better though to give users specific direction.

Oracle would very much prefer the syntax specified above, ‘{\’ and ‘\}’. I know others have expressed preferences for alternate syntax. Ideally the EG would agree on a syntax before any vendor implemented something. Unfortunately that did not happen in this case. This issue first came up for Oracle Database in 2013 when Oracle added support for MATCH RECOGNIZE. We raised the issue with the JDBC EG at that time. While we proposed the above syntax we were also very clear that we would accept any reasonable alternative the EG would accept. Unfortunately the EG did not come to any agreement. In order to support our users Oracle Database JDBC had to do something so we added the above as an Oracle proprietary extension.

I am sorry that we are less flexible about the exact syntax in this proposal. That is not how I want this group to work. But we tried our best to work with the EG before going ahead on our own.

Douglas