JDBC Store - Data360_Analyze - Latest

MS Access database connection example

This example uses the same database information as the "Acquiring data from MS Access" tutorial that you can access from the Directory in the Data360 Samples/Tutorials folder.

1. Select the JDBC Store node, then in the DbType property select MS Access.
2. In the DbName property, enter the file path to your MS Access database, for example: {{%ls.appDataDir%}}/shared/Public/samples/GettingStarted.accdb
3. In the DbTable property, specify the name of the database table where you want to load the data, for example: orders
4. In the DbUser property, enter: {{^^}}

Properties

CatalogName

Optionally specify the catalog of the target table.

This is only relevant for databases that support the concept of catalogs (e.g. SQL Server).

SchemaName

Optionally specify the schema under which to find DbTable.

This property will be sent to the database in all capital letters because Oracle requires an uppercase schema name. This capitalization does not cause problems with other databases since they are all case-insensitive. Note that in SQL Server, if the DbUser is a system administrator, then the schema name is "DBO".

If left blank, then the default schema for DbUser is used.

For cases where the table is not within a schema, set this property to {{^^}}.

DbTable

Specify the name of the database table into which the input data is loaded. Surround the table name in double quotes if the table name is a reserved word for the database that is being used.

A value is required for this property.

DbUser

Optionally specify the database username to connect to the database and load the table.

DbPassword

Optionally specify the database user's password to connect to the database. This password is stored as clear text in the data flow and sent via clear text to the Data360 Analyze server. Therefore, it's important either to put this password into a data flow-level property or to use a user with limited privileges.

DbType

Select a database type to connect to.

If you are connecting to a database that uses a driver which is not pre-installed with Data360 Analyze, then this property is not required. In this case, specify the connection details in the Advanced property group.

DbName

Specify the name of the database to connect to.

If you are connecting to a database that uses a driver which is not pre-installed with Data360 Analyze, then this property is not required. In this case, specify the connection details in the Advanced property group.

DbHost

Specify the database host to connect to e.g. localhost. Leave this property blank if connecting to MS Access.

If you are connecting to a database that uses a driver which is not pre-installed with Data360 Analyze, then this property is not required. In this case, specify the connection details in the Advanced property group.

DbUrl

Optionally specify the driver's database URL.

If a database is selected from the DbType property and a DbName and DbHost are specified, then this property is not required.

Otherwise, specify the URL in the following format: jdbc:subprotocol:subname. For example, jdbc:sqlserver://localhost:1433;databaseName=CustomerData. Provided by the driver's vendor.

See the help topic Acquiring data from a database for more information on configuring this property.

DbDriver

Optionally specify the class name of your JDBC driver.

If a database is selected from the DbType property and a DbName and DbHost are specified, then this property is not required.

Otherwise, specify the class name of your JDBC driver, as provided by the driver's vendor.

See the help topic Acquiring data from a database for more information on configuring this property.

DbOptions

Optionally specify database connection properties e.g. key=val&key=val2.

DbDriverClasspath

Optionally specify the database driver classpath.

If a database is selected from the DbType property and a DbName and DbHost are specified, then this property is not required.

Otherwise, specify the classpath with each classpath entry on a new line.

Each classpath entry must be the location on the file system of either a single jar file, or a directory containing jars or class files.

DbDriverNativeLibraryDirectories

List of directories to search for native libraries, such as DLLs and shared libraries. The directories will be used by the JVM search for any native libraries needed by the node, or any of its dependencies. More specifically, the paths will be searched for any call to System.load(...) and System.loadLibrary(...). Additionally, the paths will be used when searching for the pre-load native libraries specified by the DbDriverNativeLibraries property.

The directories must be absolute paths, or resolve to absolute paths via substitution. The set of allowable directories is also limited by the server side property ls.brain.node.java.nativeLibraryWhitelistPaths.

Any directory which does not satisfy these conditions will be ignored. Set the LogLevel property to DEBUG to trace library resolution.

DbDriverNativeLibraries

A list of names, paths, or patterns of native libraries to pre-load into the JVM, prior to loading the driver. The specified entries will be searched for in the directories listed in the DbDriverNativeLibraryDirectories property.

Note that the pre-load will only load the libraries in the JVM memory space, and will not register them for JNI binding. It is assumed that the driver itself will make the appropriate calls to System.load(...) and System.loadLibrary(...).

The primary use of this property is to load transitive dependencies for native libraries that would have normally be required to be specified via an environment variable e.g. PATH or LD_LIBRARY_PATH. The values can be of the following forms:

• A library name which will be matched using the platforms specific naming library naming scheme, for example, for Linux "foo" would match "libfoo.so", while for windows it would match "foo.dll"
• An absolute path.
• A glob pattern of the form: glob:
• A regex pattern of the form: regex:

For both glob and regex patterns the pattern will be matched against the entire absolute path of the library files.

TableAndSchemaCase

Optionally specify whether the Table and Schema should be converted to Upper Case, lower case, or have case preserved. Choose from:

