String data, right truncation error with DBD::ODBC and MS SQL Server 2000

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm using Perl, along with DBD::ODBC and MS SQL Server 2000, to create a weblog system... anyways, i gotta everything working right and i'm importing some old news records from another script's DB ( text file-based ) .. there are 108 records in total... everything goes fine ( DB is created, all other items are imported without problems ) until i hit one particular record.

#######################################
## Here is the DB structure i'm using: ##
#######################

CREATE TABLE coranto_csql (
    Coranto_newsid varchar(255) not null,
    Coranto_User ntext not null,
    Coranto_Category ntext null,
    Coranto_newstime bigint not null,
    Coranto_Subject ntext,
    Coranto_CustomField_url_link ntext,
    Coranto_CustomField_newssnippit ntext,
    Coranto_Text ntext,

    PRIMARY KEY (Coranto_newsid,Coranto_newstime)
);
[download]

####################################################
# Here is the subroutine which inserts each record.
####################################################

$Subs{'CorantoSQL_InsertDB'} = <<'END_SUB';
sub CorantoSQL_InsertDB {
    my $sthc = $corantosql_dbh->prepare("INSERT INTO coranto_csql
    (Coranto_Subject,Coranto_User,Coranto_Text,Coranto_newsid,Coranto_
+newstime,
    Coranto_Category,Coranto_CustomField_newssnippit,
    Coranto_CustomField_url_link) VALUES (?,?,?,?,?,?,?,?)");

    $sthc->bind_param(1, $Subject);
    $sthc->bind_param(2, $User);
    $sthc->bind_param(3, $Text);
    $sthc->bind_param(4, $newsid);
    $sthc->bind_param(5, $newstime);
    $sthc->bind_param(6, $Category);

    $sthc->bind_param(7, $CustomField_newssnippit);
    $sthc->bind_param(8, $CustomField_url_link);

    $sthc->execute();
    $sthc->finish();
}
END_SUB
[download]

#######################################################
# Here is the record i am trying to INSERT into the DB
#######################################################

Coranto_Subject: Don Frye, Ken Shamrock Autograph Tours

Coranto_User: garrett

Coranto_Text: <b>Official Press Release</b><br><br>As part of a nation
+wide tour to promote their fight in Japan for the Pride Fighting Cham
+pionships (on pay per view) on February 24th, Ken Shamrock (former UF
+C champion and WWF Superstar) and Don "The Predator" Frye (former UFC
+ Champion) will be in Dallas, Texas tomorrow!<br><br>Be there!<br><br
+>Pride FC videos and DVDs will be on sale for fans and this is your c
+hance to meet the legends and get your videos signed!<br><br>Also, sp
+ecial local guests are expected at each event, depending on the city.
+ You never know which big NHB fighter may show up!<br><br>Don't miss 
+a rare chance to meet two of the best in the sport!<br><br><ul><li>Ke
+n Shamrock and Don Frye<br>Wednesday, February 6th at 6:30pm<br>Fry's
+ Electronics, Dallas, Texas</li><br><li>Ken Shamrock and Don Frye<br>
+Thursday, February 7th at 6pm<br>Fry's Electronics, Phoenix, Arizona<
+/li><br><li>Ken Shamrock and Don Frye<br>Friday, February 8th at 7pm<
+br>Tower Video on Sunset, West Hollywood, California</li>

Coranto_newsid: EpEFkEuZVljCJkETqB

Coranto_newstime: 1012896000

Coranto_Category: (default)

Coranto_CustomField_newssnippit: As part of a nationwide tour to promo
+te their fight in Japan for the Pride Fighting Championships (on pay 
+per view) on February 24th, <a href="http://www.sherdog.com/fightfind
+er/displayfighter.cfm?fighterid=4">Ken Shamrock</a> (former UFC champ
+ion and WWF Superstar) and <a href="http://www.sherdog.com/fightfinde
+r/displayfighter.cfm?fighterid=124">Don "The Predator" Frye</a> (form
+er UFC Champion) will be in Dallas, Texas tomorrow, February 6, 2002.
[download]

#######################################
# CONCLUSION
#######################################

Can anyone tell me why i might be getting a "String data, right truncation" error when i try to insert this record into my DB??

I make sure the DB connection uses the following:

$corantosql_dbh->{LongReadLen} = 5120000;
$corantosql_dbh->{LongTruncOk} = 0;
[download]

