help in SQL and PreparedStatement (error)

[redashley40]

This is my first attempt in SQL and PreparedStateme nt
I have add the PreparedStateme nt and I'm not to sure if I'm doing it correctly.
When I do a test run on Choose 1 ,or 2 I get this error.
Error - com.mysql.jdbc. Statement

here is my code below

[CODE=java]public class DBAssign {

/**
* @param args
*/
static Scanner kbd;

static Connection conn = null;
//adding records...
public static void addRecord(Strin g id, String fname, String lname,
String street, String city, String state, String zip,
String hphone, String ophone, String deptasg, int yrsemp,
float mtpy) throws SQLException {
// make variables
PreparedStateme nt statement = null;

String addstring = "Insert into emptable values('" + id + "', '"
+ fname + "', " + lname + ", '" + street + "'" + city + "',"
+ state + "'," + zip + "'," + hphone + "'," + ophone + "'"
+ deptasg + "'," + yrsemp + "'," + mtpy + "',)";

try {
statement=conn. prepareStatemen t("INSERT into user values(?,?)");

statement = (PreparedStatem ent) conn.createStat ement();
boolean ret = statement.execu te(addstring);
if (ret) {
System.out.prin tln("Updated " + statement.getUp dateCount());
}//close if

} catch (SQLException e) {
System.out.prin tln("Error " + e.getMessage()) ;
throw (e);
} finally {
try {
if (statement != null) {
statement.close ();
}//close if
} catch (SQLException e2) {
System.out
.println("Error freeing resources " + e2.getMessage() );
throw (e2);
}//close e2
}//close finally

}//close exception

//add to the record
public static void addToTable() {
// get data from keyboard
String id = null;
String fname; // =null;
String lname;
String street;
String city;
String state;
String zip;
String hphone;
String ophone;
String deptasg;
int yrsemp;
float mtpy;
boolean dupkey = true;

while (dupkey == true) {
System.out.prin tln("Enter Employee ID");
id = kbd.next();
kbd.nextLine();
dupkey = findRecord(id);
if (dupkey == true) {
System.out.prin tln("Key must be unique - try again");
}
}

System.out.prin tln("Enter first name");
fname = kbd.next();
kbd.nextLine();

System.out.prin tln("Enter last name");
lname = kbd.next();
kbd.nextLine();

System.out.prin tln("Enter street name");
street = kbd.next();
kbd.nextLine();

System.out.prin tln("Enter city");
city = kbd.next();
kbd.nextLine();

System.out.prin tln("Enter state");
state = kbd.next();
kbd.nextLine();

System.out.prin tln("Enter zip code");
zip = kbd.next();
kbd.nextLine();

System.out.prin tln("Enter home phone number");
hphone = kbd.next();
kbd.nextLine();

System.out.prin tln("Enter office phone");
ophone = kbd.next();
kbd.nextLine();

System.out.prin tln("Enter dept name");
deptasg = kbd.next();
kbd.nextLine();

System.out.prin tln("Enter years employeed");
yrsemp = kbd.nextInt();
kbd.nextLine();

System.out.prin tln("Enter month pay");
mtpy = kbd.nextFloat() ;
kbd.nextLine();

try {
addRecord(id, fname, lname, street, city, state, zip, hphone,
ophone, deptasg, yrsemp, mtpy);
} catch (SQLException e) {
System.out.prin tln("Error adding record " + e.getMessage()) ;
}

}//close add

//List records
public static void listRecords() {
PreparedStateme nt statement = null;
ResultSet rs = null;

try {
statement = (PreparedStatem ent) conn.createStat ement();
rs = statement
.executeQuery(" Select empid, firstname, lastname, street, city, state, zip, homephone, officephone, department, yearsemploy, monthpay");
if (rs != null) {
while (rs.next()) {
System.out.prin tln(rs.getStrin g("empid") + " - "
+ rs.getString("f irstname") + " - "
+ rs.getInt("last name") + " - "
+ rs.getString("s treet") + " - "
+ rs.getString("c ity") + " - "
+ rs.getString("s tate") + " - "
+ rs.getString("z ip") + " - "
+ rs.getString("h phone") + " - "
+ rs.getString("o phone") + " - "
+ rs.getString("d epartment") + " - "
+ rs.getString("y earsemploy") + " - "
+ rs.getString("m onthpay"));
}// close the while loop
}// close the if loop

} catch (SQLException e) {
System.out.prin tln("Error listing records: " + e.getMessage()) ;
} finally {
try {
if (statement != null) {
statement.close ();
}
if (rs != null) {
rs.close();
}
} catch (SQLException e2) {
System.out.prin tln("Error freeing resource" + e2.getMessage() );
}
}//close finally

}//close list


//Find a record
public static boolean findRecord(Stri ng empid) {
boolean retval = true;
PreparedStateme nt statement = null;
ResultSet rs = null;

int countemps = 0;

try {
statement = (PreparedStatem ent) conn.createStat ement();
rs = statement
.executeQuery(" Select count(*) as num from emptable where empid = '"
+ empid + "'");
rs.next();
if (rs == null) {
retval = false;
} else {
countemps = rs.getInt(1);
}

// System.out.prin tln("Count "+countrecs );

if (countemps <= 0) {
retval = false;
}

} catch (SQLException e) {
System.out.prin tln("Error finding Employees record "
+ e.getMessage()) ;
} finally {
try {
if (statement != null) {
statement.close ();
}
if (rs != null) {
rs.close();
}
} catch (SQLException e2) {
System.out.prin tln("Error freeing resource " + e2.getMessage() );
retval = true;
}
}//close finally

return retval;

}//close find


public static void showMenu() {
System.out.prin tln("---------------");
System.out.prin tln("1. List employees records");
System.out.prin tln("2. Add employees record");
System.out.prin tln("3. Update employees record");
System.out.prin tln("4. Delete employees record");
System.out.prin tln("5. Exit");
}

public static int getOption() {
int optn = 99;
while (optn > 5 || optn < 1) {
System.out.prin tln("---");
System.out.prin tln("Enter option");
System.out.prin t("===>");
optn = kbd.nextInt();
}//close while
return optn;
}

public static void main(String[] args) {
int optn = 99;

String host = "localhost" ;
String database = "empdata";
String user = "root";
String pass = "";

// make keyboard object
kbd = new Scanner(System. in);

String connstring = "jdbc:mysql ://" + host + "/" + database + "?user="
+ user + "&password= " + pass;

try {
Class.forName(" com.mysql.jdbc. Driver").newIns tance();
conn = DriverManager.g etConnection(co nnstring);
while (optn != 5) {
showMenu();
optn = getOption();

switch (optn) {
case 1:
listRecords();
break;

case 2:
addToTable();
break;

case 3:
updateRecord();
break;

case 4:
deleteRecord();

case 5:
System.out.prin tln("OK - later Come back soon");

default:
break;
}//close switch
}//close while

} catch (ClassNotFoundE xception e1) {
System.out.prin tln("ERROR - Class not found " + e1.getMessage() );
} catch (SQLException e2) {
System.out.prin tln("ERROR - " + e2.getMessage() );
System.out.prin tln("ERROR - " + e2.getSQLState( ));
} catch (Exception e3) {
System.out.prin tln("Error - " + e3.getMessage() );
}
}//close main
}//close class[/CODE]



