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


Mickautsch, Alfred wrote:
-----Ursprüngliche Nachricht-----
Von: Martin Evans [mailto:martin.evans@xxxxxxxxxxxx]
Gesendet: Montag, 29. Januar 2007 09:43
An: dbi-users@xxxxxxxx
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
.

Relevant Pages

  • Re: Is there free SQL or Oracle servers for FC4?
    ... > I need t write perl script to work with one of the subject-servers. ... > will work with SQL? ... though the DBD module that is appropriate for the target database. ... use MySQL or PostgreSQL but there are DBD modules for about everything. ...
    (Fedora)
  • Re: How to execute a SQL select???? (successfully that is!)
    ... SQL table in Perl and use a returned column from that row in my Perl script. ... How can I find out EXACTLY what error (ie. SQL status and return codes) are resulting from the failed operation? ... for Mysql module: ... the Msql and Mysql modules are obsoleted by the DBI ...
    (comp.lang.perl.misc)
  • posting image data (not url) from HTML to Perlscript
    ... an HTML page to a perl script, store it in SQL 2005 (using the new ... in HTML using perl. ... Anyone got an example of how image data is posted back from a web page ... to perl script, and the accessed. ...
    (comp.lang.perl.misc)
  • read from DB and write to a text file
    ... I need to execute a qery in perl script and then write the output in a ... flat file. ... I get the output from sql like this ...
    (perl.beginners)