QueryDatabaseTable max-value-columns

[#1]

Guys, have any of you ever had this problem when using the QueryDatabaseTable?

I have a datetime field in sql server but when it passes through the QueryDatabaseTable it becomes a varchar and ends up generating an error in Nifi.

[#2]

@Daniel_AB, Welcome to our community! To help you get the best possible answer, I have tagged our Nifi experts @cotopaul @SAMSAL @MattWho @steven-matison who may be able to assist you further.

Please feel free to provide any additional information or details about your query, and we hope that you will find a satisfactory solution to your question.

[#3]

Hi @Daniel_AB,

As far as I know, when executing QueryDatabaseTable, the output you have is in AVRO Format. In order to fully use the AVRO capabilities and the data types, I would recommend to set the property "Use Avro Logical Types" to true and test how your data look like afterwards. Do you have it on false by any chance?

[#4]

"Use Avro Logical Types" is True

i'm still having this error.

9:58:06 BRT ERROR
QueryDatabaseTable[id=28149673-0187-1000-ed32-1c3a6ff83253] Unable to execute SQL select query SELECT dt_change FROM CompanyXYZ.dbo.test WHERE dt_change > '2023-03-28 09:55:38.783' due to org.apache.nifi .processor.exception.ProcessException: Error during database query or conversion of records.
- Caused by: org.apache.nifi.processor.exception.ProcessException: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Completed Status 0). Converting a varchar data type to a datetime data type resulted in an out-of-range value.
- Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Completed Status 0). Converting a varchar data type to a datetime data type resulted in a value out of range.: {}
09:57:06 BRT ERROR
QueryDatabaseTable[id=28149673-0187-1000-ed32-1c3a6ff83253] Unable to execute SQL select query SELECT dt_change FROM CompanyXYZ.dbo.test WHERE dt_change > '2023-03-28 09:55:38.783' due to org.apache.nifi .processor.exception.ProcessException: Error during database query or conversion of records.
- Caused by: org.apache.nifi.processor.exception.ProcessException: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Completed Status 0). Converting a varchar data type to a datetime data type resulted in an out-of-range value.
- Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Completed Status 0). Converting a varchar data type to a datetime data type resulted in a value out of range.: {}

[#5]

As I can see, the problem is not strictly related to NiFi, but to the query you are executing. If you try and execute this query directly on your Database, does it work? You first need to identify where the issue occurs, when you query the database or when you make the conversion.

If the problem occurs when fetching the data, I think you will need to use the convert function from sql to achieve your goal. Something like: CONVERT(VARCHAR,your_date_Value,103), where your_date_value is your date and 103 is the date format, which can be found here: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/
This is just an example of that function, you will need to apply it based on your use case.
You could also try fetching another column instead of dt_change to further debug your flow.

[#6]

In sql server the query runs normally without convert.

[#7]

using convert this occurred in the query.

[#8]

I see that you are executing a different select in your database than the one executed in NiFi. Try with that exact SQL statement and see if it works. In what you are executing now, I see no WHERE condition set on that column date. In NiFi you are executing: "SELECT dt_change FROM CompanyXYZ.dbo.test WHERE dt_change > '2023-03-28 09:55:38.783' "

As for the convert function, you receive no error message, it works just fine. But as stated above, you need to select the correct format for your use case. All the formats are present in that link.

[#9]

when running this way i got the following error.

SQL Error [242] [S0003]: An error occurred during the current command (Completed Status 0).

org.jkiss.dbeaver.model.exec.DBCException: SQL Error [242] [S0003]: Ocorreu um erro durante o comando atual (Status concluído 0).
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCResultSetImpl.nextRow(JDBCResultSetImpl.java:183)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.fetchQueryData(SQLQueryJob.java:827)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:632)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:924)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3842)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5073)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Ocorreu um erro durante o comando atual (Status concluído 0).
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer$FetchBufferTokenHandler.onDone(SQLServerResultSet.java:5403)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:99)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:5501)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1801)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1059)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCResultSetImpl.next(JDBCResultSetImpl.java:272)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCResultSetImpl.nextRow(JDBCResultSetImpl.java:180)
... 13 more

[#10]

Well, there you have it 🙂 Your problem is not related directly to NiFi and it is caused by the executed SQL statement. I am not very experienced with SQL Servers but you could try selecting all the fields from the table and see if you still encounter an error message. If the error still persists, you could use the convert function on the where clause --> CONVERT(datetime,your_value,25) or select convert(varchar, your_value, 25)