creating java queries

[riddler3479]

I am trying to create a query in java.

I have used the following syntax to create queries:

query = "SELECT * FROM TABLE WHERE VALUE = ?";

PreparedStatement ps =
conn.prepareStatement(query);

ps.setString(1,someValue);

This will not work on jboss. Instead, I have to change all of the queries to actually have the values in the query. Does anyone know why this occurs?

query = "SELECT * FROM TABLE WHERE VALUE = '" + somValue + "'";

Thanks!

[jonlee]

You will have to show the errors. Queries work fine in all our J2EE applications so some further information will help the folks here pinpoint the problem.

[riddler3479]

Here is the code for one of the queries. It works fine with the PreparedStatement on WebLogic, but will not work in JBOSS. The applications architecture is a struts application reading/writing to oracle9i lite.


public static ReturnObject getInfo(
Connection conn, long acctNbr,
String acctTypeCode) throws SQLException {


String sqlString = "SELECT * FROM TABLE WHERE " + "ACCT_NBR = " + acctNbr + " and ACCT_TYPE_CD = '" + acctTypeCode + "'";

ps = conn.prepareStatement(sqlString);
// ps.setLong(1, acctNbr);
// ps.setString(2, acctTypeCode);
rs = ps.executeQuery();


The original code had ? marks instead of the hard coded variables. I don't get any errors in the console window or java exceptions, but the data returned with the prepared statement returns either nothing or the wrong data. After I replace the ? marks with the actual values, the query executes fine. This will produce the right data results but not as fast as prepared statements. Any help would be greatly appreciated.

Thanks!

[jonlee]

It is unlikely that JBoss is causing the problem. The JVM is involved more with the SQL JDBC execution, rather than interaction with JBoss infrastructure. I would suggest using p6spy to intercept and snoop on what is passing to the DB, since this seems to be the problem area.

You are not getting any JDBC errors so execution seems fine. The lack of errors also indicates that the set commands are also working. Unless there is something wrong with optimisations or obfuscations, this seems to be the best way to move forward. You might also try invoking a direct JDBC connection and see if there are any issues.

[milowe]

Just some more things to check.

Are you using same JDBC driver ?
Are you changing the AutoCommit flag ?

[riddler3479]

Hi-

I found out the problem. I was using a jdbc odbc driver to use java code to write to a database through ODBC. This is done so change capture can occur. However when the driver goes through ODBC it gets 'wrapped' in such a way that oracle lite doesn't understand it.

I used a direct jdbc connection, bypassing ODBC, and all the code worked fine.

So if anyone has any idea how to fix the ODBC portion, any help would be welcome.

thanks-

[jonlee]

Is there a reason you cannot use the pure JDBC driver? The JDBC-ODBC bridge is known to sometimes have problems with the mapping. I think Oracle usually recommend using the pure JDBC driver whenever possible.