Use the workbench to extract SQL statements from Java™ code, create a pureQueryXML
file from SQL statements, and bind the SQL statements to the database.
Earlier in the tutorial, you created a pureQueryXML file
by running your application and capturing SQL statements. Another
way to create a pureQueryXML file containing SQL statements from your
application is by extracting the SQL from your application source
code. You extract the SQL statements that the workbench lists in SQL
outline view.
To create a pureQueryXML file from SQL statements exported
from Java source code:
- Extract the SQL from the application.
- In the SQL outline view, open the Java
Projects window by clicking the Java tab
at the bottom of view.
- In the Java Projects window, right-click
your Java source code file and
click Export SQL to File.
- Save the file that contains the extracted SQL statements
as pureQueryFolder\MySample.sql.
The workbench creates the pureQueryFolder\MySample.sql file.
Note: After
you create the file, you can open the SQL file in the SQL editor,
add cursor attribute information, and create the pureQueryXML file
from in the SQL editor.
- Create a pureQueryXML file from the SQL file.
- If needed, refresh the contents of the pureQueryFolder folder
in the Package Explorer window to display the MySample.sql file.
- In the Package Explorer, right-click
the SQL file and click .
- In Generate pureQueryXML from SQL Statements,
specify the location of the output pureQueryXML file and click Next.
- In the Java project field, select your
project.
- In the pureQueryXML file name field, enter
the name MySample.pdqxml.
- Review the updates that are proposed for the Default.genProps
File file.
The workbench updates the
Default.genProps file
with information similar to the following text, which specifies a
-rootPkgName option
with the value
myPkg for the pureQueryXML file.
C:\ODS-tutorial\pq-tutorial\pureQueryFolder\MySample.pdqxml = -rootPkgName myPkg
- Click Finish.
The workbench creates the pureQueryFolder\MySample.pdqxml pureQueryXML
file.
- View the pureQueryXML file.
In the Package
Explorer view, double-click the pureQueryFolder\MySample.pdqxml file.
The file opens in the pureQueryXML editor. The statement
set name is myPkg.
- Close the pureQueryXML file.
- Bind the SQL statements in the pureQueryXML file.
Right-click
the MySample.pdqxml file and click .
The workbench runs the
StaticBinder utility
and sends the output from that utility in the Console view:
================================================================================
The StaticBinder utility is beginning the bind operation for the pureQueryXml
file 'C:\test\ODS-tutorial\pq-tutorial\pureQueryFolder\MySample.pdqxml'.
Starting to process options:
-url "jdbc:db2://localhost:50000/SAMPLE:retrieveMessagesFromServerOnGetMessage=true;"
-username "*****" -password "*****"
-pureQueryXml "C:\test\ODS-tutorial\pq-tutorial\pureQueryFolder\MySample.pdqxml"
The StaticBinder utility successfully bound the package 'myPkgA1' for the isolation level 'UR'.
The StaticBinder utility successfully bound the package 'myPkgA2' for the isolation level 'CS'.
The StaticBinder utility successfully bound the package 'myPkgA3' for the isolation level 'RS'.
The StaticBinder utility successfully bound the package 'myPkgA4' for the isolation level 'RR'.
The bind operation completed successfully for
'C:\test\ODS-tutorial\pq-tutorial\pureQueryFolder\MySample.pdqxml'.
================================================================================
Results of the StaticBinder utility's activity:
Number of items for which the bind operation SUCCEEDED: 1
Bind for XML file MySample.pdqxml using connection SAMPLE in project pq-tutorial succeeded.
- Update the source code to run a new SQL statement and run
the application.
- Set the parameter in the application to run a new SQL
statement.
Change the value of the i variable
to 2 in the following code:
if ( args.length == 0) {
// value for the default SQL statement to run
i = 2 ;
- Save the file.
- Run the application.
Right-click the MySample.java file
and click
pureQuery
Runtime returns an error stating the SQL statement cannot be found
in the pureQueryXML file.
In the pureQuery Runtime properties
file src/pdq.properties, the value of the pureQueryXML property
is pureQueryFolder/capture.pdqxml. The pureQueryXML
file does not contain the SQL statement issued by the application.
- Change the information in the pdq.properties file
to set the MySample.pdqxml file as the pureQueryXML
file.
- Double-click the scr\pdq.properties file.
The file opens in the PDQ Properties editor.
- Set the MySample.pdqxml file as
the pureQueryXML file by changing the value of the pureQueryXML property
to pureQueryFolder/MySample.pdqxml.
pdq.captureMode=OFF
pdq.capturedOnly=TRUE
pdq.executionMode=STATIC
pdq.pureQueryXml=pureQueryFolder/MySample.pdqxml
- Run the application.
Right-click the MySample.java file
and click
The
application runs. Output appears in the
Console window.
The output is similar to the following example:
=======
IBM Optim pureQuery Runtime 2.2.1.0 build 2.25.63
=======
case 2
case 2 - large projects: 11
Finished case 2
With the correct mapping in the
pureQueryXML file MySample.java between the SQL
statement and the package on the SAMPLE database, pureQuery Runtime
can run the statement statically.