java.lang.NullPointerException while pulling data from JDBC table.

[Former Member]

Hi All,

I am working on a JDBC to Proxy scenario.

While pulling the data from JDBC table, I am getting following error in CC monitoring:

Error during conversion of query result to XML: java.lang.NullPointerException: while trying to invoke the method java.lang.Object.toString() of an object returned from java.sql.ResultSet.getObject(int)

It would be really helpful if you could please help me in this regard.

Thanks,

Aman

[baskar_gopalakrishnan2]

The error message shows clearly that it is null pointer exception. You are try to read a value from database column that has no value.  You might want to modify the select query and use where clause not null for those fields.  This way you always get records that has values in the table.

[Former Member]

Hi Bhaskar,

Thanks for your reply.

There are 2 fields in JDBC table with NULL values in all the rows:

1. Field1 (VARCHAR), and

2. Field2 (DATE)

Now, when I am including Field1 and excluding Field2 in select query of JDBC adapter, everything is running fine. However, when I do vice-versa, its giving me the null pointer exception.

That means Field2 (DATE) cannot be a NULL. Could you kindly make me understand the technicality for this?

Thanks,

Aman

[anupam_ghosh2]

Hi Aman,

                Could you post both the queries? Also could you confirm all fields in database can accept null values? If you are making thse queries directly in database instead of making them through SAP-PI are you getting same errors?

Regards

Anupam 

[Former Member]

Hi Anupam,

When I am running my SQL query directly in database (having DATE field as NULL), it is selecting data as per the query. However, when I am doing the same through PI JDBC adapter, it is giving Null pointer exception error in CC monitoring.

There are both types of fields in the database which can or cannot accept NULL values.

Field2 (DATE) can accept NULL value.

Thanks,

Aman

[Former Member]

Hello,

Looks more like conversion error between strings and integer.  It is trying to retrieve a string field whereas it expected an integer field or vice versa.  Please check the data types between the table and your structure.

Regards

Anandh.B

[Former Member]

Hi Anandh,

Thanks for your reply.

Actually the fields that I am selecting from JDBC are of various data types like 'Number' 'Varchar' and 'Date'. However, in Sender structure of PI, I am maintaining all as 'String'.

So, I guess thats not a matter of concern.

Thanks,

Aman

[Ryan-Crosby]

Hi Aman,

There is also the possibility that the source fields in the database you are querying don't have a "not null" (or some other option based on the type of database).  This would mean that it's possible that you are actually getting the value "null" from the database so if you try to read that object or do anything with it then you get a NullPointerException.

Regards,

Ryan Crosby

[anupam_ghosh2]

Hi Aman,

                There can be three reasons for which java  is throwing exceptions

1. column index mentioned to fetch a specific data from resultset is not correct.

2. There is internal error while accessing the database.

3. The query is trying to retrieve results from a closed resultset.

Try exceuting the query directly in database or through a simple java code.

I guess some parameters supplied might be wrong.

Regards

Anupam

[Former Member]

Hi Anupam,

Thanks for your reply.

Earlier, I was selecting few fields from JDBC table which I was not mapping to the target structure. Now I have updated my sender JDBC CC and selecting only those fields which I am using in mapping. By doing this, interface is running fine.

I don't understand the logic behind this. As per my understanding, as far as selected fields are there in the sender structure, it doesn't matter if we are mapping them or not. Please correct me if I am wrong.

BTW, this is a synchronous interface.

Thanks,

Aman

[anupam_ghosh2]

Hi Aman,

                   Fields are being picked up from database tables in a specific sequence. After you specify the field name, internally adapter is querying the database on basis of their positions and type. If these do not match with the table adapter will throw exceptions. Here is a code snippet

public static void alternateViewTable(Connection con)

  throws SQLException {

  Statement stmt = null;

  String query =

  "select COF_NAME, SUP_ID, PRICE, " +

  "SALES, TOTAL from COFFEES";

  try {

  stmt = con.createStatement();

  ResultSet rs = stmt.executeQuery(query);

  while (rs.next()) {

  String coffeeName = rs.getString(1);

  int supplierID = rs.getInt(2);

  float price = rs.getFloat(3);

  int sales = rs.getInt(4);

  int total = rs.getInt(5);

  System.out.println(coffeeName + "\t" + supplierID +

  "\t" + price + "\t" + sales +

  "\t" + total);

  }

  } catch (SQLException e ) {

  JDBCTutorialUtilities.printSQLException(e);

  } finally {

  if (stmt != null) { stmt.close(); }

  }

}

You can see we expect a float value for the field "price". Now the price has been assigned one  integer value 3 position "float price = rs.getFloat(3);" depending on the position of "price" in the query. Now in database table price is expected to be in column number 3 and nowhere else.

Here is where your mapping comes in. If you make a mistake here, you will get exception.

Regards

Anupam