on 03-12-2013 1:26 PM
Hi All,
Scenario: Proxy to JDBC. (Delete and Insert) , PI7.0, SQL Server
PI has to check the status field in JDBC if value is "delete" then PI should delete all the records in Table and insert the rows.
I have created two statements for respective delete and insert.
created a JDBC lookup and below Java code which is returning output as Null.
//write your code here
String Query = " ";
Channel channel = null;
DataBaseAccessor accessor = null;
DataBaseResult resultSet = null;
AbstractTrace trace=container.getTrace();
Query="SELECT TOP 1 status FROM Sample_table";
try{
//Determine a channel, as created in the Configuration
channel = LookupService.getChannel("BS_Dev","JDBC_Lookup_Receiver");
//Get a database accessor for the channel.
accessor = LookupService.getDataBaseAccessor(channel);
//Execute Query and get the values in resultset
resultSet = accessor.execute(Query);
for(Iterator rows = resultSet.getRows();rows.hasNext();)
{
Map rowMap = (Map)rows.next();
result.addValue(""+rowMap.get("status"));
//trace.addInfo("Trace Value"+rowMap.get("status"));
}
}
catch(Exception ex)
{
result.addValue(ex.getMessage());
}
I tried to get the output of rows.next() and which was "status=delete" but o/p of (""+rowMap.get("status")); was null, really confused where it went wrong.
thanks in advance for your valuable suggestions.
Regards,
Mani
Hi Mani, Give a thought if you can use stored procedure than the above code for the approach as it is simple to implement. Just use execute statement in your mapping for the stored procedure.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Mani,
did you try to execute your select statement with a database client? Do you get back any result by this manual test?
Maybe you have to add the schema name in the select:
SELECT TOP 1 status FROM schema.Sample_table
or maybe you are using an user in the JDBC channel with has not sufficient rights for accessing the table.
Kind regards,
Tobi.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Manikandan,
I am wondering about you query which is - Query="SELECT TOP 1 status FROM Sample_table";
This will return status value which is present in the first row.
Change the two line inside the for loop with the below code.
for(Iterator rows = resultSet.getRows();rows.hasNext();)
{
String str = (String)rows.next();
result.addValue(""+str);
..
}
Regards,
Ashish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.