Escape characters in torque SQL

[Sam Joseph <ga...@yha.att.ne.jp>]

Hi All,

So I mentioned this in a previous mail, but the issue has cropped up for
me again. A few days ago when I was trying to store a blob in a MySQL
database I came across the quoteAndEscapeText() function in
org.jakarta.torque.util.SqlExpression. I get the impression that this
method is called to check strings for escape characters before
performing SELECTs. It is not called during inserts or updates, since
these use prepared statements.

This method appears to replace all single quotes with two single quotes,
and depending on the database, replace a backslash with two backslashes.

In my attempts to store the blob in a mysql database with torque I was
helpfully directed to this page:

http://www.mysql.com/doc/S/t/String_syntax.html

which seems to suggest that additional escape characters are required
for mysql strings (such as backslash double quote) and also for mysql
blobs (like backslash NUL).

Even having made adjustments to take all this into account I was foiled
in my efforts to store blobs in MySQL and gave up and encoded the data
in base64.

Today I was running some tests on MSSQL that previously worked, and now
fail with single quotes in select statements, presumably because I moved
all the escape sequencing over to MySQL format.

Wouldn't it be a good idea for Torque to handle more variation in the
escape sequences used by different databases? Or am I overlooking some
other part of the code that handles this?

Thanks in advance.

CHEERS> SAM




--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: Escape characters in torque SQL

[Roger I Martin PhD <hy...@hypernexinc.com>]

Ahhh,

I use the Blob blob=rs.getBlob(1) if that result set column is a Blob
instead of rs.getString(1).  Then it can give you the data as a primitive
byte array or as a binary stream.  From there I get the byte array to other
primitive arrays  using the java.nio.* package.

> Strange I know, but necessary for my application.
Not strange at all :-)  I had a similar application for patient records
since the patient privacy act took affect.

Roger

Re: Escape characters in torque SQL

[Sam Joseph <ga...@yha.att.ne.jp>]

Hi Roger,

Actually I was able to store the blob in the MySql database using
torque, since torque already uses prepared statements to do
insert/updates. The problem I as having was performing a select. I think
it would be at least mildly difficult to modify torque so that it used
prepared statements in general for selects, since SQL selects are all
prepared dynamically.

There might be an argument for switching torque over to prepared
statements for selects that would be interesting to hear.

Anyhow I did try selecting using a prepared statement, and that also
failed. I was using the following code:

DBConnection db = Torque.getConnection( x_crit.getDbName() );
Connection connection = db.getConnection();
PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM URI
where KEY_ID = ?");
pstmt.setBytes(1, p_key_id);
ResultSet rs = pstmt.executeQuery() ;
rs.first() ;
o_cat.info("!!!!!!!!!!!!!!!!!!!!!!" + rs.getString(1)+"!!!!!!!!!!!!!!!!!");

However I still seemed to be unable to retrieve the blob with this
select. My intention was to store a public key as binary data and then
retrieve the correct row by selecting against the binary data itself.
Strange I know, but necessary for my application.

CHEERS> SAM


Roger I Martin PhD wrote:

>Hi,
>
>  
>
>>A few days ago when I was trying to store a blob in a MySQL
>>database I...
>>    
>>
>
>I am not yet applying Torque so I don't know if what I am doing is useful.
>I've been blobbing data into MySQL for quite sometime with my own home grown
>code and I don't have to deal with escape characters at all.  It seems
>escaping would hinder speed.  I simply implemented the Blob interface
>http://java.sun.com/j2se/1.4/docs/api/java/sql/Blob.html, shoving the data
>into it and then use a PreparedStatement
>http://java.sun.com/j2se/1.4/docs/api/java/sql/PreparedStatement.html and
>wham the data is in the database without escapes.  When I get a Blob back,
>it comes back in the drivers Blob implementation.  Very fast.  Is there a
>need for such code in Torque?
>
>--Roger
>
>
>
>
>  
>




--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: Escape characters in torque SQL

[Roger I Martin PhD <hy...@hypernexinc.com>]

Hi,

> A few days ago when I was trying to store a blob in a MySQL
> database I...

I am not yet applying Torque so I don't know if what I am doing is useful.
I've been blobbing data into MySQL for quite sometime with my own home grown
code and I don't have to deal with escape characters at all.  It seems
escaping would hinder speed.  I simply implemented the Blob interface
http://java.sun.com/j2se/1.4/docs/api/java/sql/Blob.html, shoving the data
into it and then use a PreparedStatement
http://java.sun.com/j2se/1.4/docs/api/java/sql/PreparedStatement.html and
wham the data is in the database without escapes.  When I get a Blob back,
it comes back in the drivers Blob implementation.  Very fast.  Is there a
need for such code in Torque?

--Roger



--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>