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.
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.
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.
SQLExtract features:
SQL Statements
SQLExtract extract the following SQL statements from PowerBuilder source code:
PowerBuilder Files
You can extract SQL from the following files (PowerBuider objects):
Extraction Statistics
For each file SQLExtract provides:
The summary information is also available:
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.
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.