Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Newbie NiFi Oracle PutSQL

avatar
Rising Star

I have some XML files that I've successfully parsed, converted to JSON (using AttributesToJSON), and then ConvertJSONToSQL, If I putfile from AttributesToJSON I have the following sample row:

{"INFOID":"1526726757","BEGIN_TIME":"2016-07-12T05:00:00-06:00","MEASVALUE":"LTHAB0113422/ECELL_WCELL:eNodeB Function Name=LTHAB0113422, RNC cell ID=30005, Local cell ID=41, Mobile country code=302, Mobile network code=220, RNC ID=1209","MEASRESULTS":"0 0 0 ","MEASTYPES":"1526729565 1526729566 1526729567 ","NODE_NAME":"LTHAB0113422"}

Yet the SQL looks like:

INSERT INTO RCS_STG.NIFI (MEASRESULTS, BEGIN_TIME, NODE_NAME, INFOID, MEASTYPES, MEASVALUE) VALUES (?, ?, ?, ?, ?, ?)

The Table however only has a rows that is all nulls? What am I missing?

1 ACCEPTED SOLUTION

avatar
Rising Star

Doh, found the problem, I forgot I was in the Java world where things are case sensetive, and Oracle which is not 😞

View solution in original post

5 REPLIES 5

avatar
Master Guru

The SQL generated is a prepared statement (the question marks are placeholders for the values). In the flow file coming out of ConvertJSONtoSQL, you should see attributes on the flow file such as "sql.args.1.type" and "sql.args.1.value", there should be a pair of attributes like that for each of the columns (looks like 6). Are those attributes present and valid?

avatar
Rising Star

Where will I find the "sql.args.1.type" and "sql.args.1.value" information logged?

avatar
Master Guru

If you stop the processor that ConvertJSONtoSQL is connected to, you will see your flow files in the connection queue (between the processors). You can right-click on that and choose ListQueue, then pick any of the files and click on the Info button (looks like a question mark) and choose the Attributes tab. That should show all the flow file attributes including the sql.args pairs. Alternatively you can connect ConvertJSONtoSQL to a LogAttribute processor and check logs/nifi-app.log to see the attributes being printed out.

avatar
Rising Star

Ok, I learned something new there is a LogAttribute process, and this is what is in the logs now, problem is nothing is being inserted into the table now?

Standard FlowFile Attributes
Key: 'entryDate'
	Value: 'Thu Jul 21 13:40:06 MDT 2016'
Key: 'lineageStartDate'
	Value: 'Thu Jul 21 13:40:05 MDT 2016'
Key: 'fileSize'
	Value: '117'
FlowFile Attribute Map Content
Key: 'RouteOnAttribute.Route'
	Value: 'Filter_Attributes'
Key: 'absolute.path'
	Value: 'C:\HDF-1.2.0.1-1\nifi\.\data-in/'
Key: 'begin_time'
	Value: '2016-07-12T05:00:00-06:00'
Key: 'file.creationTime'
	Value: '2016-07-21T13:40:05-0600'
Key: 'file.lastAccessTime'
	Value: '2016-07-21T13:40:05-0600'
Key: 'file.lastModifiedTime'
	Value: '2016-07-18T14:20:14-0600'
Key: 'file.owner'
	Value: 'BUILTIN\Administrators'
Key: 'filename'
	Value: 'A20160712.0500-0600-0530-0600_LTHAB0113422.xml'
Key: 'fragment.count'
	Value: '1'
Key: 'fragment.identifier'
	Value: '50540eda-0902-4b5a-bf7a-2b45d956fe3a'
Key: 'fragment.index'
	Value: '0'
Key: 'infoid'
	Value: '1526726706'
Key: 'measResults'
	Value: '0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NIL NIL NIL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 '
Key: 'measTypes'
	Value: '1526726986 1526726987 1526726988 1526726989 1526726990 1526726991 1526726992 1526726993 1526726994 1526727226 1526727227 1526727228 1526728306 1526728307 1526728308 1526728309 1526728310 1526728311 1526728312 1526728313 1526728314 1526728315 1526728316 1526728317 1526728318 1526728321 1526728322 1526728323 1526728324 1526728326 1526728327 1526728328 1526728329 1526728330 1526728331 1526728386 1526728387 1526728388 1526728400 1526728401 1526728402 1526728403 1526728404 1526728405 1526728406 1526728407 1526728408 1526728409 1526728410 1526728441 1526728442 1526728468 1526728469 1526728470 1526728471 1526728472 1526728497 1526728498 1526728499 1526728500 1526728501 1526728502 1526728503 1526728504 1526728505 1526728506 1526728507 1526728508 1526728509 1526728510 1526728511 1526728512 1526728513 1526728529 1526728530 1526728531 1526728532 1526728533 1526728534 1526728535 1526728536 1526728542 1526728543 1526728544 1526728560 1526728561 1526728705 1526728706 1526728707 1526728708 1526728709 1526728710 1526728746 1526728747 1526728748 1526728749 1526728750 1526728751 1526728752 1526728753 1526728754 1526728755 1526728756 1526728757 1526728758 1526728759 1526728760 1526728761 1526728889 1526728890 1526728891 1526728892 1526728893 1526728894 1526728895 1526728896 1526728897 1526728898 1526728899 1526728900 1526728901 1526729054 1526729055 1526729260 1526729422 1526729423 1526729424 1526729425 1526729426 1526729427 1526729428 1526729429 1526729432 1526729433 1526729434 1526729485 1526729486 1526729487 1526729488 1526729489 1526729490 1526729491 1526729492 1526729505 1526729506 1526729507 1526729508 1526729509 1526729510 1526729511 1526729512 1526729513 1526729514 1526729515 1526729516 1526729544 1526729573 1526730017 1526730018 1526730019 1526730020 1526730021 1526730022 1526730023 1526730024 1526730025 1526730026 1526730027 1526730028 1526730029 1526730030 1526730031 1526730032 1526730033 1526730034 1526730035 1526730036 1526730037 1526730038 1526730039 1526730040 1526730041 1526730076 1526730077 1526730078 1526730079 1526730080 1526730081 1526730082 1526730083 1526730084 1526730085 1526730086 1526730087 1526730088 1526730089 1526730090 1526730091 1526730092 1526730093 1526730094 1526730095 1526730096 1526730097 1526730098 1526730099 1526730146 1526730147 1526730148 1526730848 1526730849 1526733006 1526733007 1526733008 1526733009 1526733190 1526733191 1526733192 '
