• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Handling very long strings

Hello Experts:

I have seem to run into a problem that I don't know how to handle.

I am using Sql 7.0 for the database backend, and I store very long strings in a field defined as text.  When I pull this data into a string variable, it only retrieves about 1300 characters of data.  Im not sure if its the field definition or the variable definition that I'm having a problem with.  As long as the data is smaller than 1300 characters, it works fine.

Any ideas?

Data is stored as such:


I pull this into a string variable then run a sql statement as such:

Select * from tablename where fieldname in (Variable).
1 Solution
That's not what is happening.

when you use the In (list of items) syntax you are limited to 255 values in the list.  Note that 1300 / 5 = 260 so when you say about 1300 characters my guess is that it's really 1275 which, with 5 digit numbers, results in 255 items.

You're going to have to do something like this:

select * from tablename where fieldname in (Variable1) or fieldname in (Variable2) or fieldname in (variable3).

I'm assuming you're actually concatenating the string such as

"Select * from tablename where fieldname in (" & Variable1 & ") or fieldname in (" & Variable2 & ")"

If you know the max number of values, you can just dim the number of variables you need at design time.  Otherwise, I would use an array of strings and dynamically dimension it on the fly.  Then do something like

SQL = "Select * from tablename where "
For I = 1 to arrNames.Ubound
   SQL = SQL & "Fieldname in (" & arrNames(I - 1) & ") Or "
SQL = Left$(SQL, Len(SQL) - 4) ' to cut of the last " or "
To continue, you can build the array like this

done = false
ArrayIndex = -1
While not done
   redim preserve arrNames(arrNames.Ubound + 1)
   ArrayIndex = ArrayIndex + 1
   arrNames(ArrayIndex) = Left$(StringFromDatabase, 1275)
   StringFromDatabase = Mid$(StringFromDatabase, 1276)
   If Len(StringFromDatabase = 0 Then
      Done = True
   End If
You probably need to use the GetChunk method for retrieving the data... This is well documented.  Look up ADO/data types/managing long data types in your help files
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Finally, the 1275 value in my parsing routine won't work because it isn't allow for commas but you can easily calculate the corrrect value.

don't use getchunck - use ADO streams.
escheiderAuthor Commented:
Sorry its been awhile since I've checked on this, my wife is pregnant and haven't dedicated time to this site.

eosu, please give me an example on ADO streams..
escheiderAuthor Commented:
Decided to close this question.  I used your example and it works just fine...thanks for your time and input
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now