Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# Get columns and tables in SQL script (Java version)

THIS DEMO IS DEPRECIATED SINCE GENERAL SQL PARSER Java VERSION 1.6.0.1 please check this new version instead.

The logic to find all tables and columns in SQL script is quite simple like this:

1. Parse SQL script.

2. Iterate all statements via TGSqlParser.sqlstatements and TCustomSqlStatement.getStatements().

3. Once a statement was found, get all tables belong to this statement via TCustomSqlStatement.tables.

4. Once a table was found, get all columns belong to this table via TTable.getObjectNameReferences().

Input SQL:

    SELECT e.last_name,
           e.department_id,
           d.department_name
    FROM   employees e
           LEFT OUTER JOIN department d
             ON ( e.department_id = d.department_id );

Tables and columns:

    Tables:
    department
    employees

    Columns
    department.department_id
    department.department_name
    employees.department_id
    employees.last_name

.NET version of this document.

Furthermore, General SQL Parser can detect relationship between column and table even there are derived table in from clause like this:

    select
           s2.s2t1a1,
           s3.s3t1a1
    from
        (
          select *
          from subselect2table1 s2t1
        ) s2,
        (
           select *
           from  subselect3table1, subselect3table2
        ) s3

Table and columns detected:

    Tables:
    subselect2table1
    subselect3table1
    subselect3table2

    Columns:
    subselect2table1.s2t1a1(table determined:true)
    subselect3table1.s3t1a1(table determined:false)
    subselect3table2.s3t1a1(table determined:false)

table determined:false means that without any further meta information from database, General SQL Parser can’t determine which table s3t1a1 belongs to(subselect3table1 or subselect3table2), but, at least s3t1a1 belongs to one of those 2 tables.

Since Java version 1.4.1.4, a new public interface IMetaDatabase was added, so user can implements IMetaDatabase to provide detailed meta information from database to help SQL parser to determine relationship between column and tables. With the help of those meta information, problems mentioned above can be solved automatically by General SQL Parser.

For a more complicated Oracle plsql stored procedure:

    CREATE OR REPLACE PACKAGE BODY G_RECALC_PKG
    IS
      FUNCTION RESERVE_STATE(LBYORA IN INTEGER)
      RETURN INTEGER
      IS
        l_count   NUMBER;
        r_inv     G_RECALC_INVALIDATE_STATE % ROWTYPE;
        l_timeout NUMBER;
      BEGIN
          l_timeout := 1200;

          l_count := 0;

          -- Checking consistency in the database : excactly one row should exist there
          SELECT COUNT(*)
          INTO   l_count
          FROM   G_RECALC_INVALIDATE_STATE;

          IF l_count > 1 THEN
            SELECT MAX(LTIMEOUTSECS)
            INTO   l_timeout
            FROM   G_RECALC_INVALIDATE_STATE;

            DELETE FROM G_RECALC_INVALIDATE_STATE;

            l_count := 0;
          END IF;

          IF l_count = 0 THEN
            CLEAN_PREVIOUS_VERSION;

            INSERT INTO G_RECALC_INVALIDATE_STATE
                        (TLASTUPDATED,
                         LTIMEOUTSECS,
                         TNEXTFREEORATIME,
                         LRUNCOUNT,
                         LMAXELAPSEDSECS,
                         LLATESTRUN)
            VALUES      (NULL,
                         l_timeout,
                         NULL,
                         0,
                         0,
                         0);
          END IF;

          SELECT TLASTUPDATED,
                 LTIMEOUTSECS,
                 TNEXTFREEORATIME
          INTO   R_INV.TLASTUPDATED, R_INV.LTIMEOUTSECS, R_INV.TNEXTFREEORATIME
          FROM   G_RECALC_INVALIDATE_STATE
          FOR UPDATE WAIT 10;

          COMMIT;

          RETURN 1;
      EXCEPTION
        WHEN OTHERS THEN
                   COMMIT;

                   RETURN 0;
      END RESERVE_STATE;
    END G_RECALC_PKG;

    /

Table and columns detected:

    Tables:
    g_recalc_invalidate_state

    Columns:
    g_recalc_invalidate_state.*(table determined:true)
    g_recalc_invalidate_state.llatestrun(table determined:true)
    g_recalc_invalidate_state.lmaxelapsedsecs(table determined:true)
    g_recalc_invalidate_state.lruncount(table determined:true)
    g_recalc_invalidate_state.ltimeoutsecs(table determined:true)
    g_recalc_invalidate_state.tlastupdated(table determined:true)
    g_recalc_invalidate_state.tnextfreeoratime(table determined:true)

Download this demo: Java version,
C# version demo1,
C# version demo2

## Leave a Reply

Your email address will not be published. Required fields are marked *

Name *

Email *

Website

Add Comment

Save my name, email, and website in this browser for the next time I comment.

I accept the Privacy Policy