nhibernate-issues

[nhibernate-issues] [NHJIRA] Created: (NH-2436) Db2400 Use of parameter marker not valid

[stephane b. (JIRA) <nh...@gm...>]

Db2400 Use of parameter marker not valid
----------------------------------------

                 Key: NH-2436
                 URL: http://216.121.112.228/browse/NH-2436
             Project:  NHibernate
          Issue Type: Bug
          Components: DataProviders / Dialects
    Affects Versions: 3.0.0.CR1
            Reporter: stephane borel
            Priority: Minor


Using db2 on iSeries when running:

var query = from client in session.Query<Client>() where (client.name.Contains("XYZ")) orderby client.id select client;

the following statement is generated:

select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||?||'%')) order by client0_.ID asc

with parameter Name:p1 - Value:XYZ


This causes error
[iDB2SQLErrorException (0x80004005): SQL0418 Use of parameter marker not valid.]

The reason is that db2 cannot guess the parameter's type. The parameters should be cast to the columns parameter.

In this would generate the following statement:
select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||CAST(? as VARCHAR(255))||'%')) order by client0_.ID asc


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[nhibernate-issues] [NHJIRA] Commented: (NH-2436) Db2400 Use of parameter marker not valid

[Fabio M. (JIRA) <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20246#action_20246 ] 

Fabio Maulo commented on NH-2436:
---------------------------------

Which is the type of the parameter in the ADO.NET command ?
Can you send us the result of the SQL log ?

> Db2400 Use of parameter marker not valid
> ----------------------------------------
>
>                 Key: NH-2436
>                 URL: http://216.121.112.228/browse/NH-2436
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects, Linq Provider
>    Affects Versions: 3.0.0.CR1
>            Reporter: stephane borel
>            Priority: Minor
>
> Using db2 on iSeries when running:
> var query = from client in session.Query<Client>() where (client.name.Contains("XYZ")) orderby client.id select client;
> the following statement is generated:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||?||'%')) order by client0_.ID asc
> with parameter Name:p1 - Value:XYZ
> This causes error
> [iDB2SQLErrorException (0x80004005): SQL0418 Use of parameter marker not valid.]
> The reason is that db2 cannot guess the parameter's type. The parameters should be cast to the columns parameter.
> In this would generate the following statement:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||CAST(? as VARCHAR(255))||'%')) order by client0_.ID asc

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[nhibernate-issues] [NHJIRA] Updated: (NH-2436) Db2400 Use of parameter marker not valid

[Fabio M. (JIRA) <nh...@gm...>]

     [ http://216.121.112.228/browse/NH-2436?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Fabio Maulo updated NH-2436:
----------------------------

    Component/s: Linq Provider

> Db2400 Use of parameter marker not valid
> ----------------------------------------
>
>                 Key: NH-2436
>                 URL: http://216.121.112.228/browse/NH-2436
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects, Linq Provider
>    Affects Versions: 3.0.0.CR1
>            Reporter: stephane borel
>            Priority: Minor
>
> Using db2 on iSeries when running:
> var query = from client in session.Query<Client>() where (client.name.Contains("XYZ")) orderby client.id select client;
> the following statement is generated:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||?||'%')) order by client0_.ID asc
> with parameter Name:p1 - Value:XYZ
> This causes error
> [iDB2SQLErrorException (0x80004005): SQL0418 Use of parameter marker not valid.]
> The reason is that db2 cannot guess the parameter's type. The parameters should be cast to the columns parameter.
> In this would generate the following statement:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||CAST(? as VARCHAR(255))||'%')) order by client0_.ID asc

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[nhibernate-issues] [NHJIRA] Commented: (NH-2436) Db2400 Use of parameter marker not valid