Any help would be great.
Red

[JosAH]

statement=conn.prepareStatement("INSERT into user values(?,?)");
 
statement = (PreparedStatement) conn.createStatement();

At the second line you've effectively caused an exception. Read the API docs
for the PreparedStateme nt interface. You're supposed to fill in the
question marks (they're indexed 1, 2, 3 ... from left to right). you assign values
to them and then you execute the prepared statement.

kind regards,

Jos

[redashley]

Thanks Jos for the help...
But I did away with the Prestatement.
Found some mistakes in my coding.
I having troubles with this part thou.
I get this error which is pointer...

Error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name= 'lkasdj', Street= '3437', City 'Fullerton', State'CA', Zip = '3', Home Pho' at line 1.

I looked at the String updatestring =
where I think the problem is at.

here is my code for the update section.

[CODE=java]public class DBAssign {

/**
* @param args
*/
static Scanner kbd;
static Connection conn = null;



// searches for a emp id.
public static boolean findRecord(Stri ng empid) {
{
}// close finally

return retval;

}// close find

// updates the a record
public static void updateRecord() {
// get record to update
String empid = null;
boolean findemp = false;
while (!findemp) {
System.out.prin tln("Enter Employee ID to update");
empid = kbd.next();
kbd.nextLine();
findemp = findRecord(empi d);
if (!findemp) {
System.out.prin tln("This Employee id record " + empid
+ " does not exist - try again");
}
}// close while

// display data and request update
Statement stmt = null;
ResultSet rs = null;

String firstname;
String lastname;
String street;
String city;
String state;
String zip;
String homephone;
String officephone;
String department;
String yearsemploy;
String monthpay;

try {
stmt = conn.createStat ement();
rs = stmt.executeQue ry("Select * from emptable where empid = '"
+ empid + "'");
rs.next();
System.out.prin tln("Item First Name is "
+ rs.getString("f irstname"));
System.out.prin t("Enter new item name: ");
firstname = kbd.next();
kbd.nextLine();

System.out.prin tln("Item Last name is " + rs.getString("l astname"));
System.out.prin t("Enter new item name: ");
lastname = kbd.next();
kbd.nextLine();

System.out.prin tln("Item Street is " + rs.getString("s treet"));
System.out.prin t("Enter new item name: ");
street = kbd.next();
kbd.nextLine();

System.out.prin tln("Item City name is " + rs.getString("c ity"));
System.out.prin t("Enter new item name: ");
city = kbd.next();
kbd.nextLine();

System.out.prin tln("Item State name is " + rs.getString("s tate"));
System.out.prin t("Enter new item name: ");
state = kbd.next();
kbd.nextLine();

System.out.prin tln("Item Zip Code is " + rs.getString("z ip"));
System.out.prin t("Enter new item name: ");
zip = kbd.next();
kbd.nextLine();

System.out.prin tln("Item Home Phone is "
+ rs.getString("h omephone"));
System.out.prin t("Enter new item name: ");
homephone = kbd.next();
kbd.nextLine();

System.out.prin tln("Item Office Phone is "
+ rs.getString("o fficephone"));
System.out.prin t("Enter new item name: ");
officephone = kbd.next();
kbd.nextLine();

System.out.prin tln("Item Department is "
+ rs.getString("d epartment"));
System.out.prin t("Enter new item name: ");
department = kbd.next();
kbd.nextLine();

System.out.prin tln("Item Years Employed is "
+ rs.getString("y earsemploy"));
System.out.prin t("Enter new item name: ");
yearsemploy = kbd.next();
kbd.nextLine();

System.out.prin tln("Item Monthly pay is "
+ rs.getString("m onthpay"));
System.out.prin t("Enter new item name: ");
monthpay = kbd.next();
kbd.nextLine();

String updatestring = "Update emptable set FirstName = '"
+ firstname + "', Last name= '" + lastname + "', Street= '"
+ street + "', City '" + city + "', State'" + state
+ "', Zip = '" + zip + "', Home Phone='" + homephone
+ "', Office Phone '" + officephone + "', Department '"
+ department + "', Years Employed'" + yearsemploy
+ "', Month Pay '" + monthpay + "' where empid = '" + empid
+ "'";

stmt.execute(up datestring);
int updatenum = stmt.getUpdateC ount();

if (updatenum < 1) {
System.out.prin tln("Error on update");
} else {
System.out.prin tln("Updated " + updatenum + " records");
}
} catch (SQLException e) {
System.out.prin tln("Error - " + e.getMessage()) ;
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (rs != null) {
rs.close();
}
} catch (SQLException e2) {
System.out.prin tln("Error " + e2.getMessage() );
}
}// close finally

}// close updates



public static void showMenu() {
System.out.prin tln("---------------");
System.out.prin tln("1. List employees records");
System.out.prin tln("2. Add employees record");
System.out.prin tln("3. Update employees record");
System.out.prin tln("4. Delete employees record");
System.out.prin tln("5. Exit");
}

public static int getOption() {
int optn = 99;
while (optn > 5 || optn < 1) {
System.out.prin tln("---");
System.out.prin tln("Enter option");
System.out.prin t("===>");
optn = kbd.nextInt();
}// close while
return optn;
}

public static void main(String[] args) {
int optn = 99;

String host = "localhost" ;
String database = "empdata";
String user = "root";
String pass = "";

// make keyboard object
kbd = new Scanner(System. in);

String connstring = "jdbc:mysql ://" + host + "/" + database + "?user="
+ user + "&password= " + pass;

try {
Class.forName(" com.mysql.jdbc. Driver").newIns tance();
conn = DriverManager.g etConnection(co nnstring);
while (optn != 5) {
showMenu();
optn = getOption();

switch (optn) {
case 1:
listRecords();
break;

case 2:
addToTable();
break;

case 3:
updateRecord();
break;

case 4:
deleteRecord();
System.out.prin tln("That Employee is deleted");
break;
case 5:
System.out.prin tln("OK - later Come back soon");

default:
break;
}// close switch
}// close while

} catch (ClassNotFoundE xception e1) {
System.out.prin tln("ERROR - Class not found " + e1.getMessage() );
} catch (SQLException e2) {
System.out.prin tln("ERROR - " + e2.getMessage() );
System.out.prin tln("ERROR - " + e2.getSQLState( ));
} catch (Exception e3) {
System.out.prin tln("Error - " + e3.getMessage() );
}
}// close main
}// close class[/CODE]

