Recordset returns NO data nothing looks wrong

[akakira]

This code returns no data, but I know the query works in access, does anyone see the problem here?

String myquery="";
Connection fcpCon=null;
Statement fcpSt=null;
DataSource fcpDS=null;
Context fcpCxt=null;
ResultSet fcpRS=null;
System.out.println("Calculating closest point");
try{
fcpCxt=new InitialContext();
fcpDS=(DataSource) fcpCxt.lookup("java:comp/env/jdbc/nyshore");
fcpCon=fcpDS.getConnection();

}catch(SQLException e){System.out.println(e.getMessage());}
catch(Exception e){System.out.println("Error with SQL execute "+e.getMessage());}

try{
if(fcpCon!=null){
fcpSt=fcpCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
myquery="SELECT DISTINCT Sqr((("+getInputLat()+"-Center_X)*("+getInputLat()+"-Center_X))+(("+getInputLong()+"-Center_Y)*("+getInputLong()+"-Center_Y))) AS mydist,Center_X,Center_Y FROM tbl_Photo WHERE Center_X IS NOT NULL order by 1";
fcpRS=fcpSt.executeQuery(myquery);

fcpRS.first();

if(fcpRS==null){
System.out.println("Closest point data was null using default value");
}
else{
setInputLat(fcpRS.getDouble("Center_X"));
setInputLong(fcpRS.getDouble("Center_Y"));
System.out.println("new input lat"+fcpRS.getDouble("Center_X"));
System.out.println("new input long"+fcpRS.getDouble("Center_Y"));
}
}//end if fcpCon!=null
}catch(SQLException sqle){System.out.println("SQL ERROR "+sqle.getMessage());}
catch(Exception e){System.out.println("Error finding closest point");}

try{
if(fcpRS!=null)fcpRS.close();
if(fcpSt!=null)fcpSt.close();
if(fcpCon!=null)fcpCon.close();
}catch(SQLException sqle){System.out.println("SQL ERROR Closing Connections "+sqle.getMessage());}
catch(Exception e){System.out.println("Error finding closest point");}

 

[sedj]

Why call

fcpRS.first();

?


The usual way to use a ResultSet object is :

while (resultSet.next() {
 // access result set
}

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[akakira]

I only need the closest point which is why the distance formula is input into the database through sql using next() or first() would have been the same both return no data, but thanks for mentioning that.

 

[stefanwagner]

Can you do a

System.out.println (myquery);

and run the generated statement in an sql-frontend?

And what is the symptom for 'no data'?
Is fcpRS==null ?
Do you get an exception?

I would change:

catch(Exception e){System.out.println("Error finding closest point");}

which is useless to

catch(Exception e)
{System.err.println ("Error finding closest point: " + e.getMessage ());}

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung