Database Update with SQL.
Hi all,
i have officiailly pulled all my hair out at this stage!!! I am trying to update a table called 'order' in an access database
It has 5 Fields
field1 = Number
field2 = text
field3 = text
field4 = text
field5 = Number
So far so good.
I could not insert data using the various variable names so i tried to see in i could even insert data hard coded into a statement, this is the method i am using
publicvoid setorderData( String user, String[] value, String[] quant )throws SQLException,ClassNotFoundException
{
Connection conn =null;
Statement statement =null;
......
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:orderSystem","","");
Statement st = conn.createStatement();
st.executeUpdate("INSERT INTO order " +
"VALUES (1001, 'Simpson', 'Mr.', 'Springfield', 2001)");
conn.commit();
statement.close();
}
catch (Exception e)
{
e.printStackTrace();
System.exit(1);
}
This method is called from a JSP file. Would this have any bearing on it? Once i remove the insert part of this method. It all works fine.
Here is the error messages i receive
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.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at jdbc.JDBCBean.setorderData(JDBCBean.java:105)
at desisoft_jsp_Checkout_jsp1167951969265._jspService(desisoft_jsp_Check
out_jsp1167951969265.java:120)
at desisoft.server.JspBaseClass.service(JspBaseClass.java:48)
at desisoft.server.servlets.Servlet.service(Servlet.java:86)
at desisoft.server.servlets.Servlet.doService(Servlet.java:62)
at desisoft.server.common.HttpStream.processRequest(HttpStream.java:633)
at desisoft.server.conn.ServerThread.run(ServerThread.java:328)
Any Help is very much appreciated. Its the little things like this that p**s ya off the most!!!!
[2895 byte] By [
ronana] at [2007-11-26 14:10:18]
# 1
I'm guessing that the name of your table "order" is a reserved word in Access. You should change the name of your table to a non-reserved word and try again.
# 2
i've tried it with my customer table aswell. and i have the same problem.
I am starting to think its something to do with the Java Beans and JSP files i have, there's maybe 10 or so in my order system.
But i can access and check a password in the same class in the customer table with no problems, i just can not insert a new record?
I am CON FUZZLED.
# 3
> i've tried it with my customer table aswell. and i
> have the same problem.
>
> I am starting to think its something to do with the
> Java Beans and JSP files i have, there's maybe 10 or
> so in my order system.
Nope, it's you.
> But i can access and check a password in the same
> class in the customer table with no problems, i just
> can not insert a new record?
>
> I am CON FUZZLED.
No, you just don't know Java very well.
%
# 4
> i've tried it with my customer table aswell. and i
> have the same problem.
No, that answer was correct. If you had tried that query in Access it would have told you that "order" is a reserved word (e.g., "ORDER BY"). It's part of ANSI SQL.
>
> I am starting to think its something to do with the
> Java Beans and JSP files i have, there's maybe 10 or
> so in my order system.
Couldn't have anything to do with the fact that you don't know SQL, could it? (smirk)
I created a table named order with your columns and then created a query using your SQL. When I tried to save the query Access told me "order" was a keyword. When I renamed the table to "x" and changed the query it worked fine.
A lesson for you here: Doubt yourself more. Don't automatically assume that it's something wrong with JSPs. Always assume that you're the problem. You'll get out of your difficulties faster that way.
%
# 5
WorkForFood is one of the best database people on this forum. If he tells you something, give it serious attention.%
# 6
> Hi all,
>
> i have officiailly pulled all my hair out at this
> stage!!! I am trying to update a table called 'order'
> in an access database
So while I'm in a teaching mood, tell me how you can write a method using this table that DOESN'T involve an Order object?
Why does the parameter list for this method include arrays of Strings? Why is an array called "quant", which makes me think "quantity", any type other than int?
Your JDBC code is not good. Let me count the ways:
(1) You hardwire all your connection parameters. These should be externalized from your app so you can change databases without recompiling.
(2) You create the Connection in the method that uses it. Bad idea. You should have another object create the Connection and pass it in.
(3) You perform a write operation and don't use a transaction.
(4) You call commit on the Connection, but I don't see where you set auto commit to false.
(5) You don't rollback if an exception is thrown. You actually exit the system! Laughable.
(6) You don't close your resources properly. Connections, Statements, and ResultSets should be closed in a finally block in individual try/catch blocks. Write static utility methods to do this once and for all.
(7) You don't use PreparedStatement, which would escape String and Date properly in a database-independent way. It also guards against SQL injection attacks.
(8) You put this ugly scriptlet code in a JSP, where it doesn't belong. Better to move it into a class that you can test offline, independent from the JSP. Then let the JSP instantiate it and call its methods.
%
# 7
> i've tried it with my customer table aswell. and i> have the same problem. > Order is a SQL reserved word. From ORDER BY. So Order is certainly a problem. Your customer table may have a similar problem. Or it may be something else.
# 8
Just to reiterate point 3. Your SQL should at the very least look like thisString sql = "INSERT INTO yourtablename(field1,field2,field3,field4,field5) VALUES (1001, 'Simpson', 'Mr.', 'Springfield', 2001)";
# 9
Also you should remove the conn.commit(); from your original code. Because you don't have a transaction and it should throw a SQLException because of that. Your stack trace indicates it is not getting that far but is a problem.
From the API Javadocs
public void commit()
throws SQLException
Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object. This method should be used only when auto-commit mode has been disabled.
Throws:
SQLException - if a database access error occurs or this Connection object is in auto-commit mode
# 10
thanks alot!!! i will remove it. I did not have that in it originally, i just seen its existance on another web site and after the 80th time of trying to get it working with different " + +" '+ ' update insert etc combinations in my SQL statement. i put it in. i did not really think much about it. Chances are, it would have caused an error once the SQL statement worked and i would have removed it.
# 11
> thanks alot!!! i will remove it. I did not have that
> in it originally, i just seen its existance on
> another web site and after the 80th time of trying to
> get it working with different " + +" '+ ' update
> insert etc combinations in my SQL statement. i put it
> in. i did not really think much about it. Chances
> are, it would have caused an error once the SQL
> statement worked and i would have removed it.
Okay well good luck with the changes. If it is still having problems be
sure to post an updated stack trace. Also if you have more problems it
would be helpful to know if you have a primary key field which field it is.
field1 isn't an auto-number by any chance?
# 12
> field1 isn't an auto-number by any chance?
no it was (orderID number type, orginally) but i had removed everything from the table, All keys, in case they were interfering with it in any way.
changed everything to type text and then everything to numbers. changed the SQL to suit etc etc etc etc etc.
then came here.
# 13
So now you have something completely different than what you originally asked about. If you have a problem, then, you're going to have to show the code causing the problem and the error message that you're getting now.
# 14
"I am starting to think its something to do with the Java Beans and JSP files i have, there's maybe 10 or so in my order system."
It's responses like this that make me smirk. I see another person who doesn't know Java or SQL ignoring perfectly correct advice from WorkForFood and it makes me want to belittle them.
The problem with using "order" as a table name is easy to verify without Java (I did). I took the trouble to go into Access, create a table named "order", and see that Access itself complained when I tried to run your query. Before you got your undies soiled about my alledged poor treatment you could have run the experiment for yourself to see if I was correct. There's little evidence that you did so. You thought your time was better spent throwing a tantrum to rescue what's left of your dignity.
As far as the list of other criticisms I offered, I did that because those are things that you'll really want to know if you care about doing JDBC well. There was no attempt to belittle there. I was offering you the benefit of what I've learned about JDBC. I could care less about belittling you.
To hear you decline most of them because they don't fit into your sorry little assignment is ridiculous.
You won't be getting this assignment working with my help. I'm sure you won't miss it.
%