Thanks
Red

[JosAH]

You should use a PreparedStateme nt. The way you attempt to do it now is
extremely error prone, i.e. you have your quoting wrong. PreparedStateme nts
solve that for you easily. (have another look at that very clear error diagnostic
message).

kind regards,

Jos

[redashley]

You should use a PreparedStateme nt. The way you attempt to do it now is
extremely error prone, i.e. you have your quoting wrong. PreparedStateme nts
solve that for you easily. (have another look at that very clear error diagnostic
message).

kind regards,

Jos

Yes not to sure what it means, but I have another class section where I add new info (data) and it works just fine...
I don't know if I have enought time to learn PreparedStateme nts this is due in a day...

red
PS Not to sure if the inst. wants PreparedStateme nts as well?

[redashley]

I'm not to sure if we can use Prepared statements.

I did some corrections and got rid of the headings and now I get this error message

Error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''David', 'Wie', '1', '2','3','4','5' ,'6','7','8','9 ' where empid = '234DW'' at line 1


Thanks for the help so far.
red

[JosAH]

I'm not to sure if we can use Prepared statements.

I did some corrections and got rid of the headings and now I get this error message

Error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''David', 'Wie', '1', '2','3','4','5' ,'6','7','8','9 ' where empid = '234DW'' at line 1


Thanks for the help so far.
red

When I read that error diagnostic it again seems to be a quoting problem:
before the 'D' in David and after the last 'W' at the end, shouldn't there be a
single quite too? Take it from me: Prepared statements handle all this for
you: you just supply your String values for the question marks. No explicit
quotes are needed except for the normal Java literal String quotes.

kind regards,

Jos