JDBC PreparedStatement with "?" in the column name

Question

I am using a JDBC connection to fetch data from an Access database. The database design is not my control. In the database there are columns that have "?" included in their names, for example: Open?, Paid?, and lots more.

When I try to fetch data with a PreparedStatement it gives me an error. The query is:

SELECT Open? FROM tblJobList WHERE WeekEnding=?

I also tried to use brackets like [Open?], but the result is the same.

The error I receive is "Too few parameters ..." as I am pushing only one parameter into the PreparedStatement.

I can not use normal statement because of WeekEnding=? as this value is a Timestamp and I could not manage to work it with Statement. Only prepared statement works here.

Can anyone tell me how to use these kind of column names in a PreparedStatement?

Not sure about it, but did you try passing `?` itself as the first parameter and the `WeekEnding` value as the second parameter? — Rahul, May 08 '13 at 03:01
It created one more error. Earlier it was saying its short on 1 param now its say short on 2 parameters — W A K A L E Y, May 08 '13 at 03:27

score 2 · Answer 1 · edited May 23 '17 at 12:15

2

use the " character

"SELECT \"Open?\" FROM tblJobList WHERE WeekEnding=?"

tested this against oracle and appears to work with mssqlserver

How to select a column in SQL Server with a special character in the column name?

edited May 23 '17 at 12:15

Community

1
1

answered May 08 '13 at 03:05

BevynQ

8,089
4
25
37

It doesnt give error but doesnt give back the result properly returns Column Name instead of value – W A K A L E Y May 08 '13 at 03:24
my mistake. inside quotes becomes a literal. – BevynQ May 08 '13 at 04:05
2

Good suggestion, but unfortunately it does not work for Access ODBC (I just tested it in Eclipse and got the "Too few parameters" error when I tried to `.execute()` the prepared statement) so it appears not to help @user1614217 with their problem. – Gord Thompson May 08 '13 at 21:53
1

Problem is not with query but with prepared statment. – W A K A L E Y May 09 '13 at 08:15
I havent find the solution for it. still struggling. But with my coding experience and knowledge I can tell the problem is not the SQL query its the Preparedstatments mechanism which replaces ? in the string. – W A K A L E Y May 10 '13 at 11:45

score 1 · Answer 2 · edited May 23 '17 at 11:58

Just to update this for current technologies:

While the JDBC-ODBC Bridge and Access ODBC were unable to handle a PreparedStatement with a column name containing a question mark, the UCanAccess JDBC driver handles it just fine, as can be confirmed with the following code:

String connectionUrl = "jdbc:ucanaccess://C:/Users/Public/UCanAccessTest.accdb";
Connection conn = DriverManager.getConnection(connectionUrl);
String sql = "SELECT ID, [Open?] FROM tblJobList WHERE WeekEnding=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setDate(1, java.sql.Date.valueOf("2016-01-01"));
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    System.out.printf("%d: %s%n", rs.getInt("ID"), rs.getBoolean("Open?"));
}
conn.close();

For more information on UCanAccess, see

Manipulating an Access database from Java without ODBC

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

I am not sure but you can try // to escape the special meaning of ? and to use it as a normal character. Like:

"SELECT Open//? FROM tblJobList WHERE WeekEnding=?"

You can get something similar to your problem here: Round bracket in string with JDBC prepared statement

edited May 23 '17 at 11:58

Community

1
1

answered May 08 '13 at 04:43

Shailesh Saxena

3,472
2
18
28

No it doesnt Work. Problem is not with query but with prepared statment. – W A K A L E Y May 08 '13 at 12:38

score 0 · Answer 4 · answered Jun 09 '16 at 19:22

0

Escaping quotes in MSSQL is done by a double quote, so a '' or a "" will produce one escaped ' and ", respectively.

answered Jun 09 '16 at 19:22

Panky031

425
1
5
14

JDBC PreparedStatement with "?" in the column name

4 Answers4