Save Blob as file from SQL:Prop

1

Hi

I need to call a Stored Procedure on a MSSQL server, returning a large XML document.
What I have done.

  1. created a SQL (dct) file with one blob field, with the the turbo option on.
  2. run this code:
    SQL{PROP:SQL}
    NEXT(SQL)
    BlobToFile(SQL:DATA,‘c:\temp.xml’)

The result is only a file with the first 2033 charaters.

Is it possible or am I doing something wrong?

Regards Niels

2

Why not to define your SQL column as varchar(max)? XML is just text.

3

Hi Mauricio

Thanks.
This is my query (just an example) to the SQL server: SELECT No, Name From Items FOR XML PATH(‘Item’)
The query returns more than 1.1 mill (300 mb) lines or more, so thats why i return it into a blob.

4

I think this is a bad idea to return huge blob from SP to Clarion. Why don’t you return your result into txt file from SP and process this txt file from Clarion?

5

So maybe you need to do it in a different way. You’re talking about moving 300 MB from SQL to your client program. Why? That will make everything slow. Can’t you filter your query? or partially return some lines in a batch?

6

What are you going to do with the resultant temp.xml file?

A few things to bear in mind…
By default FOR XML returns the results as Unicode text which may not be what you want if you’re going to further process the file.
Also the result will not be a valid XML file - it will be an XML fragment without a valid root opening tag or root closing tag - so if you try opening it in Explorer it will only display the first record non-formatted.
In addition the result will be one large string of characters with no line breaks between Items.

7

Hi Graham

Thanks for the feed back.
The issue is not whether the document is a valid XML or not. I solve this by just wrapping my query with an extra SELECT () FOR XML.
My problem is that I only get 2033 charaters return in my blob no matter what I do.

8

Try with a TOP clause to see if that makes it work.
I only get 2033 characters
Is that the size of the temp.xml file on disk?
I wouldn’t save the file to c:\ as later versions of Windows can get stroppy creating files in the root folder - use BlobToFile(SQL:DATA,path() & ‘\temp.xml’) instead

See attached for example that I’ve used to retrieve 700MB XML fragments.

XMLBlob.clw (1.5 KB)

Few things to note…
1 - Dummy file doesn’t need the TURBOSQL switch because BLOB fields are outside of the RECORD declaration - so Clarion doesn’t send verification commands to the backend to check if the field is present and of the correct datatype
2 - Using Prop:SQLRowSet avoids the need for a stored procedure on the backend
3 - Note how the SQL command CASTs the Unicode text to ASCII, adds a processing instruction, valid root start and termination etc

1 Like
9

Also make sure you have an error check immediately after the Prop:SQL (or Prop:SQLRowset) because that will catch errors

PropSQLChecking

such as illegal characters in the data that will truncate the file.

1 Like
10

Cool. I was planning to try something like this to see if it works.

11

Hi Graham
Thank you very much. That was exactly what I was trying to achieve.

12

Hi,
I have tried storing data in BLOBs (TPS) and varchar(max) (SQL-Server). My data was Microsoft Word documents (ie binary).
To store binary data in SQL-Server us varchar(max).
Use BLOBTOFILE to get the data out. However, FWIW I had corruption problems in both cases and now store my data within the filesystem and only store the filename in the database. But that was only my experience.
I haven’t looked at it but might Capesoft’s XFiles help you since you are storing XML?
Regards,

Mark

13

Hi,
Twice previously I was asked to help with this problem and found they had forgotten to include CWUTIL.inc in the global map. Here is an embed to paste in.

[DEFINITION]
[SOURCE]
PROPERTY:BEGIN
PRIORITY 4000
PROPERTY:END
INCLUDE(‘CWUTIL.INC’),ONCE ! Used because will incorporate FILETOBLOB
[END]

Sorry if you have already done this, but having seen it missed before, others reading may find it helps them.

All the best, Jim

1 Like