Performs analysis of a PSQL object.

Syntax

 ibec_psql_Analyze(PSQLObject : variant; Connection : variant; Options : string) : string;

If an empty string is passed as an option string all possible options will be applied.

The function returns a report in JSON format.

Example

Following example retrieves all procedure DDL's from a database, analyzes them and saves reports in separate files:

 execute ibeblock 
 as
 begin
   db = ibec_GetDefaultConnection();

   -- Directory to save analysis reports
   sDir = 'D:\Temp\Procs';
   ibec_ForceDirectories(sDir);

   for select rdb$procedure_name
       from rdb$procedures
       -- Comment the next line if your server doesn't support packages
       where rdb$package_name is null
       order by 1
       into :ProcName
    do
    begin
      ProcName = ibec_Trim(ProcName);

      -- Pass an empty string as an option string to get pure single CREATE statement without SET TERM etc.
      sPSQL = ibec_GetObjectScript(db, ProcName, __dboProcedure, '');

      ObjPSQL = ibec_psql_Parse(sPSQL, 3, __svFB30);
      try
        s = ibec_psql_Analyze(ObjPSQL, db, '');
        ibec_SaveToFile(sDir || '\' || ProcName || '.sp.json', s, __stfOverwrite);
      finally
        ibec_psql_Free(ObjPSQL);
      end
    end;
 end;

Example of output:

 {
   "SyntaxWarnings": [
     {
       "Message": "Empty BEGIN...END statement",
       "StartPosition": 516,
       "Length": 0
     },
     {
       "Message": "Value assigned to 'INTVAR1' never used",
       "StartPosition": 57,
       "Length": 7
     }
   ],
   "Statements": [
     {
       "Statement": "for select emp_no,\r\n
             dense_rank() over (order by salary),\r\n 
             salary / count(salary) over ()\r\n 
             from employee\r\n
             into :intvar1,  :intvar2, :dpVar3\r\n  do",
       "StartPosition": 322,
       "Length": 182,
       "Plan": "PLAN SORT (EMPLOYEE NATURAL)",
       "PlanNatural": true,
       "TypeWarnings": [
         "DENSE_RANK BIGINT  ->  INTVAR2 INTEGER",
         "DIVIDE NUMERIC(18,2)  ->  DPVAR3 INTEGER"
       ],
       "Operations": [
         {
           "Action": "SELECT",
           "AffectedObject": "EMPLOYEE"
         }
       ]
     },
     {
       "Statement": "for select emp_no,\r\n
              salary / count(salary) over (),\r\n
              NTH_VALUE(salary,  2) FROM FIRST over (order by salary)\r\n
              from employee\r\n
              into :intvar1,  :intvar2, :dpVar3\r\n  do",
       "StartPosition": 527,
       "Length": 204,
       "Plan": "PLAN SORT (EMPLOYEE NATURAL)",
       "PlanNatural": true,
       "TypeWarnings": [
         "DIVIDE NUMERIC(18,2)  ->  INTVAR2 INTEGER",
         "NTH_VALUE NUMERIC(18,2)  ->  DPVAR3 INTEGER"
       ],
       "Operations": [
         {
           "Action": "SELECT",
           "AffectedObject": "EMPLOYEE"
         }
       ]
     },
     {
       "Statement": "for select emp_no,\r\n
             dept_no,\r\n
             salary / count(salary) over (partition by dept_no ORDER by dept_no nulls first) rank\r\n
             from employee\r\n
             order by emp_no\r\n
             into :intvar1,  :intvar2, :partition\r\n  do",
       "StartPosition": 753,
       "Length": 234,
       "Plan": "PLAN SORT (SORT (EMPLOYEE NATURAL))",
       "PlanNatural": true,
       "TypeWarnings": [
         "DEPT_NO CHAR(3)  ->  INTVAR2 INTEGER",
         "RANK NUMERIC(18,2)  ->  PARTITION INTEGER"
       ],
       "Operations": [
         {
           "Action": "SELECT",
           "AffectedObject": "EMPLOYEE" 
         }
       ]
     }
   ]
 }

back to top of page
<< ibec_psql_Format | IBEBlock | ibec_psql_CommentBody >>