initalize a var for a preparedStatement

[ceyesuma]

Hello
I am recieving error java.sql.SQLException: At least one parameter to the current statement is uninitialized. at ConnectStudentDAO.java at ResultSet rs=ps.executeQuery(); Not sure if the xml statment or the bean variables or if ps.close() is needed

output

run:
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
in bCreatedTables:
created table: createAdminTable
created table: createPayeeTable
created table: createStudentTable
created table: createInstructorTable
created table: createBookingTable
created table: createInstrAvailableTable
created table: createInstrumentTable
created table: createLocationTable
created table: createPaymentTable
created table: createPayrollTable
created table: createUserTable
created table: createUserGroupMappingTable
created table: createUserGroupTable
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
db and tables created: 
inserted data int: insertAdminTable
inserted data int: insertPayeeTable
inserted data int: insertStudentTable
inserted data int: insertInstructorTable
inserted data int: insertPaymentTable
inserted data int: insertUserTable
inserted data int: insertUserGroupTable
inserted data int: insertUserGroupMappingTable
CONECTION IS GOOD FOR A TEST
test string query: SELECT * FROM student

number of columns: 15
, STU_UID, STU_PASSWORD, STU_LNAME, STU_MNAME, STU_FNAME, STU_GENDER, STU_AGE, STU_START_DATE, STU_END_DATE, STU_ADDRESS, STU_STATE, STU_ZIP, STU_AREA_CODE, STU_PHONE, PAYEE_UID
, sj, s, null, null, Johnny, null, null, null, null, null, null, null, null, null, null
, ss, s, null, null, Suzie, null, null, null, null, null, null, null, null, null, null
in StudentDAO.JAVA and in  connect of the ConnectDerbyDAO
returns isConnected: true
shutdown database 
disconnected after  building the database java.sql.SQLNonTransientConnectionException: Database 'schoolofdb' shutdown.
disconnected after building the database
in LoginInfoBean
password in LoginInfoBean: [C@c8769b
LoginInfoBean: sj
profile in LoginInforBean: stu_
all models (Student.java extend LoginInfo.javaall beans extend models
in getConnection of DerbyDAOFactory
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
in StudentDAO.JAVA and in  connect of the ConnectDerbyDAO
returns isConnected: true

in ConnectStudentDAO at select(): 
in getConnection of DerbyDAOFactory
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
in StudentDAO.JAVA and in  connect of the ConnectDerbyDAO
returns isConnected: true
ps.execute SelectStudent from xml: SELECT
    stu_uid,
    stu_password,
    stu_lname,
    stu_mname,
    stu_fname,
    stu_gender,
    stu_age,
    stu_start_date,
    stu_end_date,
    stu_address,
    stu_state,
    stu_zip,
    stu_area_code,
    stu_phone,
    payee_uid
    FROM student
    WHERE stu_uid=?
May 17, 2010 10:33:54 AM view.SchoolJDesktopPane login
SEVERE: null
java.sql.SQLException: At least one parameter to the current statement is uninitialized.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown Source)
        at model.dao.ConnectStudentDAO.select(ConnectStudentDAO.java:88)

LoginInfoBean

//if this returns profile then it will build the GUI

DerbyDAOFactory.java

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import model.ModelUtils;

/**
 *
 * @author depot
 */
public class DerbyDAOFactory extends DAOFactory {

    public static void buildDB() throws FileNotFoundException, IOException, SQLException {
        ConnectDerbyDAO cdd = new ConnectDerbyDAO();
        cdd.connect();
        cdd.disconnect();
        System.out.println("disconnected after building the database");
    }

    public Connection getConnection() throws FileNotFoundException, IOException, SQLException {
        System.out.println("in getConnection of DerbyDAOFactory");
        ConnectDerbyDAO cdd = new ConnectDerbyDAO();
        cdd.connect();
        Connection conn = cdd.getConn();
        return conn;

    }

    @Override
    public AdminDAO getAdminDAO() {
        return new ConnectAdminDAO();
    }

    @Override
    public StudentDAO getStudentDAO() {
        return new ConnectStudentDAO();
    }

    @Override
    public InstructorDAO getInstructorDAO() {
        return new ConnectInstructorDAO();
    }

    @Override
    public PayeeDAO getPayeeDAO() {
        return new ConnectPayeeDAO();
    }
}

ConnectStudentDAO.java
find and confirm uid,profile,password access and set all fieled in newUser which is a new Student()
and return it.

public class ConnectStudentDAO implements StudentDAO {

    public String type;
    public Student newUserObj;
    public LoginInfo loginInfo;
    public Student studentUser;
    public DerbyDAOFactory ddf;
    public Connection conn;
    public PreparedStatement ps;
    public String uid;
    public String stuUid;
    public String password;
    
    public ConnectStudentDAO() {
    }
    //////////////////////////////////////////////////////////////////////
    //////////////////  login find user /////////////////////////
    ////////////////////////////////////////////////////////////////

    public void ConnectStudent() throws UnknownSubscriberException, SQLException, IncorrectPasswordException, LoginException, FileNotFoundException, IOException {
        System.out.println("in ConnectStudentDAO");
        DerbyDAOFactory ddf = new DerbyDAOFactory();
        conn = (Connection) ddf.getConnection();
        PreparedStatement ps = null;
        ps = (PreparedStatement) conn.prepareStatement(
                ModelUtils.getXMLResource("SelectUserGroup"));
        System.out.println("");
        System.out.println("Statement used to get usergroup: "+
                ModelUtils.getXMLResource("SelectUserGroup"));
        ResultSet rs = ps.executeQuery();
        if (!rs.next()) {
            ps.close();
            rs.close();
            throw new UnknownSubscriberException();// no exceptions are good
        }
        String groupName = rs.getString(1);
        if (groupName.equals("student")) {
            setType("student");
            System.out.println("the user group: "+getType());
            studentUser = select();
            newUserObj = studentUser;
        }

    }

    public StudentBean select() throws UnknownSubscriberException, IncorrectPasswordException, LoginException, SQLException, FileNotFoundException, IOException {
        System.out.println("");
        System.out.println("in ConnectStudentDAO at select(): ");
        DerbyDAOFactory ddf = new DerbyDAOFactory();
        conn = ddf.getConnection();
        if (conn != null) {           
            ps = null;
            
            ps = (PreparedStatement) conn.prepareStatement(
                    ModelUtils.getXMLResource("SelectStudent"));
        }
        System.out.println("ps.execute SelectStudent from xml: " + ModelUtils.getXMLResource("SelectStudent"));
        //[b]
        ResultSet rs = ps.executeQuery();
        //[/b]
        if (!rs.next()) {
            throw new UnknownSubscriberException();// no exceptions are good
        }
        String stuUid = rs.getString(1);
        System.out.println("rs.getString(1): "+stuUid);
        if (!loginInfo.getUser().equals(stuUid)) {
            System.out.println("getUser() is not equal to rs.getString(1)");
            throw new UnknownSubscriberException();
        }

        String password = rs.getString(2);
        System.out.println("rs.getString(2) password: "+password);
        if (!loginInfo.getPassword().equals(password)) {
            System.out.println("getPassword() is not equal to rs.getString(2)");
            throw new IncorrectPasswordException();
        }
        ///////////////// test output //////////////////////////
        ///////////////// test output //////////////////////////
        System.out.println(loginInfo.user);                          /////
        System.out.println(loginInfo.password);                      /////
        Properties dbProperties = ModelUtils.loadXMLResources();///
        DBOut o = new DBOut();               //////////////////////
        o.testOutput(conn, dbProperties);    //////////////////////
        ///////////////// test output //////////////////////////
        ///////////////// test output //////////////////////////

        //newUserObj.setEmail(loginInfo.getEmail());
        studentUser.setStuUid(loginInfo.getUser());
        studentUser.setPassword(loginInfo.getPassword());
        studentUser.setlName(rs.getString(3));
        studentUser.setmName(rs.getString(4));
        studentUser.setfName(rs.getString(5));
        System.out.println("rs.getString(5): "+studentUser.getfName());
        studentUser.setGender(rs.getString(6));
        studentUser.setStartDate(rs.getDate(7));
        studentUser.setEndDate(rs.getDate(8));
        studentUser.setAge(rs.getInt(9));
        studentUser.setAddress(rs.getString(10));
        studentUser.setState(rs.getString(11));
        studentUser.setZip(rs.getInt(12));
        studentUser.setAreaCode(rs.getInt(13));
        studentUser.setPhone(rs.getInt(14));
        studentUser.setPayRate(rs.getDouble(15));
        System.out.println("");
       System.out.println(" return studentUser ");
       System.out.println("");
        return (StudentBean) studentUser;

    }

   ....

[~s.o.s~]

If the query identified by the key 'SelectStudent' has placeholders (? characters), you need to set them before executing the prepared statement.

[ceyesuma]

I was afraid of that . so ? is in the statemsnt holding a place to hold the stuUid that I found in user input loginInfo.getStuUid() .....

so that has to equal a variable or the studentUser.setStuUid(loginIfo.getUser());( ?

[~s.o.s~]

Use one of the setXXX methods of the PreparedStatement to set the value of your place holder.

ps = (PreparedStatement) conn.prepareStatement(
             ModelUtils.getXMLResource("SelectUserGroup"));
ps.setString(1, user.getUserId());
ResultSet rs = ps.executeQuery();

[ceyesuma]

is that ps.setString(int parameterIndex,tring x)

not sure what int parameterIndex is though.

[ceyesuma]

I still have a '?' so I don't have a clue yet.

if (conn != null) {
 PreparedStatement ps = null;       
            conn = (Connection) ddf.getConnection();
            String queryWhere = ("INSERT INTO student(stu_uid) VALUES(?)");
            ps = conn.prepareStatement(queryWhere);
            ps.setString(1,stuUid);                
            ps.execute();
            ps.close();
            ps = null;
            studentUser.setStuUid(loginInfo.getUser());
            ps = (PreparedStatement) conn.prepareStatement(
                    ModelUtils.getXMLResource("SelectStudent"));
        }

[ceyesuma]

I didn't see your example. I'm still getting nullpointer so I'm still working with it
1.
ps = (PreparedStatement) conn.prepareStatement(
2.
ModelUtils.getXMLResource("SelectUserGroup"));
3.
ps.setString(1, user.getUserId());
4.
ResultSet rs = ps.executeQuery();

[ceyesuma]

Hello
To make things more complicated I re-routed the code because a prerequisite to find which table to use (student,instructor.etc) I have a Statement: SELECT groupName FROM ser_group AS ug,usergroup_mapping AS ugm WHERE ugm.uid=? and ugm.groupid=ug.groupid Is this the same? just use ps.setString(1,stuUid) before this statement.?

[ceyesuma]

It throws my temp exceptionso I'm not sure if all the tables are getting the correct uid. out of all those ?

in LoginInfoBean
db sets password in LoginInfoBean: [C@e70e30
LoginInfoBean: sj
db sets profile: stu_
all models (Student.java extend LoginInfo.javaall beans extend models
in getConnection of DerbyDAOFactory
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
in StudentDAO.JAVA and in  connect of the ConnectDerbyDAO
returns isConnected: true
in ConnectStudentDAO
in getConnection of DerbyDAOFactory
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
in StudentDAO.JAVA and in  connect of the ConnectDerbyDAO
returns isConnected: true

Statement used to get usergroup:  SELECT groupName FROM user_group AS ug,usergroup_mapping 
  AS ugm WHERE ugm.uid =? AND ugm.groupid=ug.groupid 
returned: null
May 17, 2010 7:45:41 PM view.SchoolJDesktopPane login
SEVERE: null
model.err.UnknownSubscriberException
        at model.dao.ConnectStudentDAO.ConnectStudent(ConnectStudentDAO.java:63)

public void ConnectStudent() throws UnknownSubscriberException, SQLException, IncorrectPasswordException, LoginException, FileNotFoundException, IOException {
        System.out.println("in ConnectStudentDAO");
        DerbyDAOFactory ddf = new DerbyDAOFactory();
        conn = ddf.getConnection();
        ps = (PreparedStatement) conn.prepareStatement(
                ModelUtils.getXMLResource("SelectUserGroup"));
        ps.setString(1, stuUid);
        System.out.println("");
        System.out.println("Statement used to get usergroup: "
                + ModelUtils.getXMLResource("SelectUserGroup"));
        ResultSet rs = ps.executeQuery();
        if (!rs.next()) {
            ps.close();
            rs.close();
            throw new UnknownSubscriberException();// no exceptions are good
        }
        String groupName = rs.getString(1);
        if (groupName.equals("student")) {
            setType("student");
            // studentUser.setStuUid(loginInfo.getUser());
            System.out.println("the user group: " + getType());
            studentUser = select();
            studentUser.setUser(loginInfo.getUser());
            System.out.println("Connect set user: " + studentUser.getUser());
            newUserObj = studentUser;
        }

    }