You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by "garthfielding2@juno.com" <ga...@juno.com> on 2005/02/25 23:47:00 UTC
Torque-3.1.1 datasql problem with single quotes.
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
Re: Torque-3.1.1 datasql problem with single quotes.
Posted by Thomas Fischer <fi...@seitenbau.net>.
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" <ga...@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