Executes SQL code inside a database, using the JDBC interface. The SQL can be entered directly into the SqlQuery property or can be provided dynamically from an input field. The query or queries can also be optionally parameterized with data from the input set.
This node runs the SQL query (or queries) that you specify in the SqlQuery property. The specified SQL query returns nothing, for example a DML statement such as INSERT, UPDATE, or DELETE, or a DDL statement.
If there is no input, it will run the query once. If there is an input, it will run any directly entered SQL in the SqlQuery property once per input record, or if the queries are provided from an input field it will run the query in each of the input records.
For information on how to establish a connection between this node and a database, see the JDBC Query node help and Acquiring data from a database.
JDBC_RESULT_QUERY_FORMAT option in the DbUrl or DbOptions property. Note that this is a breaking change if you have configured this node in Data360 Analyze prior to the 3.16.0 release. If you have existing nodes configured to connect to Snowflake using our shipped driver, where the DbUrl property has been set explicitly or if you have not set the
DbType property, you must add the JDBC_RESULT_QUERY_FORMAT option to prevent the node from failing. Properties
SqlQuery
Specify an INSERT, UPDATE, or DELETE statement, or a SQL statement that returns nothing (such as a DDL statement). The SQL statement may be parameterized by using the JDBC symbol ? instead of hard coding a value (e.g. update test_table set col1 = ?, col2 = ?). See JDBC's PreparedStatement class for more info.
Choose the (from Field) variant of this property to specify the name of an input field containing the SQL to process.
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.
CommitFrequency
Optionally specify how many rows should be processed before the data is committed.
By default commits after each row.
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.
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.
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 no date is specified. For example, in time to datetime conversion.
Values are "epoch" (1970-01-01), "today", or a date of the format YYYY-MM-DD.
BigToString
Optionally specify whether java.lang.BigInteger and java.lang.BigDecimal fields should be converted to Strings and output as such. 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 in the output records
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 in on the JVM instance running the node.
The format is name=value and one property per line.
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.
See also the topic Logging and handling Java errors.
Example data flows
A number of sample Data Flows are available from the Samples workspace, found in the Data360 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: 1 optional.