DBI Problem?

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

A Web site that I am working on has a bunch of user data that is stored in text files. I am trying to convert it to MS SQL Server 7.0.

My script reads the data from a text file into %data. I then build the SQL in the insertData sub and execute it (raiseError has previously been set). The script processes several files until I get to one particular file. All of the data in this file is fine, except for one field which has about 23,000 characters! I am attempting to stuff it into a field defined as text, which should have no problem holding it. I get the following error message:

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Strin
+g data, right truncation 
(SQL-22001)(DBD: st_execute/SQLExecute err=-1) at userDataConv.pl line
+ 101.
[download]

Line 101 is $sth->execute(@data). Here's the sub that's generating the problem:

sub insertData {

    my (@columns, @data);

    # Only insert fields with values
    # @fields contains the names of the columns in the database
    # @textFields containst the names of the columns in the text files
    
    for (0 .. $#textFields) {
        if (exists $data{$textFields[$_]} && $data{$textFields[$_]} !~
+ /^\s*$/) {
            push @columns, $fields[$_];         # Database fieldnames
            push @data, $data{$textFields[$_]}; # Corresponding hash v
+alues
        }
    }
    
    my $fields = join ',', @columns;
    my $placeHolders = ('?,' x ($#columns)) . '?';
        
    my $sql = "INSERT INTO userData ($fields) VALUES ($placeHolders)";
    my $sth = $dbh->prepare($sql);
    $sth->execute(@data);
    $sth->finish;
}
[download]

I've never worked with MS SQL Server (or ODBC) before, so I am completely at a loss. Searching the documentation has so far turned up nothing. Is it SQL Server, ODBC, or DBI which is choking? I have no idea. Any suggestions would be greatly appreciated. I can supply more data if necessary.

Cheers,
Ovid

Comment on DBI Problem? Select or Download Code

Replies are listed 'Best First'.
Re: DBI Problem? by ColtsFoot (Chaplain) on Sep 07, 2000 at 10:38 UTC
I've had similar problems when reading large fields from the database and have overcome this by using the $dbh->{LongReadLen} parameter, but as the name suggests I assume that this is just for reading. Looking at "perldoc DBI" I notice a section titled "bind_param__inout" which states The additional $max_len parameter specifies the minimum amount of memory to allocate to $bind_value for the new value. If the value is too big to fit then the execute should fail. If unsure what value to use, pick a generous length larger than the longest value that would ever be returned. The only cost of using a very large value is memory. Maybe this is what you need to set. Hope this helps	[reply]
(Ovid) Re: DBI Problem Solved by Ovid (Cardinal) on Sep 07, 2000 at 19:33 UTC
Well, the problem is solved. In case anyone gets anything like this in the future, I'll detail what I did to resolve it. First, I found out that `DBI` has a `trace` method that I can access. Adding the following line writes out `trace` information to the filename specified: # These are the trace level codes # 0 - Trace disabled. # 1 - Trace DBI method calls returning with results or errors. # 2 - Trace method entry with parameters and returning with results. # 3 - As above, adding some high-level information from the driver # and some internal information from the DBI. # 4 - As above, adding more detailed information from the driver. # Also includes DBI mutex information when using threaded Perl. # 5 As above but with more and more obscure information. DBI->trace(3, "trace.txt"); [download] The first argument is the trace level (I find that anything over 3 gives too much information and I can't understand it) and the second argument is the file to write to in append mode. Running the trace and reading the output revealed that DBI (or DBD::ODBC, I'm not sure which) was trying to force the errant field to be a `varchar`, despite the field in the database being declared as the `text` type. On MSSQL Server 7.0, `varchar` is limited to 8000 characters. The solution: change `use DBI;` to `use DBI qw':sql_types';` [download] Then, after I prepared the SQL, but before it's executed, I inserted the following line: `$sth->bind_param($bind_col, $data{'terms'}, SQL_LONGVARCHAR);` [download] `$bind_col` is the column of the `text` field (enumeration starts at 1, not zero). `$data{'terms'}` was the 23,000 character variable, and `SQL_LONGVARCHAR` forces it to be the `text` type. Thanks to everyone for your suggestions! Cheers, Ovid	[reply] [d/l] [select]
RE: (Ovid) Re: DBI Problem Solved by Fastolfe (Vicar) on Sep 07, 2000 at 19:50 UTC
Glad you got it working, though it seems strange that MS SQL Server would be reporting the truncation and not the DBI module...	[reply]
RE: RE: (Ovid) Re: DBI Problem Solved by PsychoSpunk (Hermit) on Sep 07, 2000 at 20:40 UTC
Just a note, the database will only report the error if it gets the SQL command. In this case, the ODBC driver never passes it along (what's the point if it fails the checks before going to be executed at the database?). And also, regardless of where the error occurs, DBI has a property to store all errors. $dbh->errstr or $DBI::errstr always holds the last error for the dbh or the last error that occurred. Most developers will bypass the need to physically print this by setting RaiseError => 1 when creating the dbh. It may be that MS SQL 7.0 was logging the error and Ovid didn't list that info. My own question to Ovid is why you chose to use ODBC. I'm not knocking its use, since I did the same thing, but I was curious if you tried FreeTDS, and if so, what errors came up. I had troubles with it and MS SQL 7.0 although people say it's possible, I've not seen it in my own experience, and no one has come up with any documentation that explicitly lines out what tweaks were necessary. It'd be nice if a user was so inclined to show us the light. Thanks if anyone takes this to task. ALL HAIL BRAK!!!	[reply]
Re: DBI Problem? by nop (Hermit) on Sep 07, 2000 at 04:36 UTC
Most likely the problem is not your code, and most likley it is not DBI. Most likely, the problem is with the long field and ODBC. I ran into a similar problem when I was using Win32::ODBC and had to use the SetMaxBufSize function (see www.roth.net/perl/odbc/docs/object/. I'm not sure of the DBI equivalent, but maybe this provides a clue. At worst, you could muck through the Win32::ODBC code and see what SetMaxBufSize is actually passing to the ODBC layer.	[reply]
Re: DBI Problem? by Fastolfe (Vicar) on Sep 07, 2000 at 00:41 UTC
The message is generated by the MS SQL ODBC driver (as it says in the error message), so either the database cannot handle data that large (perhaps the 'text' column definition is limited to a certain size?) or the ODBC driver can't handle it. I'd check the table definition to be sure it's set up to handle that much data. If it's correctly set up, I would check your original assumption that it's only sending 23k or whatever bytes of data for that one field.	[reply]
Re (tilly) 1: DBI Problem? by tilly (Archbishop) on Sep 07, 2000 at 00:35 UTC
I wish to remain ignorant of MS SQL Server, but when I see "right truncation" that looks like someone dropping data off of "the right end" of the field - ie truncating your string short. That might be in the database or driver, I don't know, but try running some tests and find what length that cutoff happens at.	[reply]