-2

I have a MySQL database and want to write a row into it. The problem is that MySQL do not like my query, why? This is my code:

java.sql.Timestamp date = new java.sql.Timestamp(new java.util.Date().getTime());

for (Integer articlevalue : articlesendlist) {
    for (Integer suppliervalue : suppliersendlist) {
        connection.executeQuery("INSERT INTO Bestellungen(Bestellung_ID, Artikel_ID, Lieferant_ID, Datum, Preis) VALUES (" + maxorder + ", " + articlevalue + ", " + suppliervalue + ", " +  date + ", NULL)");
    }
}

A small description for my code. The articlesendlist contains IDs from selected values from a JTabel. The same applies to the suppliersendlist. I want to write the IDs into the table "Bestellung". The variable maxorder is the current ID for the table "Bestellung".

If you need it, the exception is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '12:45:06.164, NULL)' at line 1

Please do not comment/answer with other links, I already searched for the problem and read several sites. They do not help me or are not suitable for my problem.

  • Thank you for help
Gerret
  • 2,948
  • 4
  • 18
  • 28
  • 2
    First of all, use `PreparedStatemens` to create and execute your query as your way is prone to SQL injections. – svz Aug 26 '13 at 10:56
  • can you post the executed query statement? - missed to escape the date? id is int or varchar? try this: connection.executeQuery("INSERT INTO Bestellungen(Bestellung_ID, Artikel_ID, Lieferant_ID, Datum, Preis) VALUES (" + maxorder + ", " + articlevalue + ", " + suppliervalue + ", '" + date + "', NULL)"); – Anda Iancu Aug 26 '13 at 10:57
  • @svz You cant even know if I use them or not. I wrote myself a Method and you cant know what I m using in there... – Gerret Aug 26 '13 at 11:11
  • Could the downvoter explain pls? – Gerret Aug 26 '13 at 11:18
  • @Gerret, I do know that you are not using a `PreparedStatement` from the fact that you use `String` concatenation to build your query. – svz Aug 26 '13 at 11:42
  • @svz hmm okok and what does that? `Statement query = connection.createStatement();` – Gerret Aug 26 '13 at 11:47
  • @svz would be kind if you could anwer my question. I want to know the diffrence – Gerret Aug 26 '13 at 12:43
  • @Gerret, that is a simple `Statement`. See this link for `PreparedStatement`: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html and this one also: http://stackoverflow.com/questions/3271249/ – svz Aug 26 '13 at 14:44

2 Answers2

1

Exception is obvious isn't it.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '12:45:06.164, NULL)' at line 1

You are not using quotes around date field.

However you should really avoid executing your SQL queries like this and use PreparedStatemens for this purpose.

PreparedStatemens has specific methods like setDate, setTime, setLong, setString etc and you don't need to worry about putting right quotes in your code.

anubhava
  • 761,203
  • 64
  • 569
  • 643
  • Amm maybe it is for you obvious, not for me thats why I asked here. And with that code I posted, you are not able to know if I use PreparedStatement or not. I wrote a method and there I use them... this method is called executeQuery – Gerret Aug 26 '13 at 11:10
  • You are calling `executeQuery` by manipulating String and that will require you to use quotes around date value in your insert SQL statement. – anubhava Aug 26 '13 at 11:12
  • ?? I dont understand that comment now. That makes no sense to my previus comment. – Gerret Aug 26 '13 at 11:14
  • Accepted answer is also doing the same i.e. putting quotes around inserted values. – anubhava Aug 26 '13 at 11:15
  • ... Yes I know thats the reason why I accept it. But it dosent change something on it that your comments dosent have a conenction, but ok – Gerret Aug 26 '13 at 11:19
  • That answer has **exactly followed** what I recommended in my answer: `You are not using quotes around date field.` – anubhava Aug 26 '13 at 11:21
  • Yes but he was faster... so I accept him and I dosent said that your answer is wrong I already said 3 times that your comments doesent have a connectino and I dont know why you wrote this. At my first comment I was talking about the prepared statements... – Gerret Aug 26 '13 at 11:23
0

Try changing this line: 

connection.executeQuery("INSERT INTO Bestellungen(Bestellung_ID, Artikel_ID, Lieferant_ID, Datum, Preis) VALUES (" + maxorder + ", " + articlevalue + ", " + suppliervalue + ", " +  date + ", NULL)");

to this:

connection.executeQuery("INSERT INTO Bestellungen(Bestellung_ID, Artikel_ID, Lieferant_ID, Datum, Preis) VALUES ('" + maxorder + "','" + articlevalue + "','" + suppliervalue + "','" +  date + "','NULL')");
Paddyd
  • 1,870
  • 16
  • 26