Key: 'measValue'
	Value: 'LTHAB0113422/Cell:eNodeB Function Name=LTHAB0113422, Local Cell ID=110, Cell Name=LTHAB0113422-110-2600-1-1, eNodeB ID=113422, Cell FDD TDD indication=CELL_FDD'
Key: 'mime.type'
	Value: 'text/plain'
Key: 'node_name'
	Value: 'LTHAB0113422'
Key: 'path'
	Value: '/'
Key: 'sql.args.1.type'
	Value: '12'
Key: 'sql.args.1.value'
	Value: '1526726706'
Key: 'sql.args.2.type'
	Value: '12'
Key: 'sql.args.2.value'
	Value: '2016-07-12T05:00:00-06:00'
Key: 'sql.args.3.type'
	Value: '12'
Key: 'sql.args.3.value'
	Value: 'LTHAB0113422/Cell:eNodeB Function Name=LTHAB0113422, Local Cell ID=110, Cell Name=LTHAB0113422-110-2600-1-1, eNodeB ID=113422, Cell FDD TDD indication=CELL_FDD'
Key: 'sql.args.4.type'
	Value: '12'
Key: 'sql.args.4.value'
	Value: '0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NIL NIL NIL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 '
Key: 'sql.args.5.type'
	Value: '12'
Key: 'sql.args.5.value'
	Value: '1526726986 1526726987 1526726988 1526726989 1526726990 1526726991 1526726992 1526726993 1526726994 1526727226 1526727227 1526727228 1526728306 1526728307 1526728308 1526728309 1526728310 1526728311 1526728312 1526728313 1526728314 1526728315 1526728316 1526728317 1526728318 1526728321 1526728322 1526728323 1526728324 1526728326 1526728327 1526728328 1526728329 1526728330 1526728331 1526728386 1526728387 1526728388 1526728400 1526728401 1526728402 1526728403 1526728404 1526728405 1526728406 1526728407 1526728408 1526728409 1526728410 1526728441 1526728442 1526728468 1526728469 1526728470 1526728471 1526728472 1526728497 1526728498 1526728499 1526728500 1526728501 1526728502 1526728503 1526728504 1526728505 1526728506 1526728507 1526728508 1526728509 1526728510 1526728511 1526728512 1526728513 1526728529 1526728530 1526728531 1526728532 1526728533 1526728534 1526728535 1526728536 1526728542 1526728543 1526728544 1526728560 1526728561 1526728705 1526728706 1526728707 1526728708 1526728709 1526728710 1526728746 1526728747 1526728748 1526728749 1526728750 1526728751 1526728752 1526728753 1526728754 1526728755 1526728756 1526728757 1526728758 1526728759 1526728760 1526728761 1526728889 1526728890 1526728891 1526728892 1526728893 1526728894 1526728895 1526728896 1526728897 1526728898 1526728899 1526728900 1526728901 1526729054 1526729055 1526729260 1526729422 1526729423 1526729424 1526729425 1526729426 1526729427 1526729428 1526729429 1526729432 1526729433 1526729434 1526729485 1526729486 1526729487 1526729488 1526729489 1526729490 1526729491 1526729492 1526729505 1526729506 1526729507 1526729508 1526729509 1526729510 1526729511 1526729512 1526729513 1526729514 1526729515 1526729516 1526729544 1526729573 1526730017 1526730018 1526730019 1526730020 1526730021 1526730022 1526730023 1526730024 1526730025 1526730026 1526730027 1526730028 1526730029 1526730030 1526730031 1526730032 1526730033 1526730034 1526730035 1526730036 1526730037 1526730038 1526730039 1526730040 1526730041 1526730076 1526730077 1526730078 1526730079 1526730080 1526730081 1526730082 1526730083 1526730084 1526730085 1526730086 1526730087 1526730088 1526730089 1526730090 1526730091 1526730092 1526730093 1526730094 1526730095 1526730096 1526730097 1526730098 1526730099 1526730146 1526730147 1526730148 1526730848 1526730849 1526733006 1526733007 1526733008 1526733009 1526733190 1526733191 1526733192 '
Key: 'sql.args.6.type'
	Value: '12'
Key: 'sql.args.6.value'
	Value: 'LTHAB0113422'
Key: 'sql.table'
	Value: 'NIFI'
Key: 'uuid'
	Value: 'bc5aa92e-de98-4516-b6a3-674f67e4787b'
--------------------------------------------------
INSERT INTO RCS_STG.NIFI (INFOID, BEGIN_TIME, MEASVALUE, MEASRESULTS, MEASTYPES, NODE_NAME) VALUES (?, ?, ?, ?, ?, ?)

avatar
Rising Star

Doh, found the problem, I forgot I was in the Java world where things are case sensetive, and Oracle which is not 😞