SQLExtract - Extract SQL from PowerBuilder Applications

SQLExtract tool allows you to extract embedded and dynamic SQL statements from PowerBuilder applications to SQL files.

The tool was developed to extract SQL statements from applications accessing Oracle and Microsoft SQL Server databases, but with some limitations, you can use it for any database.

Why SQLExtract

SQLExtract can be helpful if you need to review, assess, analyze, optimize, convert or test SQL statements in your PowerBuilder application.

Although you can browse the source code in PowerBuilder IDE or any other editor to find SQL statements, it can be time consuming. Moreover, SQL statements often include PowerBuilder specific clauses (INTO, USING sqlca i.e.), host variables (:var i.e.) and escaping (~”) that need to removed if you want to run a SQL statement in a Query tool.

SQLExtract is capable of automatically extracting SQL statements from PowerBuilder source files, removing PowerBuilder clauses and saving the statements to SQL files.

How to Use

Before you can use SQLExtract you have to export PowerBuilder libraries (.pbl files) to source code (a set of .srd, srf, srw etc. files). For more details, see Export a PowerBuilder Library.

Then you can run SQLExtract from command line.

Features

SQLExtract features:

SQL Statements

SQLExtract extract the following SQL statements from PowerBuilder source code:

  • DELETE
  • INSERT
  • UPDATE
  • SELECT

PowerBuilder Files

You can extract SQL from the following files (PowerBuider objects):

  • .sra - Application Object
  • .srd - DataWindow Object
  • .srw - Window Object
  • .sru - User Object
  • .srf - Function Object
  • .srq - Query Object

Extraction Statistics

For each file SQLExtract provides:

  • Number or extracted SQL statements
  • Number of lines in extracted SQL statements
  • Total number of lines in the file

The summary information is also available:

  • Total number of files
  • Total number of files containing SQL statements
  • Total number of extracted SQL statements
  • Total number of SELECT, INSERT, UPDATE and DELETE statements
  • Total number of lines in extracted SQL statements
  • Total number of lines in all files

Dynamic SQL in PowerBuilder

Dynamic SQL is a SQL statement stored in a string (double quoted string literal).

In PowerBuilder application, you can find a dynamic SQL in DataWindow objects (SELECT statement):

DataWindow Object (a fragment of .srd file):

  $PBExportHeader$detail.srd
  datawindow(units=0 timer_interval=0 color=79741120 processing=0 ...)
  ...
  table(column=(type=char(90) updatewhereclause=yes name=cities_name dbname="cities.name" )
          column=(type=char(90) updatewhereclause=yes name=states_state dbname="states.name" )
  retrieve="SELECT cities.name, states.name 
                 FROM cities, states
                 WHERE cities.state = states.id AND cities.rep = :rep_code" 
  arguments=(("rep_code", string)) )

You can see that the retrieve clause of the DataWindow definition contains a SQL SELECT statement.

Limitations

SQLExtract does not extract queries defined in the PowerBuilder internal query language (PBSELECT).

DataWindow Object (a fragment of .srd file using PBSELECT):

  $PBExportHeader$d_dddw_m_miljozon_typer.srd
  datawindow(units=0 timer_interval=0 color=16777215 processing=0 ...)
  ...
  table(column=(type=char(40) updatewhereclause=yes name=col1 dbname="tabe.col1")...
  retrieve="PBSELECT( VERSION(400) TABLE(NAME=~"tab1~" ) COLUMN(NAME=~"tab1.col1~") 
  WHERE(    EXP1 =~"~~~"tab1~~~".~~~"col2~~~"~"   OP =~"=~"    EXP2 =~"2~"    LOGIC =~"and~" ) ...
  WHERE(    EXP1 =~"~~~"tab1~~~".~~~"col2~~~"~"   OP =~"in~"    EXP2 =~"59, 60, 61, 62~" ) )"

Also sometime SQL fragments of a dynamic SQL are too small to identify automatically, for example

PowerBuilder:

  if ls_id <> "''" then
   ls_where = ls_where + ' AND ID = ' + ls_id
  end if

This example adds a condition to the WHERE clause of a SELECT statement.