The other thing could be that you have quotes when you are setting the
parameter. Normally; you do not need to add the quotes explicitly as
Toplink can deduce the correct parameter type and send the correct
actual value the DBMS expects. Hence; the actual SQL issued could be:
SELECT * FROM container WHERE contid = '''Tank00000001''' AND
siteid = 'YoniCntr' AND > subcontid > 0 ORDER BY subcontid ASC
To remove the quotes; just change the statement
em.createNativeQuery(sql.toString(),
Container.class).setParameter(1, "'"+contid+"'").getResultList();
to
em.createNativeQuery(sql.toString(),
Container.class).setParameter(1,
contid).getResultList();
- Ravi
-----Original Message-----
From: Tom Ware [mailto:tom.ware_at_oracle.com]
Sent: Thursday, May 14, 2009 6:04 AM
To: persistence_at_glassfish.dev.java.net
Subject: Re: SQL Parameter Binding - how to enable?
Hi,
It looks like parameter binding is working. The "bind =>
['Tank00000001']"
string in your logging output gives that impression.
If you execute the same query in your DB query, does it work? (i.e.
use
'Tank00000001' in your DB query rather than 'Tank00000000'.
If that does not help, it would be a good idea to increase the
logging level
to FINEST and take a look at the logging information when your session
logs in
to ensure you are logging into the database as you expect.
-Tom
yonestar wrote:
> Hello,
>
> I can't seem to get parameter binding to work! Does it need to be
enabled?
> I am using Glassfish, Toplink, NetBeans, and Derby -- the standard
NetBeans
> package.
>
> MY JAVA:
> StringBuffer sql = new StringBuffer("SELECT * FROM container WHERE
contid =
> ?1 AND ...... ");
> em.createNativeQuery(sql.toString(), Container.class).setParameter(1,
> "'"+contid+"'").getResultList();
>
> MY PERSISTANCE.XML:
> <property name="toplink.bind-all-parameters" value="true"/>
> <property name="toplink.jbdc.bind-parameters" value="true"/>
>
> SQL THAT GETS EXECUTED:
> SELECT * FROM container WHERE contid = ? AND siteid = 'YoniCntr' AND
> subcontid > 0 ORDER BY subcontid ASC
> bind => ['Tank00000001']
>
>
> And I get no results!
> I have written the exact same query without parameter binding, which
gets
> executed like
> SELECT * FROM container WHERE contid = 'Tank00000000' AND siteid =
> 'YoniCntr' AND subcontid > 0 ORDER BY subcontid ASC
>
> And results do return!
>
> Please advise, many thanks!