menu
  Home  ==>  papers  ==>  db  ==>  interbase  ==>  interbase_stored_procedure_grammar   

Interbase Stored Procedure Grammar - Felix John COLIBRI.

  • abstract : The BNF Grammar of the Interbase Stored Procedure. This grammar can be used to build stored procedure utilities, like pretty printers, renaming tools, Sql Engine conversion or ports
  • key words : Stored procedure - Interbase - BNF grammar - Parser
  • software used : Windows XP, Delphi 6, Interbase 6
  • hardware used : Pentium 2.800Mhz, 512 M memory, 140 G hard disc
  • scope : Delphi 1 to 2006, Turbo Delphi for Windows, Kylix, Interbase
  • level : Delphi developer, Interbase developer
  • plan :


1 - Interbase Sql Grammar

One of our customer recently wanted to perform some heavy duty computations on his Interbase Stored Procedures (analysis and transformations).

Instead of plunging head on in an ad-hoc tool, we started to build a general parser. So we had to start-off with a BNF grammar. We already presented a simplified Sql grammar for the main INSERT, UPDATE, DELETE and SELECT requests. So we extended this grammar to handle the stored procedures as well.



Before the grammar presentation, here is a simple example of a stored procedure contained in the Interbase Sql Reference page:

 
CREATE PROCEDURE ORG_CHART
RETURNS (HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25),
MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER)
AS
DECLARE VARIABLE mngr_no INTEGER;
DECLARE VARIABLE dno CHAR(3);
BEGIN
FOR SELECT H.DEPARTMENT, D.DEPARTMENT, D.MNGR_NO, D.DEPT_NO
FROM DEPARTMENT D
LEFT OUTER JOIN DEPARTMENT H ON D.HEAD_DEPT = H.DEPT_NO
ORDER BY D.DEPT_NO
INTO :head_dept, :department, :mngr_no, :dno
DO
BEGIN
IF (:mngr_no IS NULL) THEN
BEGIN
MNGR_NAME = '--TBH--';
TITLE = '';
END
ELSE
SELECT FULL_NAME, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NO = :mngr_no
INTO :mngr_name, :title;
SELECT COUNT(EMP_NO)
FROM EMPLOYEE
WHERE DEPT_NO = :dno
INTO :emp_cnt;
SUSPEND;
END

If this does'nt look too inviting, here is another version of the same procedure, reformatted using our grammar:

 
create procedure org_chart
    returns (pv_head_dept char(25), pv_department char(25), 
        pv_mngr_name char(20), pv_title char(5), 
        pv_emp_cnt integer)
  as
    declare variable l_mngr_no integer;
    declare variable l_dno char(3);
  begin
    for
      select h.departmentd.departmentd.mngr_nod.dept_no
        from department d 
          left outer join department h 
            on d.head_depth.dept_no
        order by d.dept_no
        into : pv_head_dept: pv_department: l_mngr_no
            : l_dno
      do
        begin
          if (: l_mngr_no is null)
            then
              begin
                pv_mngr_name= '--TBH--';
                pv_title= '';
              end
            else
              select full_namejob_code
                from employee
                where emp_no: l_mngr_no
                into : pv_mngr_name: pv_title;

          select count(emp_no)
            from employee
            where dept_no: l_dno
            into : pv_emp_cnt;

          suspend;
        end
  end

The grammar was used to indent the text (and add prefixes), but the bold and coloring was performed using the Sql To HTML utility.




2 - Stored procedure grammar

2.1 - The Full Interbase grammar

We started with our full Interbase Grammar. This grammar was created by extracting from the Interbase documentation all BNF fragment, and putting then together in the same file.

This is a HUGE 530 line grammar. Our previous INSERT, UPDATE, DELETE and SELECT grammar was 78 line long.



The stored procedure grammar integrates back

  • the expressions (arithmetic, comparisons etc)
  • the stored procedure productions proper


2.2 - The Stored Procedure BNF Grammar

The basic structure is (partial):

 
stored_procedureCREATE PROCEDURE procedure_name
      [ '(parameter_name_and_type_list ')' ]
      [ RETURNS '(parameter_name_and_type_list ')' ]
      AS procedure_body  .
  procedure_body= [ variable_declaration_list ] compound_statement .
    variable_declaration_list = declaration { declaration } .
      declarationDECLARE VARIABLE NAME data_type ';.
    compound_statementBEGIN { compound_statement | statement [ ';' ] } END .
      statementassignment | exit_control | error_handling
          | other | sql_request | control .



And when we display all the details, we get:

 
stored_procedureCREATE PROCEDURE procedure_name
      [ '(parameter_name_and_type_list ')' ]
      [ RETURNS '(parameter_name_and_type_list ')' ]
      AS procedure_body  .
  procedure_nameNAME .
  data_typeSMALLINT | INTEGER | FLOAT | DOUBLE PRECISION
      | ( DECIMAL | NUMERIC )
          [ '(integer [ ',integer ] ')' ]
      | DATE | TIME | TIMESTAMP
      | ( CHAR | CHARACTER [ VARYING ] | VARCHAR )
          [ '(integer ')' ] [ CHARACTER SET CHARACTER_SET_NAME ]
      | ( NCHAR | NATIONAL ( CHARACTER | CHAR)  )
          [ VARYING ] [ '(integer ')' ]
      | BLOB    .
    integerNUMBER .
  parameter_name_and_type_listname_and_type { ',name_and_type } .
    name_and_typeNAME data_type .

  procedure_body= [ variable_declaration_list ] compound_statement .
    variable_declaration_list = declaration { declaration } .
      declarationDECLARE VARIABLE NAME data_type ';.
    compound_statementBEGIN { compound_statement | statement [ ';' ] } END .
      statement=
          | assignment
          | exit_control
          | error_handling
          | other
          | sql_request
          | control
          .
        simple_or_compound_statementcompound_statement | statement [ ';' ] .

        value_litterallitteral | [ ':' ] NAME [ '.NAME ] .
          litteralINTEGER_LITTERAL | STRING_LITTERAL 
              | DOUBLE_LITTERAL | NULL .
        integer_litteralINTEGER_LITTERAL .

        table_or_view_nameNAME .
        name_view_procedureNAME .

        column_nameNAME [ '.NAME ] .
        collation_nameNAME .
        alias_nameNAME .

        selectF .
        select_expressionselect .

        functionsaverage | count | max | min | sum | upper . // | cast .
          averageAVG '(' [ ALL | DISTINCT ] value_litteral ').
          countCOUNT '(' ( '*' | [ ALL | DISTINCT ] value_litteral ) ').
          maxMAX '(' [ ALL | DISTINCT ] value_litteral ').
          minMIN '(' [ ALL | DISTINCT ] value_litteral ').
          sumSUM '(' [ ALL | DISTINCT ] value_litteral ').
          upperUPPER '(value_litteral ').
          castCAST '(value_litteral AS data_type ').
        function_or_value= [ '+' | '-'] ( functions | value_litteral ) .

        search_conditionsearch_value { ( OR | AND ) search_condition } .
          search_valuesimple_value { arithmetic_operator simple_value } .
             arithmetic_operator=  '+' | '-' | '*' | '/' | '||.
             simple_value=
                  function_or_value
                    [ [ NOT ] ( between | like | in | compare | containing | starting )
                      | IS [ NOT ] NULL ]
                | ( ALL | SOME | ANY ) '(select_column_list ')'
                | EXISTS '(select_expression ')'
                | SINGULAR '(select_expression ')'
                | '(search_condition ')'
                | NOT search_condition .
              select_one_columnselect .
              select_column_listselect .

              betweenBETWEEN value_litteral AND value_litteral .
              likeLIKE value_litteral [ ESCAPE value_litteral ].
              inIN '(value_litteral { ',value_litteral } | select_column_list ').
              comparecompare_operator ( search_value | '(select_one_column ')' ) .
                compare_operator= '=' | '<' | '>' | '<=' | '>=' | '<>.
              containingCONTAINING value_litteral .
              startingSTARTING [ WITH ] value_litteral .

        selectSELECT
            column_clause
            FROM from_table_reference { ',from_table_reference }
            [ WHERE search_condition ]
            [ GROUP BY column_name
              [ COLLATE collation_name ] { ',column_name [ COLLATE collation_name ] } ]
            [ HAVING search_condition ]
            [ UNION select_expression [ ALL ] ]
            [ ORDER BY order_list ]
            .
          column_clause= [ DISTINCT | ALL ] ( '*' | column_detail { ',column_detail } ) .
            column_detailfunctions | value_litteral [ element_index ] .
              element_index= '[' ':NAME '].

          from_table_referenceNAME ( procedure_end | join_end ) | direct_joined_table .
            join_type= ( [ INNER | { LEFT | RIGHT | FULL } [OUTER] ] ) JOIN .
            procedure_end= [ '(value_litteral { ',value_litteral } ')' ] [ alias_name ]
                [ join_type name_view_procedure  [ alias_name ] ON search_condition ] .

            join_onF .
            joined_table= ( name_view_procedure join_on | '(joined_table ')' ) { join_on } .
            join_onjoin_type ( joined_table | name_view_procedure ) [ alias_name ] 
                ON search_condition .
            join_endjoin_on  { join_on } .
            direct_joined_table= '(joined_table ')' { join_on } .

          order_list= ( column_name | integer_litteral ) [ COLLATE collation_name ]
              [ ascending_or_descending ] { ',order_list } .
            ascending_or_descendingASC | ASCENDING | DESC | DESCENDING .

        assignmentvariable_name '=search_condition .
          variable_nameNAME .

        exit_controlEXIT | SUSPEND .

        error_handling=
            | proc_whenever
            | proc_when
            | proc_exception
            .

          proc_wheneverWHENEVER ( NOT FOUND | SQLERROR | SQLWARNING ) 
                ( GOTO label_name | CONTINUE ) .
            label_nameNAME .
          exception_nameNAME.
          // -- must be before END: some kind of TRY  ... EXCEPT concept
          proc_whenWHEN ( error_code { ',error_code } | ANY ) DO
              simple_or_compound_statement.
            error_codeEXCEPTION exception_name | ( SQLCODE | GDSCODE ) 
                error_litteral .
              error_litteral= [ '+' | '-' ] integer_litteral .
          proc_exceptionEXCEPTION exception_name .

        other=
            | proc_post_event
            | execute_procedure
            .

          proc_post_eventPOST_EVENT ( event_string | column_name ) .
            event_stringEVENT_STRING .
          execute_procedureEXECUTE PROCEDURE NAME [ input_parameters ] 
              [ RETURNING_VALUES output_parameters ] .
            parameter_name= ':NAME .
            input_parametersparameter_name { ',parameter_name } .
            output_parametersparameter_name { ',parameter_name } .

        sp_selectselect [ into_clause ] .
          into_clauseINTO output_parameter { ',output_parameter } .
            output_parameter= ':NAME .

        sql_requestinsert | update | delete | sp_select .
          updateUPDATE table_or_view_name SET set_value { ',set_value } 
              [ WHERE search_condition ] .
            set_valuecolumn_name '=search_condition .
          insertINSERT INTO table_or_view_name
              [ '(column_name { ',column_name } ')' ]
              ( VALUES '(value_litteral { ',value_litteral } ')
                | select_expression ) .
          deleteDELETE [ TRANSACTION transaction_name ] FROM table_name
              ( [ WHERE search_condition ] | WHERE CURRENT OF cursor_name ) .
            transaction_nameNAME .
            cursor_nameNAME .
            table_nameNAME .

        control=
              proc_if
            | proc_while
            | for_select .

          condition_namesearch_condition .

          proc_ifIF '(condition_name ')THEN simple_or_compound_statement
              [ ELSE simple_or_compound_statement ]  .
          proc_whileWHILE '(condition_name ')DO simple_or_compound_statement .
          for_selectFOR sp_select DO simple_or_compound_statement .



Just a couple of comments

  • like all our grammars, the Stored Procedure Grammar is not a "validating grammar" but an "operational grammar": our purpose was to analyze and manipulate Stored Procedure declarations, not to run them. We did not want to generate Interbase BLR pseudo code or check scripts for syntactic correctness.

    It is easy to check that our grammar allows more general texts than allowed by the Interbase engine. For instance, in value_litteral we allow both the colon : and the dot .:

     
    value_litteral= litteral | [ ':' ] NAME [ '.' NAME ] .

    and this would be correct in any assignment to an output parameter, but not in the column clause between SELECT and FROM

  • on the other hand, some production were rearranged to handle our test suite. For instance, in the SHOW_LANGS stored procedure, we found the [:index] element:

     
    create procedure show_langs
        (p_code varchar(5), p_grade smallintp_cty varchar(15) )
        returns (pv_languages varchar(15) )
      as
        declare variable l_i integer;
      begin
        l_i= 1;

        while (l_i<= 5) do
        begin
          select language_req[: i]
            from job
            where ( (job_code: p_code
                and (job_grade: p_gradeand (job_country: p_cty
                and (language_req is not null) )
            into : pv_languages;

          /* -- Prints 'NULL' instead of blanks */
          if (pv_languages= ' ')
            then
              pv_languages= 'NULL';

          l_il_i+ 1;

          suspend;
        end
      end

    therefore we added this indexing possibility in the column_clause of SELECT :

     
    selectSELECT
        column_clause
        ...ooo...

      column_clause= [ DISTINCT | ALL ] ( '*' | column_detail { ',column_detail } ) .
        column_detailfunctions | value_litteral [ element_index ] .
          element_index= '[' ':NAME '].

    But should this index sometime show up in the WHERE or VALUES parts, then we would move the indexing element up in a more general rule.

    Certainly carefully reading the manual would bring back the answer. But this is not a sure thing: the textual description is not as accurate as BNF, and their BNF is approximate and illustrative. So we adopted this more pragmatic approach.

    Did Jim STARKEY use a grammar ?. The answer is a resounding YES. In the Interbase 6 sources, you find some YACC bits and pieces. First, reverse engineering YACC grammars into LL1 BNF is not that simple, and we did it on occasion for languages like Java or C++. However Interbase contains MANY languages (DDL, DML, BLR, embedded Sql...), and my feeling is that those YACC files were used as a starting point for some of the parsers. So there is a possibility (possibility ? well...) that they were not kept up to date with the language evolutions. So we are back to square one, which means adjusting the grammar to whatever the Sql Engine accepts.

  • our overall structure of the grammar is arbitrary:
    • the local variables could be placed in the procedure header instead of the body. Since procedures cannot be nested, this is not an issue
    • we tried to isolate the different concepts of the statement stored procedure:
      • assignment
      • exit_control
      • error_handling
      • pure sql_requests
      • control and other
      All the groups could have been merged into a single statement alternative.
  • the grammar might still contains some LL1 duplicates (rule where several alternate elements start with the same terminal token)
  • the main problems was the : semi colon. The rule tells that all statements are terminated by a semi-colon BUT NOT the BEGIN .. END. For instance:

     
    if (pv_languages= ' ')
      then
        pv_languages= 'NULL';   /* <== ; */

    l_il_i+ 1;

    but:

     
    if (pv_languages= ' ')
      then
        begin
          pv_languages= 'NULL';
        end          /* <== no ; */

    l_il_i+ 1;

    and this rule wreaks total havoc in the statement rules. You CANNOT use:

     
    compound_statementBEGIN { general_statement } END .
      general_statementcompound_statement | statement ';.
        statementassignment | if_statement | ...ooo... .
          if_statementIF condition then general_statement .
          ...ooo...

    because if_statement is in the iteration, and there statement is always followed by the semi-colon. We tried to place the semicolon after the rules that cannot use compound statement, but to no avail.

    The only solution would be to move the semi-colon issue up at the body level, but then all the arithmetic would also have to be brought out of the body.

    So we accepted to make the semi-colon an option after each statement, which does parse our stored procedures, but is much more general that what the Sql Engine accepts (semi colon are optional everywhere !). Once again, this grammar is not a validating grammar, but an operational one

  • finally we presented a "stored procedure only" grammar: all the arithmetic as well as the general INSERT, UPDATE, DELETE and SELECT Sql requests are nested within the body. In a full Sql grammar, they would be outside of the stored procedure. In addition, a full Sql grammar would include all the other rules (ALTER, COMMIT, GRANT etc)


2.3 - Testing the grammar

To test the grammar, we used a test set including the procedures extracted from:


3 - Download the Grammar Text

Here are the source code files:

As usual:
  • please tell us at fcolibri@felix-colibri.com if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will be helpful for other readers
  • we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to fcolibri@felix-colibri.com.
  • or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button
    Name :
    E-mail :
    Comments * :
     

  • and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in your blog or newsgroup posts when relevant. That's the way we operate: the more traffic and Google references we get, the more articles we will write.



4 - References

Here are some Stored Procedure links:


5 - The author

Felix John COLIBRI works at the Pascal Institute. Starting with Pascal in 1979, he then became involved with Object Oriented Programming, Delphi, Sql, Tcp/Ip, Html, UML. Currently, he is mainly active in the area of custom software development (new projects, maintenance, audits, BDE migration, Delphi Xe_n migrations, refactoring), Delphi Consulting and Delph training. His web site features tutorials, technical papers about programming with full downloadable source code, and the description and calendar of forthcoming Delphi, FireBird, Tcp/IP, Web Services, OOP  /  UML, Design Patterns, Unit Testing training sessions.
Created: jan-07. Last updated: feb-2018 - 98 articles, 232 .ZIP sources, 1232 figures
Copyright © Felix J. Colibri   http://www.felix-colibri.com 2004 - 2018. All rigths reserved
Back:    Home  Papers  Training  Delphi developments  Links  Download
the Pascal Institute

Felix J COLIBRI

+ Home
  + articles_with_sources
    + database
      + interbase
        – interbase_tutorial
        – stored_proc_grammar
        – using_system_tables
      – firebird_trans_simulator
      + sql_server
      + bdp
      – db_refactoring
      – sql_parser
      – sql_to_html
      – sniffing_interbase
      – eco_tutorial
      – dbx4_programming
      – blackfishsql
      – rave_pdf_intraweb
      – rave_reports_tutorial
      – rave_reports_video
      – embarcadero_er/studio
      + firedac
      – bde_unidac_migration
      + oracle
    + web_internet_sockets
    + oop_components
    + uml_design_patterns
    + debug_and_test
    + graphic
    + controls
    + colibri_utilities
    + colibri_helpers
    + delphi
    + firemonkey
    + compilers
    + vcl
  + delphi_training
  + delphi_developments
  + sweet_home
  – download_zip_sources
  + links
Contacts
Site Map
– search :

RSS feed  
Blog