Prepared Statement vs. select

[Faiz Abdelhafid]

Hello folks,

I am trying to use a prepared statement to select a particular row or a set of rows (does not matter ! ). I could use prepared statement to update rows but not to retrieve them from a MySQL Database.
I used parametric statements in other languages like php and asp, it must be a solution with JDBC, to get a result from a parametric query.

   

 
PreparedStatement pstmt;

pstmt=DBManager.openDBConnection().prepareStatement("SELECT ENAME=? FROM emp WHERE EMPNO=?");


 ResultSet rs=pstmt.executeQuery();



            while(rs.next()){
          
           pstmt.setString(1, "MARTIN");
           pstmt.setInt(2, 7654);

        

           System.out.println(rs.getString(2));
           
            }



 

The code runs without errors but also without results !

waiting for your comments

Thanks !

[Paul Clapham]

Well, that looks like invalid SQL to me. I don't know what it's supposed to return, I can't even figure out what the column names in the resulting table would be.

But besides that, you do need to set the parameters for the PreparedStatement before you execute the query.

[Faiz Abdelhafid]

Hello

My query is something like

 select EMPNAME FROM emp WHERE EMPNO = [parameter]  

parameter is an integer value like 2093, 2039, etc.

to clarify more my request:
If I would re-write it in another language it would be like the following:

  Query="select ENAME FROM emp WHERE DEPTNO='"' & parameter & ""  

what do you suggest ?

Thank you

[Paul Clapham]

I suggest you set the parameters for the PreparedStatement before you execute the query. In fact I believe I already suggested that.

[Faiz Abdelhafid]

Hello,

Yes in fact you mentioned that before, but you also wrote that my sql query is invalid.

besides the parameters where is the problem ?

Thanks

[Ireneusz Kordal]

Faiz Abdelhafid wrote:
Yes in fact you mentioned that before, but you also wrote that my sql query is invalid.
besides the parameters where is the problem ?

Hi,

here is your code:

SELECT ENAME=? FROM emp WHERE EMPNO=?

so your query is like this:

SELECT ENAME='parameter1'
FROM emp 
WHERE EMPNO='parameter2'

I am not an SQL expert, but as I know, comparison (logical) operator '=' is allowed in expressions after a WHERE/HAVING keywords,
but is not allowed after a SELECT keyword.
I tried to run this query on Oracle and got an error: "FROM keyword not found where expected"

[Faiz Abdelhafid]

Hello,

I already found the correct syntax. First, I thought that Java parametric Queries are just like any other language, but it's a little bit different from PHP and ASP.

  "SELECT [COL] FROM [TABLE] WHERE [COL]" + "in(param1,param2,..)""   

Best Regards,

[Jinglong Wang]

May be you code is wrong..
I think it's like this:

PreparedStatement pstmt;

pstmt=DBManager.openDBConnection().prepareStatement("SELECT ENAME FROM emp WHERE ENAME =? and EMPNO=?");
pstmt.setString(1, "MARTIN");
pstmt.setInt(2, 7654);
ResultSet rs=pstmt.executeQuery();

while(rs.next()){
           System.out.println(rs.getString(2));       
}

remember try catch the exception and throw out so that you can know where the wrong..