[stephane b. (JIRA) <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20280#action_20280 ] 

stephane borel commented on NH-2436:
------------------------------------

using log4net and show_sql=true, I get

NHibernate.SQL: select client0_.ID as id1_, client0_.NAME as name1_ from DEVENV.CLIENTS client0_ where client0_.NAME like ('%'||?||'%') order by client0_.ID asc;
p0 = 'MARAIS SERVICES LLC' [Type: String (0)]

I do not have access to the SQL log on the server.


To my understanding, as DB2 is not able to guess what will be the type of the parameter when the statement is being prepared, the concatenation ('%'||?||'%') used in the like clause fails.

This impacts on the following methods: contains(), startswith(), endswith() since it uses concatenation
(there is no problem using equals() method)

The workaround would be to enclose the "?" with CAST(? as VARCHAR(size)) in the SQL statement whenever there is a concatenation involving a parameter

> Db2400 Use of parameter marker not valid
> ----------------------------------------
>
>                 Key: NH-2436
>                 URL: http://216.121.112.228/browse/NH-2436
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects, Linq Provider
>    Affects Versions: 3.0.0.CR1
>            Reporter: stephane borel
>            Priority: Minor
>
> Using db2 on iSeries when running:
> var query = from client in session.Query<Client>() where (client.name.Contains("XYZ")) orderby client.id select client;
> the following statement is generated:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||?||'%')) order by client0_.ID asc
> with parameter Name:p1 - Value:XYZ
> This causes error
> [iDB2SQLErrorException (0x80004005): SQL0418 Use of parameter marker not valid.]
> The reason is that db2 cannot guess the parameter's type. The parameters should be cast to the columns parameter.
> In this would generate the following statement:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||CAST(? as VARCHAR(255))||'%')) order by client0_.ID asc

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[nhibernate-issues] [NHJIRA] Commented: (NH-2436) Db2400 Use of parameter marker not valid

[Ted L. (JIRA) <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20768#action_20768 ] 

Ted Lum commented on NH-2436:
-----------------------------

I'm experiencing this problem as well. This is still an issue with 3.1.

> Db2400 Use of parameter marker not valid
> ----------------------------------------
>
>                 Key: NH-2436
>                 URL: http://216.121.112.228/browse/NH-2436
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects, Linq Provider
>    Affects Versions: 3.0.0.CR1
>            Reporter: stephane borel
>            Priority: Minor
>
> Using db2 on iSeries when running:
> var query = from client in session.Query<Client>() where (client.name.Contains("XYZ")) orderby client.id select client;
> the following statement is generated:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||?||'%')) order by client0_.ID asc
> with parameter Name:p1 - Value:XYZ
> This causes error
> [iDB2SQLErrorException (0x80004005): SQL0418 Use of parameter marker not valid.]
> The reason is that db2 cannot guess the parameter's type. The parameters should be cast to the columns parameter.
> In this would generate the following statement:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||CAST(? as VARCHAR(255))||'%')) order by client0_.ID asc

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[nhibernate-issues] [NHJIRA] Commented: (NH-2436) Db2400 Use of parameter marker not valid

[Carlo D. (JIRA) <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20908#action_20908 ] 

Carlo DiGiacomo commented on NH-2436:
-------------------------------------

I am using 3.1 and I am also having the same issue.

> Db2400 Use of parameter marker not valid
> ----------------------------------------
>
>                 Key: NH-2436
>                 URL: http://216.121.112.228/browse/NH-2436
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects, Linq Provider
>    Affects Versions: 3.0.0.CR1
>            Reporter: stephane borel
>            Priority: Minor
>
> Using db2 on iSeries when running:
> var query = from client in session.Query<Client>() where (client.name.Contains("XYZ")) orderby client.id select client;
> the following statement is generated:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||?||'%')) order by client0_.ID asc
> with parameter Name:p1 - Value:XYZ
> This causes error
> [iDB2SQLErrorException (0x80004005): SQL0418 Use of parameter marker not valid.]
> The reason is that db2 cannot guess the parameter's type. The parameters should be cast to the columns parameter.
> In this would generate the following statement:
> select client0_.ID as id0_, client0_.NAME as name0_ from DEVENV.CLIENTS client0_ where (client0_.NAME like ('%'||CAST(? as VARCHAR(255))||'%')) order by client0_.ID asc

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira