Hello,
I am working on getting a community version of DB2 working in the latest version of Metabase. Currently running into an issue with using dates/timestamp as parameters in a query. I currently get this error back in the UI:
[jcc][1091][10417][4.27.25] Invalid data conversion: Parameter instance 2021-08-25T00:00-04:00 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815
The console is showing this stack trace:
2021-08-25 10:11:20,635 ERROR middleware.catch-exceptions :: Error processing query: null {:database_id 4, :started_at #t "2021-08-25T10:11:18.967490100-04:00[America/New_York]", :state "42815", :json_query {:database 4, :native {:template-tags {:date {:id "e8af606e-dcce-3afd-3fa8-bde625a20d8d", :name "date", :display-name "Date", :type "date", :default "2021-08-25", :required true}}, :query "select *\nfrom ltl400tst3.frp001\njoin etllib.cymd_to_date\n on fhpdat = cymd_date \nwhere date = {{date}}"}, :type "native", :parameters [], :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}}, :status :failed, :class com.ibm.db2.jcc.am.SqlSyntaxErrorException, :stacktrace ["com.ibm.db2.jcc.am.b7.a(b7.java:810)" "com.ibm.db2.jcc.am.b7.a(b7.java:66)" "com.ibm.db2.jcc.am.b7.a(b7.java:116)" "com.ibm.db2.jcc.am.bh.a(bh.java:1496)" "com.ibm.db2.jcc.am.bh.a(bh.java:1436)" "com.ibm.db2.jcc.am.k7.a(k7.java:2603)" "com.ibm.db2.jcc.am.k7.setObject(k7.java:2493)" "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:1034)" "--> driver.sql_jdbc.execute$set_object.invokeStatic(execute.clj:219)" "driver.sql_jdbc.execute$set_object.invoke(execute.clj:211)" "driver.sql_jdbc.execute$fn__37261.invokeStatic(execute.clj:243)" "driver.sql_jdbc.execute$fn__37261.invoke(execute.clj:241)" "driver.sql_jdbc.execute$fn__37267.invokeStatic(execute.clj:257)" "driver.sql_jdbc.execute$fn__37267.invoke(execute.clj:255)" "driver.sql_jdbc.execute$set_parameters_BANG_$fn__37269.invoke(execute.clj:267)" "driver.sql_jdbc.execute$set_parameters_BANG_.invokeStatic(execute.clj:263)" "driver.sql_jdbc.execute$set_parameters_BANG_.invoke(execute.clj:259)" "driver.sql_jdbc.execute$fn__37273.invokeStatic(execute.clj:282)" "driver.sql_jdbc.execute$fn__37273.invoke(execute.clj:270)" "driver.sql_jdbc.execute$prepared_statement_STAR_.invokeStatic(execute.clj:312)" "driver.sql_jdbc.execute$prepared_statement_STAR_.invoke(execute.clj:309)" "driver.sql_jdbc.execute$statement_or_prepared_statement.invokeStatic(execute.clj:336)" "driver.sql_jdbc.execute$statement_or_prepared_statement.invoke(execute.clj:333)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:476)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:463)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:472)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:463)" "driver.sql_jdbc$fn__87275.invokeStatic(sql_jdbc.clj:54)" "driver.sql_jdbc$fn__87275.invoke(sql_jdbc.clj:52)" "query_processor.context$executef.invokeStatic(context.clj:59)" "query_processor.context$executef.invoke(context.clj:48)" "query_processor.context.default$default_runf.invokeStatic(default.clj:68)" "query_processor.context.default$default_runf.invoke(default.clj:66)" "query_processor.context$runf.invokeStatic(context.clj:45)" "query_processor.context$runf.invoke(context.clj:39)" "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)" "query_processor.reducible$pivot.invoke(reducible.clj:31)" "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__48772.invoke(mbql_to_native.clj:25)" "query_processor.middleware.check_features$check_features$fn__47886.invoke(check_features.clj:39)" "query_processor.middleware.limit$limit$fn__48758.invoke(limit.clj:37)" "query_processor.middleware.cache$maybe_return_cached_results$fn__47338.invoke(cache.clj:204)" "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__49018.invoke(optimize_temporal_filters.clj:204)" "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__50950.invoke(validate_temporal_bucketing.clj:50)" "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__46457.invoke(auto_parse_filter_values.clj:43)" "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__41867.invoke(wrap_value_literals.clj:161)" "query_processor.middleware.annotate$add_column_info$fn__41764.invoke(annotate.clj:608)" "query_processor.middleware.permissions$check_query_permissions$fn__47758.invoke(permissions.clj:81)" "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__49879.invoke(pre_alias_aggregations.clj:40)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__47959.invoke(cumulative_aggregations.clj:60)" "metabase_enterprise.sandbox.query_processor.middleware.row_level_restrictions$apply_row_level_permissions$fn__51468.invoke(row_level_restrictions.clj:335)" "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__50176.invoke(resolve_joined_fields.clj:102)" "query_processor.middleware.resolve_joins$resolve_joins$fn__50489.invoke(resolve_joins.clj:171)" "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__46033.invoke(add_implicit_joins.clj:190)" "query_processor.middleware.large_int_id$convert_id_to_string$fn__48722.invoke(large_int_id.clj:59)" "query_processor.middleware.format_rows$format_rows$fn__48703.invoke(format_rows.clj:74)" "query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__45327.invoke(add_default_temporal_unit.clj:23)" "query_processor.middleware.desugar$desugar$fn__48025.invoke(desugar.clj:21)" "query_processor.middleware.binning$update_binning_strategy$fn__46844.invoke(binning.clj:229)" "query_processor.middleware.resolve_fields$resolve_fields$fn__47561.invoke(resolve_fields.clj:34)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__45682.invoke(add_dimension_projections.clj:312)" "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__45911.invoke(add_implicit_clauses.clj:147)" "metabase_enterprise.sandbox.query_processor.middleware.row_level_restrictions$apply_row_level_permissions$fn__51468.invoke(row_level_restrictions.clj:335)" "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__50899.invoke(upgrade_field_literals.clj:40)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__46196.invoke(add_source_metadata.clj:123)" "metabase_enterprise.sandbox.query_processor.middleware.column_level_perms_check$maybe_apply_column_level_perms_check$fn__50980.invoke(column_level_perms_check.clj:30)" "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__50051.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)" "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__46404.invoke(auto_bucket_datetimes.clj:147)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__47608.invoke(resolve_source_table.clj:45)" "query_processor.middleware.parameters$substitute_parameters$fn__49861.invoke(parameters.clj:111)" "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__47660.invoke(resolve_referenced.clj:79)" "query_processor.middleware.expand_macros$expand_macros$fn__48409.invoke(expand_macros.clj:184)" "query_processor.middleware.add_timezone_info$add_timezone_info$fn__46205.invoke(add_timezone_info.clj:15)" "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__50852.invoke(splice_params_in_response.clj:32)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50062$fn__50066.invoke(resolve_database_and_driver.clj:31)" "driver$do_with_driver.invokeStatic(driver.clj:60)" "driver$do_with_driver.invoke(driver.clj:56)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50062.invoke(resolve_database_and_driver.clj:25)" "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48649.invoke(fetch_source_query.clj:274)" "query_processor.middleware.store$initialize_store$fn__50861$fn__50862.invoke(store.clj:11)" "query_processor.store$do_with_store.invokeStatic(store.clj:44)" "query_processor.store$do_with_store.invoke(store.clj:38)" "query_processor.middleware.store$initialize_store$fn__50861.invoke(store.clj:10)" "query_processor.middleware.validate$validate_query$fn__50906.invoke(validate.clj:10)" "query_processor.middleware.normalize_query$normalize$fn__48785.invoke(normalize_query.clj:22)" "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__46051.invoke(add_rows_truncated.clj:35)" "metabase_enterprise.audit.query_processor.middleware.handle_audit_queries$handle_internal_queries$fn__32061.invoke(handle_audit_queries.clj:162)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__50837.invoke(results_metadata.clj:147)" "query_processor.middleware.constraints$add_default_userland_constraints$fn__47902.invoke(constraints.clj:42)" "query_processor.middleware.process_userland_query$process_userland_query$fn__49948.invoke(process_userland_query.clj:134)" "query_processor.middleware.catch_exceptions$catch_exceptions$fn__47842.invoke(catch_exceptions.clj:173)" "query_processor.reducible$async_qp$qp_STAR___33932$thunk__33933.invoke(reducible.clj:103)" "query_processor.reducible$async_qp$qp_STAR___33932.invoke(reducible.clj:109)" "query_processor.reducible$sync_qp$qp_STAR___33941$fn__33944.invoke(reducible.clj:135)" "query_processor.reducible$sync_qp$qp_STAR___33941.invoke(reducible.clj:134)" "query_processor$process_userland_query.invokeStatic(query_processor.clj:241)" "query_processor$process_userland_query.doInvoke(query_processor.clj:237)" "query_processor$fn__51514$process_query_and_save_execution_BANG___51523$fn__51526.invoke(query_processor.clj:253)" "query_processor$fn__51514$process_query_and_save_execution_BANG___51523.invoke(query_processor.clj:245)" "query_processor$fn__51558$process_query_and_save_with_max_results_constraints_BANG___51567$fn__51570.invoke(query_processor.clj:265)" "query_processor$fn__51558$process_query_and_save_with_max_results_constraints_BANG___51567.invoke(query_processor.clj:258)" "api.dataset$run_query_async$fn__67441.invoke(dataset.clj:56)" "query_processor.streaming$streaming_response_STAR_$fn__67420$fn__67421.invoke(streaming.clj:72)" "query_processor.streaming$streaming_response_STAR_$fn__67420.invoke(streaming.clj:71)" "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)" "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)" "async.streaming_response$do_f_async$fn__20506.invoke(streaming_response.clj:84)"], :context :ad-hoc, :error "[jcc][1091][10417][4.27.25] Invalid data conversion: Parameter instance 2021-08-25T00:00-04:00 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815", :row_count 0, :running_time 0, :data {:rows [], :cols []}}
The driver I am basing this off of has the following functions implemented. Are any of these the one I need to modify to fix the issue I'm experiencing?
For reference, I'm mostly basing off of this https://github.com/alisonrafael/metabase-db2-driver/blob/master/src/metabase/driver/db2.clj
`;; Filtering with dates causes a -245 error. ;;v0.33.x
;; Explicit cast to timestamp when Date function is called to prevent db2 unknown parameter type.
;; Maybe it could not to be necessary with the use of DB2_DEFERRED_PREPARE_SEMANTICS
(defmethod sql.qp/->honeysql [:db2 Date]
[_ date]
(hx/->timestamp (t/format "yyyy-MM-dd" date))) ;;v0.34.x needs it?
;;(hx/->timestamp (du/format-date "yyyy-MM-dd HH:mm:ss" date))) ;;v0.33.x
(defmethod sql.qp/->honeysql [:db2 Timestamp]
[_ date]
(hx/->timestamp (t/format "yyyy-MM-dd HH:mm:ss" date)))
;; MEGA HACK from sqlite.clj ;;v0.34.x
;; Fix to Unrecognized JDBC type: 2014. ERRORCODE=-4228
(defn- zero-time? [t]
(= (t/local-time t) (t/local-time 0)))
(defmethod sql.qp/->honeysql [:db2 LocalDate]
[_ t]
(hsql/call :date (hx/literal (du/format-sql t))))
(defmethod sql.qp/->honeysql [:db2 LocalDateTime]
[driver t]
(if (zero-time? t)
(sql.qp/->honeysql driver (t/local-date t))
(hsql/call :datetime (hx/literal (du/format-sql t)))))
(defmethod sql.qp/->honeysql [:db2 LocalTime]
[_ t]
(hsql/call :time (hx/literal (du/format-sql t))))
(defmethod sql.qp/->honeysql [:db2 OffsetDateTime]
[driver t]
(if (zero-time? t)
(sql.qp/->honeysql driver (t/local-date t))
(hsql/call :datetime (hx/literal (du/format-sql t)))))
(defmethod sql.qp/->honeysql [:db2 OffsetTime]
[_ t]
(hsql/call :time (hx/literal (du/format-sql t))))
(defmethod sql.qp/->honeysql [:db2 ZonedDateTime]
[driver t]
(if (zero-time? t)
(sql.qp/->honeysql driver (t/local-date t))
(hsql/call :datetime (hx/literal (du/format-sql t)))))
;; (.getObject rs i LocalDate) doesn't seem to work, nor does (.getDate); ;;v0.34.x
;; Merged from vertica.clj e sqlite.clj.
;; Fix to Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461
(defmethod sql-jdbc.execute/read-column [:db2 Types/DATE]
[_ _ ^ResultSet rs _ ^Integer i]
(let [s (.getString rs i)
t (du/parse s)]
t))
(defmethod sql-jdbc.execute/read-column [:db2 Types/TIME]
[_ _ ^ResultSet rs _ ^Integer i]
(let [s (.getString rs i)
t (du/parse s)]
t))
(defmethod sql-jdbc.execute/read-column [:db2 Types/TIMESTAMP]
[_ _ ^ResultSet rs _ ^Integer i]
(let [s (.getString rs i)
t (du/parse s)]
t))`
