You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user-cs@ibatis.apache.org by Chen Jin <cj...@helpathome.com> on 2008/02/01 16:50:02 UTC

how to escape # in the sql statement?

Hi All,

 

I am now in a funny situation. I am using IBatis.NET to connect to a legacy
database system, which there is a column which name contains a "#".
IBatis.NET seems tried to think the # is the started of an in-line
parameter. I use parameter map rather than inline parameter and I even use
quotas to quote the field name but it still does not work (the exception
says "Unrecognized parameter mapping field:."). I check the document there
is no such escape character to escape the "#". Can any one help?

 

Chen Jin

RE: how to escape # in the sql statement?

Posted by Chen Jin <cj...@helpathome.com>.

Sure. Here it is (I am using Odbc 2.0 provider to connect a DataFlex
database)

 

<?xml version="1.0" encoding="utf-8" ?>

<sqlMap namespace="Billing"

      xmlns="http://ibatis.apache.org/mapping"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >

 

      <alias>

            <typeAlias alias="Invoice" type=" Billing.Data.ClientInvoice,
Billing" />

            <typeAlias alias="InvoiceDetail" type="
Billing.Data.ClintInvoiceDetail, Billing" />

    <typeAlias alias="BillingParam" type=" Billing.BillingParameter,
Billing" />

  </alias>

 

  <parameterMaps>

    <parameterMap id="BillingParameters" class="BillingParam">

      <parameter property="Period"/>

      <parameter property="Supplement"/>

    </parameterMap>  

  </parameterMaps>

  

      <resultMaps>

            <resultMap id="InvoiceResult" class="Invoice">

      <result property="InvoiceNumber" column="INVOICE#" />

      <result property="Contract" column="CONTRACT" />

      <result property="CentryPeriod" column="CENTPERIOD" />

      <result property="SupplementNo" column="SUPPLEMENT_NO" />

      <result property="ClientNumber" column="CLIENT_NUMBER" />

      <result property="ClientName" column="CLIENT_NAME" />

      <result property="SSN" column="CLIENT_SSN" />

      <result property="Hours" column="HOURS_RECEIVED" />

      <result property="Amount" column="AMOUNT_BILLED" />

      <result property="Type" column="TYPE" />

      <result property="Status" column="STATUS" />

      </resultMap>

            

            <resultMap id="DetailResult" class="InvoiceDetail">

      <result property="InvoiceNumber" column="INVOICE#" />

      <result property="DateFrom" column="DATEFROM" />

      <result property="DateTo" column="DATETO" />

      <result property="Hours" column="HOURS" />

      <result property="Amount" column="AMOUNT" />

      <result property="BillCode" column="BILLCODE" />

            </resultMap>

      </resultMaps>

      

      <statements>

    <select id="GetInvoicesForDetroitAAABilling"
parameterMap="BillingParameters" resultMap="InvoiceResult" >

      <![CDATA[

        SELECT I.INVOICE#, I.CONTRACT, I.CENTPERIOD, I.SUPPLEMENT_NO,
I.CLIENT_NUMBER, 

          C.LAST_NAME + ', ' + C.FIRST_NAME AS CLIENT_NAME, C.SSN AS
CLIENT_SSN, 

          I.HOURS_RECEIVED, I.AMOUNT_BILLED, I.TYPE, I.STATUS

        FROM INVOICE I, CLIENT C

        WHERE I.CLIENT_NUMBER = C.CLIENT_NUMBER

          AND I.CONTRACT IN ('C', 'D', 'E', 'F')

          AND I.PERIOD = ?

          AND I.SUPPLEMENT_NO = ?

          AND I.AMOUNT_BILLED > 0

      ]]>

    </select>

 

    <select id="GetInvoiceDetailsByInvoiceNumber" parameterClass="Int"
resultMap="DetailResult">

      SELECT INVOICE#, DATEFROM, DATETO, HOURS, AMOUNT, BILLCODE

      FROM INVDTL

      WHERE INVOICE# = #value#

    </select>

      </statements>

 

</sqlMap>

 

 

Chen Jin

Application Developer
Help At Home, Inc.
1 N. State Street
Suite 1500
Chicago, IL 60602

Phone: (312)704-0081
Toll free: (800)404-3191
Fax: (312)704-1111

http://www.helpathome.com <http://www.helpathome.com/> 

CONFIDENTIALITY NOTICE:  This e-mail and any files or attachments
transmitted with it are confidential and may be protected by legal
privilege.  This e-mail is intended solely for the use of the individual or
entity to whom this e-mail is addressed.  If you are not the intended
recipient of this e-mail, you are hereby notified that any use,
dissemination, distribution or copying of this e-mail is strictly
prohibited.  If you have received this e-mail in error, please notify the
sender immediately by e-mail and delete this e-mail from your system.

  _____  

From: Michael Schall [mailto:mike.schall@gmail.com] 
Sent: Friday, February 01, 2008 4:29 PM
To: user-cs@ibatis.apache.org
Subject: Re: how to escape # in the sql statement?

 

