docs/index.asciidoc in logstash-input-jdbc-4.3.14 vs docs/index.asciidoc in logstash-input-jdbc-4.3.16
- old
+ new
@@ -138,20 +138,48 @@
# ... other configuration bits
}
}
---------------------------------------------------------------------------------------------------
+==== Prepared Statements
+Using server side prepared statements can speed up execution times as the server optimises the query plan and execution.
+
+NOTE: Not all JDBC accessible technologies will support prepared statements.
+
+With the introduction of Prepared Statement support comes a different code execution path and some new settings. Most of the existing settings are still useful but there are several new settings for Prepared Statements to read up on.
+Use the boolean setting `use_prepared_statements` to enable this execution mode. Use the `prepared_statement_name` setting to specify a name for the Prepared Statement, this identifies the prepared statement locally and remotely and it should be unique in your config and on the database. Use the `prepared_statement_bind_values` array setting to specify the bind values, use the exact string `:sql_last_value` (multiple times if necessary) for the predefined parameter mentioned before. The `statement` (or `statement_path`) setting still holds the SQL statement but to use bind variables you must use the `?` character as a placeholder in the exact order found in the `prepared_statement_bind_values` array.
+
+NOTE: Building count queries around a prepared statement is not supported at this time and because jdbc paging uses count queries under the hood, jdbc paging is not supported with prepared statements at this time either. Therefore, `jdbc_paging_enabled`, `jdbc_page_size` settings are ignored when using prepared statements.
+
+Example:
+[source,ruby]
+---------------------------------------------------------------------------------------------------
+input {
+ jdbc {
+ statement => "SELECT * FROM mgd.seq_sequence WHERE _sequence_key > ? AND _sequence_key < ? + ? ORDER BY _sequence_key ASC"
+ prepared_statement_bind_values => [":sql_last_value", ":sql_last_value", 4]
+ prepared_statement_name => "foobar"
+ use_prepared_statements => true
+ use_column_value => true
+ tracking_column_type => "numeric"
+ tracking_column => "_sequence_key"
+ last_run_metadata_path => "/elastic/tmp/testing/confs/test-jdbc-int-sql_last_value.yml"
+ # ... other configuration bits
+ }
+}
+---------------------------------------------------------------------------------------------------
+
+
[id="plugins-{type}s-{plugin}-options"]
==== Jdbc Input Configuration Options
This plugin supports the following configuration options plus the <<plugins-{type}s-{plugin}-common-options>> described later.
[cols="<,<,<",options="header",]
|=======================================================================
|Setting |Input type|Required
-| <<plugins-{type}s-{plugin}-plugin_timezone>> |<<string,string>>, one of `["local", "utc"]`|No
| <<plugins-{type}s-{plugin}-clean_run>> |<<boolean,boolean>>|No
| <<plugins-{type}s-{plugin}-columns_charset>> |<<hash,hash>>|No
| <<plugins-{type}s-{plugin}-connection_retry_attempts>> |<<number,number>>|No
| <<plugins-{type}s-{plugin}-connection_retry_attempts_wait_time>> |<<number,number>>|No
| <<plugins-{type}s-{plugin}-jdbc_connection_string>> |<<string,string>>|Yes
@@ -168,19 +196,23 @@
| <<plugins-{type}s-{plugin}-jdbc_validate_connection>> |<<boolean,boolean>>|No
| <<plugins-{type}s-{plugin}-jdbc_validation_timeout>> |<<number,number>>|No
| <<plugins-{type}s-{plugin}-last_run_metadata_path>> |<<string,string>>|No
| <<plugins-{type}s-{plugin}-lowercase_column_names>> |<<boolean,boolean>>|No
| <<plugins-{type}s-{plugin}-parameters>> |<<hash,hash>>|No
+| <<plugins-{type}s-{plugin}-plugin_timezone>> |<<string,string>>, one of `["local", "utc"]`|No
+| <<plugins-{type}s-{plugin}-prepared_statement_bind_values>> |<<array,array>>|No
+| <<plugins-{type}s-{plugin}-prepared_statement_name>> |<<string,string>>|No
| <<plugins-{type}s-{plugin}-record_last_run>> |<<boolean,boolean>>|No
| <<plugins-{type}s-{plugin}-schedule>> |<<string,string>>|No
| <<plugins-{type}s-{plugin}-sequel_opts>> |<<hash,hash>>|No
| <<plugins-{type}s-{plugin}-sql_log_level>> |<<string,string>>, one of `["fatal", "error", "warn", "info", "debug"]`|No
| <<plugins-{type}s-{plugin}-statement>> |<<string,string>>|No
| <<plugins-{type}s-{plugin}-statement_filepath>> |a valid filesystem path|No
| <<plugins-{type}s-{plugin}-tracking_column>> |<<string,string>>|No
| <<plugins-{type}s-{plugin}-tracking_column_type>> |<<string,string>>, one of `["numeric", "timestamp"]`|No
| <<plugins-{type}s-{plugin}-use_column_value>> |<<boolean,boolean>>|No
+| <<plugins-{type}s-{plugin}-use_prepared_statements>> |<<boolean,boolean>>|No
|=======================================================================
Also see <<plugins-{type}s-{plugin}-common-options>> for a list of options supported by all
input plugins.
@@ -282,11 +314,11 @@
* This is a required setting.
* Value type is <<string,string>>
* There is no default value for this setting.
-JDBC driver class to load, for exmaple, "org.apache.derby.jdbc.ClientDriver"
+JDBC driver class to load, for example, "org.apache.derby.jdbc.ClientDriver"
NB per https://github.com/logstash-plugins/logstash-input-jdbc/issues/43 if you are using
the Oracle JDBC driver (ojdbc6.jar) the correct `jdbc_driver_class` is `"Java::oracle.jdbc.driver.OracleDriver"`
[id="plugins-{type}s-{plugin}-jdbc_driver_library"]
===== `jdbc_driver_library`
@@ -407,10 +439,26 @@
* Value type is <<hash,hash>>
* Default value is `{}`
Hash of query parameter, for example `{ "target_id" => "321" }`
+[id="plugins-{type}s-{plugin}-prepared_statement_bind_values"]
+===== `prepared_statement_bind_values`
+
+ * Value type is <<array,array>>
+ * Default value is `[]`
+
+Array of bind values for the prepared statement. `:sql_last_value` is a reserved predefined string
+
+[id="plugins-{type}s-{plugin}-prepared_statement_name"]
+===== `prepared_statement_name`
+
+ * Value type is <<string,string>>
+ * Default value is `""`
+
+Name given to the prepared statement. It must be unique in your config and in the database
+
[id="plugins-{type}s-{plugin}-record_last_run"]
===== `record_last_run`
* Value type is <<boolean,boolean>>
* Default value is `true`
@@ -504,9 +552,16 @@
When set to `true`, uses the defined
<<plugins-{type}s-{plugin}-tracking_column>> value as the `:sql_last_value`. When set
to `false`, `:sql_last_value` reflects the last time the query was executed.
+[id="plugins-{type}s-{plugin}-use_prepared_statements"]
+===== `use_prepared_statements`
+
+ * Value type is <<boolean,boolean>>
+ * Default value is `false`
+
+When set to `true`, enables prepare statement usage
[id="plugins-{type}s-{plugin}-common-options"]
include::{include_path}/{type}.asciidoc[]
:default_codec!: