DBX + Oracle : CR/LF converted to LF/LF

[Stef_SM]

Hello,0

Using BDS 2006, win32.

I encounter a problem while updating a field (varchar2(2000)) on an
oracle DB, with the provided Oracle driver.

Where I run :

SQLConn.ExecuteDirect('UPDATE FOO_TABLE SET FOO_FIELD =' +
QuotedStr(Memo1.Text));

Memo1.Text can contain line feeds (CR/LF), but once executed, after
verificaitons, the field FOO_FIELD contains only LF/LF. All the CR/LF
are changed to LF/LF...

If use a TSQLQuery, with a parameter :
UPDATE FOO_TABLE SET FOO_FIELD = :MEMO1_TEXT
with TSQLQuery.ParamByName('MEMO1_TEXT').AsString := Memo1.Text;
and ExecSQL method... then FOO_FIELD contains CR/LF, that's ok in this
case.

I've no problem with SQLServer, in both cases FOO_FIELD contains CR/LF.

Does anyone know this problem and have an explanaiton or a solution to
have the sdame behavior with Oracle driver ?

Thanks.

[Bob Swart]

Hi Stef,

Post by Stef_SM
SQLConn.ExecuteDirect('UPDATE FOO_TABLE SET FOO_FIELD =' +
QuotedStr(Memo1.Text));

I assume you use the QuotedStr from SysUtils, which only makes sure that
each ' is preceded by another (escaping) ' character.

Post by Stef_SM
Memo1.Text can contain line feeds (CR/LF), but once executed, after
verificaitons, the field FOO_FIELD contains only LF/LF. All the CR/LF
are changed to LF/LF...

Very strange.

Post by Stef_SM
Does anyone know this problem and have an explanaiton or a solution to
have the sdame behavior with Oracle driver ?

It could be the Oracle dbExpress driver that ships with Delphi 2006, of
course.

What happens if you pass the SQL command without the parameter (i.e. the
same SQL text that your pass to ExecuteDirect) directly as CommandText
for the TSQLQuery? (so without parameter), just as a test...

Groetjes,
Bob Swart

[Stef_SM]

Thank you for your reply Dr Bob :)

Post by Stef_SM
SQLConn.ExecuteDirect('UPDATE FOO_TABLE SET FOO_FIELD =' +
QuotedStr(Memo1.Text));

I assume you use the QuotedStr from SysUtils, which only makes sure that each
' is preceded by another (escaping) ' character.

Yes, this is SysUtils.QuotedStr that is used.

What happens if you pass the SQL command without the parameter (i.e. the same
SQL text that your pass to ExecuteDirect) directly as CommandText for the
TSQLQuery? (so without parameter), just as a test...

SQLQuery2.CommandText := 'UPDATE FOO_TABLE SET FOO_FIELD = ' +
SysUtils.QuotedStr(Memo1.Lines.Text);

behaves like SQLConnection.ExecuteDirect... CR/LF are changed to LF/LF.

Only the use of TSQLQuery + params works, but using this solution
involves too large changes in the code.

Note that this application was migrated from BDE to DBX one year ago,
and all was correct with BDE.

[Bob Swart]

Hi Stef,

Post by Stef_SM
SQLQuery2.CommandText := 'UPDATE FOO_TABLE SET FOO_FIELD = ' +
SysUtils.QuotedStr(Memo1.Lines.Text);
behaves like SQLConnection.ExecuteDirect... CR/LF are changed to LF/LF.

So we can assume that somewhere in the Oracle dbExpress driver, the
CR/LF pairs in the SQL are changed to LF/LF.

Post by Stef_SM
Only the use of TSQLQuery + params works, but using this solution
involves too large changes in the code.

I understand.

Post by Stef_SM
Note that this application was migrated from BDE to DBX one year ago,
and all was correct with BDE.

I can only recommend trying another Oracle dbExpress driver. Perhaps the
one that ships with Delphi 2007 works better.

Having said that, I can highly recommend the dbExpress drivers made by
CoreLab. They also include DBX4 drivers that can be linked in with your
Delphi 2007 executable (that will not help your Delphi 2006 version, but
at least it's something to try).

You can download a 30-day trial edition of the CoreLab Oracle driver
from their website at www.crlab.com. It's only a small change in the
SQLConnection component to swap to the other driver (as also explained
in their readme and my Delphi Database Development paperback), and in
general their drivers perform better than the standard ones in the box
with Delphi...

Groetjes,
Bob Swart

[Stef_SM]

Post by Bob Swart
Having said that, I can highly recommend the dbExpress drivers made by
CoreLab. They also include DBX4 drivers that can be linked in with your
Delphi 2007 executable (that will not help your Delphi 2006 version, but at
least it's something to try).

We already use the drivers from CoreLab for SQLServer connections, but
still use the one shipped with Delphi 2006 for Oracle connections.

Post by Bob Swart
You can download a 30-day trial edition of the CoreLab Oracle driver from
their website at www.crlab.com. It's only a small change in the SQLConnection
component to swap to the other driver (as also explained in their readme and
my Delphi Database Development paperback), and in general their drivers
perform better than the standard ones in the box with Delphi...

I tried Oracle driver from Corelab, and it works well in both
SqlConnection.ExecuteDirect and TSqlQuery.ExecSQL + Param. So no doubt
that the driver shipped with Delphi 2006 is the problem. Is it a bug,
or do I misuse it, that's what I would like to know, if there is an
anwser.

The dark side of changing the driver is that thousands of deployed
applications have to be updated with copy of dll's. That's not so
simple...

I found a workaround, this solution is under evaluation : suppose we
make :

GoodString := StringReplace(Memo1.lines.text, #13 + #10,
SysUtils.QuotedStr('||CHR(13)||CHR(10)||'), [rfReplaceAll]);

and then :

SQLConnection.ExecuteDirect(GoodString);

This works well, and the implementation of this solution is easier than
changing SqlConnection.ExecuteDirect to TSqlQuery.ExecSQL + Param.

We are stating on the solution of changing to the Corelab driver for
the next version of our application, with other expected benefits
(performance for example).

Best regards.

[Bob Swart]

Hi Stef,

Post by Stef_SM
I tried Oracle driver from Corelab, and it works well in both
SqlConnection.ExecuteDirect and TSqlQuery.ExecSQL + Param. So no doubt
that the driver shipped with Delphi 2006 is the problem.

Yes, I figured as much.

Post by Stef_SM
GoodString := StringReplace(Memo1.lines.text, #13 + #10,
SysUtils.QuotedStr('||CHR(13)||CHR(10)||'), [rfReplaceAll]);

So you let Oracle substitute these for CR/LF again. Clever, but Oracle
will be (a bit) more busy now...

Post by Stef_SM
This works well, and the implementation of this solution is easier than
changing SqlConnection.ExecuteDirect to TSqlQuery.ExecSQL + Param.

Indeed.

It may be even easier if an updated dbExpress driver existed. The one
from Delphi 2007 for Win32 has the same name (dbxora30.dll) so if you
have a copy of Delphi 2007 for Win32 (Enterprise), you may want to give
that version of the driver a go. Just in case it's already fixed...

Post by Stef_SM
We are stating on the solution of changing to the Corelab driver for the
next version of our application, with other expected benefits
(performance for example).

Yes, certainly.

Groetjes,
Bob Swart

[Stef_SM]

So you let Oracle substitute these for CR/LF again. Clever, but Oracle will
be (a bit) more busy now...

In the situation when this occurs, I think the extra work for Oracle
will be negligible.

It may be even easier if an updated dbExpress driver existed. The one from
Delphi 2007 for Win32 has the same name (dbxora30.dll) so if you have a copy
of Delphi 2007 for Win32 (Enterprise), you may want to give that version of
the driver a go. Just in case it's already fixed...

I've no copy of Delphi 2007, but I woul'd be very interested to make
this test.

Is someone reading volonteer ?

[Bob Swart]

Hi Stef,

Post by Stef_SM
I've no copy of Delphi 2007, but I woul'd be very interested to make
this test.
Is someone reading volonteer ?

I would love to see the result of the test, but have no way to reach you
by e-mail (yet)...

Groetjes,
Bob Swart

[Stef_SM]

Post by Stef_SM
Is someone reading volonteer ?

A friend of mine made the test... and the result is that the Delphi
2007 driver (11.0.2902.10471) gives the same results than the Delphi
2006 driver (10.0.2151.25345).

Bedankt veel, Bob.

[Bob Swart]

Hi Stef,

A friend of mine made the test... and the result is that the Delphi 2007
driver (11.0.2902.10471) gives the same results than the Delphi 2006
driver (10.0.2151.25345).

Then perhaps you are willing to submit a bug report using Quality
Central - http://cc.codegear.com ??

Bedankt veel, Bob.

Graag gedaan!

Groetjes,
Bob Swart

[Stef_SM]

Then perhaps you are willing to submit a bug report using Quality Central -
http://cc.codegear.com ??

Done, Report #: 60961

Best regards.