This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes Model to add to a database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Model to add to a database" Watch "Model to add to a database" New topic
Author

Model to add to a database

Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
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 ]
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1131

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

Joined: Mar 31, 2005
Posts: 126
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

Joined: Mar 31, 2005
Posts: 126
Trying to get this solved, would it help if I posted my SaleController.java file? and/or my database schema for my sale relation?
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

what exception did you get ? posting the stacktrace of exception would be helpful


shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
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

Joined: Aug 13, 2004
Posts: 1081

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

Joined: Mar 31, 2005
Posts: 126
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

Joined: Aug 13, 2004
Posts: 1081

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

Joined: Mar 31, 2005
Posts: 126
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

Joined: Aug 13, 2004
Posts: 1081

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
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

try statement.executeUpdate(). Sometimes drivers can be finicky with the genereal execute command
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
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 ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30361
    
150

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 ]

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
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

Joined: Aug 13, 2004
Posts: 1081


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

Joined: Mar 31, 2005
Posts: 126
I'm using Microsoft Access. I'll change the name of the variable in my database and see what happens.
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
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

Joined: Aug 13, 2004
Posts: 1081

cheers

Shailesh
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Model to add to a database