• Use Driver Settings
• To Upper
• To Lower
• Preserve

The default value is To Upper.

Notes: Most databases (MySQL on windows, MS SQL Server, etc.) have case-insensitive column and table names, so this property will often be irrelevant. However, the database metadata obtained from the JDBC driver should be able to provide this information. The default is therefore to use the driver's settings.

CommitFrequency

Optionally specify the number of rows that are processed before the data is committed. The larger the number, the more data that is loaded into the database before a commit is made. In general, smaller values will require the store to run more slowly while a larger value causes the store to run more quickly. However, if the CommitFrequency is too high, then the store might take even longer than with a small CommitFrequency.

This property may be set if the BatchMethod is not set to disabled.

BatchMode

Optionally specify the method used to execute the SQL. Choose from:

• disabled - batching will not be used.
• standard - batching will be used if the Database claims support for batching and the CommitFrequency is greater than 1.
• force - batching will be used if the CommitFrequency is greater than 1. (Note: This option does not validate that batching is supported by the Database).

The default value is standard.

LoadedDataOutput

If this optional property is set to the name of the second, optional output pin, then this node sends all records that load into the database to this second output. The purpose of this pin is to provide a list of all input records that were loaded into the database. This list contrasts the list on the ErroredData pin which contains all rows that did not load.

To describe this feature in more detail, data output by this property is taken from the input. It is not taken from the database itself. Therefore, defaults will not appear in the output data, and changes made by triggers will not be reflected either.

The default value is blank.

EmptySchemaAsNull

Optionally specify whether the database will be queried for the table with a null schema name, when the SchemaName property is not set. Otherwise, an empty string will be used.

In order to determine the columns from the input that should be loaded into the database, the database first needs to be queried for the columns on the table using the JDBC driver's getColumns(...) method. If the schema provided to this query is null, then this means that the schema name will be ignored when searching for the table and columns. If the schema provided to this query is an empty string, then this tells the driver to look for a table without a schema name.

The default value is False.

UnicodeBinding

Optionally specify how Unicode strings should be passed to the JDBC driver. Choose from:

• CHARACTER - Bind the string using the mechanism for CHAR and VARCHAR types.
• NATIONAL CHARACTER - Bind the string using the mechanism for NCHAR and NVARCHAR types.

DefaultDate

Optionally specify the date to use when a date is required and none is specified. The most common case where this value is used is when coercing a value from a time type into a datetime type. In this case, the date portion of the datetime is taken from this property. Choose from:

• epoch - 1970-01-01
• today

Alternatively, the user can enter a date in the format YYYY-MM-DD.

BigToString

Optionally specify whether java.lang.BigInteger & java.lang.BigDecimal fields should be output as strings. The internal BRD formats numeric types (double, long) cannot handle numeric values greater than 8 bytes long. If these types are not converted to string, then they must be output as byte array data.

The default value is True.

StringToUnicode

Optionally specify whether or not all java strings should be output as Unicode.

The default value is False.

ByteArrayToBase64

Optionally specify whether or not byte arrays can be output as Base64 encoded strings. When this is set to false, byte arrays cannot be output by this node.

The default value is False.

ExtraFieldBehavior

Optionally specify how this node behaves if the input data has a field that is not present in the database table. Choose from:

• log - log the extra field and continue processing
• error - raise an error
• ignore - ignore the extraneous field and continue processing without logging anything

The default value is log.

MissingColumnBehavior

Optionally specify how this node behaves if the input is missing a field that exists in the database. Choose from:

• log - log the missing field and continue processing
• error - raise an error
• ignore - ignore the missing field and continue processing without logging anything

The default value is error.

AbortThreshold

Optionally specify the number of errors that are tolerated before this node stops processing and raises an error. In most cases, there is only one error per record. Thus, the AbortThreshold could be thought of as the number of records on the ErroredData pin before this node stops processing the input.

The default value is 0.

JvmMaxHeapSize

Optionally specify the maximum heap size of the JVM instance that runs the node.

If this property is not set, then the value defaults to the value set in the server side property ls.brain.node.java.javaMaxHeapSize.

JvmProperties

Optionally specify properties to set on the JVM instance running the node. Specify one property per line in the following format:

name=value

Example: java.net.useSystemProxies=true

LogConfig

The optional LogConfig property is rarely given a value. By default, the JavaNode creates a parsable (i.e., xml) log in the usual place, namely the directory ({{^logTempDir^}} /user/data flow/run_name/. Its logging threshold is set by the LogLevel property.

Customization of JavaNode logging is achieved by specifying an XML text value for the LogConfig property. You can create multiple logs, each of which filters log events by LogLevel and/or ErrorCode or some other user-defined logic supplied as a Java class. A log can also subscribe to events generated by another log.

Example data flows

A number of sample Data Flows are available from the Samples workspace, found in the Analyze Directory page.

In the Directory under the /Data360 Samples/Node Examples/ folder, you will find "Integrating with Databases", which shows examples of how to use this node.

Inputs and outputs

Inputs: in1.

Outputs: ErroredData, 1 optional.