Generating pureQuery code from multiple SQL statements

From multiple SQL statements, you can generate data access objects or inline methods that use pureQuery code. The statements can be in SQL scripts in the workbench or they can be in script files in the file system.

About this task

Depending on your license configuration, this functionality might not be available. You can work with SQL scripts that are in pureQuery-enabled Java™ projects in the Java perspective and in data development projects in the SQL and Routine Development perspective. You can work with SQL scripts that are open in the SQL and XQuery editor and you also can import SQL statements that are in script files.

An SQL script can contain one or more SQL statements. The number of SQL statements that a script contains or the number of statements that you select determines the wizard that you use to generate pureQuery code. For scripts that contain a single SQL statement and for scripts in which you select a single SQL statement, see Generating pureQuery code from single SQL statements.

You can use this process to generate pureQuery code only for valid SELECT, INSERT, UPDATE, DELETE, or CALL statements. If a JDBC prepareStatement() method of an SQL statement results in errors, a message box displays the error message that is returned.

Procedure

To generate pureQuery code from multiple SQL statements:

  1. Open the Generate pureQuery Code from SQL Statements wizard by performing one of the following sets of steps:
    Option Description
    For SQL statements that are in an SQL script that is open in the SQL and XQuery editor:
    1. Optional: If you want to generate pureQuery code for only some of the SQL statements in the script, select each of the statements in its entirety. If you do not select any statements, pureQuery code is generated for all of the SQL statements.
    2. Right-click the selected SQL statements or, if you did not select any statements, right-click anywhere in the editor, and then select Generate pureQuery Code.
    For SQL statements that are in an SQL script in a pureQuery-enabled Java project:
    1. Switch to the Java perspective.
    2. Right-click the .sql file in the project that contains the SQL statements, and then select Generate pureQuery Code.
    For SQL statements that are in an SQL script in a data development project:
    1. Switch to the SQL and Routine Development perspective.
    2. Expand the SQL Scripts folder in the project.
    3. Right-click the script that contains the SQL statements, and then select Generate pureQuery Code.
    For SQL statements that are in a script file in the file system:
    1. Select File > New > Other.
    2. In the New wizard, expand the Data node, select pureQuery Annotated-method Interface, and then click Next.
  2. Specify information on the pureQuery Code Generation page and then click Next. If you opened the wizard from an SQL script in the work bench, the Statements list on the SQL Statements page is populated with the SQL statements in the script.
  3. To import SQL statements from a script file in the file system, follow these steps:
    1. Specify the terminator that the SQL statements in the file use.
    2. Click Import, and then select the script file.
  4. Optional: Change the name of a bean or a method in the list, by double-clicking the name that you want to change, and then entering the new name. If you change the name of a bean, the default name of its corresponding method is updated with the new bean name.
  5. Optional: If you have an SQL SELECT statement, review and edit the bean information to generate by clicking the SELECT statement in the list, and then clicking Details.
  6. Optional: If you have an SQL CALL statement, review and edit the custom call handler to use in the generated method by clicking the CALL statement in the list, and then clicking Details.
  7. Complete the steps of the wizard. To see information about the fields in the wizard, press F1.
  8. Click Finish to generate the files that you specified.

Results

When you generate pureQuery code, the workbench creates a bean that you can use to access your database. For example, you can generate code from an UPDATE statement or a SELECT statement. When you generate code from a SELECT statement, the workbench creates a bean that you can use to hold query results. If you do not use AS clauses in SELECT statements that produce joins or unions, or that contain calculated columns, query results might contain columns with non-unique names. Whether the workbench is able to resolve this problem when generating a bean to represent query results depends on the type of database that your application uses.


Feedback