Member since
10-16-2017
23
Posts
3
Kudos Received
1
Solution
My Accepted Solutions
Title | Views | Posted |
---|---|---|
1666 | 03-16-2018 03:38 AM |
06-22-2020
08:41 PM
Hi, I have the same issue, i am using the (HandleHttpRequest--> {Some Process}-->handlehttpresponse) when we invoking most of the time we getting the same error. Could you let me know how you fixed? Thansk Shakeel
... View more
04-15-2020
05:56 AM
@mburgess Hope you doing well, could you help on my issues. My script running fine but its process keeping oracle connection inactive and not closing, when i am checking in database its showing inactive session which NIFI created by the NIFI user.
... View more
04-13-2020
11:30 PM
Hi All, I am facing issues with Execute Script processor. I am executing Groovy script which have oracle DB in Execute Script processor but its not closing the DB connection. Issues: Database connection are not closing and its making Inactive connection in database. The Groovy script we added proper open and close connections. Could you help fix this issues, also let me know where I am not correct . DB Connection Configuration is like that : Execute Script Process are configured as fallowing: Groovy Script as following : import org.apache.commons.io.IOUtils import org.apache.nifi.controller.ControllerService import org.apache.nifi.processor.io.StreamCallback import java.nio.charset.* import groovy.sql.OutParameter import groovy.sql.Sql import oracle.jdbc.OracleTypes import java.sql.ResultSet def lookup = context.controllerServiceLookup def dbServiceName = ConnectionPoolScriptExecution.value def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find { cs -> lookup.getControllerServiceName(cs) == dbServiceName } def conn = lookup.getControllerService(dbcpServiceId)?.getConnection(); def sql = new Sql(conn); def flowFile = session.get() if(!flowFile) return //Session Values strApplication = flowFile.getAttribute('application') strUsername = flowFile.getAttribute('username') strPassword = flowFile.getAttribute('password') // SQL Query Creation and execution def ValidOrNot String sqlString = "select FN_HP_IS_VALID_APP('${strApplication}','${strUsername}','${strPassword}') as ValidOrNot from dual"; try { rowNum = 0 sql.eachRow(sqlString, { dataResult -> //println(dataResult.ValidOrNot) ValidOrNot = dataResult.ValidOrNot }) } catch(Exception ex){ conn?.close() } finally { conn?.close() } conn?.close() // Assign values to session object def attrMap = ['VALIDORNOT':ValidOrNot,'sqlString':sqlString] flowFile = session.putAllAttributes(flowFile, attrMap) session.transfer(flowFile, REL_SUCCESS)
... View more
Labels:
03-22-2018
07:55 AM
Hi All, I have a oracle stored proc and that proc using the Arry type input parameter create or replace TYPE "ARR_HP_STU_CERT_USERNAME" as table of varchar2(32767); PROC Input type create or replace PROCEDURE sp_hp_stu_cert_status_chk (
in_track_id IN NUMBER,
in_username IN arr_hp_stu_cert_username,
in_certification_id IN VARCHAR2,
out_details OUT SYS_REFCURSOR,
out_status_code OUT NUMBER,
out_status_desc OUT VARCHAR2)
AS
v_prc_name VARCHAR2 (50) := 'sp_hp_stu_cert_status_chk';
v_count NUMBER;
v_user_id VARCHAR2 (500);
required_fields EXCEPTION;
BEGIN
I have written a groovy code to execute that proc but its giving error about casting : /*import org.apache.commons.io.IOUtils
import org.apache.nifi.controller.ControllerService
import org.apache.nifi.processor.io.StreamCallback */
import java.nio.charset.*
import groovy.sql.OutParameter
import groovy.sql.InParameter
import groovy.sql.Sql
import oracle.jdbc.OracleTypes
import java.sql.ResultSet
//import java.sql.*
def sql = Sql.newInstance('jdbc:oracle:thin:@//XXXXX.itcs.entsvcs.net:1525/XXXX', 'XXXX', 'XXXXX$', 'oracle.jdbc.OracleDriver')
in_track_id='1798'
in_certification_id='crtfy000000000003144'
OutParameter CURSOR_PARAMETER = new OutParameter() {
public int getType() {
return OracleTypes.CURSOR;
}
};
InParameter ArrayUserName = new InParameter() {
public int getType() {
return OracleTypes.ARRAY;
}
public Object getValue(){
}
};
def data = []
String sqlString ="""{call sp_hp_stu_cert_status_chk(?, ?, ?, ?, ?, ?)}""";
def status_desc
def status_code
def status_data
def testID
def parametersList = [in_track_id, ArrayUserName, in_certification_id, CURSOR_PARAMETER, Sql.NUMERIC ,Sql.VARCHAR];
sql.call(sqlString, parametersList) {out_details, out_status_code,out_status_desc ->
status_desc = out_status_desc
status_code = out_status_code
};
Print(status_desc)
Print(status_code)
Can anyone help how I can pass "ARR_HP_STU_CERT_USERNAME" as ARRY input in our stored procedure
... View more
Labels:
03-22-2018
07:05 AM
1 Kudo
Solved using the Groovy Code. Stored PROC using the following input and output. in_track_id IN NUMBER, in_username IN VARCHAR2, in_course_no IN VARCHAR2, out_details OUT SYS_REFCURSOR, out_status_code OUT NUMBER, out_status_desc OUT VARCHAR2 import org.apache.commons.io.IOUtils
import org.apache.nifi.controller.ControllerService
import org.apache.nifi.processor.io.StreamCallback
import java.nio.charset.*
import groovy.sql.OutParameter
import groovy.sql.Sql
import oracle.jdbc.OracleTypes
import java.sql.ResultSet
//DB Setting Start
//Harcoded connection string
//def sql = Sql.newInstance('jdbc:oracle:thin:@//ecuxxx.xxx.xxx.net:1526/dbSchema', 'UserID', 'UserPass$', 'oracle.jdbc.OracleDriver')
//Get the conncation string from NIFI conncation pool
def lookup = context.controllerServiceLookup
def dbServiceName = ConncationPool.value
def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find {
cs -> lookup.getControllerServiceName(cs) == dbServiceName
}
def conn = lookup.getControllerService(dbcpServiceId).getConnection();
sql = Sql.newInstance(conn);
////Get the session values from Nifi flow Start
def flowFile = session.get()
if(!flowFile) return
in_track_id = flowFile.getAttribute('body_track_id')
in_username = flowFile.getAttribute('body_username')
in_course_no = flowFile.getAttribute('body_course_number')
////Get the session values from Nifi flow END
// special OutParameter for cursor type
OutParameter CURSOR_PARAMETER = new OutParameter() {
public int getType() {
return OracleTypes.CURSOR;
}
};
def data = []
// Stored proc having 3 input and 3 out put, one out put having CURSOR datatype
String sqlString ="""{call <StoredPrcoName>(?, ?, ?, ?, ?, ?)}""";
// rs contains the result set of cursor and data values start Shakeel
def status_desc
def status_code
def status_data
//def parametersList = ['20585', 'A3843UP', '00009934', CURSOR_PARAMETER, Sql.NUMERIC ,Sql.VARCHAR]; // testing purpose
def parametersList = [in_track_id, in_username, in_course_no, CURSOR_PARAMETER, Sql.NUMERIC ,Sql.VARCHAR];
sql.call(sqlString, parametersList) {out_details, out_status_code,out_status_desc ->
status_desc = out_status_desc
status_code = out_status_code
out_details.eachRow {
data << it.toRowResult()
status_data = data
}
};
// rs contains the result set of cursor and data values end
//Set the session values start
def attrMap = ['status_desc':status_desc, 'status_code':String.valueOf(status_code),'status_data':String.valueOf(status_data),'Conn':String.valueOf(conn)]
flowFile = session.putAllAttributes(flowFile, attrMap)
session.transfer(flowFile, REL_SUCCESS)
... View more
03-16-2018
03:38 AM
1 Kudo
Solved using the Groovy Code. Hope it will help someone.. Stored PROC using the following input and output in_track_id IN NUMBER, in_username IN VARCHAR2,
in_course_no IN VARCHAR2,
out_details OUT SYS_REFCURSOR,
out_status_code OUT NUMBER,
out_status_desc OUT VARCHAR2) import org.apache.commons.io.IOUtils
import org.apache.nifi.controller.ControllerService
import org.apache.nifi.processor.io.StreamCallback
import java.nio.charset.*
import groovy.sql.OutParameter
import groovy.sql.Sql
import oracle.jdbc.OracleTypes
import java.sql.ResultSet
//DB Setting Start
//Harcoded connection string
//def sql = Sql.newInstance('jdbc:oracle:thin:@//ecuxxx.xxx.xxx.net:1526/dbSchema', 'UserID', 'UserPass$', 'oracle.jdbc.OracleDriver')
//Get the conncation string from NIFI conncation pool
def lookup = context.controllerServiceLookup
def dbServiceName = ConncationPool.value
def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find {
cs -> lookup.getControllerServiceName(cs) == dbServiceName
}
def conn = lookup.getControllerService(dbcpServiceId).getConnection();
sql = Sql.newInstance(conn);
////Get the session values from Nifi flow Start
def flowFile = session.get()
if(!flowFile) return
in_track_id = flowFile.getAttribute('body_track_id')
in_username = flowFile.getAttribute('body_username')
in_course_no = flowFile.getAttribute('body_course_number')
////Get the session values from Nifi flow END
// special OutParameter for cursor type
OutParameter CURSOR_PARAMETER = new OutParameter() {
public int getType() {
return OracleTypes.CURSOR;
}
};
def data = []
// Stored proc having 3 input and 3 out put, one out put having CURSOR datatype
String sqlString ="""{call <StoredPrcoName>(?, ?, ?, ?, ?, ?)}""";
// rs contains the result set of cursor and data values start Shakeel
def status_desc
def status_code
def status_data
//def parametersList = ['20585', 'A3843UP', '00009934', CURSOR_PARAMETER, Sql.NUMERIC ,Sql.VARCHAR]; // testing purpose
def parametersList = [in_track_id, in_username, in_course_no, CURSOR_PARAMETER, Sql.NUMERIC ,Sql.VARCHAR];
sql.call(sqlString, parametersList) {out_details, out_status_code,out_status_desc ->
status_desc = out_status_desc
status_code = out_status_code
out_details.eachRow {
data << it.toRowResult()
status_data = data
}
};
// rs contains the result set of cursor and data values end
//Set the session values start
def attrMap = ['status_desc':status_desc, 'status_code':String.valueOf(status_code),'status_data':String.valueOf(status_data),'Conn':String.valueOf(conn)]
flowFile = session.putAllAttributes(flowFile, attrMap)
session.transfer(flowFile, REL_SUCCESS)
... View more
03-16-2018
03:29 AM
Hi @Ward Bekker I am trying the same code but getting error : Code is below one , could you let me know what is wrong, I have added dependency as well. import net.sf.json.JSON import net.sf.json.JSONObject import net.sf.json.JSONSerializer import net.sf.json.xml.XMLSerializer String str = '''{ "glossary": { "title": "example glossary", "GlossDiv": { "title": "S", "GlossList": { "GlossEntry": { "ID": "SGML", "SortAs": "SGML", "GlossTerm": "Standard Generalized Markup Language", "Acronym": "SGML", "Abbrev": "ISO 8879:1986", "GlossDef": { "para": "A meta-markup language, used to create markup languages such as DocBook.", "GlossSeeAlso": ["GML", "XML"] }, "GlossSee": "markup" } } } } }'''JSON json = JSONSerializer.toJSON( str ) XMLSerializer xmlSerializer = new XMLSerializer() xmlSerializer.setTypeHintsCompatibility( false ) String xml = xmlSerializer.write( json ) print(xml)
... View more
03-01-2018
03:05 AM
Thanks @Matt Burgess I checked but issues is not with "print", error due to "cx_Oracle" object. Could you guide how i can run the my python code with ExecuteProcess or ExecuteStreamCommand process? do you have configuration details ?
... View more
02-28-2018
06:46 AM
Hi, I have written a simple python script to execute the oracle stored proc and its working when i executing using the python IDEL, when i am integrating in NIFI its giving error . ERROR : SyntaxError: no viable alternative at input '\n' in <script> at line number 1 at column number 15; rolling back session: {}
import sys
import getpass
import platform
import cx_Oracle
my_connection = cx_Oracle.connect('USer/Password@server.entsvcs.net:1526/database')
my_cursor = my_connection.cursor()
in_track_id='20585'
in_username='A3843UP'
in_course_no='00009934'
my_cursor=my_connection.cursor()
cur_var=my_cursor.var(cx_Oracle.CURSOR)
cur_var1=my_cursor.var(cx_Oracle.NUMBER)
cur_var2=my_cursor.var(cx_Oracle.STRING)
my_cursor.callproc('sp_hp_course_status_chk', [in_track_id, in_username,in_course_no, cur_var,cur_var1,cur_var2])
print (cur_var.getvalue().fetchall())
print (int(cur_var1.getvalue()))
print (cur_var2.getvalue())
#This code is working in python IDLE but getting error when adding in NIFI
... View more
Labels:
02-19-2018
09:22 AM
@Artem Ervits Do you have any sample groovy code which execute Stored Pprocedue with input and output parameter ?
... View more
01-16-2018
04:28 AM
@Eyad Garelnabi and @Rahul Reddy I am also working on the SOAP with WSDL file to extract the SOAP response but not able to configure, could you share your process and configuration details so that i can make my one. Also when i am trying to do using this post https://github.com/apsaltis/nifi-soap, I am not able to find the path nifi-soap-nar/target/nifi-soap-nar-0.0.1.nar and file in Github. I am using the windows 7, can help how to install and configuration
... View more
01-10-2018
03:16 AM
Thanks Lot @Shu for your details description explanation. My Flow now look like as below .Thanks Lot
... View more
01-09-2018
08:01 AM
1 Kudo
@Matt Burgess I need one help I have one rest API and that API return the response in JSON format. In that JSON one attribute is Certificate and that attribute we need to save in the txt file. Could you help on that details are as following : https://hpe-api.sabacloud.com/v1/login site:NA2PRD0002 password:restWS0808 user:WS_ADMIN
... View more
Labels:
10-30-2017
06:54 AM
Thanks lot Shu, One more thing , is that possible to catch the output of the ExecuteStreenCommand and add that values in the PutEmail Body ? like in my 4th Script wanted to count the files and that files count i wanted to send through email
... View more
10-30-2017
01:46 AM
Hi All, can any one help me on that ?
... View more
10-27-2017
07:22 AM
Hi All, I have a use case in NIFI. I have 4 shell script which one I am willing to execute one
by one. Like Script one Complete then Second
Script and then 3 rd Script and then 4 th one. After 4 th
Script completion I willing to send the notification email. I have created the workflow using the ExecuteProcess but not
able to link all the ExecuteProcess, also when I am adding the putEmail its sending
continues email to me but I need one
email after 4 th Script successful completion. Issues : 1) How I can link one process to another 2) How I can send one email after 4th Process completion Image having flow which i have created and configuration details If anyone having template could you share pls
... View more
Labels:
10-25-2017
08:15 AM
Hi Pierre, Can you help me on NIFI. NIFI installed on my windows laptop. I am trying to execute a .sh script which hosted on linux machine. Linux machine i can connect with user id and password. How i can do this using NIFI.
... View more
10-25-2017
07:55 AM
Hi Matt, Do you have any template for that ?
... View more
10-25-2017
07:49 AM
Thanks for the response, do you have template for that ?
... View more
10-23-2017
03:06 AM
@Matt Thanks for the response. I saw your post and its good. My Requirement is, I have some .SH script which hosted on a Linux machine and i wanted to execute those script using the NIFI. Issues:NIFI ruining at my laptop and I am trying to get the files from Linux machine how i can achive this ?
... View more
10-19-2017
03:17 AM
@Marco, thanks for the Answre, But from my local windows machine how i can execute the SSH command. what i am trying to do from my local machine i wanted to execute this command #!/bin/sh passwrod="Test@Test123" ssh ahmadshe:$passwrod@c4t21128.itcs.hpecorp.net /opt/batchapps/dsas/Jobs/LOAD_ASSURENET_DATA_INTO_DSAS/test.sh
and the test.sh will execute on Linux Remote machine #!/bin/sh echo "Test Command Execution" >> /opt/batchapps/dsas/Jobs/LOAD_ASSURENET_DATA_INTO_DSAS/text.txt could you guide me how i can do
... View more
10-16-2017
03:56 AM
I am working on a POC and I have installed the NIFI at my laptop, now i am trying to execute the SH script which hosted on Linux machine, could you guide me on step by steps.
... View more
Labels: