File Upload Wizard

The File Upload Wizard allows you to build a File Upload command and send it to a remote database server. You can build a File Upload command to create, replace, append data to, or update a table in a remote database.

This online help file describes two versions of the File Upload Wizard:

• The Java 1 version
  If you are using a Java 1 browser, then the Java 1 version of the File Upload Wizard is displayed.
• The Java 2 version
  If you are using a Java 2-enabled browser, then the Java 2 version of the File Upload Wizard is displayed.

The remote database server must be running on an iSeries or AS/400, and an appropriate Java Database Connectivity (JDBC) driver must be present on the client workstation. The Host On-Demand client and the Database On-Demand client already include a JDBC driver from the AS/400 Toolbox for Java, which allows the Host On-Demand client or Database On-Demand client to access DB2/400 data on a properly configured iSeries or AS/400 (see the Database URL field on the Logon tab).

Database servers running on other host platforms are supported only if the appropriate JDBC driver is installed on the Host On-Demand or Database On-Demand client workstation (see the Database URL field on the Logon tab).

The File Upload Wizard can read data files in various file formats, including XML (see File below). You can save and reuse File Upload statements (see File below).

You can set default values for some of the fields in the File Upload Wizard:

• For a Host On-Demand client:
  See Data Transfer Defaults.
• For a Database On-Demand client:
  See Database On-Demand User Options.

A sequence of tabs guides you through the process of building and running a File Upload statement. These tabs are:

• Logon
• Table
• Key Columns
• File

Logon

• Database URL
  See Specifying a database URL.

  If the File Upload Wizard is launched from a 5250 Display session, then the File Upload Wizard initially displays in the Database URL field a default database URL containing the name of the current iSeries host.

• Userid (Java 1 browser)
  User ID (Java 2-enabled browser)
  Type the user ID required (if any) to connect to the remote database server.
• Password
  Type the password required (if any) to connect to the remote database server.
• Driver description (Java 1 browser)
  
• Driver identifier (Java 2-enabled browser)
  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, see Registering a JDBC driver.

• Class name (Java 1 browser)
  
• Other (Java 2-enabled browser)
  This field must contain the fully qualified Java class name of the JDBC driver.
  • If, in the Driver Description (or Driver Identifier) field, you selected the name of a registered driver, then Host On-Demand displays the fully qualified Java class name for you in this field and does not allow you to modify it.
  • But if, in the Driver Description (or Driver Identifier) field, you selected Other, then you must type the fully qualified class name of the driver into this 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.

  The driver must be installed on the workstation and must be accessible to the Host On-Demand client or Database On-Demand client (see Deploying a JDBC driver).

• Table Filter (File Upload)
  This field does not appear unless enabled by the Host On-Demand administrator.

  Type a comma-separated list of schema names, schema filters, table names, and table filters. This list specifies the schemas and tables that you want to appear in the List of available tables on the Table tab of the File Upload Wizard after you log on to the database.

  This Table Filter field is one of several levels of filtering for the List of available tables (see Level 4. Value for the current SQL or File Upload statement).

  For information on creating a list of schemas and tables, see Specifying a list of available schemas and tables.

• Connect
  Click Connect to connect to the remote database server.

  If you are using a Java 1 browser and you are having problems logging on or connecting to the host, see Problems logging on or connecting.

  Disconnect
  Click Disconnect to disconnect from the remote database server.

Table

• File Upload type
  Select the type of File Upload statement that you want to send to the remote database server:
  • Click Create to create a new table in the remote database.
  • Click Replace to overwrite the contents of an existing table in the remote database.
  • Click Append to append data to an existing table in the remote database.
  • Click Update to update selected parts of a table in the remote database.
• The following fields are used only for a Create operation:
  • New table name:
    Type the name of the new table to be created in the remote database.
  • Reference table
    For a Create operation, click Reference table if you want to use an existing table in the remote database as the source for field descriptions for the new table.
  • Saved SQL statement
    For a Create operation, click Saved SQL statement if you want to use a saved SQL statement as the source for field descriptions for the new table.
  • Select a saved SQL statement
    For a Create operation, this list is displayed when you select Saved SQL statement as the source for field descriptions for the new table. Select the saved SQL statement that you want to use as the source for field descriptions.