Can you post your actual sqlmap xml so we can see what you are trying to do?

On 2/1/08, Chen Jin <cj...@helpathome.com> wrote:

I tried ## and ####, neither works. However, I think that is great we can
have a new syntax to avoid that. Thanks!

 

Chen Jin

  _____  

From: Clinton Begin [mailto:clinton.begin@gmail.com] 
Sent: Friday, February 01, 2008 11:32 AM
To: user-cs@ibatis.apache.org
Subject: RE: how to escape # in the sql statement?

 

Yeah, JDEdwards?  :-)

 

But yes, you just double the character ## or $$

 

I'm proposing a new syntax for iBATIS 3.x:   #{...} and ${...} respectively.
Thoughts and comments welcome.

 

Clinton

 

 

 

 

From: Michael Schall [mailto:mike.schall@gmail.com] 
Sent: February-01-08 9:11 AM
To: user-cs@ibatis.apache.org
Subject: Re: how to escape # in the sql statement?

 

I'm pretty sure you just double the character... I know if you want a $ in
the query, you double it.

http://www.mail-archive.com/user-cs@ibatis.apache.org/msg01897.html

On Feb 1, 2008 9:50 AM, Chen Jin <cj...@helpathome.com> wrote:

Hi All,

 

I am now in a funny situation. I am using IBatis.NET to connect to a legacy
database system, which there is a column which name contains a "#".
IBatis.NET seems tried to think the # is the started of an in-line
parameter. I use parameter map rather than inline parameter and I even use
quotas to quote the field name but it still does not work (the exception
says "Unrecognized parameter mapping field:."). I check the document there
is no such escape character to escape the "#". Can any one help?

 

Chen Jin

Re: how to escape # in the sql statement?

Posted by Michael Schall <mi...@gmail.com>.

Can you post your actual sqlmap xml so we can see what you are trying to do?

On 2/1/08, Chen Jin <cj...@helpathome.com> wrote:
>
> I tried ## and ####, neither works. However, I think that is great we can
> have a new syntax to avoid that. Thanks!
>
>
>
> Chen Jin
> ------------------------------
>
> *From:* Clinton Begin [mailto:clinton.begin@gmail.com]
> *Sent:* Friday, February 01, 2008 11:32 AM
> *To:* user-cs@ibatis.apache.org
> *Subject:* RE: how to escape # in the sql statement?
>
>
>
> Yeah, JDEdwards?  :-)
>
>
>
> But yes, you just double the character ## or $$
>
>
>
> I'm proposing a new syntax for iBATIS 3.x:   #{...} and ${...}
> respectively.  Thoughts and comments welcome.
>
>
>
> Clinton
>
>
>
>
>
>
>
>
>
> *From:* Michael Schall [mailto:mike.schall@gmail.com]
> *Sent:* February-01-08 9:11 AM
> *To:* user-cs@ibatis.apache.org
> *Subject:* Re: how to escape # in the sql statement?
>
>
>
> I'm pretty sure you just double the character... I know if you want a $ in
> the query, you double it.
>
> http://www.mail-archive.com/user-cs@ibatis.apache.org/msg01897.html
>
> On Feb 1, 2008 9:50 AM, Chen Jin <cj...@helpathome.com> wrote:
>
> Hi All,
>
>
>
> I am now in a funny situation. I am using IBatis.NET to connect to a
> legacy database system, which there is a column which name contains a "#".
> IBatis.NET seems tried to think the # is the started of an in-line
> parameter. I use parameter map rather than inline parameter and I even use
> quotas to quote the field name but it still does not work (the exception
> says "Unrecognized parameter mapping field:…"). I check the document there
> is no such escape character to escape the "#"… Can any one help?
>
>
>
> Chen Jin
>
>
>
>
>

RE: how to escape # in the sql statement?

Posted by Chen Jin <cj...@helpathome.com>.

I tried ## and ####, neither works. However, I think that is great we can
have a new syntax to avoid that. Thanks!

 

Chen Jin

  _____  

From: Clinton Begin [mailto:clinton.begin@gmail.com] 
Sent: Friday, February 01, 2008 11:32 AM
To: user-cs@ibatis.apache.org
Subject: RE: how to escape # in the sql statement?

 

Yeah, JDEdwards?  :-)

 

But yes, you just double the character ## or $$

 

I'm proposing a new syntax for iBATIS 3.x:   #{...} and ${...} respectively.
Thoughts and comments welcome.

 

Clinton

 

 

 

 

From: Michael Schall [mailto:mike.schall@gmail.com] 
Sent: February-01-08 9:11 AM
To: user-cs@ibatis.apache.org
Subject: Re: how to escape # in the sql statement?

 

I'm pretty sure you just double the character... I know if you want a $ in
the query, you double it.

http://www.mail-archive.com/user-cs@ibatis.apache.org/msg01897.html

On Feb 1, 2008 9:50 AM, Chen Jin <cj...@helpathome.com> wrote:

Hi All,

 

