Database On-Demand File Upload

Database On-Demand provides an interface for uploading files from a client to a host database. The Database On-Demand File Upload feature is entirely different than the iSeries File Upload feature. The advantages of the File Upload feature include:

• Manipulates database data specifically
• Provides for different File Upload statement types
• Uploads numerous file formats
• Connects to any database using a JDBC driver

The File Upload feature also differs from the Database On-Demand SQL Wizard in that the wizard allows you to work with records in a host database, while File Upload allows you to work with entire files in a host database.

A set of tabs guides you through the process of building and executing a valid File Upload statement. The tabs include:

• Logon
• Table
• Key Columns
• File

Logon

Click the Logon tab to connect to the target database. All fields are required.

• Database URL
  Type the URL for the database you want to work with. Consult your JDBC driver documentation for the format of the database URL. For example, the iSeries Toolbox for Java JDBC driver requires:

  jdbc:as400://iSeriesname

  where iSeriesname is the fully-qualified network name of the database host.

  To use a proxy server when connecting to the iSeries database, include the proxy server name and port number:

    jdbc:as400://iSeriesname;proxy server=HODServerName:proxyServerPort
    

• Userid/Password
  Type your user ID and password for the specified database.
• Driver description
  Select the JDBC driver used to communicate with the specified database.
• Class name
  This field contains the class name of the driver associated with the descriptive name in the Driver field.

Enter the required information and Click Connect to open a connection to the host database.

• If you see this message when logging on: Please disable the JIT compiler and restart the browser.
  you must stop your browser, rename the Netscape jit*.dll file so that it is not a dll file type and restart your browser. This file is located in the \program files\netscape\communicator\program\java\bin\ directory.



• If you see this message when logging on: Please reset the environment variable CLASSPATH to NULL.
  you must stop your browser, reset the CLASSPATH to NULL and restart your browser. Check the documentation on your operating system for information regarding how to reset environment variables.
• On Netscape 4.x, you cannot connect to a V5R1 iSeries host that has long password support enabled. The Netscape 4.x JVM does not implement the APIs required to encrypt long passwords. You can use a different browser or install the Netscape 4.x Java 2 plug-in.

After connecting, the connect button is replaced by a Disconnect button, which allows you to disconnect from the database. Also, the following set of tabs are added to the Logon tab:

Table

Click the Table tab to specify the tables you want to access in your File Upload statement, and the type of File Upload statement that you will generate.

1. Select the File Upload statement type you want to use. Valid statement types include:
   
   
   • Create
     Allows you to create a new table in the host database, using the field descriptions from the existing reference table or a saved SQL statement, and append the data read from the selected file to the new table.
   
   
   
   • Replace
     Allows you to overwrite the contents of the existing host table with data read from the client file.
   
   
   
   • Append
     Allows you to append the data read from the client file to the existing host table.
   
   
   
   • Update
     Allows you to update the existing host table, using specified key columns, with data read from the client file. For example, you could:
     
     
     1. Use the Database On-Demand SQL Wizard to select which records to download from an existing database table, and save the data to one of the supported file types
     
     
     
     2. Manipulate the data with any program that supports the file type in which you saved the data
     
     
     
     3. Upload the entire file to the host database table, instead of updating the records individually with the SQL Wizard


2. If you are creating a table, type a name for the new table, and select whether to use the field descriptions from a reference table, or from a saved SQL statement. Choose either the table or the saved SQL statement you want to access from the list. If you are making changes to a table, choose Replace, Append or Update and select the table the data is replacing, appending to or updating.



3. Click Next.

Click View schema(s) to add tables from additional schemas. A schema is similar to a database or library. For iSeries schemas, the defaults are what is in the default library list for the user profile.

Click Refresh to update the list of tables that are displayed. New tables that were added to the schema are displayed in the Table list; deleted tables are removed. Refresh does not reset any selections that have been made.

When you finish with the Table tab, click Next.

Key Columns

This tab displays only if you select the File Upload type "Update" on the Table tab. The available columns window contains the list of the defined columns in the host database table that you can use to uniquely identify the records you want to update.

1. Select one or more columns from the Available columns list.
   • Click Select all to select all columns in the list.
   • Click Remove all to remove all columns in the list.


2. Use the Add<< and >>Remove buttons to move column names from one list to another.

If the database table you selected already has key columns defined, the key fields are automatically selected from the available columns window and placed in the selected columns window.

You must select the correct columns that uniquely identify the records you want to update, or the update operation results in inconsistent data.

When you finish with the Key Columns tab, click Next.

File

Click the File tab to specify the name of the file to upload. The file type is automatically selected if the file extension is recognized. File Upload supports the following file types:

Database On-Demand File Upload cannot support the latest Lotus 1-2-3 or Excel file formats because of the layout and the native methods needed read these files types.

You can save the File Upload statement you have created or updated by clicking Save Statement.

You can upload your file by clicking Run Statement.