JavaServer Pages and SQL (Access)

[nick128]

I'm working on a class assignment and can't get help anywhere, that Java API doesn't have waht i need, and really it's probably a syntatical error, i don't know what i'm doing wrong. I have to put a boolean into a access DB, should be simple, but i get a variety of answers depending on how i word things. here's my code, or at least teh parts relating to this:

boolean status = Boolean.valueOf(request.getParameter("lstStatus"));
String mySQL = "INSERT INTO inventory (PID, ProductName, Quantity, CheckedOut) VALUES (" + productID + ", '" + productName + "', " + quantity + ", " + status + "')";

If i don't make the boolean a Boolean (class) then i get an incompatiable type error, which is weird item #1. If i make it a class i get this:

javax.servlet.ServletException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression 'false')'.

WTH is going on here?

 

[bunker]

Check the format of the boolean in your access table. It can be set to Yes/No (default) True/False or 0/1.

Make sure the format is True/False

 

[kamper]

I'd recommend getting a dump of the actual sql used so you know exactly what's being passed on. Then take that string to access somewhere where you can just type it in and run it. Fool around with the syntax until you get it right and then figure out how to produce that syntax from your java code.

Another solution that I prefer to use is PreparedStatement instead of Statement. I assume you're using the sun jdbc/odbc bridge? I'm not sure if there would be any differences between that and a normal driver (I've used postgres and sql server) but here's how you'd go about it:

PreparedStatement stmt = connection.prepareStatement("insert into iventory (PID, ProductName, Quantity, CheckedOut) values (?, ?, ?, ?)");
stmt.setObject(1, productID);
stmt.setObject(2, productName);
stmt.setObject(3, quantity);
stmt.setObject(4, status);
ResultSet rs = stmt.executeQuery();

That's a bit verbose but I usually abstract it with a utility method that takes a PreparedStatement and an Object[] and puts them in with a loop. When you use PreparedStatement the driver takes care of type conversion and does very important things like quote escaping for you.

 

[nick128]

ok, did what you said, ended up with this:

String productID = request.getParameter("txtID");
String productName = request.getParameter("txtName");
String quantity = request.getParameter("txtQuantity");
String status = request.getParameter("status");




// Statement stmnt = conn.createStatement();

PreparedStatement stmt = conn.prepareStatement("INSERT INTO inventory (PID, ProductName, Quantity, CheckedOut) values (?, ?, ?, ?)");
stmt.setObject(1, productID);
stmt.setObject(2, productName);
stmt.setObject(3, quantity);
stmt.setObject(4, status);
ResultSet rs = stmt.executeQuery();

now i get:

javax.servlet.ServletException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:825)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:758)
org.apache.jsp.JSPLab07.doadd_jsp._jspService(doadd_jsp.java:126)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

root cause

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:89)
org.apache.jsp.JSPLab07.doadd_jsp._jspService(doadd_jsp.java:101)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

Now what Played with converting to different data types instead of strings, nothing worked

 

[kamper]

Hmm, you used Integer for the productID and quantity and Boolean for status? The other thing to try would be to use the proper setX methods like setBoolean and setInt. Check out the javadoc at http://java.sun.com/j2se/1.5.0/docs/api...x.html?java/sql/PreparedStatement.html

But if that doesn't work it might just be the limitation of the driver. As I said, I've never used the jdbc/odbc bridge. If the PreparedStatement doesn't work then you could always just try to figure out the correct sql syntax and try to do it the way you were before.

 

[nick128]

Well, the ProductID isn't an Int, its a mixed string of letters and numbers (stupid teachers...) and boolean for the status was giving me even more issues so i gave up and made it a string. guess i'm just gonna have to raise hell in class this week until she gives us a decent explaination of inserting data. there was one powerpoint slide on it. one, and only one, and that one slide covered inserting, deleting and updating.

 

[kamper]

:evil: I'm beginning to feel a personal dislike for the prof that laid out that course material

 

[nick128]

None of us like her, we all feel sorry for the prof who actually has to teach it. She is using the old prof's powerpoints, which are filled with horrible code snippets that don't even compile. I love SQL and PHP, it's what i use at eevfd.com, but this JSP stuff is horrendous in comparison, at least as far as help and documentation is concerned. i don't have much of a problem with the code itself, Java has it's place, but there is little to no help in print for JSP. I have O'Reilly's first edition JSP book, and it doesn't help for things like this at all, it uses netbeans waaaaaaaaaaaaay too much. Oh well. 5 more weeks. Then it's back to the sunshine and getting my ass back in shape over a summer of manual labor!

 

[nick128]

blah, stupid stupid strings. after all the frustration, i discovered i had to put ' around the productID. so now i put ' around all strings, just to be safe. I ended up going through and trying to insert one field at a time, once i saw that it died on the first one i stuck it in quotes and it worked like a charm. Bloody picky, access is...

 

[kamper]

Heh. Any database, well actually sql itself, (and just about any computer language) requires that string values are in quotes. Anything outside of quotes is either an identifier or numeric.