I am now in a funny situation. I am using IBatis.NET to connect to a legacy
database system, which there is a column which name contains a "#".
IBatis.NET seems tried to think the # is the started of an in-line
parameter. I use parameter map rather than inline parameter and I even use
quotas to quote the field name but it still does not work (the exception
says "Unrecognized parameter mapping field:."). I check the document there
is no such escape character to escape the "#". Can any one help?

 

Chen Jin

RE: how to escape # in the sql statement?

Posted by Clinton Begin <cl...@gmail.com>.

Yeah, JDEdwards?  :-)

 

But yes, you just double the character ## or $$

 

I'm proposing a new syntax for iBATIS 3.x:   #{...} and ${...} respectively.
Thoughts and comments welcome.

 

Clinton

 

 

 

 

From: Michael Schall [mailto:mike.schall@gmail.com] 
Sent: February-01-08 9:11 AM
To: user-cs@ibatis.apache.org
Subject: Re: how to escape # in the sql statement?

 

I'm pretty sure you just double the character... I know if you want a $ in
the query, you double it.

http://www.mail-archive.com/user-cs@ibatis.apache.org/msg01897.html

On Feb 1, 2008 9:50 AM, Chen Jin <cj...@helpathome.com> wrote:

Hi All,

 

I am now in a funny situation. I am using IBatis.NET to connect to a legacy
database system, which there is a column which name contains a "#".
IBatis.NET seems tried to think the # is the started of an in-line
parameter. I use parameter map rather than inline parameter and I even use
quotas to quote the field name but it still does not work (the exception
says "Unrecognized parameter mapping field:."). I check the document there
is no such escape character to escape the "#". Can any one help?

 

Chen Jin

RE: how to escape # in the sql statement?

Posted by Clinton Begin <cl...@gmail.com>.

Then you need 4....

 

colname####

 

 

Clinton

 

From: Chen Jin [mailto:cjin@helpathome.com] 
Sent: February-01-08 9:47 AM
To: user-cs@ibatis.apache.org
Subject: RE: how to escape # in the sql statement?

 

Thanks! However, it seems this does not work. Now the driver told me the
column, which has 2 #s (XXX##), could not be found.

 

Chen Jin

  _____  

From: Michael Schall [mailto:mike.schall@gmail.com] 
Sent: Friday, February 01, 2008 10:11 AM
To: user-cs@ibatis.apache.org
Subject: Re: how to escape # in the sql statement?

 

I'm pretty sure you just double the character... I know if you want a $ in
the query, you double it.

http://www.mail-archive.com/user-cs@ibatis.apache.org/msg01897.html

On Feb 1, 2008 9:50 AM, Chen Jin <cj...@helpathome.com> wrote:

Hi All,

 

I am now in a funny situation. I am using IBatis.NET to connect to a legacy
database system, which there is a column which name contains a "#".
IBatis.NET seems tried to think the # is the started of an in-line
parameter. I use parameter map rather than inline parameter and I even use
quotas to quote the field name but it still does not work (the exception
says "Unrecognized parameter mapping field:."). I check the document there
is no such escape character to escape the "#". Can any one help?

 

Chen Jin

RE: how to escape # in the sql statement?

Posted by Chen Jin <cj...@helpathome.com>.

Thanks! However, it seems this does not work. Now the driver told me the
column, which has 2 #s (XXX##), could not be found.

 

Chen Jin

  _____  

From: Michael Schall [mailto:mike.schall@gmail.com] 
Sent: Friday, February 01, 2008 10:11 AM
To: user-cs@ibatis.apache.org
Subject: Re: how to escape # in the sql statement?

 

I'm pretty sure you just double the character... I know if you want a $ in
the query, you double it.

http://www.mail-archive.com/user-cs@ibatis.apache.org/msg01897.html

On Feb 1, 2008 9:50 AM, Chen Jin <cj...@helpathome.com> wrote:

Hi All,

 

I am now in a funny situation. I am using IBatis.NET to connect to a legacy
database system, which there is a column which name contains a "#".
IBatis.NET seems tried to think the # is the started of an in-line
parameter. I use parameter map rather than inline parameter and I even use
quotas to quote the field name but it still does not work (the exception
says "Unrecognized parameter mapping field:."). I check the document there
is no such escape character to escape the "#". Can any one help?

 

Chen Jin

Re: how to escape # in the sql statement?

Posted by Michael Schall <mi...@gmail.com>.

I'm pretty sure you just double the character... I know if you want a $ in
the query, you double it.

http://www.mail-archive.com/user-cs@ibatis.apache.org/msg01897.html

On Feb 1, 2008 9:50 AM, Chen Jin <cj...@helpathome.com> wrote:

>  Hi All,
>
>
>
> I am now in a funny situation. I am using IBatis.NET to connect to a
> legacy database system, which there is a column which name contains a "#".
> IBatis.NET seems tried to think the # is the started of an in-line
> parameter. I use parameter map rather than inline parameter and I even use
> quotas to quote the field name but it still does not work (the exception
> says "Unrecognized parameter mapping field:…"). I check the document there
> is no such escape character to escape the "#"… Can any one help?
>
>
>
> Chen Jin
>
>
>