Model to add to a database

[Jason Kwok]

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]

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:

template.append(" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

Good Luck,
Avi.
[ April 21, 2005: Message edited by: Avi Abrami ]

[Jason Kwok]

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:

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());
System.out.println(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();
}

[Jason Kwok]

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]

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


shailesh

[Jason Kwok]

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]

you must be doing something like in your controller servlet

try
{
}
catch(SQLException se)
{
//put this line or log exception if using any logging
  se.printStackTrace();//This would go in system log
}

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

Shailesh

[Jason Kwok]

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

try
{
}
catch(SQLException se)
{
//put this line or log exception if using any logging
  se.printStackTrace();//This would go in system log
}

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]

You should get it on Tomcat console check for it.

alternatively you can do like this in your controller

try
{
}
catch(SQLException se)
{
   se.printStackTrace();
   RequestDispatcher view = request.getRequestDispatcher("error.jsp");
   request.setAttribute("SQLExc",se);
   view.forward(request, response);
}

then in JSP

Throwable throwable = null;
if(request.getAttribute("SQLExc") != null)
{
throwable = (Throwable)request.getAttribute("SQLExc");
 if(throwable != null && throwable instanceof SQLException)
 {
    SQLException se = (SQLException)throwable;
    se.printStackTrace(response.getWriter());
 }
 
}

This will print stacktrace on JSP page
[ April 21, 2005: Message edited by: Shailesh Chandra ]

[Jason Kwok]

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]

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]

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

[Jason Kwok]

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]

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.

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

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]

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:

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.executeUpdate();
}

[Shailesh Chandra]

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]

I'm using Microsoft Access. I'll change the name of the variable in my database and see what happens.

[Jason Kwok]

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]

cheers

Shailesh