By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,874 Members | 1,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,874 IT Pros & Developers. It's quick & easy.

help in SQL and PreparedStatement (error)

P: 1
This is my first attempt in SQL and PreparedStatement
I have add the PreparedStatement 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

Expand|Select|Wrap|Line Numbers
  1. public class DBAssign {
  2.  
  3. /**
  4. * @param args
  5. */
  6. static Scanner kbd;
  7.  
  8. static Connection conn = null;
  9. //adding records...
  10. public static void addRecord(String id, String fname, String lname,
  11. String street, String city, String state, String zip,
  12. String hphone, String ophone, String deptasg, int yrsemp,
  13. float mtpy) throws SQLException {
  14. // make variables
  15. PreparedStatement statement = null;
  16.  
  17. String addstring = "Insert into emptable values('" + id + "', '"
  18. + fname + "', " + lname + ", '" + street + "'" + city + "',"
  19. + state + "'," + zip + "'," + hphone + "'," + ophone + "'"
  20. + deptasg + "'," + yrsemp + "'," + mtpy + "',)";
  21.  
  22. try {
  23. statement=conn.prepareStatement("INSERT into user values(?,?)");
  24.  
  25. statement = (PreparedStatement) conn.createStatement();
  26. boolean ret = statement.execute(addstring);
  27. if (ret) {
  28. System.out.println("Updated " + statement.getUpdateCount());
  29. }//close if
  30.  
  31. } catch (SQLException e) {
  32. System.out.println("Error " + e.getMessage());
  33. throw (e);
  34. } finally {
  35. try {
  36. if (statement != null) {
  37. statement.close();
  38. }//close if
  39. } catch (SQLException e2) {
  40. System.out
  41. .println("Error freeing resources " + e2.getMessage());
  42. throw (e2);
  43. }//close e2
  44. }//close finally
  45.  
  46. }//close exception
  47.  
  48. //add to the record
  49. public static void addToTable() {
  50. // get data from keyboard
  51. String id = null;
  52. String fname; // =null;
  53. String lname;
  54. String street;
  55. String city;
  56. String state;
  57. String zip;
  58. String hphone;
  59. String ophone;
  60. String deptasg;
  61. int yrsemp;
  62. float mtpy;
  63. boolean dupkey = true;
  64.  
  65. while (dupkey == true) {
  66. System.out.println("Enter Employee ID");
  67. id = kbd.next();
  68. kbd.nextLine();
  69. dupkey = findRecord(id);
  70. if (dupkey == true) {
  71. System.out.println("Key must be unique - try again");
  72. }
  73. }
  74.  
  75. System.out.println("Enter first name");
  76. fname = kbd.next();
  77. kbd.nextLine();
  78.  
  79. System.out.println("Enter last name");
  80. lname = kbd.next();
  81. kbd.nextLine();
  82.  
  83. System.out.println("Enter street name");
  84. street = kbd.next();
  85. kbd.nextLine();
  86.  
  87. System.out.println("Enter city");
  88. city = kbd.next();
  89. kbd.nextLine();
  90.  
  91. System.out.println("Enter state");
  92. state = kbd.next();
  93. kbd.nextLine();
  94.  
  95. System.out.println("Enter zip code");
  96. zip = kbd.next();
  97. kbd.nextLine();
  98.  
  99. System.out.println("Enter home phone number");
  100. hphone = kbd.next();
  101. kbd.nextLine();
  102.  
  103. System.out.println("Enter office phone");
  104. ophone = kbd.next();
  105. kbd.nextLine();
  106.  
  107. System.out.println("Enter dept name");
  108. deptasg = kbd.next();
  109. kbd.nextLine();
  110.  
  111. System.out.println("Enter years employeed");
  112. yrsemp = kbd.nextInt();
  113. kbd.nextLine();
  114.  
  115. System.out.println("Enter month pay");
  116. mtpy = kbd.nextFloat();
  117. kbd.nextLine();
  118.  
  119. try {
  120. addRecord(id, fname, lname, street, city, state, zip, hphone,
  121. ophone, deptasg, yrsemp, mtpy);
  122. } catch (SQLException e) {
  123. System.out.println("Error adding record " + e.getMessage());
  124. }
  125.  
  126. }//close add
  127.  
  128. //List records 
  129. public static void listRecords() {
  130. PreparedStatement statement = null;
  131. ResultSet rs = null;
  132.  
  133. try {
  134. statement = (PreparedStatement) conn.createStatement();
  135. rs = statement
  136. .executeQuery("Select empid, firstname, lastname, street, city, state, zip, homephone, officephone, department, yearsemploy, monthpay");
  137. if (rs != null) {
  138. while (rs.next()) {
  139. System.out.println(rs.getString("empid") + " - "
  140. + rs.getString("firstname") + " - "
  141. + rs.getInt("lastname") + " - "
  142. + rs.getString("street") + " - "
  143. + rs.getString("city") + " - "
  144. + rs.getString("state") + " - "
  145. + rs.getString("zip") + " - "
  146. + rs.getString("hphone") + " - "
  147. + rs.getString("ophone") + " - "
  148. + rs.getString("department") + " - "
  149. + rs.getString("yearsemploy") + " - "
  150. + rs.getString("monthpay"));
  151. }// close the while loop
  152. }// close the if loop
  153.  
  154. } catch (SQLException e) {
  155. System.out.println("Error listing records: " + e.getMessage());
  156. } finally {
  157. try {
  158. if (statement != null) {
  159. statement.close();
  160. }
  161. if (rs != null) {
  162. rs.close();
  163. }
  164. } catch (SQLException e2) {
  165. System.out.println("Error freeing resource" + e2.getMessage());
  166. }
  167. }//close finally
  168.  
  169. }//close list
  170.  
  171.  
  172. //Find a record
  173. public static boolean findRecord(String empid) {
  174. boolean retval = true;
  175. PreparedStatement statement = null;
  176. ResultSet rs = null;
  177.  
  178. int countemps = 0;
  179.  
  180. try {
  181. statement = (PreparedStatement) conn.createStatement();
  182. rs = statement
  183. .executeQuery("Select count(*) as num from emptable where empid = '"
  184. + empid + "'");
  185. rs.next();
  186. if (rs == null) {
  187. retval = false;
  188. } else {
  189. countemps = rs.getInt(1);
  190. }
  191.  
  192. // System.out.println("Count "+countrecs);
  193.  
  194. if (countemps <= 0) {
  195. retval = false;
  196. }
  197.  
  198. } catch (SQLException e) {
  199. System.out.println("Error finding Employees record "
  200. + e.getMessage());
  201. } finally {
  202. try {
  203. if (statement != null) {
  204. statement.close();
  205. }
  206. if (rs != null) {
  207. rs.close();
  208. }
  209. } catch (SQLException e2) {
  210. System.out.println("Error freeing resource " + e2.getMessage());
  211. retval = true;
  212. }
  213. }//close finally
  214.  
  215. return retval;
  216.  
  217. }//close find
  218.  
  219.  
  220. public static void showMenu() {
  221. System.out.println("---------------");
  222. System.out.println("1. List employees records");
  223. System.out.println("2. Add employees record");
  224. System.out.println("3. Update employees record");
  225. System.out.println("4. Delete employees record");
  226. System.out.println("5. Exit");
  227. }
  228.  
  229. public static int getOption() {
  230. int optn = 99;
  231. while (optn > 5 || optn < 1) {
  232. System.out.println("---");
  233. System.out.println("Enter option");
  234. System.out.print("===>");
  235. optn = kbd.nextInt();
  236. }//close while
  237. return optn;
  238. }
  239.  
  240. public static void main(String[] args) {
  241. int optn = 99;
  242.  
  243. String host = "localhost";
  244. String database = "empdata";
  245. String user = "root";
  246. String pass = "";
  247.  
  248. // make keyboard object
  249. kbd = new Scanner(System.in);
  250.  
  251. String connstring = "jdbc:mysql://" + host + "/" + database + "?user="
  252. + user + "&password=" + pass;
  253.  
  254. try {
  255. Class.forName("com.mysql.jdbc.Driver").newInstance();
  256. conn = DriverManager.getConnection(connstring);
  257. while (optn != 5) {
  258. showMenu();
  259. optn = getOption();
  260.  
  261. switch (optn) {
  262. case 1:
  263. listRecords();
  264. break;
  265.  
  266. case 2:
  267. addToTable();
  268. break;
  269.  
  270. case 3:
  271. updateRecord();
  272. break;
  273.  
  274. case 4:
  275. deleteRecord();
  276.  
  277. case 5:
  278. System.out.println("OK - later Come back soon");
  279.  
  280. default:
  281. break;
  282. }//close switch
  283. }//close while
  284.  
  285. } catch (ClassNotFoundException e1) {
  286. System.out.println("ERROR - Class not found " + e1.getMessage());
  287. } catch (SQLException e2) {
  288. System.out.println("ERROR - " + e2.getMessage());
  289. System.out.println("ERROR - " + e2.getSQLState());
  290. } catch (Exception e3) {
  291. System.out.println("Error - " + e3.getMessage());
  292. }
  293. }//close main
  294. }//close class


