SQL Parameter Binding - how to enable?

[yonestar]

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!

[tware]

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!

[Gordon Yorke-2]

The parameter value you are passing to the query is "Tank00000001" but
when you write the SQL yourself you are using "Tank00000000".  Are you
sure data exists for when the parameter is "Tank00000001"?
--Gordon

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

[Kulkarni, Ravi]

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@...]
Sent: Thursday, May 14, 2009 6:04 AM
To: persistence@...
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!

[yonestar]

Kulkarni,

That was it!  No quotes needed.

Many thanks,
Yoni

Kulkarni, Ravi wrote:

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:[e-mail]]
Sent: Thursday, May 14, 2009 6:04 AM
To: [e-mail]
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!