Friday, October 16, 2020

PrioXRef 3 - insert without parameters

Every day I've been checking the Priority cross referencer and fault analyser (XPrioRef) against a variety of my own procedures and every day I've come across new constructs that have to be handled. Yesterday I came across a real doozy (as Ned from 'Groundhog day' would say):

INSERT INTO PART SELECT * FROM PART ORIG WHERE PART = :PART;
For those who aren't au faire with SQL, this means 'insert into the (linked) table PART all the fields from the real table PART for all tuples where the part number is equal to the variable PART. As the variable can only have one value, it means that only one tuple gets copied into the linked table. Statements like these occur when the linked table is about to be sent to an external procedure.

Until now, XPrioRef has handled two kinds of insert statements: INSERT INTO <table> (<fields>) and INSERT INTO <table> <alias> (<fields>). The above statement has no alias and no list of fields; as the character after the table name on the first line is not an opening bracket, XPrioRef thought that the next token (it's a 'top hat', ^, that signals the end of the line) was an alias, so there was a reference to PART ^.  There is also a problem with the line count at this stage. Then the asterisk in the second line (which in this context means 'bring all the fields') was parsed as punctuation, causing the parser to be even more bewildered.

Handling the asterisk was easy (the character after 'SELECT' is checked, and if it's an asterisk then it's swallowed) but handling the first line was not easy. The next token after ^ had to be read, and if it's SELECT, then the 'insert into' part has been finished and then 'SELECT' is handled. 

This is all very ad hoc, something that I had tried to avoid, but it seems that the confusing syntax demands this. Of course, within the SQL engine there is a parser that has to execute the statement; I'm only trying to list references and determine what is happening at the time of the reference.

After adding a fix for this kind of statement, running PrioXRef on the same procedure produced the expected output. Now I wait for the next misreference to appear.

No comments: