ConvertJSONToSQL Issue: None of the fields in the JSON map to the columns

[#1]

Hello,

I'm trying to load the data from a sql server DB to another sql server DB. Please find the flow below.

ExecuteSQL -> ConvertAvroToJSON -> ReplaceText -> ConvertJSONToSQL -> PutSQL

I'm having the issue in the ConvertJSONToSQL processor. Please find the log below. Can you please help me how to fix it.

ConvertJSONToSQL[id=xxxx] Failed to convert StandardFlowFileRecord[uuid=xxxx,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1556828161853-12488, container=default, section=200], offset=596441, length=1116],offset=0,name=11007398837232637,size=1116] to a SQL INSERT statement due to org.apache.nifi.processor.exception.ProcessException: None of the fields in the JSON map to the columns defined by the dbo.xxxx table; routing to failure: org.apache.nifi.processor.exception.ProcessException: None of the fields in the JSON map to the columns defined by the dbo.SITE_TYP table

ConvertJSONToSQL properties

Sample Json

[{"TYP_ID": 1, "TYP_NM": "JERY"}]

Target DDL

CREATE TABLE dbo.xxxx(

TYP_ID intIDENTITY(1,1),

TYP_NM varchar(256)NOT NULL,

DEL_FLG char(1)DEFAULT 'N' NOT NULL,

CRT_DTM datetimeDEFAULT GETDATE() NOT NULL,

UPDT_USER_ID varchar(11)DEFAULT CURRENT_USER NOT NULL,

UPDT_DTM datetimeDEFAULT GETDATE() NOT NULL,

CONSTRAINT PK_TYP PRIMARY KEY CLUSTERED (TYP_ID)

)

[#2]

What does the generated SQL coming from ConvertJSONToSQL look like?

Are the fields correctly uppercased?

Does your database lowercase the column names?

Did you try setting the "Translate Field Names" property to "true" in ConvertJSONToSQL?

Does the case of the table name in the SQL match the case of the table name in the DB?

If you're using "dbo.xxxx" as the Table Name property in ConvertJSONToSQL, instead try using just "xxxx" as the Table Name, and setting either Catalog Name or Schema Name (depending on your DB) to "dbo" (or DBO if necessary).

[#3]

Hi,

Have a scenario :

any JSON array, has key in double quotes and value in double quotes, i.e. { "jsonElementKey1" : "jsonElementValue1",...}

convertJSONToSQL processor expects the JSON key and value to be in double quotes, else it can't convert to SQL. For the MS SQL inserts, the string column values expects single quote, so if the above JSON example is converted to {"jsonElementKey1" : 'jsonElementValue1" } using replaceText processor prior to convertJSONtoSQL, the convertJSONtoSQL fails stating single quote as invalid character. Can you help to get out of this deadlock scenario?

Thanks,

Sam

[#4]

Hi, I had the same problem using Oracle database (11g), and this was solved by changing the<Table Name> field and<Schema name> field to UPPER CASE in the ConvertJSONToSQL Processor.

[#5]

@mburgess @SamCloud  convert jsontosql is giving SQL but with '?' marks.

example: "DELETE FROM tab1 WHERE id = ? AND feature_name = ? AND state = ? AND tenant_id = ?"

When i print via putfile, the json has valid values.. 

[{"id":1,"feature_name":"fet1","state":16,"tenant_id":"abc123"}]

Want to understand where I am doing it wrong.. Please help