db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject Re: Torque-3.1.1 datasql problem with single quotes.
Date Sat, 26 Feb 2005 19:07:41 GMT




Hi,

This is not as easy as you put it. Problem ist that different databases use
different escape characters to create a single quote in a string. There are
many databases which double the single quote, but e.g. mysql uses a
backslash, and postgresql uses both.

Can you please open a scarab issue on this ?
(http://issues.apache.org/scarab/issues/)

@the other developers
Does anybody know if (and if yes, where) the escape character for Strings
can be found ? I found nothing in org.apache.torque.engine.platform in
HEAD, and also nothing in the db.props in the 3_1_branch.

   Thanks,

         Thomas


"garthfielding2@juno.com" <garthfielding2@juno.com> schrieb am 25.02.2005
23:47:00:

>
> Using Torque-3.1.1 and attempting to work with the tutorial from
>
> (http://db.apache.org/torque/tutorial/index.html) I decided to attempt
> using an XML file to pre-populate the tables with default data using
> "maven torque:datasql" and "maven torque:insert-sql".
>
> The problem occurred with the following attribute in my -data.xml file:
> Title="The Hobbit (Leatherette Collector's Edition)"
>
> The existing system would replace the single-quote ( ' ) with a
> backslash-quote ( \' ) instead of two single-quotes ( '' ) as expected
> by the database (in my case SQL Server).  Everywhere that I have seen
> uses two single-quotes to escape a SQL statement that contains a single
> quote within a string, such as the following:  INSERT INTO book
> (...,title,...) VALUES (...,'The Hobbit (Leatherette Collector''s
> Edition)',...);
>
> The problem lies in the following file:
> torque-3.1.1-
>
src\src\generator\src\java\org\apache\torque\engine\database\transform\XmlToData.

> java
>
> public String getEscapedValue()
> {
>   StringBuffer sb = new StringBuffer();
>   sb.append("'");
>   sb.append(StringUtils.replace(val, "'", "\\'"));
>   sb.append("'");
>   return sb.toString();
> }
>
> This is what I changed it to in order to get it to work properly:
>
> public String getEscapedValue()
> {
>   StringBuffer sb = new StringBuffer();
>   sb.append("'");
>   sb.append(StringUtils.replace(val, "'", "''")); //<-- Changed from \'
to ''
>   sb.append("'");
>   return sb.toString();
> }
>
> I thought you would want to update this in your project.
>
> Thanks, Garth.
>
>
>
>
>
>
>
>
>
>
> p.s., here is my schema, dtd, and data XML files in case you want to
> quickly reproduce this.
> //The *-schema.xml
> <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
> <!DOCTYPE database SYSTEM
> "http://db.apache.org/torque/dtd/database_3_1.dtd">
>
> <database
> name="Bookstore"
> defaultIdMethod="idbroker">
>
> <table name="book" description="Book Table">
>   <column
>     name="book_id"
>     required="true"
>     primaryKey="true"
>     type="INTEGER"
>     description="Book Id"/>
>   <column
>     name="title"
>     required="true"
>     type="VARCHAR"
>     size="255"
>     description="Book Title"/>
>   <column
>     name="isbn"
>     required="true"
>     type="VARCHAR"
>     size="24"
>     javaName="ISBN"
>     description="ISBN Number"/>
>   <column
>     name="publisher_id"
>     required="true"
>     type="INTEGER"
>     description="Foreign Key Publisher"/>
>   <column
>     name="author_id"
>     required="true"
>     type="INTEGER"
>     description="Foreign Key Author"/>
>   <foreign-key foreignTable="publisher">
>     <reference
>       local="publisher_id"
>       foreign="publisher_id"/>
>   </foreign-key>
>   <foreign-key foreignTable="author">
>     <reference
>       local="author_id"
>       foreign="author_id"/>
>   </foreign-key>
> </table>
> <table name="publisher" description="Publisher Table">
>   <column
>     name="publisher_id"
>     required="true"
>     primaryKey="true"
>     type="INTEGER"
>     description="Publisher Id"/>
>   <column
>     name="name"
>     required="true"
>     type="VARCHAR"
>     size="128"
>     description="Publisher Name"/>
> </table>
> <table name="author" description="Author Table">
>   <column
>     name="author_id"
>     required="true"
>     primaryKey="true"
>     type="INTEGER"
>     description="Author Id"/>
>   <column
>     name="first_name"
>     required="true"
>     type="VARCHAR"
>     size="128"
>     description="First Name"/>
>   <column
>     name="last_name"
>     required="true"
>     type="VARCHAR"
>     size="128"
>     description="Last Name"/>
> </table>
> </database>
>
>
> //The *-data.dtd file
> <!ELEMENT dataset (
>   Book|
>   Publisher|
>   Author)*>
> <!ATTLIST dataset
>   name CDATA #REQUIRED
> >
>
> <!ELEMENT Book EMPTY>
> <!ATTLIST Book
>   BookId CDATA #REQUIRED
>   Title CDATA #REQUIRED
>   ISBN CDATA #REQUIRED
>   PublisherId CDATA #REQUIRED
>   AuthorId CDATA #REQUIRED
> >
>
> <!ELEMENT Publisher EMPTY>
> <!ATTLIST Publisher
>   PublisherId CDATA #REQUIRED
>   Name CDATA #REQUIRED
> >
>
> <!ELEMENT Author EMPTY>
> <!ATTLIST Author
>   AuthorId CDATA #REQUIRED
>   FirstName CDATA #REQUIRED
>   LastName CDATA #REQUIRED
> >
>
>
>
>
> //The *-data.xml
> <?xml version="1.0" encoding="UTF-8" standalone="no" ?>
> <dataset name="all">
> <!-- Publishers -->
> <Publisher PublisherId="1" Name="Houghton Mifflin"/>
> <Publisher PublisherId="2" Name="Scholastic Paperbacks"/>
> <Publisher PublisherId="3" Name="Arthur A. Levine Books"/>
>
> <!-- Authors -->
> <Author AuthorId="1" FirstName="J.R.R." LastName="Tolkien"/>
> <Author AuthorId="2" FirstName="J.K." LastName="Rowling"/>
>
> <!-- Books -->
> <Book BookId="1" Title="The Hobbit (Leatherette Collector's Edition)"
> ISBN="0395177111" PublisherId="1" AuthorId="1"/>
> <Book BookId="2" Title="The Lord of the Rings (Leatherette Collector's
> Edition)" ISBN="0395193958" PublisherId="1" AuthorId="1"/>
>
> <Book BookId="3" Title="Harry Potter and the Goblet of Fire (Book 4)"
> ISBN="0439139600" PublisherId="2" AuthorId="2"/>
> <Book BookId="4" Title="Harry Potter and the Order of the Phoenix
> (Book 5)" ISBN="043935806X" PublisherId="3" AuthorId="2"/>
> </dataset>
>
>
> ___________________________________________________________________
> Speed up your surfing with Juno SpeedBand.
> Now includes pop-up blocker!
> Only $14.95/month -visit http://www.juno.com/surf to sign up today!
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message