[
https://issues.apache.org/jira/browse/NIFI-8524?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17343905#comment-17343905
]
Roger Sliva commented on NIFI-8524:
-----------------------------------
Some additional information on this - when I converted the target table (SQL
Server) to use NCHAR and NVARCHAR instead of CHAR and VARCHAR, the
PutDatabaseRecord succeeded!
The PutDatabaseRecord uses a default JsonTreeReader set to infer the schema.
The schema was defined at the time the JSON was built as:
{
"type": "record",
"name": "gprmc_pcpti_schema",
"fields" : [
\{"name": "uuid", "type": ["null", "string"]},
\{"name": "current_ts", "type": ["null", "string"]},
\{"name": "GPRMC_Sentence_ID", "type": ["null", "string"]},
\{"name": "GPRMC_Fix_Time", "type": ["null", "string"]},
\{"name": "GPRMC_Data_Status", "type": ["null", "string"]},
\{"name": "GPRMC_Latitude", "type": ["null", "string"]},
\{"name": "GPRMC_Latitude_Dir", "type": ["null", "string"]},
\{"name": "GPRMC_Longitude", "type": ["null", "string"]},
\{"name": "GPRMC_Longitude_Dir", "type": ["null", "string"]},
\{"name": "GPRMC_Ground_Speed", "type": ["null", "string"]},
\{"name": "GPRMC_Track_Made_Good", "type": ["null", "string"]},
\{"name": "GPRMC_UT_Date", "type": ["null", "string"]},
\{"name": "GPRMC_Mag_Var", "type": ["null", "string"]},
\{"name": "GPRMC_Mag_Var_Dir", "type": ["null", "string"]},
\{"name": "GPRMC_Checksum", "type": ["null", "string"]},
\{"name": "PCPTI_Sentence_ID", "type": ["null", "string"]},
\{"name": "PCPTI_Vehicle_ID", "type": ["null", "string"]},
\{"name": "PCPTI_Value1", "type": ["null", "string"]},
\{"name": "PCPTI_Checksum", "type": ["null", "string"]}
]
}
Here is an obfuscated record sample.
[\{"uuid":"5aadfbd4-2fd0-4b67-8fdf-9d40e3a015a8","current_ts":"2021-05-11
13:22:14.016","GPRMC_Sentence_ID":"GPRMC","GPRMC_Fix_Time":"202212.000","GPRMC_Data_Status":"A","GPRMC_Latitude":"xx24.51859","GPRMC_Latitude_Dir":"N","GPRMC_Longitude":"xx151.44189","GPRMC_Longitude_Dir":"W","GPRMC_Ground_Speed":"0.0","GPRMC_Track_Made_Good":"0.0","GPRMC_UT_Date":"110521","GPRMC_Mag_Var":"","GPRMC_Mag_Var_Dir":"","GPRMC_Checksum":"D*7D","PCPTI_Sentence_ID":"PCPTI","PCPTI_Vehicle_ID":"Vxxx","PCPTI_Value1":"202212","PCPTI_Checksum":"202212*2A"}]
Here is the original table schema (before changing CHAR to NCHAR, etc.)
CREATE TABLE Fire.GPS_All_Records(
uuid varchar(36) NULL,
current_ts datetime NULL,
GPGGA_Sentence_ID varchar(10) NULL,
GPGGA_Fix_Time varchar(20) NULL,
GPGGA_Latitude varchar(20) NULL,
GPGGA_Latitude_Dir char(1) NULL,
GPGGA_Longitude varchar(20) NULL,
GPGGA_Longitude_Dir char(1) NULL,
GPGGA_Fix_Quality varchar(20) NULL,
GPGGA_Number_of_Satellites varchar(20) NULL,
GPGGA_Horizontal_Dilution varchar(20) NULL,
GPGGA_MSL_Altitude varchar(20) NULL,
GPGGA_Altitude_Unit char(1) NULL,
GPGGA_MSL_Height varchar(20) NULL,
GPGGA_Height_Unit char(1) NULL,
GPGGA_DGPS_Station_ID varchar(20) NULL,
GPGGA_Checksum varchar(10) NULL,
GPVTG_Sentence_ID varchar(10) NULL,
GPVTG_Track_Made_Good varchar(20) NULL,
GPVTG_Relative_To_North varchar(10) NULL,
GPVTG_Track_Made_Good_Mag varchar(20) NULL,
GPVTG_Relative_To_Mag_North varchar(10) NULL,
GPVTG_Speed varchar(10) NULL,
GPVTG_Speed_Is_Knots char(1) NULL,
GPVTG_Ground_Speed varchar(10) NULL,
GPVTG_Ground_Speed_Is_kph char(1) NULL,
GPVTG_Checksum varchar(20) NULL,
GPRMC_Sentence_ID varchar(10) NULL,
GPRMC_Fix_Time varchar(20) NULL,
GPRMC_Data_Status varchar(10) NULL,
GPRMC_Latitude varchar(20) NULL,
GPRMC_Latitude_Dir char(1) NULL,
GPRMC_Longitude varchar(20) NULL,
GPRMC_Longitude_Dir char(1) NULL,
GPRMC_Ground_Speed varchar(20) NULL,
GPRMC_Track_Made_Good varchar(20) NULL,
GPRMC_UT_Date varchar(20) NULL,
GPRMC_Mag_Var varchar(20) NULL,
GPRMC_Mag_Var_Dir varchar(10) NULL,
GPRMC_Checksum varchar(20) NULL,
PCPTI_Sentence_ID varchar(10) NULL,
PCPTI_Vehicle_ID varchar(20) NOT NULL,
PCPTI_Value1 varchar(20) NULL,
PCPTI_Checksum varchar(20) NULL
) ON PRIMARY
> PutDatabaseRecord fails when inserting or updating a CHAR column
> ----------------------------------------------------------------
>
> Key: NIFI-8524
> URL: https://issues.apache.org/jira/browse/NIFI-8524
> Project: Apache NiFi
> Issue Type: Bug
> Affects Versions: 1.13.2
> Environment: RHEL 7.4 using OpenJDK 11 with mssql-jdbc-9.2.1.jre11.jar
> Reporter: Roger Sliva
> Priority: Major
>
> PutDatabaseRecord fails when inserting or updating into a SQL Server 12+
> table with a CHAR(1) column.
> com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN
> to CHAR is unsupported.
> This works in 1.11.4
> When the CHAR columns are converted to VARCHAR the fail does not occur.
>
> From nifi-app.log:
> 2021-05-05 09:43:13,938 ERROR [Timer-Driven Process Thread-10]
> o.a.n.p.standard.PutDatabaseRecord
> PutDatabaseRecord[id=88544076-0170-1000-abaf-0ff508f882b1] Failed to put
> Records to database for
> StandardFlowFileRecord[uuid=07ecd86b-9568-4f3f-a7e9-03b59a1069be,claim=StandardContentClaim
> [resourceClaim=StandardResourceClaim[id=1620232684227-3, container=default,
> section=3], offset=272241,
> length=539],offset=0,name=6eee14cb-6c23-497e-b01a-84875d76c810,size=539].
> Routing to failure.: com.microsoft.sqlserver.jdbc.SQLServerException: The
> conversion from UNKNOWN to CHAR is unsupported.
> com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN
> to CHAR is unsupported.
> at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
> at
> com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:1112)
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:1588)
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:1507)
> at
> org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529)
> at
> org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529)
> at jdk.internal.reflect.GeneratedMethodAccessor375.invoke(Unknown Source)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> at
> org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)
> at
> org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.access$100(StandardControllerServiceInvocationHandler.java:38)
> at
> org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler$ProxiedReturnObjectInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:240)
> at com.sun.proxy.$Proxy150.setObject(Unknown Source)
> at
> org.apache.nifi.processors.standard.PutDatabaseRecord.executeDML(PutDatabaseRecord.java:736)
> at
> org.apache.nifi.processors.standard.PutDatabaseRecord.putToDatabase(PutDatabaseRecord.java:841)
> at
> org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:487)
> at
> org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
> at
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1173)
> at
> org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:214)
> at
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
> at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
> at
> java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
> at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
> at
> java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
> at
> java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
> at
> java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
> at java.base/java.lang.Thread.run(Thread.java:834)
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
