To open the Configure SQL Statement window, follow these steps:
In a 3270 Display session, 5250 Display session, or VT Display session,
click Actions > Transfer Data > Receive Data from Host.
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:
Click SQL Wizard.
The Configure SQL Statement window closes.
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).
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.
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.
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.
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.
