Help Topics Concepts Package Class

## Database Import Bean Properties and Use

### Properties

The Database Import Bean panel provides these options; see the Sample Connection Data for some valid combinations.

Driver name
  The name of the JBDC driver to load. Defaults to IBM's DB2. Case is significant. This field is editable, and any additions are preserved in the Able.properties file. The class will be dynamically loaded and must be in your CLASSPATH before starting the Agent Editor.
Protocol
  The name of the protocol to use when creating an SQL connection. Defaults to "jdbc". This field is editable, and any additions are preserved in the Able.properties file.
Subprotocol
  The subprotocol to use when creating an SQL connection. Defaults to "db2". This field is editable, and any additions are preserved in the Able.properties file.
Database
  The database to use when creating an SQL connection. This field is editable, and any additions are preserved in the Able.properties file. For local databases, just enter the name of a database such as SampleDB. For remote databases, enter the system name and database using the '//' format, for example //dbserver/SampleDB.
Options
  Any options to be included in the JDBC URL for the URL parameter of the DriverManager's getConnection method.
Userid
  The userid to use when creating an SQL connection.
Password
  The password used for the SQL connection. The password is not saved when the bean is serialized.
Schema
  The schema containing the desired table. In some database implementations, this is the table owner.
Table
  The name of the table to be read.
Column selection
  A list of column names separated by commas. It defaults to "*". This is used as the clause immediately after the word "SELECT" in the query.
Row selection
  If provided, the value from the row selection field is becomes part of the query immediately after the word "WHERE". Do not include "WHERE" in this String; if data is provided, it will be included automatically.
Record cache count
  Enter the number of records to be read from the file and stored in memory. To read the entire file and store in memory, select All.
Randomize data
  Check this box if the file data should be presented in a random sequence. For some beans such as BackPropagation, this can be beneficial; for other beans such as Temporal Difference Learning the record sequence is critical.
Steps per cycle
  Enter the number of records to be processed in one cycle.
Relative to table size
  Check this box if the Steps per cycle value is to be used in conjunction with the number of records in the table. When not selected, the Steps per cycle value is the absolute number of records in a cycle. When selected, the value is multiplied by the table size to obtain the number of records in a cycle. This allows the cycle size to be proportionate to the number of records in a table - 0.5 means half the records in the table should be processed for each cycle, 2.0 means each record in the table should be processed twice in one cycle.
Compute field statistics
  Check this box if the field statistics are to be computed when the file is opened. When field statistics are computed, the definition file is read to determine the record layout for the file. The values in each field are examined. For each discrete and categorical field, the set of unique values is determined. For continuous fields, the minimum, maximum, and mean are determined. When field statistics are not computed, the record layout and statistics used to read the file are 'borrowed' from another data source. Starting with the active data source, the container agent's list of data sources is scanned until field layout and statistics data is located from a previously opened data source. If none is found, the definition file will be read and statistics computed in spite of the setting.

### Use

The Database Import Bean panel is used to connect to an SQL database and read records from a table. It can generate a filter bean to convert its values which are usually passed to a bean such as a neural network.

Steps in using the panel include:

1. Enter the Driver, Protocol, Subprotocol, Database and any Options necessary to create the SQL database connection.
2. Enter the Schema and Table name from which to read data.
3. Enter specific columns if desired, separating each column name with a comma (","). Enter "*" if all columns are desired.
4. If you wish to include or exclude rows in the query, enter the desired SQL statement without the "WHERE" lead-in.
5. Select the number of records to cache in memory.
6. Check Randomize data if beneficial to the receiving bean.
7. If this table is a test data source, has previously been opened with statistics computed, or for some other reason its layout and statistics is to be derived from another data source in the container, uncheck the Compute field statistics box.
8. Press the Open Table button. This will open the table, count the number of records it contains, and read the number of records specified into memory.
9. Press the Generate Filters button to create a translate filter that will convert the import data to numeric values, and a translate filter that will revert numeric values for output fields to the original domain. The convert filter will be connected to the import bean.

### Sample Connection Data

Here are combinations of connection data for various platforms as reported by Able developers and community members:

| Driver name | Protocol | Subprotocol | [Schema SchemaSeparator]Database
IBM DB2 Universal Database V7 | COM.ibm.db2.jdbc.app.DB2Driver | jdbc | db2 | sample
IBM Cloudscape | com.ibm.db2j.jdbc.DB2jDriver | jdbc | db2j | sample
Oracle | oracle.jdbc.driver.OracleDriver | jdbc | oracle oracle | driver:@machine:port:serviceID thin:@site:1580:sample
iSeries Java Toolbox JDBC | com.ibm.as400.access. AS400JDBCDriver | jdbc | as400 | dbname sample
Microsoft SQL | sun.jdbc.odbc.JdbcOdbcDriver | jdbc | odbc | dbname (default database provided by using the System DSN from ODBC settings) sample
MySQL | com.mysql.jdbc.Driver | jdbc | mysql | //host/database //localhost/test

The JDBC URL for the URL parameter of the DriverManager's getConnection method is constructed by appending the protocol, subprotocol, and database delimited with a colon (':'), and any options separated by a semi-colon (';').