• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Model to add to a database

 
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I'm using the MVC pattern, and I have a controller servlet that sends the connection and a loaded bean to it's corresponding model. For some reason when the model takes over to add to my database I get an SQL exception. What is going wrong here?

Here is the function within my model:
[ code ]
public void makeSale(Connection connection, saleBean sale) throws SQLException
{
StringBuffer template = new StringBuffer();
template.append("INSERT INTO sale (userID, date, ccType, ccNum, expiry, ccHolder, address, city, province, postal)");
template.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

PreparedStatement statement = connection.prepareStatement(template.toString());

statement.setInt(1, sale.getUserID());
statement.setString(2, sale.getDate());
statement.setString(3, sale.getCCType());
statement.setString(4, sale.getCCNumber());
statement.setString(5, sale.getExpiry());
statement.setString(6, sale.getCCHolder());
statement.setString(7, sale.getAddress());
statement.setString(8, sale.getCity());
statement.setString(9, sale.getProvince());
statement.setString(10, sale.getPostal());
statement.execute();
}
[ code ]
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jason,
You need to have a space between the end of your column list and the VALUES keyword. Compare the code below with the code you posted and spot the difference:

Good Luck,
Avi.
[ April 21, 2005: Message edited by: Avi Abrami ]
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well I made that correction, and as before it compiles just fine, but crashes when the statement tries to execute.

Here is my updated method:
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Trying to get this solved, would it help if I posted my SaleController.java file? and/or my database schema for my sale relation?
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
what exception did you get ? posting the stacktrace of exception would be helpful


shailesh
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't get a stack trace, I've programmed my controller to redirect to a basic error page if an SQLException is thrown. My controller uses try to get a database connection, and I've put a catch for that try which catches SQLExceptions. Is there a way I can view an error stack if I remove the redirect from the catch?
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
you must be doing something like in your controller servlet


unless you dont know what is exception ,how will you solve it

Shailesh
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Shailesh Chandra:
you must be doing something like in your controller servlet


unless you dont know what is exception ,how will you solve it

Shailesh



True enough!! You are right I have that very try and catch structure. All I have within the catch is

RequestDispatcher view = request.getRequestDispatcher("error.jsp");
view.forward(request, response);

So if I include the: se.printStackTrace(); where the se object is the SQLException, when I run the servlet on tomcat, it will print the exception stack? or do I have to view it elsewhere?

Thanks for your help Shailesh, I really appreciate you helping me out. Keep the tips coming!!
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You should get it on Tomcat console check for it.

alternatively you can do like this in your controller





then in JSP



This will print stacktrace on JSP page
[ April 21, 2005: Message edited by: Shailesh Chandra ]
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Awesome!! I'll try that out, just to be clear, if I just wanted to view the stack in the tomcat window, would I just put se.printStackTrace(); or would I have to do something like System.out.println(se.printStackTrace());??

Thanks again, you've been so helpful!! I'll let you know the outcome shortly.
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jason Kwok:

would I just put se.printStackTrace(); or would I have to do something like System.out.println(se.printStackTrace());??




only se.printStackTrace()will do the job

and no need to thanks here in javarach every one will work with you but not work for you
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
try statement.executeUpdate(). Sometimes drivers can be finicky with the genereal execute command
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey Shailesh,
Here's the output from the exception:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source) at infostore.models.saleModel.makeSale(saleModel.java:30) at infostore.controllers.SaleController.doPost(SaleController.java:79) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2344) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:163) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1011) at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1106) at java.lang.Thread.run(Unknown Source)

[edited to remove code tags because they were making the page unreadable]
[ April 21, 2005: Message edited by: Jeanne Boyarsky ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jason,
There's actually a syntax error in the query itself. If you take a careful look at the following two lines, you'll notice that there is no space between the close paren after the word postal and before the word value.



So the database tries to execute the following, which doesn't work.
INSERT INTO sale (userID, date, ccType, ccNum, expiry,
ccHolder, address, city, province, postal)VALUES (?, ?, ?,
?, ?, ?, ?, ?, ?, ?)
[ April 21, 2005: Message edited by: Jeanne Boyarsky ]
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually I've already made that correction, you're absolutely right though, that was one problem I originally overlooked. Here is my current makeSale method:

 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


template.append("INSERT INTO sale (userID, date , ccType, ccNum, expiry, ccHolder, address, city, province, postal)");



Jason,
Could you check the second column of query which is name as date.
Date is a datatype keyword. I am surprised if your database didn't stop you
create a columns with same name.
Could you tell us which database are you using


Shailesh
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm using Microsoft Access. I'll change the name of the variable in my database and see what happens.
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's a miracle Shailesh!! It works!! You got it... it was the date variable, I changed it to saleDate and it works now!!

Thank you so much Shailesh, and to everyone else who contributed to a final solution to my agony!!
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
cheers

Shailesh
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic