cancel
Showing results for 
Search instead for 
Did you mean: 

Help required on JDBC lookup

manikandan_shanmugam3
Active Participant
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member229310
Active Participant
0 Kudos

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.

tobiasberneck
Explorer
0 Kudos

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.

Former Member
0 Kudos

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

baskar_gopalakrishnan2
Active Contributor
0 Kudos

Please check whether next() returns boolean or string. I don't have 7.0 version running. But I suspect some problem in retrieving the resultset values.  Please check in that area.