Working with the JDBC Driver

To work with the JDBC driver, you must first connect to the target system with an SQL CONNECT TO command.

• If the target is on the same IBM i, or the username and password to connect to a remote DB is specified elsewhere such as a server authentication entry or in the Force RDB User and Password fields of the Add New Directory Entry Details - Part B​ screen, use the command:
  CONNECT TO directory_entry
  where directory_entry is the name of the Directory Entry to which data will be written.
• Otherwise, connect using the username and password from the target's DB credentials:
  CONNECT TO directory_entry USER username USING 'password'

To associate the library and table on the target system to which you are writing with the source that you are reading, use the COMMENT ON command:
COMMENT ON library.table IS source_url

where

• library.table shows the library and table on the target system to which you are writing
• source_url shows the file on the source system that you are reading

such as
COMMENT ON QGPL.MY_TABLE IS 'file:///tmp/ customers.xls'
which specifies that data is

• read from the /tmp/customers.xls file on the current system's IFS, and
• written to the MY_TABLE table in the QGPL library, also on the current system.

If you omit the library name, as in

COMMENT ON MY_TABLE IS 'file:///tmp/ customers.xls'

or use the library name QTEMP, the table is created in a temporary library that only exists during the current session. It is not to be confused with the current job's QTEMP library.

The URL for accessing the table depends on the protocol being used:

• File: primarily for accessing files in the IFS file system:
  
  COMMENT ON QGPL.MY_TABLE IS 'file:///tmp/customers.xls'

• HTTP: primarily for the web
  COMMENT ON QGPL.MY_TABLE IS 'http://www.example.info/gui/db_gate/ms.xls'

• FTP
  COMMENT ON QGPL.MY_TABLE IS 'ftp://example.com/readme.txt'

• SMB for Microsoft Windows shares for which a server authentication entry for the target server exists
  
  COMMENT ON QGPL.MY_TABLE IS 'smb://192.168.1.181/shareddocs/sales.csv'

• SMB when supplying User + Password for Microsoft Windows shares:
  
  COMMENT ON QGPL.MY_TABLE IS 'smb://user:password@192.168.1.181...'

To specify that you are reading from a sheet within an Excel file or a table embedded in ODF Text Documents (LibreOffice/OpenOffice), include an @ sign and the number of the sheet in the COMMENT ON statement. For example, to specify sheet 2 in the example, use the command:
COMMENT ON QGPL.MY_TABLE IS 'file:///tmp/customers.xls@2'

You can then access the table with a SELECT statement such as
SELECT* FROM QGPL.MY_TABLE

You must indicate the library name, even if you did not state it in the COMMENT ON statement (in which case it would be the QTEMP library that only exists for the current session).

The table is created within the specified library. The driver then has read-only access to the source PC file.