Problem with MySQL and prepared statement 1

[welshspoon]

Hiya

I am trying to carry out a query on a MySQL database and output the results as single records which can be scrolled through on a JPanel. However, when executed the code I get

SELECT q.Question, q.AnswerOne, q.AnswerTwo, q.AnswerThree, q.AnswerFour FROM te
st t, questionbank q WHERE t.? = q.QuestionID
java.sql.SQLException: 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 ''Q
uestionOne' = q.QuestionID' at line 1

The code I have written is shown below

try {
            // Load the JDBC driver
            String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
            Class.forName(driverName);

            // Create a connection to the database
            String serverName = "localhost";
            String mydatabase = "project";
            String url = "jdbc:mysql://" + serverName +  "/" + mydatabase; // a JDBC url
            String username = "root";
            String password = "Password";
            con = DriverManager.getConnection(url, username, password);

        } // end try

        catch (java.lang.Exception ex) {
            ex.printStackTrace();
        }
		ResultSet rs = null;
		        try{
		        //    String data = "QuestionOne";
		            if (QueryStmt == null) {
		                String Query = "SELECT q.Question, q.AnswerOne, q.AnswerTwo, q.AnswerThree, q.AnswerFour FROM test t, questionbank q" + " WHERE t.? = q.QuestionID";
		                System.out.println(Query);
		                QueryStmt = con.prepareStatement("SELECT q.Question, q.AnswerOne, q.AnswerTwo, q.AnswerThree, q.AnswerFour FROM test t, questionbank q" + " WHERE t.? = q.QuestionID");
		            }
		            QueryStmt.setString(1,"QuestionOne");
		            rs = QueryStmt.executeQuery();
		            while(rs.next()) {
					}

        } // end try

        catch (java.lang.Exception ex) {
            ex.printStackTrace();
        }

Thanks in advance

 

[sedj]

What is "q.?" - thats not valid SQL syntax .

In PreparedStatements, the query ( '?' ) operator is a bind variable - into which you may insert variables - but not in the way you are using it - you MUST define the constraints of the query (ie the execution plan) upfront.

Eg, valid syntax would be :

select * from table_name where field_one = ?

But you cannot use the query operator to define which fields you constrain your SQL on.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[welshspoon]

The q is questionbank

I've tried running the SQL statement outside of Java and it works fine.

Thanks for your help

 

[sedj]

I'm sorry, but that is just not valid syntax.

I know 'q' is the questionbank table - I am saying that you cannot use 'q.?' syntax - you cannot prepare a statement where a bind variable is the table field name - it is JUST NOT VALID.

You may use a bind variable for the constraint DATA - but not the constraint FIELDS.

This is valid :

PreparedStatement ps = conn.prepareStatement("select * from forum where subject LIKE ?");
This uses a bind variable for the data - you are saying - prepare me a statement where the variable data will be what to match the 'subject' field on the 'forum' table.

This is NOT valid :

PreparedStatement ps = conn.prepareStatement("select * from forum where ? LIKE ?");
You see the difference - you cannot use a bind variable for the field constraint on the query.

I have just tested this (in case I was going mad) on MySQL 4 myself.

I have been using JDBC for 5 years - I have written JDBC connection pooling software. Please believe me and reassess your understanding of JDBC bind variables.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[welshspoon]

Sorry I thought I had caused some miss understanding.

Thank you so much for your help

 

[sedj]

No probs, good luck

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk