Re: AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

Mickautsch, Alfred wrote:
>> -----Ursprüngliche Nachricht-----
>> Von: Martin Evans [mailto:martin.evans@easysoft.com]
>> Gesendet: Montag, 29. Januar 2007 09:43
>> An: dbi-users@perl.org
>> Betreff: Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server
> [...]
>> As no one else seems to have replied to you, if you can send a schema
>> and working Perl script using DBD::ODBC that demonstrates the problem
>> I'll take a look.
>>
>> Martin
> [...]
>
> Thank you for your response Martin,
>
> I have attached a ZIP file with the SQL script for creating the table with the NCLOB(ntext) field and a perl script which inserts a text into this table. You should edit the perl script to suit your environment.
>
> Thank you for your help.
>
> Servus -- Alfred

When I run this I do appear to getless chrs back than I put in but on
further investigation you are not putting the right number in.

The first issue is the back slashes in the here document.

my $text = <<_EOF;
hello\\\\
_EOF
print length($text), "\n";

prints 8 not 10 because \\ in a here document ends up as \. So half of
your '\\\\' go straight away to '\\'.

I have to admit to not being able to run your code on Windows at the
moment but from UNIX to SQL Server via various drivers we have the
following and they all work as expected:

isql -v install_dsn
SQL> insert into test_ntext values ('\\\\');
SQL> select * from test_ntext;
| \\\\ |

and in Perl:

use DBI;
$h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
$s = $h->prepare(q/insert into test_ntext values(?)/);
$f = q/\\\\/;
$s->execute($f);

isql -v install_dsn
SQL> select * from test_ntext;
| \\ |

returns \\ because Perl itself turned q/\\\\/ to '\\' before passing it
to the driver.

Also with newlines, they appear to be kept:

use DBI;
$h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
$s = $h->prepare(q/insert into test_ntext values(?)/);
$f = <<_EOF;
\\\\
\\\\
_EOF
$s->execute($f);
isql -v install_dsn
SQL> select * from test_ntext;
| \\
\\|

NOTE, those newlines above are UNIX new lines (in otherwords line feeds)
and I note the file you sent had dos line endings CR/LF
so I'm not sure you are losing the new lines but I am sure your losing
half of your back slashes down to Perl.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

RE: AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

Martin Evans [mailto:martin.evans@easysoft.com] wrote:
>
> The first issue is the back slashes in the here document.
>
> my $text = <<_EOF;
> hello\\\\
> _EOF
> print length($text), "\n";
>
> prints 8 not 10 because \\ in a here document ends up as \.
> So half of
> your '\\\\' go straight away to '\\'.

You can use a single-quoted here-doc if you want literal backslashes:

my $text = <<'_EOF';
hello\\\\
_EOF
print length($text), "\n";

prints 10.

A single-quoted here-doc is the only way in Perl to turn off absolutely all
interpolation.

Ronald

Re: AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

Martin Evans wrote:
>
> Mickautsch, Alfred wrote:
>>> -----Ursprüngliche Nachricht-----
>>> Von: Martin Evans [mailto:martin.evans@easysoft.com]
>>> Gesendet: Montag, 29. Januar 2007 09:43
>>> An: dbi-users@perl.org
>>> Betreff: Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server
>> [...]
>>> As no one else seems to have replied to you, if you can send a schema
>>> and working Perl script using DBD::ODBC that demonstrates the problem
>>> I'll take a look.
>>>
>>> Martin
>> [...]
>>
>> Thank you for your response Martin,
>>
>> I have attached a ZIP file with the SQL script for creating the table
>> with the NCLOB(ntext) field and a perl script which inserts a text
>> into this table. You should edit the perl script to suit your
>> environment.
>>
>> Thank you for your help.
>>
>> Servus -- Alfred
>
> When I run this I do appear to getless chrs back than I put in but on
> further investigation you are not putting the right number in.
>
> The first issue is the back slashes in the here document.
>
> my $text = <<_EOF;
> hello\\\\
> _EOF
> print length($text), "\n";
>
> prints 8 not 10 because \\ in a here document ends up as \. So half of
> your '\\\\' go straight away to '\\'.
>
> I have to admit to not being able to run your code on Windows at the
> moment but from UNIX to SQL Server via various drivers we have the
> following and they all work as expected:
>
> isql -v install_dsn
> SQL> insert into test_ntext values ('\\\\');
> SQL> select * from test_ntext;
> | \\\\ |
>
> and in Perl:
>
> use DBI;
> $h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
> $s = $h->prepare(q/insert into test_ntext values(?)/);
> $f = q/\\\\/;
> $s->execute($f);
>
> isql -v install_dsn
> SQL> select * from test_ntext;
> | \\ |
>
> returns \\ because Perl itself turned q/\\\\/ to '\\' before passing it
> to the driver.
>
> Also with newlines, they appear to be kept:
>
> use DBI;
> $h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
> $s = $h->prepare(q/insert into test_ntext values(?)/);
> $f = <<_EOF;
> \\\\
> \\\\
> _EOF
> $s->execute($f);
> isql -v install_dsn
> SQL> select * from test_ntext;
> | \\
> \\|
>
> NOTE, those newlines above are UNIX new lines (in otherwords line feeds)
> and I note the file you sent had dos line endings CR/LF
> so I'm not sure you are losing the new lines but I am sure your losing
> half of your back slashes down to Perl.
>
> Martin

Sorry, forgot to say. The inserted text is 100001 chrs according to
length in Perl and the returned chr length is 97145. I had 1486 lines
containing \\\\ and got 97145 chrs back so that suggests all I lost was
every \\\\ turned into \\ (i.e. no loss of line feeds).

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com