Problems while getting data from weblogic server when using connection pooling

[jason.3dmagic]

Hello,

I am new to database connection pooling. I am using weblogic server, Java 1.6, MySql database. I have done connection pooling in weblogic server. The problem arise when I try to run select query I cannot fetch data in ResultSet. For insert it works fine...

import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DatabaseToText implements Serializable {

public DatabaseToText() {
super();
// TODO Auto-generated constructor stub
}

public static void main(String[] args) {

Connection conn;
Statement stmt;
ResultSet rs;
String str1, str2;
List l1 = new ArrayList();

try {
System.out.println("in try block");
Properties prop = new Properties();



prop.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory");
prop.put(Context.PROVIDER_URL, "t3://localhost:7001");

System.out.println("properties are set ");

Context ctx = new InitialContext(prop);
System.out.println("b4 lookup(mysqljndi)");

Object obj = ctx.lookup("mysqljndi"); // java:comp/env/CPDS
System.out.println("afta lookup(mysqljndi)");

DataSource ds = (DataSource) obj;
conn = ds.getConnection();
stmt = conn.createStatement();

String query = "select * from logindb.userregister";
System.out.println("query is = " + query);
rs = stmt.executeQuery(query);

System.out.println("size of rs is :: " + rs.getFetchSize());

Iterator i = l1.iterator();
Object obj2 = (Object) rs;
System.out.println("values in rs are :: " + rs.getString(0));

if (rs != null) {
while (i.hasNext()) {
l1.add(rs.getString(0));
l1.add(rs.getString(1));

// System.out.println("username is :: " + str1 + "password is : " + str2);
}
} else {
System.out.println("no values get fetched");
}
ctx.close();

} catch (Exception e) {
e.printStackTrace();
}

}
}


This is the program and following is the output

in try block
properties are set
b4 lookup(mysqljndi)
afta lookup(mysqljndi)
query is = select * from logindb.userregister
size of rs is :: 0
java.sql.SQLException: java.lang.ClassCastException: weblogic.jdbc.common.internal.ReaderBlockGetterImp l_811_WLStub cannot be cast to java.lang.String
at weblogic.jdbc.rmi.SerialResultSet.getString(Serial ResultSet.java:130)
at DatabaseToText.main(DatabaseToText.java:71)


Console is showing errors....

Please Help


Regards, Jason

[masijade]

You would get failures in this code regardless of whether it's using a pool or not

rs = stmt.executeQuery(query);

System.out.println("size of rs is :: " + rs.getFetchSize());

Iterator i = l1.iterator();
Object obj2 = (Object) rs;
// you have not called next() or first() or last() so there is no selected row
System.out.println("values in rs are :: " + rs.getString(0));

[Tolls]

The result set starts before the first record.
You can't access any of the columns until you have called next()...which returns a boolean to say whether there is a next or not.

[jason.3dmagic]

Sorry I posted wrong code when i was playing with the code.....
Following is the exact code

import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DatabaseToText implements Serializable {

  public DatabaseToText() {
    super();
    // TODO Auto-generated constructor stub
  }

  public static void main(String[] args) {

    Connection conn;
    Statement stmt;
    ResultSet rs;
    String str1, str2;
    List l1 = new ArrayList();

    try {
      System.out.println("in try block");
      Properties prop = new Properties();

     

      prop.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory");
      prop.put(Context.PROVIDER_URL, "t3://localhost:7001");

      System.out.println("properties are set  ");

      Context ctx = new InitialContext(prop);
      System.out.println("b4 lookup(mysqljndi)");

      Object obj = ctx.lookup("mysqljndi"); // java:comp/env/CPDS
      System.out.println("afta lookup(mysqljndi)");

      DataSource ds = (DataSource) obj;
      conn = ds.getConnection();
      stmt = conn.createStatement();

      String query = "select * from logindb.userregister";
      System.out.println("query is = " + query);
      rs = stmt.executeQuery(query);

      System.out.println("size of  rs is  ::   " + rs.getFetchSize());

      if (rs != null) {
        while (rs.next()) {
          str1 = rs.getString(0);
          str2 = rs.getString(1);
          System.out.println("username is :: " + str1 + "password is : " + str2);
        }
      } else {
        System.out.println("no values get fetched");
      }
      ctx.close();

    } catch (Exception e) {
      e.printStackTrace();
    }

  }
}

OUTPUT IN CONSOLE:::::

in try block
properties are set
b4 lookup(mysqljndi)
afta lookup(mysqljndi)
query is = select * from logindb.userregister
size of rs is :: 0
java.sql.SQLException: weblogic.rmi.extensions.RemoteRuntimeException: Unexpected Exception - with nested exception:
[java.rmi.MarshalException: error marshalling return; nested exception is:
java.io.NotSerializableException: com.mysql.jdbc.ResultSet]
at weblogic.jdbc.rmi.SerialResultSet.next(SerialResul tSet.java:89)
at DatabaseToText.main(DatabaseToText.java:69)

The error is at line while(rs.next())
Record is getting inserted but when i use select query i cannot get the output...

[Tolls]

Is your connection pool on a separate server then?

As it says, that MySQL result set isn't serializable.

[jason.3dmagic]

I am developing it on a single PC...
Everything is installed on my PC

Yes i agree that ResultSet is not serialized......
but if you look at the console output closely the rs.getFetchSize() is giving 0.
I have data in the table the getFetchSize() output should have been 1.

Though I am able to insert data into database....

[Tolls]

getFetchSize() does not return the row count. It tells you how many rows it will get from the database once you have scrolled through all the ones it has already supplied. So a fetch size of 10 will retrieve 10 rows, then 10 more once you have called next() 10 times, and so on.

I suspect fetch size doesn't involve hitting the db, and also is only an int so is automatically serializable in any case. However actually fetching the data across with a next() isn't.

You say you're on a single PC, however your code is not running inside weblogic, as in a normal webapp, so it still has to transfer from your web server to your app, albeit on the same machine.

[jason.3dmagic]

SO, Tolls
Please tell me how do I conquer the problem??
I will really appreciate your help...

[Tolls]

I can only assume this is happening because you are connecting to your data source in a wierd way. Why are you running a main() app against a connection pool on a server? It doesn't make architectural sense.

The pool on weblogic is intended to be used by apps deployed on that same weblogic server, not to be used by an app external to weblogic.

[jason.3dmagic]

Hello Tolls,

Thank you for replying. I tried what you had suggested. I configured weblogic server from myeclipse and run the program but it is again giving me error on the same line and for the same reason.
I am very confused now. I tried every possible things i could do.

Please help

Thanks and Regards,
Jason

[Tolls]

If you're running a main() then it is not running in weblogic. It is an external app.

[jason.3dmagic]

Hello Tolls,

You solved it... Thank you very much...

I was trying connection pooling in a simple java program later I tried for simple login program and started server from myeclipse internally. Guess wat it is running.
Now I want to do connection pooling in JBoss. Do you know how to do connection pooling in JBoss?? I have know idea about how to do it in JBoss. :confused:

[masijade]

Read the manual? It is documented, you know?