Hard to tell but is there a space between ) and VALUES?
Bob
Sent from my Dell Axim X30
-----Original Message-----
>From: "Jack Lauman"<[EMAIL PROTECTED]>
>Sent: 1/23/05 9:32:45 PM
>To: "SERVLET-INTEREST@JAVA.SUN.COM"<SERVLET-INTEREST@JAVA.SUN.COM>
>Subject: Help with JDBC query
>
>I'm getting the following error in an insert, the update works fine.
>
>Is there a way to get a more informative error message about the error?
>
>Does anyone see a syntax error that I missed?
>
>I'm using MySQL 4.1.8 and Connector/J 3.0.16.
>
>19:13:20,906 INFO [STDOUT] -----SQLException-----
>19:13:20,906 INFO [STDOUT] SQLState: 42000
>19:13:20,921 INFO [STDOUT] Message: Syntax error or access violation
>message from server: "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 '' at line 1"
>19:13:20,921 INFO [STDOUT] Vendor: 1064
>19:13:20,937 INFO [STDOUT] descriptiveCopy:
>
>
>sql.setSqlValue("INSERT INTO Restaurant " +
>"(Name, Cuisine, ChefsName, Address_1, Address_2," +
>"Neighborhood, City, State, ZIP, Country," +
>"OfficePhone, ReservationPhone, FaxPhone, Email, Web," +
>"HandicappAccess, CreditCards, CostBreakfast, CostLunch, CostDinner," +
>"DressCode, Reservations, NonSmoking, OffStreetParking, OutsideDining," +
>"Banquet, BanquetCapacity, Catering, ServiceTypes, DeliveryService," +
>"LowCarbMenu, ChildMenu, ServesBooze, Entertainment, PhotoURL," +
>"ImageCredit, LogoURL, DescriptiveCopy, AtAGlance, NearBy," +
>"RestaurantOrder, Subscriber, SubscriptionExpired, UserID) " +
>
> "VALUES(?, ?, ?, ?, ?," +
> "?, ?, ?, ?, ?," +
> "?, ?, ?, ?, ?," +
> "?, ?, ?, ?, ?," +
> "?, ?, ?, ?, ?," +
> "?, ?, ?, ?, ?," +
> "?, ?, ?, ?, ?," +
> "?, ?, ?, ?, ?," +
> "?, ?, ?, ?");
>
>
>} else {
>
>// Update an existing restaurant
>sql.setSqlValue("UPDATE Restaurant SET " +
>"Name = ?, Cuisine = ?," +
>"ChefsName = ?, Address_1 = ?, Address_2 = ?, Neighborhood = ?, City = ?," +
>"State = ?, ZIP = ?, Country = ?, OfficePhone = ?, ReservationPhone = ?," +
>"FaxPhone = ?, Email = ?, Web = ?, HandicappAccess = ?, CreditCards = ?," +
>"CostBreakfast = ?, CostLunch = ?, CostDinner = ?, DressCode = ?,
>Reservations = ?," +
>"NonSmoking = ?, OffStreetParking = ?, OutsideDining = ?, Banquet = ?,
>BanquetCapacity = ?," +
>"Catering = ?, ServiceTypes = ?, DeliveryService = ?, LowCarbMenu = ?,
>ChildMenu = ?," +
>"ServesBooze = ?, Entertainment = ?, PhotoURL = ?, ImageCredit = ?,
>LogoURL = ?," +
>"DescriptiveCopy = ?, AtAGlance = ?, NearBy = ?" +
>"WHERE RestaurantID = ?");
>
>}
>List values = new ArrayList();
> values.add(request.getParameter("name"));
>values.add(request.getParameter("cuisine"));
>values.add(request.getParameter("chef"));
>values.add(request.getParameter("address1"));
>values.add(request.getParameter("address2"));
>values.add(request.getParameter("neighborhood"));
>values.add(request.getParameter("city"));
>values.add(request.getParameter("state"));
>values.add(request.getParameter("zip"));
>values.add(request.getParameter("country"));
>values.add(request.getParameter("officePhone"));
>values.add(request.getParameter("reservationPhone"));
>values.add(request.getParameter("fax"));
>values.add(request.getParameter("email"));
>values.add(request.getParameter("web"));
>
>int access = 0;
>String param = request.getParameter("access");
>if(param != null && param.equals("on")){
>access = 1;
>}
>values.add("" + access);
>
>/**
> * visa = 1, mc = 2, amex = 4, discover = 8, diners = 16
> * other usable id's are: 32, 64 and 128
> *
> * This may have to be changed to accomodate Java 5.0 enum
> */
>int cc = 0;
>param = request.getParameter("visa");
>if(param != null && param.equals("on")){
>cc |= 1;
>}
>
>param = request.getParameter("mastercard");
>if(param != null && param.equals("on")){
>cc |= 2;
>}
>param = request.getParameter("americanExpress");
>if(param != null && param.equals("on")){
>cc |= 4;
>}
>param = request.getParameter("discover");
>if(param != null && param.equals("on")){
>cc |= 8;
>}
>
>param = request.getParameter("diners");
>if(param != null && param.equals("on")){
>cc |= 16;
>}
>values.add("" + cc);
>
> values.add(request.getParameter("costBreakfast"));
>values.add(request.getParameter("costLunch"));
>values.add(request.getParameter("costDinner"));
>values.add(request.getParameter("dressCode"));
>values.add(request.getParameter("reservations"));
>
>int nonSmoking = 0;
>param = request.getParameter("nonSmoking");
>if(param != null && param.equals("on")){
>nonSmoking = 1;
>}
>values.add("" + nonSmoking);
>
>int offStreetParking = 0;
>param = request.getParameter("offStreetParking");
>if(param != null && param.equals("on")){
>offStreetParking = 1;
>}
>values.add("" + offStreetParking);
>
>int outsideDining = 0;
>param = request.getParameter("outsideDining");
>if(param != null && param.equals("on")){
>outsideDining = 1;
>}
>values.add("" + outsideDining);
>
>int banquet = 0;
>param = request.getParameter("banquet");
>if(param != null && param.equals("on")){
>banquet = 1;
>}
>values.add("" + banquet);
>
>values.add(request.getParameter("banquetCapacity"));
>
>int catering = 0;
>param = request.getParameter("catering");
>if(param != null && param.equals("on")){
>catering = 1;
>}
>values.add("" + catering);
>
> values.add(request.getParameter("serviceTypes"));
>
>int deliveryService = 0;
>param = request.getParameter("deliveryService");
>if(param != null && param.equals("on")){
>deliveryService = 1;
>}
>values.add("" + deliveryService);
>
>int lowCarbMenu = 0;
>param = request.getParameter("lowCarbMenu");
>if(param != null && param.equals("on")){
>lowCarbMenu = 1;
>}
>values.add("" + lowCarbMenu);
>
>int childMenu = 0;
>param = request.getParameter("childMenu");
>if(param != null && param.equals("on")){
>childMenu = 1;
>}
>values.add("" + childMenu);
>
>int servesBooze = 0;
>param = request.getParameter("servesBooze");
>if(param != null && param.equals("on")){
>servesBooze = 1;
>}
>values.add("" + servesBooze);
> values.add(request.getParameter("entertainment"));
>values.add(request.getParameter("image"));
>values.add(request.getParameter("imageCredit"));
>values.add(request.getParameter("logo"));
>values.add(request.getParameter("descriptiveCopy"));
>values.add(request.getParameter("atAGlance"));
>values.add(request.getParameter("nearBy"));
>
> if(pathInfo.equals("/restaurantUpdate")){
>
> values.add(request.getParameter("restaurant"));
>}
>
>// Add these params if this is a new restaurant
>else{
>values.add(newOrder);
>values.add(request.getParameter("userName"));
>
>int subscriber = 0;
>param = request.getParameter("subscriber");
>if(param != null && param.equals("on")){
>subscriber = 1;
>}
>values.add("" + subscriber);
>
>int subscriptionExpired = 0;
>param = request.getParameter("subscriptionExpired");
>if(param != null && param.equals("on")){
>subscriptionExpired = 1;
>}
>values.add("" + subscriptionExpired);
>}
>
>values = new ArrayList(); <-- I tried sql.setValues(values) here
>result = sql.executeUpdate(); but it also didn't work...
>
>// Get the id of the new from MySQL LAST_INSERT_ID
>// then redirect to: changeWebsites.jsp
>if(result != 0 && pathInfo.equals("/restaurantNew")){
>sql.setSqlValue("SELECT LAST_INSERT_ID()");
>sql.setValues(values);
>Result res = sql.executeQuery();
>if (res != null && res.getRowCount() > 0){
>Long id = (Long)res.getRows()[0].get("LAST_INSERT_ID");
>pathInfo = request.getContextPath();
>
> response.sendRedirect(response.encodeRedirectURL(pathInfo +
>"/secure/" + request.getParameter("redirect") + "?restaurant=" + id));
>return;
>}
>}
>}
>
>___________________________________________________________________________
>To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
>of the message "signoff SERVLET-INTEREST".
>
>Archives: http://archives.java.sun.com/archives/servlet-interest.html
>Resources: http://java.sun.com/products/servlet/external-resources.html
>LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
>
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
