PrepareStatement

[Bumsys]

Hello!!! I have a question:

String query = "select logfiles from "
+ getFullTableName(Tables.LOG)
+ " where ipadr = ? and application = ? and
time_started = ? for update";

PreparedStatement ps =
manager.getConnection().prepareStatement(
query);
ps.setString(1, ipAdrStr);
ps.setString(2, applStr);
ps.setTimestamp(3, sqlDate);

ResultSet rs = ps.executeQuery();

how can i get sql query that is formed by PrepareStatement?

 

[Lew]

String query = "select logfiles from "
+ getFullTableName(Tables.LOG)
+ " where ipadr = ? and application = ? and
time_started = ? for update";

PreparedStatement ps =
manager.getConnection().prepareStatement(
query);
ps.setString(1, ipAdrStr);
ps.setString(2, applStr);
ps.setTimestamp(3, sqlDate);

ResultSet rs = ps.executeQuery();

how can i get sql query that is formed by PrepareStatement?

Via 'query' - the actualized query with the positional parameters in place is
not available, because that exists only in a compiled form that is not
available to the calling program. Internally the system never generates a
string version of that query. The only query string available is the one
referenced via your 'query' variable.

If you are using log4j you can log the query with something like:

logger.debug( "Query: "+ query );
(java.util.logging: logger.fine( "Query: "+ query ); )
logger.debug( "IP addr: "+ ipAdrStr );
etc.

P.S., You only needed to post your question once.

P.P.S., It's a bad idea to embed implementation details ("Str") in variable
names ("ipAdrStr"). What if you changed it from a String to an IpAddr type?
Your variable names should be drawn from the modeled domain of discourse, not
from program implementation: "ipAddress".

 

[Bumsys]

Why may so:
when i get query:
String query = "select logfiles from " + getFullTableName(Tables.LOG)
+ " where ipadr = '" + ipAdrStr + "'";
PreparedStatement ps = manager.getConnection().prepareStatement(
query);
ResultSet rs = ps.executeQuery();

i get some info from table. But when i use:

String query = "select logfiles from " + getFullTableName(Tables.LOG)
+ " where ipadr = ?";
PreparedStatement ps = manager.getConnection().prepareStatement(
query);
ps.setString(1, ipAdrStr);
ResultSet rs = ps.executeQuery();

i get nothing from table???

 

[Bumsys]

database oracle...

 

[Martin Gregorie]

Why may so:
when i get query:
String query = "select logfiles from " + getFullTableName(Tables.LOG)
+ " where ipadr = '" + ipAdrStr + "'";
PreparedStatement ps = manager.getConnection().prepareStatement(
query);
ResultSet rs = ps.executeQuery();

i get some info from table. But when i use:

String query = "select logfiles from " + getFullTableName(Tables.LOG)
+ " where ipadr = ?";
PreparedStatement ps = manager.getConnection().prepareStatement(
query);
ps.setString(1, ipAdrStr);
ResultSet rs = ps.executeQuery();

i get nothing from table???
>

What exceptions are thrown?
How are you retrieving the rows in the result set?

 

[Bumsys]

throw Exhausted Resultset!
I changed to
String query = "select logfiles from " + getFullTableName(Tables.LOG)
+ " where ipadr like ?";
PreparedStatement ps = manager.getConnection().prepareStatement(
query);
ps.setString(1, ipAdrStr + "%");
ResultSet rs = ps.executeQuery();
and all works.
but now i have problem with date:
String query = "select logfiles from " + getFullTableName(Tables.LOG)
+ " where ipadr like ? and time_started = ?";
PreparedStatement ps = manager.getConnection().prepareStatement(
query);
ps.setString(1, ipAdrStr + "%");
ps.setTimestamp(2, sqlDate);
ResultSet rs = ps.executeQuery();
and this sql query does not work!
Error: Exhausted Resultset!
Although in database exist data.
What can I do???

 

[Lew]

throw Exhausted Resultset!
I changed to
String query = "select logfiles from " + getFullTableName(Tables.LOG)
+ " where ipadr like ?";
PreparedStatement ps = manager.getConnection().prepareStatement(
query);
ps.setString(1, ipAdrStr + "%");
ResultSet rs = ps.executeQuery();
and all works.
but now i have problem with date:
String query = "select logfiles from " + getFullTableName(Tables.LOG)
+ " where ipadr like ? and time_started = ?";
PreparedStatement ps = manager.getConnection().prepareStatement(
query);
ps.setString(1, ipAdrStr + "%");
ps.setTimestamp(2, sqlDate);
ResultSet rs = ps.executeQuery();
and this sql query does not work!
Error: Exhausted Resultset!
Although in database exist data.
What can I do???

Drop the extra question marks, for one.

Quote exact error messages and provide complete examples, even more importantly.

It looks like your data is not what you think.

Also, you don't give a clue as to the type of the variable 'sqlDate'.
(Terrible name for a variable, btw - all implementation and none of it informs
as to the domain purpose.) Is it a java.sql.Date? If so, it's not as good a
match for TIMESTAMP data (what is the SQL type? You *never* tell us these
things!) as a java.sql.Timestamp, because the JDBC driver throws away the time
part of a java.sql.Date.

Give us better information and we can deliver better insight.

 

[SNK111]

Embedding SQL in Java

For detailed information on Statement, PreparedStatement, CallableStatement you can check this link shahriarnk.com/Shahriar-N-K-Research-Embedding-SQL-in-C-Sharp-Java.html.

The article at the above location also describes how to pass embedded SQL queries, calling stored procedures, pass parameter etc from Java database applications as well as C#.NET database applications.

Shahriar Nour Khondokar: shahriarnk.com/