MySQL in Java, stament with parameters

Question

fugmag 0 Newbie Poster

13 Years Ago

Hello I am trying to get a SELECT stament to work i Java. The staement has a parameter, but Eclipse gives me the following error message:

You have an error in your SQL syntax, check the manual that corresponds to your MySQL version......

Is it possible to show me how to use ' and " in the statement

String todo = ("SELECT * FROM Emp WHERE Name = '"+text1+"')")

More details:

public ResultSet connectTwo(String text1) {
		  Connection conn = null;
		  try {
		      Class.forName("com.mysql.jdbc.Driver").newInstance();
		      conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "Mg78QnuE");
		  	      
		      String todo = ("SELECT * FROM Emp WHERE Name = '"+text1+"')") ;

Thank You!

java mysql

3 Contributors
6 Replies
221 Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by masijade

masijade 1,351 Industrious Poster

13 Years Ago

Do not cobble together SQL statements like this. See the API docs and the JDBC tutorials for PreparedStatement.

masijade 1,351 Industrious Poster

13 Years Ago

Hi, I have some additional information.
private void calculateOperation()
    {
        ControllerNewYork controller = new ControllerNewYork();
        ResultSet res = controller.connectTwo(txtName.getText());
        
       String st = res.toString();
        
        System.out.println(st);
The print I get to console is
com.mysql.jdbc.JDBC4ResultSet@18a992f
What is this?
Thank You.

You do not call toString on a ResultSet. What that is the classname and the Hash Address for the object (I.E. the result of Object's toString method).

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

javinpaul -6 Newbie Poster · Answer 1 · 2011-02-08T20:11:09+00:00

Hi,

It seems that final query string which you are passing to mysql is not correctly formatted.

1.
      String todo = ("SELECT * FROM Emp WHERE Name = '"+text1+"')")

I see a closing bracket there ")" try removing that.

Also is better to output your final "todo" String before passing it to execute. then you will have clear idea whether query string is correct or not.

Thanks
Javin

fugmag 0 Newbie Poster · Answer 2 · 2011-02-09T15:04:58+00:00

Hi,
It seems that final query string which you are passing to mysql is not correctly formatted.
1.
      String todo = ("SELECT * FROM Emp WHERE Name = '"+text1+"')")
I see a closing bracket there ")" try removing that.
Also is better to output your final "todo" String before passing it to execute. then you will have clear idea whether query string is correct or not.
Thanks
Javin

Thank You Javin.
I wrote the String 'todo' to console and validated that it worked.

I have one Java class with the method connectTwo (returning a Resultset) as below:

public ResultSet connectTwo(String text1) {
		  Connection conn = null;
		  try {
		      Class.forName("com.mysql.jdbc.Driver").newInstance();
		      conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "Mg78QnuE");
		  	  
		      String todo = ("SELECT * FROM Emp WHERE Name =' "+text1+" ' ");
		      System.out.println(todo);
		      
		      Statement statement = conn.createStatement();
	          rs = statement.executeQuery(todo);
 
	            JOptionPane.showMessageDialog(null, "it worked!","Yeah",
                      JOptionPane.INFORMATION_MESSAGE);
			  
		    } catch(Exception e) {
	            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",
                      JOptionPane.ERROR_MESSAGE);
			}
		    
		    finally {
		    	try 	{
		    		if(conn != null)
		    			conn.close();
		    	} catch(SQLException e) {}
		    }
		return rs;
		}

I also have another Java class with the method calculateOperation()

private void CalculateOperation()
    {
        ControllerNewYork controller = new ControllerNewYork();
        ResultSet res = controller.connectTwo(txtName.getText());
        
        try {
			while (res.next()) {                              
				String navn = res.getString("Name");
				System.out.println(navn);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
  
        clearControls();
    }

My question is, do you think it is possible to say (as I actually do):

ControllerNewYork controller = new ControllerNewYork();
        ResultSet res = controller.connectTwo(txtName.getText());
        
        try {
			while (res.next()) {                              
				String navn = res.getString("Name");
				System.out.println(navn);

The print I get to console is:

SELECT * FROM Emp WHERE Name =' Erik Arisholm '
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:794)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7145)
at newyork.ViewNewYork.calculateOperation(ViewNewYork.java:98)
at newyork.ViewNewYork.actionPerformed(ViewNewYork.java:82)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

I wonder where I close my resultset?

(Stacktrace: Operation not allowed after ResultSet closed)

Can you see where?

Cheers

Magnus

fugmag 0 Newbie Poster · Answer 3 · 2011-02-09T15:45:55+00:00

Hi, I have some additional information.

private void calculateOperation()
    {
        ControllerNewYork controller = new ControllerNewYork();
        ResultSet res = controller.connectTwo(txtName.getText());
        
       String st = res.toString();
        
        System.out.println(st);

The print I get to console is

com.mysql.jdbc.JDBC4ResultSet@18a992f

What is this?

Thank You.

masijade 1,351 Industrious Poster Team Colleague Featured Poster · Answer 4 · 2011-02-09T15:56:45+00:00

For the "Operation Not Allowed" error, if you would take the time to look at the API docs, you would see that whenever you execute a query using a Statement object all currently opened ResultSets associated with (i.e. created using) that Statement object will be automatically closed. Use a separate statement object for any additional queries you wish to do while still using the ResultSet of another.