Re: Help with JDBC query

[Richard Yee]

You are missing the "VALUES" clause before the open parenthesis.
-Richard
At 07:32 PM 1/23/2005, you wrote:
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