PrepareStatement

Thread view:

Bumsys@gmail.com - 29 Jan 2008 11:47 GMT

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?

Reply to this Message

Lew - 29 Jan 2008 13:50 GMT

> String query = "select logfiles from "
> + getFullTableName(Tables.LOG)
[quoted text clipped - 11 lines]
>
> 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".

Signature

Lew

Reply to this Message

Bumsys@gmail.com - 29 Jan 2008 14:08 GMT

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???

Reply to this Message

Bumsys@gmail.com - 29 Jan 2008 14:23 GMT

database oracle...

Reply to this Message

Martin Gregorie - 29 Jan 2008 21:26 GMT

> Why may so:
> when i get query:
[quoted text clipped - 14 lines]
>
> i get nothing from table???

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

Signature

martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

Reply to this Message

Bumsys@gmail.com - 30 Jan 2008 08:45 GMT

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???

Reply to this Message

Lew - 30 Jan 2008 15:13 GMT

> throw Exhausted Resultset!
> I changed to
[quoted text clipped - 17 lines]
> 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.

Signature

Lew

Reply to this Message

PrepareStatement

Free Magazines