• 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:

65000,65001,65002,65003,......

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

Select * from tablename where fieldname in (Variable).
0
escheider
Asked:
escheider
1 Solution
 
bob_onlineCommented:
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 "
Next
SQL = Left$(SQL, Len(SQL) - 4) ' to cut of the last " or "
0
 
bob_onlineCommented:
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
Wend
0
 
trkcorpCommented:
ASSUMING ADO:
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
0
Get expert help—faster!

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

 
bob_onlineCommented:
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.

0
 
eosuCommented:
don't use getchunck - use ADO streams.
0
 
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..
0
 
escheiderAuthor Commented:
Decided to close this question.  I used your example and it works just fine...thanks for your time and input
0
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