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
Re: Help with JDBC query
I think you put the ) after the end of sql expression. It is not going
to make it into a parsed query.
One more suggestion - put these fields one per line - it is going to be
too hard to debug them this way - they are all on the same line.
Best regards,
Edmon Begoli
Jack Lauman 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
Re: Help with JDBC query
Actually you need to add a ) at the end:
?, ?, ?, ?));
Classic case of unmatched bracket. Inner one is for the sql string as per
Edmon and outer for your method call sql.setSqlValue( );
Doug
- Original Message -
From: Edmon Begoli [EMAIL PROTECTED]
To: Tomcat Users List tomcat-user@jakarta.apache.org
Sent: Sunday, January 23, 2005 10:36 PM
Subject: Re: Help with JDBC query
I think you put the ) after the end of sql expression. It is not going to
make it into a parsed query.
One more suggestion - put these fields one per line - it is going to be
too hard to debug them this way - they are all on the same line.
Best regards,
Edmon Begoli
Jack Lauman 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
