[freetds] BLOB retrieval

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Tue May 3 11:21:29 EDT 2005


> 
> >>Hello,
> >>
> >>My current business amounts to dumping MS SQLServer tables 
> >>that, among 
> >>others, contain TEXT (or NTEXT) data.
> >>
> >>Of course, they are always longer than the current textsize ;-)
> >>
> >>I was happy enough to find that the associated text-pointer 
> >>is returned 
> >>in the BLOBINFO data structure, for each blob column.
> >>I still need to know the length of that BLOB, so I can :
> >>
> >>    SET TEXTSIZE @size_large_enough
> >>
> >>and then :
> >>
> >>    READTEXT Table.column @the_pointer 0 @size
> >>
> >>My current guess is that I should augment the selected column with 
> >>DATALENGTH( blob_column1), DATALENGTH( blob_column2 ) ...
> >>which does not seems to be such a good idea, since automatically 
> >>patching SQL requests in such a way sounds hard to me. 
> >>Besides, I need 
> >>to know those column names in advance.
> >>
> >>Thanks for any help,
> >>    
> >>
> >I really don't understand.A simple "select * from table" isn't enough
> >??
> >  
> >
> Well, it seems to me that it isn't, and if it were, what would be the 
> use of that BLOBINFO struct ?
> 
> As said in the FAQ ( 
>
http://www.schemamania.org/projects/freetds/doc/htdoc/faq.html#textdata
)
> "The maximum data length of a text column is governed by the textsize 
> variable on the server", or the text field is truncated to that
maximum 
> size. My BLOBs can sometimes be huge things, but sometimes not, and I
am 
> reluctant to systematically
>
>    SET TEXTSIZE @insanely_large_value
>
> without knowing that such a large value will be necessary (worried
about 
> memory consumption, you see).
> 
> I hope I am clarifying things a bit, and reiterates: is there another 
> way than patching the request to reach the size of Blob fields ?
> 
> Thanks again,
> 
> Alexis
> 

Ok, that's fine. You can't get full lenght while fetching cause the only
returned length is cropped to your textsize. One option is to issue a
query like "select * from table where 0=1" and than build another query
like "select no_blob, blob, datalength(blob)... from table" and retrieve
(with another connection) full data with readtext.
ctlib implements cursors so you can use a single connection.
Bill is more used to transfer data from server to server see also his
patch at
http://sourceforge.net/tracker/?atid=407808&group_id=33106&func=browse.

bye
  freddy77


More information about the FreeTDS mailing list