Null results when trying to execute SQL service through JAVA

, ,
1

Hi All,
I am tryinh to execute SELECT SQL query through JAVA service in webMethods. When i run the service I get the resultList(documentList) as output , but it has null resultList elements in it. I dont understand why key/value pairs arent added to the list. Below is my code in try block

java.sql.Statement stmt = null;
java.sql.ResultSet rs = null;
IData resultList = new IData[1000];
java.sql.Connection dbConnection =null;
try
{
stmt = dbConnection.createStatement();
rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
int i=0;
while ( rs.next() ) {
IDataCursor idcpipelineCursor = resultList[i].getCursor();
for (int j=1;j<=numberOfColumns;j++){
String columnName = rsmd.getColumnName(j);
idcpipelineCursor.setKey(columnName);
String columnValue = rs.getString(j);
idcpipelineCursor.setValue(columnValue);
//idcpipelineCursor.insertAfter(columnName,columnValue);
idcpipelineCursor.next();
}
i++;
idcpipelineCursor.destroy();
}

rs.close();
stmt.close();
IDataUtil.put(pipelineCursor ,“resultList”, resultList);

The database connections and everything is working fine through this JAVA service. The query is correct because i tried it through Adapter service. So all those other dependents are working.

Any kind of help would be gratly appreciated. Thanks!

2

This seems very very strange for me

because your dbConnection is null…

3

I am initializing the dbConnection before in the code, which was not listed the code above. As I said all that is working.There seems to be problem with assigning key/value pair to the resultList doclist.

4

Why in Java and not using a JDBC adapter service?

5

Two issues i think:

1. resultList IData array is initialized but no IData is created before obtaining a cursor on it.
In this line “IDataCursor idcpipelineCursor = resultList[i].getCursor();” you are trying to obtain a cursor on resultList[i] which is not created yet. Look into this method: IDataFactory.create() and try using it before getting a cursor to create IData object for each row of data.

2. setkey and setValue methods cannot be used without positioning cursor. Look into positioning methods such as next(), last(), etc on IDataCursor object.

But as Rob already asked, why using Java not the adapter services? Any specific reason?

HTH
Suren

6

Well, The firm i work for has 100 different stores and each store has a seperate DB. We decide that it would not be feasible to create 100 different JDBC adapters connections. So we wrote a custom JAVA servicwe which would connect to the store on the fly when p[rovided with sql driver name, db name store number etc.

7

Thanks very much Suren!!! it worked dude!!! Basically I created a an empty IData object for rach resultList document List and it worked.

8

Glad it worked!

9

Hi.I have same requirement and i have done the same but its showing as null in the output.Below is my code.

// pipeline
IDataCursor pipelineCursor = pipeline.getCursor();
Connection c = null;
ResultSet rs = null;
Statement s = null;
String sqlString = null;
int rowCount = 0;
String statusCode = “0”;
String statusMsg = “”;
ArrayList list = new ArrayList();
//List list = new List();

try {

IData inRec = IDataUtil.getIData( pipelineCursor, "inRec" );
String    WhereCondition = IDataUtil.getString( pipelineCursor, "WhereCondition" );
String    Table = IDataUtil.getString( pipelineCursor, "Table" );
if ( inRec == null) throw new Exception ("inRec record is empty.");

String  errString = "";
boolean errCode   = false; 

    if (WhereCondition == null) {
           errString += (errCode) ? ", WhereCondition" : "WhereCondition" ;
        errCode = true;
    }

    if (Table == null) {
           errString += (errCode) ? ", Table" : "Table" ;
        errCode = true;
    }

if ( errCode ) throw new Exception(errString + " Field(s) are empty");

pipelineCursor.destroy();

IDataCursor inRecCursor = inRec.getCursor();

// get columns

int n = IDataUtil.size(inRecCursor);

String[] colNames = new String[n];
inRecCursor.first();
for( int j=0; j<n; j++ )
{
    colNames[j] = inRecCursor.getKey();
    inRecCursor.next() ;
}

String selectList = "";
int colCount = colNames.length;

for (int i=0; i<colNames.length; i++) {
    selectList += colNames[i];
    if ((i+1) != colNames.length) {
        selectList += ", ";
    }        
}

inRecCursor.destroy();

// DBrec
IData    DBrec    = IDataUtil.getIData( pipelineCursor, "DBrec" );
String  dbUrl    = "";
String  user     = "";
String  password = "";
String  DBClass  = "";
if ( DBrec != null)
{
    IDataCursor DBrecCursor = DBrec.getCursor();
        dbUrl    = IDataUtil.getString( DBrecCursor, "DbUrl" );
        user     = IDataUtil.getString( DBrecCursor, "User" );
        password = IDataUtil.getString( DBrecCursor, "Pwd" );
        DBClass  = IDataUtil.getString( DBrecCursor, "DBClass" );
    DBrecCursor.destroy();
}
else {
        dbUrl    = getParameter("dbURLSOM", "SpGeneralUtil");
        user     = getParameter("dbUserSOM", "SpGeneralUtil");
        password = getParameter("dbPassSOM", "SpGeneralUtil");
        DBClass  = getParameter("dbDriver", "SpGeneralUtil");
}

// Establish DB connection
Class.forName( DBClass );
c = DriverManager.getConnection(dbUrl, user, password);
s = c.createStatement();

// Create output IData object
IData resultList = new IData[1000];
IData resultList1 = IDataFactory.create();

// Prepare SQL Statement
sqlString = "SELECT " + selectList + " " +
"FROM " + Table + " " +
"WHERE " + WhereCondition + " ";

rs = s.executeQuery(sqlString);    
int j=0;
while (rs.next() )
    {
     IDataCursor idcpipelineCursor = resultList[j].getCursor();
        for (int i=0; i<colCount; i++) {
      String columnName = rs.getString(colNames[i]);
      idcpipelineCursor.setKey(columnName);
      String columnValue = rs.getString(i); 
          idcpipelineCursor.setValue(columnValue);
      idcpipelineCursor.next();
     
      IDataUtil.put( pipelineCursor, "resultList",  resultList);
     }
       j++;
   idcpipelineCursor.destroy();

 }// while(rs.next()) 

if (rowCount <= 0) {
        statusMsg =" No rows fetched for the SQL statement : " + sqlString ;
        statusCode= "1";
        c.close(); 
    }

 if (rs != null)    rs.close();
if (s != null) s.close(); 
if (c != null) c.close(); 

// pipeline
IDataCursor pipelineCursor_1 = pipeline.getCursor();
IDataUtil.put( pipelineCursor_1, "outRec", resultList1 );
IDataUtil.put( pipelineCursor_1, "StatusCode", statusCode );
IDataUtil.put( pipelineCursor_1, "StatusMsg", statusMsg );
//outRecCursor.destroy();
pipelineCursor_1.destroy();

}
catch (Exception e) {
try {
if (rs != null) rs.close();
if (s != null) s.close();
if (c != null) c.close();
}
catch (SQLException f) {
}
IDataCursor pipelineCursor_e = pipeline.getCursor();
IDataUtil.put(pipelineCursor_e, “StatusCode”, “1”);
IDataUtil.put(pipelineCursor_e, “StatusMsg”, "DatabaseUtil:getInfoFromDataBases exception : " + e.getMessage() + ". SQL Statement : " + sqlString );
pipelineCursor_e.destroy();
}

Please do the changes in my code where is the mistake done as i am new to webMethods developer.

Thanks in advance…