Configure SQL Statement

To open the Configure SQL Statement window, follow these steps:

1. In a 3270 Display session, 5250 Display session, or VT Display session, click Actions > Transfer Data > Receive Data from Host.
2. On the Receive Data from Host window, click New.

The Configure SQL Statement window has two main sections: a statement section and a result section.

The statement section occupies the upper area of the window and includes the following fields: Database URL, Driver Identifier, Driver Class, User ID, Password, and Statement. To change the contents of one of these fields, move the text cursor into the field and type the text that you want.

The result section occupies the lower area of the window and includes the remaining fields: Output Result To, File Name, File Type, Overwrite, and Append. You can use the fields in this section to write the data resulting from an SQL Select statement into a file. (By default, Host On-Demand sends the data resulting from an SQL Select statement to the display.)

Information about saving and re-opening SQL statements:

• You can configure and save an SQL statement in either of two windows:
  • In the Configure SQL Statement window; or
  • In the SQL Wizard
• However, you cannot transfer configured information from the SQL Wizard to the Configure SQL Statement window.
• Moreover, when you Open a previously saved SQL statement (you do this by clicking Open in the Receive Data from Host window) then Host On-Demand opens the statement in the same window in which you saved it. Therefore:
  • If you configured and saved the SQL statement in the Configure SQL Statement window, then Host On-Demand opens the previously saved SQL statement in the Configure SQL Statement window.
  • In contrast, if you configured and saved the SQL statement in the SQL Wizard, then Host On-Demand opens the previously saved SQL statement in the SQL Wizard.

SQL Wizard

Click SQL Wizard to open the SQL Wizard. With the SQL Wizard you can create an SQL statement, test it, view the results, and save the SQL statement. Follow these steps to use the SQL Wizard:

1. Click SQL Wizard.
2. The Configure SQL Statement window closes.
3. The SQL Wizard opens.
   • If you or your Host On-Demand administrator have specified any default values for the Data Transfer Defaults, then Host On-Demand uses these values to initialize the appropriate fields in the SQL Wizard (see Data Transfer Defaults).
4. Use the SQL Wizard to accomplish the tasks that you need to accomplish. You can:
   • Log on to a remote database server.
   • View the names of the available schemas and tables.
   • Create an SQL statement.
   • Run the SQL statement and view the results.
5. If you want to save the SQL statement that you have created in the SQL Wizard, then you can do one of the following actions:
   • If you are running a Java 1 browser:
     • On the SQL tab of the SQL Wizard, click Save SQL; or
     • On the Results tab of the SQL Wizard, click Save SQL.
   • If you are running a Java 2-enabled browser:
     • On the Review tab of the SQL Wizard, click Save; or
     • On the Results tab of the SQL Wizard, click Save SQL.
   Host On-Demand saves the SQL statement inside the current session definition. The SQL statement appears in the Current Session subtree of the Receive Data from Host window.
6. When you ready to close the SQL Wizard, click Cancel. If you have not saved the SQL statement, it is lost.

You cannot copy configuration information from the SQL Wizard to the Configure SQL Statement window.

When you open an SQL statement that you previously saved in the SQL Wizard, then Host On-Demand opens the SQL statement in the SQL Wizard (see Information about saving and re-opening SQL statements).

Database URL

Type a Database URL (see Specifying a database URL).

Driver Identifier

The listbox includes the names of all the JDBC drivers that have been registered with Host On-Demand. Expand the listbox and click the name of the registered JDBC driver that you want to use, or click Other to specify another driver.

For general information on using a JDBC driver with the Host On-Demand client, see Registering a JDBC driver.

Driver Class

The fully qualified Java class name of the JDBC driver.

• If, in the Driver Identifier field, you selected the name of a registered driver, then Host On-Demand displays the class name for you in the Driver Class field and does not allow you to modify the class name.
• But if, in the Driver Identifier field, you selected Other, then you must type the fully qualified class name of the driver into the Driver Class field. Case is significant (COM is different from com). If you do not know the fully qualified class name, contact the provider of the driver.

User ID

The user ID required to access the database (if required.)

Password

The password required to access the database (if required.) When you type a password (such as mypass) into the Password field, Host On-Demand displays the password using asterisks (****** ).

Statement

The SQL statement that you want to send to the database server. You can create the same four types of SQL statements that are available in the SQL Wizard:

• Select
• Insert
• Update
• Delete

Host On-Demand does not check the format of the SQL statement for validity. If the format of the SQL statement is invalid, a run-time error occurs when the Host On-Demand processes the SQL statement, and Host On-Demand displays an error message.

Output Result To

Selects the output destination for the data resulting from an SQL statement:

• Display
  Host On-Demand displays the data in the Query Results window. This option is the default.
• File
  Host On-Demand writes the data to a file.
  • Type the path of the file into the File Name field. Note: You must specify a complete file path and name, including the drive letter if any (for example, c:\Documents and Settings\user1\output.txt).
  • Expand the File Type listbox and click a database file type.
  • Click Overwrite to overwrite an existing file, or Append to append to it.

File Name

If the SQL statement is an SQL Select statement:

• If you want to send the data resulting from the SQL Select statement to the display, leave this field blank.
• If you want to send the data resulting from the SQL Select statement to a file, type the path and name of the file into this field, or click Browse to select an already existing file.

If the SQL statement is an SQL Insert, Update, or Delete statement, then ignore this field.

File Type

Click the database type that you want for the output file. Ignore this field if the type of the SQL statement is Insert, Update, or Delete.

When the file type is HTML or XML, Host On-Demand has additional options (such as table border, cell-spacing, and others for the HTML file type, and XML format and character encoding for the XML file type) with default values that you can modify in the SQL Wizard (on the Output tab) but that you cannot modify here in the Configure SQL Statement window (you get the default values).

The default values are:

• For HTML:
  -- Left align, Include border, Include column headings, and many other minor display options.
  
• For XML:
  -- Excel XML format with Unicode UTF-8 encoding

If you want to set one of these options to some value other than the default, then use the SQL Wizard to construct the SQL statement (see Information about saving and re-opening SQL statements) and specify the values that you want (in the SQL Wizard, click the Output tab, select File, select HTML or XML, and then click Settings). When you run the SQL statement, Host On-Demand uses the values that you specify.

Overwrite, Append

Click Overwrite to overwrite the contents of an existing file, or click Append to append the data to an existing file. If the file does not exist, then either Overwrite or Append creates the new file. Ignore this field if the type of the SQL statement is Insert, Update, or Delete.

Run

Click Run to run the SQL statement. The effects of running an SQL statement from this window are the same as when you click Run in the Receive Data From Host window (see Run ).

Save

Click Save to store the SQL statement inside the current session definition. The SQL statement appears in the Current Session subtree of the Receive Data from Host window.

When you open an SQL statement that you previously saved in the Configure SQL Statement window, then Host On-Demand opens the SQL statement in the Configure SQL Statement window (see Information about saving and re-opening SQL statements).

The Configure SQL Statement window has many of the same fields as the SQLQuery window in the Macro Editor. For more detailed information on these fields, see SQLQuery action in the Macro Programming Guide.

Related topics:

• Receive Data from Host
• Query Results
• SQLQuery action (<sqlquery> element) Macro Programming Guide
• Send Data to Host
• Data Transfer Defaults
• Overview of database access