Any help would be great.
Red
May 14 '07 #1
Share this Question
Share on Google+
6 Replies


Expert 10K+
P: 11,448
Expand|Select|Wrap|Line Numbers
  1. statement=conn.prepareStatement("INSERT into user values(?,?)");
  2.  
  3. statement = (PreparedStatement) conn.createStatement();
  4.  
At the second line you've effectively caused an exception. Read the API docs
for the PreparedStatement 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
May 14 '07 #2

P: 8
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.

Expand|Select|Wrap|Line Numbers
  1. public class DBAssign {
  2.  
  3.     /**
  4.      * @param args
  5.      */
  6.     static Scanner kbd;
  7.     static Connection conn = null;
  8.  
  9.  
  10.  
  11.     // searches for a emp id.
  12.     public static boolean findRecord(String empid) {
  13.     {
  14.         }// close finally
  15.  
  16.         return retval;
  17.  
  18.     }// close find
  19.  
  20.     // updates the a record
  21.     public static void updateRecord() {
  22.         // get record to update
  23.         String empid = null;
  24.         boolean findemp = false;
  25.         while (!findemp) {
  26.             System.out.println("Enter Employee ID to update");
  27.             empid = kbd.next();
  28.             kbd.nextLine();
  29.             findemp = findRecord(empid);
  30.             if (!findemp) {
  31.                 System.out.println("This Employee id record " + empid
  32.                         + " does not exist - try again");
  33.             }
  34.         }// close while
  35.  
  36.         // display data and request update
  37.         Statement stmt = null;
  38.         ResultSet rs = null;
  39.  
  40.         String firstname;
  41.         String lastname;
  42.         String street;
  43.         String city;
  44.         String state;
  45.         String zip;
  46.         String homephone;
  47.         String officephone;
  48.         String department;
  49.         String yearsemploy;
  50.         String monthpay;
  51.  
  52.         try {
  53.             stmt = conn.createStatement();
  54.             rs = stmt.executeQuery("Select * from emptable where empid = '"
  55.                     + empid + "'");
  56.             rs.next();
  57.             System.out.println("Item First Name is "
  58.                     + rs.getString("firstname"));
  59.             System.out.print("Enter new item name: ");
  60.             firstname = kbd.next();
  61.             kbd.nextLine();
  62.  
  63.             System.out.println("Item Last name is " + rs.getString("lastname"));
  64.             System.out.print("Enter new item name: ");
  65.             lastname = kbd.next();
  66.             kbd.nextLine();
  67.  
  68.             System.out.println("Item Street is " + rs.getString("street"));
  69.             System.out.print("Enter new item name: ");
  70.             street = kbd.next();
  71.             kbd.nextLine();
  72.  
  73.             System.out.println("Item City name is " + rs.getString("city"));
  74.             System.out.print("Enter new item name: ");
  75.             city = kbd.next();
  76.             kbd.nextLine();
  77.  
  78.             System.out.println("Item State name is " + rs.getString("state"));
  79.             System.out.print("Enter new item name: ");
  80.             state = kbd.next();
  81.             kbd.nextLine();
  82.  
  83.             System.out.println("Item Zip Code is " + rs.getString("zip"));
  84.             System.out.print("Enter new item name: ");
  85.             zip = kbd.next();
  86.             kbd.nextLine();
  87.  
  88.             System.out.println("Item Home Phone is "
  89.                     + rs.getString("homephone"));
  90.             System.out.print("Enter new item name: ");
  91.             homephone = kbd.next();
  92.             kbd.nextLine();
  93.  
  94.             System.out.println("Item Office Phone is "
  95.                     + rs.getString("officephone"));
  96.             System.out.print("Enter new item name: ");
  97.             officephone = kbd.next();
  98.             kbd.nextLine();
  99.  
  100.             System.out.println("Item Department is "
  101.                     + rs.getString("department"));
  102.             System.out.print("Enter new item name: ");
  103.             department = kbd.next();
  104.             kbd.nextLine();
  105.  
  106.             System.out.println("Item Years Employed is "
  107.                     + rs.getString("yearsemploy"));
  108.             System.out.print("Enter new item name: ");
  109.             yearsemploy = kbd.next();
  110.             kbd.nextLine();
  111.  
  112.             System.out.println("Item Monthly pay is "
  113.                     + rs.getString("monthpay"));
  114.             System.out.print("Enter new item name: ");
  115.             monthpay = kbd.next();
  116.             kbd.nextLine();
  117.  
  118.             String updatestring = "Update emptable set FirstName = '"
  119.                     + firstname + "', Last name= '" + lastname + "', Street= '"
  120.                     + street + "', City '" + city + "', State'" + state
  121.                     + "', Zip = '" + zip + "', Home Phone='" + homephone
  122.                     + "', Office Phone '" + officephone + "', Department '"
  123.                     + department + "', Years Employed'" + yearsemploy
  124.                     + "', Month Pay '" + monthpay + "' where empid = '" + empid
  125.                     + "'";
  126.  
  127.             stmt.execute(updatestring);
  128.             int updatenum = stmt.getUpdateCount();
  129.  
  130.             if (updatenum < 1) {
  131.                 System.out.println("Error on update");
  132.             } else {
  133.                 System.out.println("Updated " + updatenum + " records");
  134.             }
  135.         } catch (SQLException e) {
  136.             System.out.println("Error - " + e.getMessage());
  137.         } finally {
  138.             try {
  139.                 if (stmt != null) {
  140.                     stmt.close();
  141.                 }
  142.                 if (rs != null) {
  143.                     rs.close();
  144.                 }
  145.             } catch (SQLException e2) {
  146.                 System.out.println("Error " + e2.getMessage());
  147.             }
  148.         }// close finally
  149.  
  150.     }// close updates
  151.  
  152.  
  153.  
  154.     public static void showMenu() {
  155.         System.out.println("---------------");
  156.         System.out.println("1. List employees records");
  157.         System.out.println("2. Add employees record");
  158.         System.out.println("3. Update employees record");
  159.         System.out.println("4. Delete employees record");
  160.         System.out.println("5. Exit");
  161.     }
  162.  
  163.     public static int getOption() {
  164.         int optn = 99;
  165.         while (optn > 5 || optn < 1) {
  166.             System.out.println("---");
  167.             System.out.println("Enter option");
  168.             System.out.print("===>");
  169.             optn = kbd.nextInt();
  170.         }// close while
  171.         return optn;
  172.     }
  173.  
  174.     public static void main(String[] args) {
  175.         int optn = 99;
  176.  
  177.         String host = "localhost";
  178.         String database = "empdata";
  179.         String user = "root";
  180.         String pass = "";
  181.  
  182.         // make keyboard object
  183.         kbd = new Scanner(System.in);
  184.  
  185.         String connstring = "jdbc:mysql://" + host + "/" + database + "?user="
  186.                 + user + "&password=" + pass;
  187.  
  188.         try {
  189.             Class.forName("com.mysql.jdbc.Driver").newInstance();
  190.             conn = DriverManager.getConnection(connstring);
  191.             while (optn != 5) {
  192.                 showMenu();
  193.                 optn = getOption();
  194.  
  195.                 switch (optn) {
  196.                 case 1:
  197.                     listRecords();
  198.                     break;
  199.  
  200.                 case 2:
  201.                     addToTable();
  202.                     break;
  203.  
  204.                 case 3:
  205.                     updateRecord();
  206.                     break;
  207.  
  208.                 case 4:
  209.                     deleteRecord();
  210.                     System.out.println("That Employee is deleted");
  211.                     break;
  212.                 case 5:
  213.                     System.out.println("OK - later Come back soon");
  214.  
  215.                 default:
  216.                     break;
  217.                 }// close switch
  218.             }// close while
  219.  
  220.         } catch (ClassNotFoundException e1) {
  221.             System.out.println("ERROR - Class not found " + e1.getMessage());
  222.         } catch (SQLException e2) {
  223.             System.out.println("ERROR - " + e2.getMessage());
  224.             System.out.println("ERROR - " + e2.getSQLState());
  225.         } catch (Exception e3) {
  226.             System.out.println("Error - " + e3.getMessage());
  227.         }
  228.     }// close main
  229. }// close class
Thanks
Red
May 14 '07 #3

Expert 10K+
P: 11,448
You should use a PreparedStatement. The way you attempt to do it now is
extremely error prone, i.e. you have your quoting wrong. PreparedStatements
solve that for you easily. (have another look at that very clear error diagnostic
message).

kind regards,

Jos
May 14 '07 #4

P: 8
You should use a PreparedStatement. The way you attempt to do it now is
extremely error prone, i.e. you have your quoting wrong. PreparedStatements
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 PreparedStatements this is due in a day...

red
PS Not to sure if the inst. wants PreparedStatements as well?
May 14 '07 #5

P: 8
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
May 14 '07 #6

Expert 10K+
P: 11,448
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
May 14 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.