JDBC INSERT PreparedStatement into Access

[LenRI]

I'm having issues with the PreparedStatement when connecting to a MS Access DB wither through the JDBC-ODBC bridge or through a thirdy party level 3 JDBC driver. What I have at this point looks like something along these lines:

Connection con = DriverManager.getConnection(url, username, password);

ResultSet rs;
Statement stat = con.createStatement();

String query = "INSERT (ABC) INTO TEST VALUES(?)";
PreparedStatement pstat = con.prepareStatement(query);

pstat.setInt(1, 1);
pstat.executeUpdate();

When I execute it this, the code throws an SQLException referring to a sytax error:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6879)

at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7036)

at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3104)

at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)

at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:136)

at TCPREdition.main(TCPREdition.java:32)

Exception in thread "main"



What's going on here?

 

[palbano]

>> What's going on here?

Your trying to execute a SQL Statement dynamically from code before you even tested it in the database environment.


-pete

 

[JVZ]

Just to add to pete's response - The problem is with your SQL Statement, the correct would be:

INSERT INTO table_name
VALUES (value1, value2,....)

You can also specify the columns for which you want to insert data:

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)


so try doing the following:

String query = "INSERT INTO TEST VALUES(?)";

For more information please check out:

http://www.w3schools.com/sql/default.asp

Hope this helps.

 

[IsraelZa]

I don't know much about JDBC but I'm just guessing, without testing, that the problem might be because you omitted a terminator (semicolon or another character) after the SQL statement, which most RDB's require.

 

[smilyface]

try running this code segement.

ResultSet rs;
Statement stat = con.createStatement();

String query = &quot;INSERT INTO TEST(<column_name>,[<column_name>]) VALUES(?)&quot;;
PreparedStatement pstat = con.prepareStatement(query);

pstat.setInt(1, 1);
pstat.executeUpdate();

Basically your sql query is wrong.
The correct query would be as follows:
String query = &quot;INSERT INTO TEST(ABC) VALUES(?)&quot;;
[/b]
ABC would be your column name and the column in which u want to insert will be of number type.

 

[LenRI]

yeah i got the fact that the sql was wrong shortly after i posted the message. thank you all for replying