• The next four fields are slightly different in the Java 1 version (displayed if you are using a Java 1 browser) and the Java 2 version (displayed if you are using a Java 2-enabled browser):
  • If you are using a Java 1 browser:
    • Select a reference table (Create)
      Select a table (Replace, Append, Update)
      This a list of the schemas and tables that are available to be used in the current File Upload statement (see List of available tables).

      The contents of this list are determined by several levels of filtering (see Levels of filtering for schema names and table names).

      For a Create, select the checkbox next to the name of the table that you want to serve as the source for the field descriptions for the new table to be created.

      For a Replace, Append, or Update, select the checkbox next to the name of the table that you want to be the target of the Replace, Append, or Update operation.

    • View schema(s)
      Click View schema(s) to select the names of schemas that you want to appear in the list of tables under Select a Table.
    • Filter table(s)
      Click Filter table(s) to specify the names or types of tables that you want to appear in the list of tables under Select a Table.

  • If you are using a Java 2-enabled browser:
    • Available tables
      This a list of the schemas and tables that are available to be used in the current File Upload statement (see List of available tables).

      The contents of this list are determined by several levels of filtering (see Levels of filtering for schema names and table names).

      For a Create operation, select one table to serve as the source for the field descriptions for the new table to be created in the remote database.

      For a Replace, Append, or Update operation, select one table to become the target of the Replace, Append, or Update operation.

      Move the name of the table that you want to use for the Create, Replace, Append, or Update operation from the list of Available tables to the Selected table field:

      1. Select a table name.
      2. Click >.

    • Selected table
      This field contains the name of the table to be used for the Create, Replace, Append, or Update operation, either as a reference table (Create) or as the target of the operation (Replace, Append, or Update).

      To remove a selected table name, click <.

    • Filter Schemas
      Click Filter Schemas to select the names of schemas that you want to appear in the list of Available tables.
    • Filter Tables
      Click Filter Tables to specify the names or types of tables that you want to appear in the list of Available tables.

• Refresh
  Click Refresh to refresh the list of tables under Select a Table.

Key Columns

The Key Columns tab is available only when the type of the File Upload statement is Update.

• Available column(s)
  This list initially contains the names of all the columns that exist in the table that you selected on the Tables tab. Move the names of the columns that you want to update to the list of Selected Column(s).

  If the table that you selected already has key columns defined, Host On-Demand automatically moves the names of the key columns to the list of Selected Column(s).

• Selected column(s)
  This list contains the names of the columns that you want to update.

  If you select the wrong columns, then the File Upload statement can write the wrong type of data into the columns.

File

Click the File tab to specify the source file that you want to use for the File Upload statement.

• File name
  Type the path and name of the source file, or click Browse to browse for a file.
• File type
  Click the file format of the source file:

  • ASCII Text (*.txt)
    The data is ASCII text formatted in columns.
  • Comma separated values (*.csv)
    Also known as CSV format. The data is ASCII text, with each line containing one record, and the fields in each record separated by commas.
  • Lotus 1-2-3 (*.wk1)
    Lotus 1-2-3 file format.
  • Microsoft Excel - BIFF3 (*.xls)
    Microsoft Excel 3.0 file format (BIFF3).
  • Microsoft Excel - BIFF4 (*.xls)
    Microsoft Excel 4.0 file format (BIFF4).
  • XML (*.xml) (File Upload)
    Two types of XML file are supported:

    Excel XML

    This type of XML file can be opened and modified by Microsoft Excel 2003. See The format of an Excel XML file.

    DTD XML

    This type of XML file is a valid XML document with data formatted in accordance with the DatabaseOnDemand DTD. See The format of a DTD XML file.

    The File Upload Wizard does not validate the XML document against the DatabaseOnDemand DTD. (However, many XML editors do support validation. You can use one of these editors to validate the document, if the document still contains the DatabaseOnDemand DTD.)

    The File Upload Wizard uses the NAME attribute of the COLUMN element of the DTD to match a column in the XML file (for example, DESCRIPT) to the same-named column in the remote database.

    You can use Windows NotePad or any standard XML editor to edit the contents of the DTD XML file (to add, remove, or change data), so long as:

    • The changes are consistent with the DatabaseOnDemand DTD.
    • The DatabaseOnDemand DTD is not modified. (However, you can delete the DTD.)

• Save Statement
  Click Save Statement to save the File Upload statement for re-use later.
• Run Statement
  Click Run Statement to send the File Upload statement to the remote database server.

Related topics