3 Replies Latest reply on Dec 20, 2001 9:31 AM by davidjencks

    Why i canot prepare statement?

    ipozeng

      Hi,friends
      I used M$ jdbc driver to manipulate the SQL server 2000 RDBMS.It runs faster than odbc-jdbc bridge but when used in a SessionBean i encountered the following problem:
      ...
      con = getConnection();
      stmt = con.prepareStatement("select * from coffees");
      ...
      the prepareStatement worked fine! The coffees table in sql2000 has COL_NAME, PRICE fields.
      However,
      stmt = con.prepareStatement("select * from coffees where (COF_NAME=?)");
      will throw "Unable to create PreparedStatement!" exception !!!

      I was frustrated now.please help me figure it out!

      Best Regards!

        • 1. The standalone application works fine
          ipozeng

          import java.sql.*;

          public class DbTest
          {
          public static void main(String[] args)
          {
          String sDBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
          String sConnStr = "jdbc:microsoft:sqlserver://192.168.1.129:1433";

          Connection conn;
          ResultSet rs;
          PreparedStatement stmt;

          try {
          long timestart=System.currentTimeMillis();

          Class.forName(sDBDriver);
          conn = DriverManager.getConnection(sConnStr,"ihpms","ihpms");
          System.out.println("row size=" + conn.getMetaData().getMaxRowSize());

          timestart=System.currentTimeMillis()-timestart;
          System.out.println( "Connection Cost millseconds:" + timestart);


          System.out.println( "Maximum row size: " + conn.getMetaData().getMaxRowSize());

          timestart=System.currentTimeMillis();
          stmt = conn.prepareStatement("select * from coffees coffees where COF_NAME=?");
          stmt.setString(1,"Columbian");
          rs = stmt.executeQuery();

          timestart=System.currentTimeMillis()-timestart;
          System.out.println( "Fetch Cost millseconds:" + timestart);

          timestart=System.currentTimeMillis();
          while(rs.next()) {
          System.out.println(rs.getString(1) + "\t" + rs.getString(2));
          }
          timestart=System.currentTimeMillis() - timestart;
          System.out.println( "Display Cost millseconds:" + timestart);

          if(rs != null)
          rs.close();
          if(stmt != null)
          stmt.close();
          if(conn != null)
          conn.close();

          }
          catch(Exception ex) {
          System.out.println(ex.getMessage());
          }
          }
          }

          Any suggestions are appreciated!

          • 2. It works now but...
            ipozeng

            By adding attribute SelectMethod=cursor in jboss.jcml,i can run the program and it succeeds updating the table :)
            However after finishing i got a message "Cannot commit a transactional connection:See JDBC 2.0 Optional Package Specification section 7.1 (p25)" :(
            what does it mean?

            Any suggestions are appreciated!

            • 3. Re: It works now but...
              davidjencks

              perhaps you are using cmt and calling commit on your connection.