Skip to main content
Daniel_AB
Daniel_AB
Silver ⭐️⭐️
March 28, 2023
Question

QueryDatabaseTable max-value-columns

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.

10 replies

VidyaSargur
Community Manager
VidyaSargur
Community Manager
March 28, 2023

@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.

Translate
Regards, Vidya Sargur, Community Manager Was your question answered? Make sure to mark the answer as the accepted solution. If you find a reply useful, say thanks by clicking on the thumbs up button.
cotopaul
cotopaul
Platinum ⭐️
March 28, 2023

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?

Translate
Daniel_AB
Daniel_ABAuthor
Silver ⭐️⭐️
March 28, 2023

Captura de tela de 2023-03-28 11-40-47.pngCaptura de tela de 2023-03-28 11-41-04.png"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.: {}

Translate
cotopaul
cotopaul
Platinum ⭐️
March 28, 2023

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.

Translate
cotopaul
cotopaul
Platinum ⭐️
March 28, 2023

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.

Translate
Terms & ConditionsAccessibility statement