VARCHAR over 256 characters return always 256?

Forum related to MS SQL Server

Moderators: gto, cipto_kh, EgonHugeist

noctrex
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 25.05.2011, 09:24

VARCHAR over 256 characters return always 256?

Postby noctrex » 28.07.2011, 08:13

I have a database in which there are many VARCHAR(1000) fields, but it seems that zeos 6.6.6 always returns only the first 256 charaters.
Is there a solution for this problem?
I cannot alter the database, so I must find a solution within delphi.

jeremicm
Senior Boarder
Senior Boarder
Posts: 61
Joined: 18.10.2006, 17:07
Contact:

Postby jeremicm » 28.07.2011, 08:47

varchar(1000) holds only 1000 bytes, not 1000 characters... try using varchar(max) instead....

Wild_Pointer
Expert Boarder
Expert Boarder
Posts: 162
Joined: 18.03.2008, 13:03
Contact:

Postby Wild_Pointer » 28.07.2011, 08:52

hello noctrex,

I don't know how it is with ms sql, but with postgresql I use text datatype in DB for fields that are more than 255 char long. That way I get memofield instead of stringfield and it may contain more than 255 chars.

Maybe someone here with experience using ms sql will add some new ideas...

Good luck!

noctrex
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 25.05.2011, 09:24

Postby noctrex » 28.07.2011, 09:05

Thanks for your answer, I've tried the following on a test database:

when using a nvarchar(max) field (I need an N* type field for encoding greek charaters) I get this error:
"General SQL Server error: Check messages from the SQL Server. Unicode data in a Unicode-only collation or ntext data cannot br sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier."

And when I try to get a ntext field I get the following:
"General SQL Server error: Check messages from the SQL Server. Argument data type ntext is invalid for argument 1 of replace function."

Only setting the field to a nvarchar(N) works, but always only getting the first 256 characters :/

For the record, I'm using Delphi 7 with Zeos 6.6.6

noctrex
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 25.05.2011, 09:24

Postby noctrex » 28.07.2011, 09:54

Small quick'n'dirty workaround for this issue:

I just split, inside the sql query, the long varchar into pieces of 250 chars like this:

SUBSTRING(ISNULL(Ap_Memo, ''''), 0, 250) AS Ap_Memo01,
SUBSTRING(ISNULL(Ap_Memo, ''''), 250, 250) AS Ap_Memo02,
SUBSTRING(ISNULL(Ap_Memo, ''''), 500, 250) AS Ap_Memo03,
SUBSTRING(ISNULL(Ap_Memo, ''''), 750, 250) AS Ap_Memo04,
SUBSTRING(ISNULL(Ap_Memo, ''''), 1000, 250) AS Ap_Memo05,
.. and so on...

and concatenate them back together into one large string inside Delphi.
I know it's not optimal, but at least it works.

rautgigi
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 08.10.2006, 19:54

256 chars limit

Postby rautgigi » 22.05.2012, 10:10

Hi,

Using ntwdblib from microsoft it will limit the varchar to 256. This is a driver limitation. You can use ADO, or freeTDS in order to use varchar with more than 256 chars. It should work also with unicode data ....

Bogdan


Return to “MS SQL Server”

Who is online

Users browsing this forum: No registered users and 1 guest