< Previous | Next >

Lesson 4: Creating a pureQueryXML file from SQL statements exported from Java source code

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:

  1. Extract the SQL from the application.
    1. In the SQL outline view, open the Java Projects window by clicking the Java tab at the bottom of view.
    2. In the Java Projects window, right-click your Java source code file and click Export SQL to File.
    3. 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.
  2. Create a pureQueryXML file from the SQL file.
    1. If needed, refresh the contents of the pureQueryFolder folder in the Package Explorer window to display the MySample.sql file.
    2. In the Package Explorer, right-click the SQL file and click pureQueryXML > Generate pureQueryXML File.
    3. 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.
    4. 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
    5. Click Finish.

    The workbench creates the pureQueryFolder\MySample.pdqxml pureQueryXML file.

  3. 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.

  4. Close the pureQueryXML file.
  5. Bind the SQL statements in the pureQueryXML file.

    Right-click the MySample.pdqxml file and click pureQuery > Bind.

    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.
  6. Update the source code to run a new SQL statement and run the application.
    1. 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 ;
    2. Save the file.
  7. Run the application.

    Right-click the MySample.java file and click Run As > Java application

    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.

  8. Change the information in the pdq.properties file to set the MySample.pdqxml file as the pureQueryXML file.
    1. Double-click the scr\pdq.properties file.

      The file opens in the PDQ Properties editor.

    2. 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
  9. Run the application.

    Right-click the MySample.java file and click Run As > Java application

    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.

Lesson checkpoint

You extracted SQL statements from your application source code, created a pureQueryXML file from the SQL statements,
From the workbench, you performed the following tasks:
  • Extracted SQL statements from a Java code file.
  • Converted the SQL statements to a pureQueryXML file.
  • Performed the binding process on the pureQueryXML file.
  • Configured pureQuery Runtime by setting pureQuery Runtime properties.

From the SQL Outline view, you created a pureQueryXML file from a file that contains SQL statements from your Java source code. For information about the SQL Outline view, see SQL Outline view.

You can also create a pureQueryXML file from a text file that contains SQL statements outside the workbench. Use the Java-based pureQuery utility GeneratePureQueryXml utility to create a pureQueryXML file from a text file that contains SQL statements and optionally cursor attribute information. For information about the utility, see GeneratePureQueryXml utility.

< Previous | Next >

Feedback