Results 1 to 14 of 14
  1. #1
    miko5054 is offline Member
    Join Date
    Mar 2010
    Posts
    50
    Rep Power
    0

    Default sql select ,error in your SQL syntax

    im trinig to select data by using this code...

    {code} public void printAccountId() {
    try {
    st = con.createStatement();
    ResultSet rs = st
    .executeQuery("SELECT * FROM accounts" + " WHERE accountId =? ");
    ps.setInt(1, accountAction.getAccountId());
    while (rs.next()) {
    int passCheck = rs.getInt(3);
    System.out.println(passCheck);
    }

    rs.close();
    st.close();

    } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }{code}



    if im putting a data where the ? is i can retrieve the result but i want to use the getAccountId method


    this is the error that im reciving



    {code}
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: 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 '?)' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Construc tor.java:532)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:40 9)
    at com.mysql.jdbc.Util.getInstance(Util.java:384)
    at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:3562)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:3494)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:19 60)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :2114)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2690)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2619)
    at com.mysql.jdbc.StatementImpl.executeQuery(Statemen tImpl.java:1465)
    at dbexemple.Accounts.printAccountId(Accounts.java:73 )
    at dbexemple.DataBaseActionMain.main(DataBaseActionMa in.java:24)
    {code}

    thanks miki

  2. #2
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    7

    Default

    Don't execute the query before setting the query parameter ...

  3. #3
    miko5054 is offline Member
    Join Date
    Mar 2010
    Posts
    50
    Rep Power
    0

    Default

    ok i changed the code to

    Java Code:
     public void printAccountId() {
    		try {
    			
    			String query = "SELECT	*  FROM  accounts" + " WHERE accountId =? ";
    		
    			ps = con.prepareStatement(query);
    			ps.setInt(1, accountAction.getAccountId());
    
    			ps.execute();
    			while (rs.next()) {
    				int passCheck = rs.getInt(1);
    
    				System.out.println(passCheck);
    			}
    
    			rs.close();
    			ps.close();
    
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}

    and now i get

    Java Code:
      Exception in thread "main" java.lang.NullPointerException


    evantuly what im trying to do is the print each parameter in the row of the cus id..

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Where have you defined "rs" in that code?

  5. #5
    miko5054 is offline Member
    Join Date
    Mar 2010
    Posts
    50
    Rep Power
    0

    Default

    at the start of the code under the class name

    Java Code:
      public class Accounts {
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 1L;
    	PreparedStatement ps = null;
    	Statement st = null;
    	Connection con = null;
    	ResultSet rs = null;

  6. #6
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    5

    Default

    Where is the rs defined, and did you set up a outparameter
    ie
    Java Code:
    ps.registerOutParameter(6, OracleTypes.INTEGER);
    Troubleshoot with my format for a json object

    Java Code:
    <%@ page language="java" pageEncoding="UTF-8"%>
    
    <%@ page import="com.cecs.util.*" %>
    <%@ page import="com.cecs.sample.*" %>
    <%@ page import="java.util.*" %>
    <%@ page import="java.sql.*" %>
    <%@ page import="oracle.jdbc.*" %>
    <%@ page import="org.json.*" %>
    <%
    	// Establish connection to DB and instantiate required DB objects
    	Connection con = Utility.getOracleConnection();
    	CallableStatement cs = null;
    	ResultSet rs = null;
    	
    	try {
    		// retreive the querystring parameter passed in URL name value pair
    			int survey_id = 0;
    			String comments = "";
    			
    			
    		if (request.getParameter("sui") != "") {
    			survey_id = Integer.parseInt(request.getParameter("sui"));
    		} // ends if
    		
    		
    		
    		
    		// build a Result Set Stored Proc                         //1 2 
    		String query = "BEGIN SURVEY_REVIEW_PKG.SP_SEL_CUST_COMMENTS(?,?); END;";
    		
    		// build a callable statement
    		cs = con.prepareCall(query);
    		
    		// set the SP in params
    		cs.setInt(1, survey_id);
    		
    		
    		cs.registerOutParameter(2, OracleTypes.VARCHAR);
    		
    		
    		cs.execute();
    		
    		comments = cs.getString(2); 
    		
    		RecordListSurveyDetails_Comments rl = new RecordListSurveyDetails_Comments();
    		ArrayList<RecordSurveyDetails_Comments> recordList = rl.createRecordList(rs);
    
    		    JSONObject store = new JSONObject();
    		    
    		    
    			Iterator<RecordSurveyDetails_Comments> it = recordList.iterator();
    			
    			// Instantiate the Record object for reuse
    			RecordSurveyDetails_Comments rec = null;
    
    			// Create the JSON object used to pass the resultset from Java to JavaScript
    		    JSONArray json = new JSONArray();
    			
    		    JSONObject obj = new JSONObject();
    		    obj.put("comments",	comments);
    		    json.put(obj);
    			
    		    
    			store.put("results",json);
    			
    		// Close all DB connection objects
    		Utility.closeResources(con, cs, rs);
    		
    		out.print(store);   
      		out.flush(); 
    
    	} catch (SQLException e) {
    		Utility.displayAndLogException("SQLException() - " + e.getMessage(), e);
    		Utility.closeResources(con, cs, rs);
    	} catch (Exception e) {
    		Utility.displayAndLogException("JSONException() - " + e.getMessage(), e);
    		Utility.closeResources(con, cs, rs);
    	}	
    %>
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  7. #7
    miko5054 is offline Member
    Join Date
    Mar 2010
    Posts
    50
    Rep Power
    0

    Default

    this is the entire code

    Java Code:
     package dbexemple;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class Accounts {
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 1L;
    	PreparedStatement ps = null;
    	Statement st = null;
    	Connection con = null;
    	ResultSet rs = null;
    
    	AccountsSerializable accountAction = new AccountsSerializable(21345,
    			"miki", 7954, "aaa", "aasde");
    
    	public void getConnection() {
    
    		try {
    			Class.forName("com.mysql.jdbc.Driver").newInstance();
    			con = (Connection) DriverManager.getConnection(
    					"jdbc:mysql://localhost:3306/miki", "root", "fuckyou");
    			System.err.println("oooookkkkkkkkkkkkkkkkkkkk");
    
    		} catch (Exception e) {
    			System.err.println("Exception: " + e.getMessage());
    			e.printStackTrace();
    		}
    	}
    
    	public void insertAccountsDataBase() {
    
    		try {
    			String query = "INSERT INTO accounts (accountId,cusId,amounts,credit, comment )values (?,?,?,?,?)";
    			ps = con.prepareStatement(query);
    			ps.setInt(1, accountAction.getAccountId());
    
    			ps.setString(2, accountAction.getCusId());
    			ps.setInt(3, accountAction.getAmounts());
    			ps.setString(4, accountAction.getCredit());
    			ps.setString(5, accountAction.getComment());
    
    			ps.executeUpdate();
    
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			System.out.println("updateDataBase eroor");
    			e.printStackTrace();
    
    		} finally {
    			try {
    				ps.close();
    			} catch (SQLException e) {
    				System.out.println("after close exeption");
    				e.printStackTrace();
    				System.out.println(e.getStackTrace());
    			}
    
    		}
    
    	}
    
    	public void printAccountId() {
    		try {
    			
    			String query = "SELECT	*  FROM  accounts" + " WHERE accountId =? ";
    		
    			ps = con.prepareStatement(query);
    			ps.setInt(1, accountAction.getAccountId());
    			
    			ps.execute();
    			while (rs.next()) {
    				int passCheck = rs.getInt(1);
    
    				System.out.println(passCheck);
    			}
    
    			rs.close();
    			ps.close();
    
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    
    	public void deleteRow() {
    
    		try {
    			String query = ("DELETE FROM accounts  WHERE  cusId = ?");
    
    			ps = con.prepareStatement(query);
    			// ps.(1, accountAction.getCusId());
    
    			ps.executeUpdate();
    
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			System.out.println("updateDataBase eroor");
    			e.printStackTrace();
    
    		} finally {
    			try {
    				ps.close();
    			} catch (SQLException e) {
    				System.out.println("after close exeption");
    				e.printStackTrace();
    				System.out.println(e.getStackTrace());
    			}
    		}
    
    	}
    }

    and that is the exception

    Java Code:
       oooookkkkkkkkkkkkkkkkkkkk
    Exception in thread "main" java.lang.NullPointerException
    	at dbexemple.Accounts.printAccountId(Accounts.java:78)
    	at dbexemple.DataBaseActionMain.main(DataBaseActionMain.java:24)

  8. #8
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    5

    Default

    haha nice password.

    I really feel like you need to do an out parameter
    similar to this,
    Java Code:
    cs.registerOutParameter(2, OracleTypes.VARCHAR);
    because you are looking for the value with
    Java Code:
    int passCheck = rs.getInt(1);
    but it cannot find it because you dont have the register out param. also I know if you want an out param you need two question marks, one for in and one for out
    Java Code:
     //**************************************************in | out
    String query = "BEGIN SURVEY_REVIEW_PKG.SP_SEL_CUST_COMMENTS(?,?); END;";
    Java Code:
    public void printAccountId() {
    		try {
    			
    			String query = "SELECT	*  FROM  accounts" + " WHERE accountId =? ";
    		
    			ps = con.prepareStatement(query);
    			ps.setInt(1, accountAction.getAccountId());
    			
    			ps.execute();
    			while (rs.next()) {
    				int passCheck = rs.getInt(1);
    
    				System.out.println(passCheck);
    			}
    
    			rs.close();
    			ps.close();
    
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,802
    Rep Power
    19

    Default

    Why on earth does he need an out parameter?
    This is a query, not a call to a stored procedure.
    This here:
    String query = "SELECT * FROM accounts" + " WHERE accountId =? ";

    Does not need an out parameter.

    Right, to the OP. What line does the exception occur on, because that will tell you pretty quickly what thing is null. Once we know that we can probably pretty quickly figure out what's wrong. Without that we can't...at least not easily. And you want to make life easy for us, I expect.

  10. #10
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    5

    Default

    sorry... not really a sql or database guy, I wrote my code figuring out someone elses, json object code and the guy I work with just gave me the procedure and I had an in and an out. So i figured... maybe it had a similar idea? since it comes up null

    yes.. i am a newbie college grad...

    line 78 is is null. which is..
    Java Code:
    while (rs.next())
    so his Resultset is not changing from null.
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  11. #11
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    5

    Default

    Could he try this?

    Java Code:
    rs = (ResultSet) ps.getInt(1);
    or maybe?
    Java Code:
    while (ps.next()) {
    				int passCheck = ps.getInt(1);
    
    				System.out.println(passCheck);
    			}
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  12. #12
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by miko5054 View Post
    at the start of the code under the class name

    Java Code:
      public class Accounts {
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 1L;
    	PreparedStatement ps = null;
    	Statement st = null;
    	Connection con = null;
    	ResultSet rs = null;
    No. That's where you declare it. Now, in the earlier code snippet, where did you define it? I'm trying to give you a hint, here.

  13. #13
    miko5054 is offline Member
    Join Date
    Mar 2010
    Posts
    50
    Rep Power
    0

    Default solvd

    thanks for the help!!!


    Java Code:
       	rs=ps.executeQuery();
    thats good !!!!

  14. #14
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,802
    Rep Power
    19

    Default

    Quote Originally Posted by Sno View Post
    Could he try this?

    Java Code:
    rs = (ResultSet) ps.getInt(1);
    or maybe?
    Java Code:
    while (ps.next()) {
    				int passCheck = ps.getInt(1);
    
    				System.out.println(passCheck);
    			}
    If you don't actually know JDBC then it might be an idea not answering the questions? Because none of that is correct, and it tends to muddy the waters for the person trying to get a correct answer.

Similar Threads

  1. No more syntax error
    By ideyatech in forum Java Software
    Replies: 2
    Last Post: 04-22-2010, 04:20 AM
  2. Syntax error on token
    By egun in forum New To Java
    Replies: 4
    Last Post: 02-11-2010, 10:57 AM
  3. stuck on same syntax error....
    By Moltisanti in forum New To Java
    Replies: 2
    Last Post: 09-01-2009, 04:26 AM
  4. how to solve syntax error
    By pro85 in forum Java Applets
    Replies: 5
    Last Post: 04-06-2009, 11:20 AM
  5. syntax error
    By gabriel in forum New To Java
    Replies: 3
    Last Post: 08-03-2007, 03:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •