473,320 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Text truncated to 255 chars with ODBC

code green
1,726 Expert 1GB
I am using an ODBC connection to a MS Access database.
When using a SELECT statement and collecting the data via php ODBC functions, I find that the data from TEXT fields is truncated to 255 characters.
The texr fields are up to 1400 chars in length and some contain HTML tags,
but the maximum length returned is 255.
Any insight appreciated
Oct 2 '07 #1
8 8786
Motoma
3,237 Expert 2GB
I am using an ODBC connection to a MS Access database.
When using a SELECT statement and collecting the data via php ODBC functions, I find that the data from TEXT fields is truncated to 255 characters.
The texr fields are up to 1400 chars in length and some contain HTML tags,
but the maximum length returned is 255.
Any insight appreciated
Check the value of odbc.defaultlrl. This may be causing truncation is set low.
Oct 2 '07 #2
code green
1,726 Expert 1GB
Thanks Motoma, already found that suggestion in php manual.
Tried [PHP]ini_set ('odbc.defaultlrl','65536');[/PHP]Had no effect.
Expand|Select|Wrap|Line Numbers
  1. Bill Gates 1 TSDN 0
Oct 2 '07 #3
Motoma
3,237 Expert 2GB
Thanks Motoma, already found that suggestion in php manual.
Tried [PHP]ini_set ('odbc.defaultlrl','65536');[/PHP]Had no effect.
Expand|Select|Wrap|Line Numbers
  1. Bill Gates 1 TSDN 0
I've been doing some Googling because your problem perplexed me. Take a look at this link, specifically the example output for a TEXT column.
Oct 2 '07 #4
code green
1,726 Expert 1GB
Thanks again Motoma. had a look at the article and the following article seems to confirm our fears.
http://support.microsoft.com/kb/214854
So it seems to be a Jet problem. I am copying the data into a MySql DB
Apparently you are supposed to chunk the data in by CHAR size bundles of 255 bytes.
Don't fancy writing the code for that.
May have a look at other type connections, ADO possibly, but only used this with Visual Basic, never php.
Oct 3 '07 #5
code green
1,726 Expert 1GB
Just noticed the maximum size of an Access TEXT field is 255.
They are MEMO fields that I am reading not TEXT fields.
Hope this hasn't mislead anybody.
The same problem still exists of course.
Oct 3 '07 #6
code green
1,726 Expert 1GB
I cannot believe this. I found these words of widom at http://www.codeguru.com/forum/showth...hreadid=320358
Expand|Select|Wrap|Line Numbers
  1. Apparently, it will automatically truncate MEMO fields if your query has a DISTINCT or a GROUP BY in it. 
  2. (You'd think MSDN would warn you about this). All my test queries had a DISTINCT, and a couple had GROUP BY's
Which led me to this article http://www.jdhodges.com/log/1321/
I will have to filter using PHP instead of SQL. That means a complete re-write of my script.
At this point I can only assume this is the correct solution.
The same truncation problem also occurs using mssql DBs but I don't know if the cause is the same.
Oct 3 '07 #7
Motoma
3,237 Expert 2GB
I cannot believe this. I found these words of widom at http://www.codeguru.com/forum/showth...hreadid=320358
Expand|Select|Wrap|Line Numbers
  1. Apparently, it will automatically truncate MEMO fields if your query has a DISTINCT or a GROUP BY in it. 
  2. (You'd think MSDN would warn you about this). All my test queries had a DISTINCT, and a couple had GROUP BY's
Which led me to this article http://www.jdhodges.com/log/1321/
I will have to filter using PHP instead of SQL. That means a complete re-write of my script.
At this point I can only assume this is the correct solution.
The same truncation problem also occurs using mssql DBs but I don't know if the cause is the same.
All is starting to become apparent now; truncation occurs (and I knew this before, I just didn't think you had led us down this path) on text fields when using DISTINCT and GROUP BY statements because of the overhead of comparing variable length strings as opposed to fixed length strings.

I had just assumed that when you came into the forums and said "ODBC IS TRUNCATING TEXT FIELDS!" you had tested a simple SELECT query to back your statement up ;)

Be careful now, because this truncation will mean that TEXT (MEMO) fields are only distinct on the first 255 characters!
Oct 3 '07 #8
code green
1,726 Expert 1GB
Sorry Motoma. I wasn't aware of this problem with memo fields.
Thar was careless of me calling them text fields.
Just goes to show that if I had posted my query with the problem you may have spotted the reason earlier.
Oct 3 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Guinness Mann | last post by:
I'm running SQL Server 2K (sp3a) and when I run the following query (in query analyzer): SELECT id, LEN(ForeignWord) as Length, ForeignWord FROM Words WHERE Language ='Thai' and...
4
by: Arabian143 | last post by:
Hey everyone .. i have a website going .. .. ofcourse you use the Ws-FTP to upload the photos to the page .. and i have to use SQL program to insert the file names and values into the...
1
by: relaxedrob | last post by:
Hi All! I am using the DB2 CLP tool and want to format my text columns in a query to truncate the results of a wide text field - 50 char field but I want to display only the first 10 chars. ...
0
by: Elena Ghetti | last post by:
I have a large 49,000-record table from Excel I would like to import to Access(2002 but the same happens also with XP 2003), mostly text and memo fields. When I use the import wizard, fields are...
8
by: gene.ellis | last post by:
Hello. I using a simply SELECT statement to retrieve some data from a SQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXT because the amount of data. Anyway, my SQL statements...
1
by: Kuldeep | last post by:
When i export data from MySQL(server 5.0) i get followng error. SQLSTATE: 01004 String data, right truncated can sombody explain why this is happenning and possible solution!!! consider it...
1
by: skins96 | last post by:
Hello there, I know very little ASP, so I'm begging for help. The script was written by my predacessor.... I haven't made a single change to the script, but all of the suddent I started...
2
by: =?Utf-8?B?VG9tIEdhcnRo?= | last post by:
I know that I get pretty dumb late in the day, so I hope someone can just point out the source my stupidity. Below is partial code from a class. I'm accessing the 2 properties and getting...
0
by: Rich P | last post by:
you could establish a formula where you count the number of characters being displayed when the column width is 50 and then how many chars are displayed with the width is 60, 70, ... Then if the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.