further SQL parsing improvement

[wolframhaussig]

Is your feature request related to a problem? Please describe.
We would like to further improve the Span naming for JDBC spans

• Calling DB procedures
  • SQL "{call demoSp(?, ?)}" gets the name "{call"
  • SQL "{ call demoSp(?, ?)}" gets the name "{"
• SQL merge
  • SQL "Merge into testTable using(select * from table2) src ..." gets the name "Merge"

Describe the solution you'd like
For the "{call demoSp(?, ?)}" I would like to get "call demoSp" as Span name.
For "Merge into testTable ..." I would like to get at least "Merge testTable", maybe it is even possible to get "Merge testTable with table2" as span name?

[eyalkoren]

Implemented in version 1.7.0.
Did you try it out?

[wolframhaussig]

Oh, sorry that was a fact I wanted to add: I am already using version 1.7.0, sorry.

[eyalkoren]

Ahh, thanks. Will look into it then.

[eyalkoren]

Are those all the cases you found out not working as expected, or are the additional ones?
Please try to provide the most exhaustive list of examples, this can help us out.

[eyalkoren]

🤦‍♂ Wrong button..

[wolframhaussig]

We have an Oracle 12c DB running and Elastic Stack running on version 6.5.4. I upgraded today to apm-agent 1.7.0 and the following SQL namings works fine: UPDATE, SELECT, DELETE.

The following SQLs are not parsed correctly:
merge into APPLICATION_VERSION target using (select APPLICATION_ID, ? VERSION from APPLICATION where APPLICATION_NAME=? ) source on (target.APPLICATION_ID=source.APPLICATION_ID and target.VERSION=source.VERSION) when matched then update set STARTUP=sysdate, LAST_KEEP_ALIVE_SIGNAL=sysdate when not matched then insert(APPLICATION_ID,VERSION,STARTUP,FIRST_STARTUP,LAST_KEEP_ALIVE_SIGNAL) values(source.APPLICATION_ID, source.VERSION,sysdate,sysdate,sysdate)
This gets "merge" as Span name.

{ call TRACE(CONTEXT=>?, WHO=>?, WHAT=>?, TARGET=>?, TEXT=>?) }
This gets only "{" as Span name.

[felixbarny]

/cc @axw

[axw]

Thanks @wolframhaussig. I'm not personally familiar with Oracle's dialect, having { in the query is foreign to me. If you happen to know where I can find the syntax definition, that would be a great help.

MERGE doesn't look too difficult to handle.

[wolframhaussig]

@axw Here is the description: https://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#JJDBC28075

[wolframhaussig]

The curly braces seem to be not oracle specific but JDBC specific: https://db.apache.org/derby/docs/10.0/manuals/reference/sqlj230.html
"JDBC provides a way of smoothing out some of the differences in the way different DBMS vendors implement SQL. This is called escape syntax. Escape syntax signals that the JDBC driver, which is provided by a particular vendor, scans for any escape syntax and converts it into the code that the particular database understands. This makes escape syntax DBMS-independent.

A JDBC escape clause begins and ends with curly braces. A keyword always follows the opening curly brace:

{keyword }"

[axw]

Thanks for the pointers @wolframhaussig!