WIN32::ODBC - text field String data, right truncation

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

i am trying to retrieve the value from MS Sql stored in a column type - Text size 16
I insert the data without a problem but then when i try to query it back out in perl i get the following error

Error Text: [Microsoft][ODBC SQL Server Driver]String data, right trun
+cation

#I create conenction object like this
my $dsn = "DRIVER={SQL Server};SERVER=$sqlserver;UID=$username;PWD=$pa
+ssword;DATABASE=$dbname";
$db = new Win32::ODBC($dsn);
[download]

i can query it fine in enterprise manager. Thanks for the help in advance

Comment on WIN32::ODBC - text field String data, right truncation Download Code

Replies are listed 'Best First'.
Re: WIN32::ODBC - text field String data, right truncation by LanceDeeply (Chaplain) on Nov 08, 2004 at 22:26 UTC
you may need to : `select maxsize = max(datalen(YourTextColumn)) from YourTableName` [download] and then call `# # set $size to maxsize from sql call above... # $db->SetMaxBufSize($size);` [download] -hth	[reply] [d/l] [select]
Re: WIN32::ODBC - text field String data, right truncation by terra incognita (Pilgrim) on Nov 08, 2004 at 21:38 UTC
Are you talking about the Text column type? That is a long character object that is stored elsewhere in the DB. The 16 is not the size of the object, but the size of the pointer to that object. As VSarkiss indicated more information woudl be really helpful.	[reply]
Re^2: WIN32::ODBC - text field String data, right truncation by ikegami (Patriarch) on Nov 08, 2004 at 22:58 UTC
Microsoft refers to VARCHAR and/or CHAR as Text, and I'd bet the OP is as well. (IIRC, Memo is what Microsoft calls TEXT fields.)	[reply]
Re^3: WIN32::ODBC - text field String data, right truncation by terra incognita (Pilgrim) on Nov 08, 2004 at 23:06 UTC
Text is the value that is shown in the Enterprise Manager and in the . It is a column type introduced in Version 7 IIRC. Here is an excerpt from the online books. ntext, text, and image Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set. ntext Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-92 synonym for ntext is national text. text Variable-length non-Unicode data in the code page of the server and with a maximum length of 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes. image Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes. [download]	[reply] [d/l]
Re: WIN32::ODBC - text field String data, right truncation by bmann (Priest) on Nov 08, 2004 at 22:07 UTC
Check DBI documentation for "LongReadLen". Something like the following in your connect statement might help: ~~`my $dbh = DBI->connect("dbi:ODBC:$datasource", $username, $password , {LongReadLen => 1500, RaiseError => 1}) \|\| die "Error connecting: $!";`~~ ~~[download]~~ HTH Update: or it might not help, since you're using Win32::ODBC, not DBI!	[reply] [d/l]
Re: WIN32::ODBC - text field String data, right truncation by VSarkiss (Monsignor) on Nov 08, 2004 at 21:24 UTC
You need to show the actual code you're using to insert and retrieve the data, as well as the DDL to create the table. Otherwise it's pretty much impossible to say.	[reply]