# Automatically determine the binding type to be used for each column

$corantosql_dbh->{odbc_default_bind_type} = 0; # before creating the q
+uery...
[download]

Thanks!

formatting fix by chromatic on Sat Mar 30 17:16:53 UTC 2002

Comment on String data, right truncation error with DBD::ODBC and MS SQL Server 2000 Select or Download Code

Replies are listed 'Best First'.
One Thing Of Litte To No Consequence, And What Hopefully Is An Answer by Revelation (Deacon) on Mar 30, 2002 at 16:25 UTC
Primary Keys I was reading along and it struck me that you were using two primaray keys. That's a no no ;) I would recomend examining your data structure, and optimizing it a tad bit more. If you liked, you could even get rid of the news_id field, and us an autoincremental field. I would advise reading this for information on how to setup your database. String data, right truncation Now onto your real problem, the string error, I quickly googled and found this on a website. Some other monk will be able to varify if this is true: What does a Link ODBC Gateway error of "String data, right truncation" mean? This error is generated by the ODBC driver and suggests that you are trying to populate a table column which has a set maximum character limit with data that exceeds this limit. To avoid this problem, we suggest setting up your table's columns so that they have exactly the same data types as the formats of your form's fields (including size limitations). Helpful? But wait, there's more! I did a tad bit more research on ODBC with some code I had from a while back, and found this: `$st->bind_param($i, $sql, DBI::SQL_LONGVARCHAR);` I noticed that your code was missing a DBI::SQL_LONGVARCHAR, and wanted to see what the heck in meant. So I went onto cpan, and found the pertinant information for DBD::ODBC. Here's what it says: Note: those trying to bind variables need to remember that you should use the following syntax: `use DBI; ... $sth->bind_param(1, $str, DBI::SQL_LONGVARCHAR);` [download] It's possible that there are more problems, or that these aren't the only problems, but I hope this helps you in your debugging process. Gyan Kapur gyan.kapur@rhhllp.com	[reply] [d/l] [select]
Re: One Thing Of Litte To No Consequence, And What Hopefully Is An Answer by Anonymous Monk on Mar 30, 2002 at 17:46 UTC
Not to get too far OT, but why is it a "no no" to have two primary keys? By two primary keys, you mean a composite primary key, right? (since you can't have two primary keys by definition -- you can have two candidate keys though). Either way, I think it's wrong to suggest that either composite candidate keys or multiple candidate keys is a design error. In many cases it's precisely the right thing to do, since you're trying to express the meaning of your data.	[reply]
Re: Re: One Thing Of Litte To No Consequence, And What Hopefully Is An Answer by Revelation (Deacon) on Mar 30, 2002 at 20:09 UTC
If you look at the database the user has, and the data given by that database, you will see that his composite key is a combination of a string id, and the time the news item was generated (or something along those lines). This is used either to reduce the risk of having a primary key used twice, as we're using two elements, or because of the user's lack of knowledge of SQL (there's no reason to use a composite key, if one of the keys in that composite key can be primary in itself.) I admit my statement isn't true, but it would have resulted in the correct application by the anon monk ;) (I have sinned.) The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. If you were to benchmark a statement using a composite primary key, or a one attribute primary key, you would find that they single primary key would go faster. (Try using SQL's `explain` feature. This goes with the standard perl notion, of doing things with as little code as possible, while maintaining maximum flexibility, and pumping out high performance. From what I saw of the use of this monk's primary (composite) key, it would be easyer to create either a random string, which would be verified by the database as unused as a key, or just an integer, using auto_increment for the key. Primary keys are used to speed up queries (for the most part), and using a primary composite key doesn't help that out. In some instances it is necessary to create a primary composite key, but for the most part a primary key should suffice. That's why I was hinting at the fact that a composite key was not necessary, but you got me on the fact that my statement is actually not valid, and was not thought out... my bad ;) Sorry for the tangent, but I thought it was necessary to explain my reasoning. Gyan Kapur gyan.kapur@rhhllp.com	[reply] [d/l]
Re: String data, right truncation error with DBD::ODBC and MS SQL Server 2000 by rdfield (Priest) on Mar 30, 2002 at 15:53 UTC
You'd need to show how you're splitting up the input data. My guess it that it's not right, and you're trying to stuff all of your data into the first column, hence the overflow. rdfield	[reply]