DBI/ODBC Truncating Long Fields

[LostS]

OK I am having problems with my ODBC call truncating Long fields that are 4000+ characters long and has \n's and/or \r's in the field. Here is my code:

#!c:\perl\bin

use DBI;

$user = "user";

$password = "password";

my $dbh = DBI->connect("dbi:ODBC:Remedy", $user, $password) or die "Ca
+n't connect to Remedy: $DBI::errstr";

my $sth = $dbh->prepare( q{SELECT ISSUE_NUMBER, SUBMISSION_DATE, ASSIG
+NED_TO, ISSUE_PRIORITY, STATUS, FULL_DESCRIPTION, ISSUE_DUE_DATE, ISS
+UE_CLOSE_DATE, RESOLUTION_PROCESS_NOTES, ORIGINATOR FROM "BMS:ITS Iss
+ue Creation"}) or die "Can't prepare statement: $DBI::errstr";

my $rc = $sth->execute or die "Can't execute statement: $DBI::errstr";

print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n";
print "Field names: @{ $sth->{NAME} }\n";

open (FILE, ">test.txt");

while (($issue_num, $sub_date, $assigned_to, $issue_priority, $status,
+ $full_desc, $issue_due_date, $issue_close_date, $res_notes, $origina
+tor) = $sth->fetchrow_array) {
    print FILE "$issue_num\n";
    print FILE "$sub_date\n";
    print FILE "$assigned_to\n";
    print FILE "$issue_priority\n";
    print FILE "$status\n";
    print FILE "$full_desc\n";
    print FILE "$issue_due_date\n";
    print FILE "$issue_close_date\n";
    print FILE "$res_notes\n";
    print FILE "$originator\n";
    print FILE "------------------------------------------------\n\n";
}

close (FILE);

# check for problems which may have terminated the fetch early
die $sth->errstr if $sth->err;

$dbh->disconnect;

exit;

Now for DB Info:

ISSUE_NUMBER - VARCHAR2(15)
SUBMISSION_DATE - NUMBER(15)
ASSIGNED_TO - VARCHAR2(30)
ISSUE_PRIORITY - VARCHAR2(20)
STATUS - VARCHAR2(20)
FULL_DESCRIPTION - VARCHAR2(4000)
ISSUE_DUE_DATE - NUMBER(15)
ISSUE_CLOSE_DATE - NUMBER(15)
RESOLUTION_PROCESS_NOTES - CLOB
ORIGINATOR - VARCHAR2(30)
Any suggestions??

-----------------------
Billy S.
Slinar Hardtail - Hand of Dane
Datal Ephialtes - Guildless
RallosZek.Net Admin/WebMaster

perl -e '$cat = "cat"; if ($cat =~ /\143\x61\x74/) { print "Its a cat!
+\n"; } else { print "Thats a dog\n"; } print "\n";'

[dws]

I am having problems with my ODBC call truncating Long fields that are 4000+ characters long

Have you looked at the DBD::ODBC POD? The Frequently Asked Questions section says:

How do I read more than N characters from a Memo | BLOB | LONG field?

See LongReadLen in the DBI docs.
Example:

 $dbh->{LongReadLen} = 20000;  
 $sth = $dbh->prepare(``select long_col from big_table'');  
 $sth->execute;  
 etc

[LostS]

You RULE... and no I was looking at the DBI Man page... THANK YOU....

-----------------------
Billy S.
Slinar Hardtail - Hand of Dane
Datal Ephialtes - Guildless
RallosZek.Net Admin/WebMaster



perl -e '$cat = "cat"; if ($cat =~ /\143\x61\x74/) { print "Its a cat!
+\n"; } else { print "Thats a dog\n"; } print "\n";'

[bart]

Hmm... the DBI docs do mention "LongReadLen" too.

"LongReadLen" (unsigned integer, inherited)

This attribute may be used to control the maximum length of long fields ("blob", "memo", etc.) which the driver will read from the database automatically when it fetches each row of data. The "LongReadLen" attribute only relates to fetching and reading long values; it is not involved in inserting or updating them.

[BazB]

Is this happening for all columns, or just particular ones?

Your database will truncate FULL_DESCRIPTION at 4000 characters:

FULL_DESCRIPTION - VARCHAR2(4000)

Cheers.

BazB.

---

If the information in this post is inaccurate, or just plain wrong, don't just downvote - please post explaining what's wrong.
That way everyone learns.

[LostS]

No the Database call was truncating the entries after like 255 char's or something... as for the DB truncating that is up to the DB people... not me... I just needed to make sure I grabed all the data they spit at me... Thank you :)

-----------------------
Billy S.
Slinar Hardtail - Hand of Dane
Datal Ephialtes - Guildless
RallosZek.Net Admin/WebMaster



perl -e '$cat = "cat"; if ($cat =~ /\143\x61\x74/) { print "Its a cat!
+\n"; } else { print "Thats a dog\n"; } print "\n";'