db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "garthfielding2@juno.com" <garthfieldi...@juno.com>
Subject Torque-3.1.1 datasql problem with single quotes.
Date Fri, 25 Feb 2005 22:47:00 GMT

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


Mime
View raw message