JDBC Query - Data360_Analyze - Latest

Simple database connection

1. If you are connecting to a database for which the driver is installed with Data360 Analyze, in the Properties panel, expand the Connection property group, then select the database type from the list in the DbType property.
2. Specify the name of the database that you are connecting to in the DbName property.
   Tip: If you're connecting to MS Access, enter the file path to your MSAccess database. If you're connecting to an Oracle database, enter the system identifier (SID).
3. Specify the database hostname in the DbHost property.
4. If required as part of your database connection, specify a username and password in the DbUser and DbPassword properties.

Advanced database connection

1. If you are connecting to a database for which the driver is not pre-installed with Data360 Analyze, the first step is to download the JDBC driver (JAR file) from the database vendor and install it separately. We recommend that you install third party driver files in the following location, ensuring that drivers for different databases are stored in separate sub-directories:

   <Data360Analyze site configuration directory>/site-<port>/lib/java/db/<driverName>

   For example: <site directory>/lib/java/msaccess/ucanaccess-4.0.4.jar

   Tip: It is not recommended to install third party JAR files in any location within the Data360 Analyze installation directory.
2. In the DbDriverClasspath property, point to the location of the third party JDBC driver that you downloaded in step 1. If there is just one driver file that you want to reference, you can specify a direct path to the file, or if there are multiple driver files, you can specify the directory where they are located. You can also specify multiple directories, separating the entries with a semi-colon.

   If you have installed your driver into the recommended <Data360Analyze site configuration directory>/site-<port>/lib/java/<driverName> location, it is recommended to reference the site-<port> path using the substitution syntax: %ls.appDataDir%/lib/java/<driverName> For example: %ls.appDataDir%\lib\java\msaccess\ucanaccess-4.0.4.jar

3. In the DbUrl property, enter a JDBC connection URL to supply the connection details to the database that you are accessing. In general, JDBC connection URLs begin with jdbc:, then the subprotocol and subname vary depending on the driver. The general format for specifying the database connection URL is as follows: jdbc:<subprotocol>:<subname>
   Note: When a value is entered into the DbUrl property, this supersedes any corresponding values in the Connection property group.

   For examples of how to configure this property, see Acquiring data from a database.

4. Specify a JDBC driver class name in the DbDriver property. This property is optional because in some cases the driver class name can be inferred from the DbUrl, however, you should specify a value in this property if you are using a JDBC3 driver, or if the JAR file includes multiple variants of the driver.

   For examples of how to configure this property, see Acquiring data from a database.

   Tip: If you are having trouble connecting to a database, see Troubleshooting database connection errors.

If there is no input, the node will run the query once. If there is an input, the node will run the query once per input line.

Properties

SqlQuery

Specify the query to extract the data. This can be a SELECT statement or any SQL statement that returns rows.

Choose the (from Field) variant of this property to specify the name of an input field containing the SQL to process.

The SQL statement may be parameterized by using the JDBC symbol ? instead of hard coding a value (for example, select * from test_table where col1 = ?). See JDBC's PreparedStatement class for more information.

A value is required for this property.

SqlQueryFieldBindings

Optionally specify a comma separated list of input fields to bind to SQL query properties. The number of input fields must match the number of properties in the SQLQuery.

DbUser

Optionally specify the database username to connect to the database.

DbPassword

Optionally specify the password used to connect to the database.

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 or key=value entry per line.

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.

ConcatenationMode

Optionally specify how to merge the result data if processing multiple different queries. Choose from:

• Exact - The fields for all result sets must be the same.
• Union - Outputs all of the fields across the queries, filling in nulls for fields not present in a given result set.
• Intersection - Outputs all the fields common to the result sets.

The default value is Exact.

TypeConversion

Optionally specify how to handle fields that have the same name, but different types when processing multiple different result sets. Choose from:

• none - No type conversion, all fields must match exactly.
• numeric - Numerical conversions will occur, int -> longint -> double precision float.
• unrestricted - Numerical conversions will occur; additionally, incompatible types will be converted to string, if possible.

The default value is none.

FetchSize

Optionally specify the number of rows that should be fetched from the database when more rows are needed for a result set.

This value may not be respected by the JDBC driver. Increasing the FetchSize may improve performance but may also increase the load on the node and the database.

If specified, the value must be an integer. Setting this property to 0 or a negative value will result in the default FetchSize settings of the JDBC driver being used.

The default value is 1000.

DefaultToUnicode

Optionally specify whether string columns should default to Unicode type.

If set to False, string columns default to string type.

The default value is True.

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.

SqlDateType

Optionally select a datetype in which to map a SQL DATE. Choose from:

• date
• datetime

DefaultDate

Optionally select the date type to use when no date is specified, for example, in time to datetime conversion. Choose from:

• epoch (1970-01-01)
• today
• Or enter a date of the format YYYY-MM-DD

NullAsZero

Deprecated. Optionally specify whether to return null values in numeric columns as 0 instead of null.

This property is deprecated. In the next feature release, this property will go away, and the default behavior will be to always return null for null values in numeric columns.

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.

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

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: 1 optional.

Outputs: Query Results.