You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Sergio Gonzalez <sa...@sky.net.co> on 2003/11/22 02:07:13 UTC
can't set parameters in prepared statements with DBCP and
jakarta.
Hello.
I'm new to the list, so please be patient with me. :-)
I looked deeply on the net about a little problem I've trying to change my
servlets from normal sql connections (mysql thru jdbc driver), to the
pooled version using DBCP in a tomcat powered web server.
when doing a normal connection I do something like:
url = "jdbc:mysql://localhost/testdb";
Class.forName ("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection (url, "user", "password");
String query = "SELECT FIELD1 FROM TABLE1 WHERE FIELD2 = ?";
preparedStatement stmt = conn.prepareStatement(query);
stmt.setInt(1,1234567890);
ResultSet result = stmt.executeQuery();
and everything goes well. I can retrieve data from the resultset, etc; but
when using this:
Context ctx = null;
Connection conn = null;
PreparedStatement stmt = null;
DataSource ds = null;
String query = "SELECT FIELD1 FROM TABLE WHERE FIELD2 = ?";
try{
ctx = new InitialContext();
if(ctx == null )
hrow new Exception("No Context");
ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");
if (ds != null){
conn = ds.getConnection();
stmt = conn.prepareStatement(query);
stmt.setString(1,username);
stmt.setString(2,password);
ResultSet result = stmt.executeQuery();
if(respuesta.next()){
...
}else{
System.err.println("no rows retrieved");
}
}
}catch(SQLException e)
{
}
The query is executed, but I just can't get any rows. Then I looked into
the mysqld.log and I found the query was made but without any parameters.
The resultset give me rows when I do it directly, I mean:
query = "SELECT FIELD1 FROM TABLE WHERE FIELD2 = "+String.valueOf(1234567890);
What am I doing wrong?
Thanks a lot in advance for the help.
Sergio Gonzalez
sagonzal@sky.net.co
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: can't set parameters in prepared statements with DBCP and jakarta.
Posted by José Antonio Pérez Testa <ja...@indra.es>.
You are setting username and password as parameters to de
PreparedStatement, not to the Connection.
There are no results when the query is executed with the first parameter
equal to username!!
Dirk Verbeeck wrote:
> Why are you using the two stmt.setString in your second code example?
> Using the normal stmt.setInt should work just fine.
>
> -- Dirk
>
> Sergio Gonzalez wrote:
>
>> Hello.
>>
>> I'm new to the list, so please be patient with me. :-)
>>
>>
>> I looked deeply on the net about a little problem I've trying to
>> change my servlets from normal sql connections (mysql thru jdbc
>> driver), to the pooled version using DBCP in a tomcat powered web
>> server.
>>
>> when doing a normal connection I do something like:
>>
>> url = "jdbc:mysql://localhost/testdb";
>> Class.forName ("com.mysql.jdbc.Driver");
>> Connection conn = DriverManager.getConnection (url, "user", "password");
>> String query = "SELECT FIELD1 FROM TABLE1 WHERE FIELD2 = ?";
>> preparedStatement stmt = conn.prepareStatement(query);
>> stmt.setInt(1,1234567890);
>> ResultSet result = stmt.executeQuery();
>>
>> and everything goes well. I can retrieve data from the resultset,
>> etc; but when using this:
>>
>> Context ctx = null;
>> Connection conn = null;
>> PreparedStatement stmt = null;
>> DataSource ds = null;
>> String query = "SELECT FIELD1 FROM TABLE WHERE FIELD2 = ?";
>>
>> try{
>> ctx = new InitialContext();
>> if(ctx == null )
>> hrow new Exception("No Context");
>> ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");
>>
>> if (ds != null){
>> conn = ds.getConnection();
>> stmt = conn.prepareStatement(query);
>> stmt.setString(1,username);
>> stmt.setString(2,password);
>> ResultSet result = stmt.executeQuery();
>> if(respuesta.next()){
>> ...
>> }else{
>> System.err.println("no rows retrieved");
>> }
>> }
>> }catch(SQLException e)
>> {
>> }
>>
>> The query is executed, but I just can't get any rows. Then I looked
>> into the mysqld.log and I found the query was made but without any
>> parameters.
>>
>> The resultset give me rows when I do it directly, I mean:
>>
>> query = "SELECT FIELD1 FROM TABLE WHERE FIELD2 =
>> "+String.valueOf(1234567890);
>>
>> What am I doing wrong?
>>
>>
>>
>> Thanks a lot in advance for the help.
>>
>>
>>
>>
>>
>> Sergio Gonzalez
>> sagonzal@sky.net.co
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>>
>>
>>
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
-------------------------------------------------------------------------------------------------------------------
Este correo electrónico y, en su caso, cualquier fichero anexo al mismo, contiene información de carácter confidencial exclusivamente dirigida a su destinatario o destinatarios. Queda prohibida su divulgación, copia o distribución a terceros sin la previa autorización escrita de Indra. En el caso de haber recibido este correo electrónico por error, se ruega notificar inmediatamente esta circunstancia mediante reenvío a la dirección electrónica del remitente.
The information in this e-mail and in any attachments is confidential and solely for the attention and use of the named addressee(s). You are hereby notified that any dissemination, distribution or copy of this communication is prohibited without the prior written consent of Indra. If you have received this communication in error, please, notify the sender by reply e-mail
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: can't set parameters in prepared statements with DBCP and jakarta.
Posted by Dirk Verbeeck <di...@pandora.be>.
Why are you using the two stmt.setString in your second code example?
Using the normal stmt.setInt should work just fine.
-- Dirk
Sergio Gonzalez wrote:
> Hello.
>
> I'm new to the list, so please be patient with me. :-)
>
>
> I looked deeply on the net about a little problem I've trying to change
> my servlets from normal sql connections (mysql thru jdbc driver), to the
> pooled version using DBCP in a tomcat powered web server.
>
> when doing a normal connection I do something like:
>
> url = "jdbc:mysql://localhost/testdb";
> Class.forName ("com.mysql.jdbc.Driver");
> Connection conn = DriverManager.getConnection (url, "user", "password");
> String query = "SELECT FIELD1 FROM TABLE1 WHERE FIELD2 = ?";
> preparedStatement stmt = conn.prepareStatement(query);
> stmt.setInt(1,1234567890);
> ResultSet result = stmt.executeQuery();
>
> and everything goes well. I can retrieve data from the resultset, etc;
> but when using this:
>
> Context ctx = null;
> Connection conn = null;
> PreparedStatement stmt = null;
> DataSource ds = null;
> String query = "SELECT FIELD1 FROM TABLE WHERE FIELD2 = ?";
>
> try{
> ctx = new InitialContext();
> if(ctx == null )
> hrow new Exception("No Context");
> ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");
>
> if (ds != null){
> conn = ds.getConnection();
> stmt = conn.prepareStatement(query);
> stmt.setString(1,username);
> stmt.setString(2,password);
> ResultSet result = stmt.executeQuery();
> if(respuesta.next()){
> ...
> }else{
> System.err.println("no rows retrieved");
> }
> }
> }catch(SQLException e)
> {
> }
>
> The query is executed, but I just can't get any rows. Then I looked into
> the mysqld.log and I found the query was made but without any parameters.
>
> The resultset give me rows when I do it directly, I mean:
>
> query = "SELECT FIELD1 FROM TABLE WHERE FIELD2 =
> "+String.valueOf(1234567890);
>
> What am I doing wrong?
>
>
>
> Thanks a lot in advance for the help.
>
>
>
>
>
> Sergio Gonzalez
> sagonzal@sky.net.co
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org