Large binary data on SQL Server question 1

[TomasDill]

Looks like I'm going to test real power and speed of VFP with SQL Server...
I need to store large binary data (>1MB) in a single field. Tried to use image type on SQL Server, but it is hard to handle it in VFP because it converted to General type. Tried to use text for this, but SQL server (or ODBC?) spoils data by extracting all codes like '5C0A' from binary data (looks weird), so large ZIP file cannot be opened any more.
Question 1. What the best way to store binary data on SQL Server? Is it possible to query data and store image field from SQL Server in memo field in VFP?
Question 2. How to copy content of general field into disk file? For memo field we have 'Copy Memo' command (or FWRITE(nHandle, MyTable.MyLargeMemoField). What is for General? [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[jonscott8]

Hi Vlad,

Tried to use image type on SQL Server

If the image is a disk file, is there a reason you can't just store a path reference to the file?

Example:

MyImageField='C:\MyPath\MyImage.jpg'

How to copy content of general field into disk file?

I havent dealt much with General fields(i do everything i can to avoid them ), but you might explore the DoVerb method of the OleBoundControl. It might take user intervention to save it to a disk file or some API or WSH function to send keystrokes to the app to get it to save. Good Luck and inform us on your findings. [sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>Focus on the solution....Not the problem.[/sig]

 

[TomasDill]

If the image is a disk file, is there a reason you can't just store a path reference to the file?

Nope. I need to store binary data. For example, large ZIP file.

i do everything i can to avoid them
Me too. Never used them. However, looks like I have no other way here. All that I need - just store ~1MB of binary codes on SQL server in a single field...

Well, I found way by some hacking of DBF file, however need to test it to check if it is reliable.
Any other suggestions? [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[foxdev]

Sorry, Vlad, but I think the official answer is to use the Image datatype in SQLS (which can support up to 2GB in a single column).

The handling of it in VFP will indeed be a nightmare. I know I'll need to do this myself on one of my projects, but I haven't made it there yet. Probably the best way (a complete guess) is to bring back the non-Image columns as you normally would, and use a separate call to bring back the binary data. Perhaps call a stored procedure and pass it a file name, and that SP can take care of writing out the image to the designated destination without having to worry about dragging it through ODBC. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TomasDill]

Robert, I made it by quite simple and better way. Your suggestions are good, but for large files (~10MB) it will be significant overload for SQL Server to run stored procedures over such amount of data. I try to make that part as simple as possible to do not overload network and SQL Server.
Yes, you're right, only image type on SQL Server good to store such data. I even managed to create a way of read/write such data using some hacking of DBF file. This require, however, one extra copying of data on local computer, but this is not critical bacause it is on LOCAL computer. See sample code below. I put it as is without comments, but hope you will understand what is 'oApp.oServer' object.
Let me know if this method reliable. I hope position of first field type in DBF header in future versions of VFP will not change...

* WRITE part
lcFileName = "F:\TWAIN20.ZIP" && test file that get corrupted when use text type on SQLS
if file("F:\ttt.dbf")
	erase ("F:\ttt.dbf")
endif
if file("F:\ttt.fpt")
	erase ("F:\ttt.fpt")
endif
select 0
create table "F:\ttt" FREE (mData M)
select ttt
append blank
append memo mData from (lcFileName) overwrite
use
ll = fopen("F:\ttt.dbf",12)
fseek(ll,43)
fwrite(ll,'G')
fclose(ll)

use F:\ttt alias __DATA
* here we now have cursor with general field that match to image type on SQLS

lcSQL = 'insert into test values (?__DATA.mData)'
if !(oApp.oServer.RunQuery(m.lcSQL,"TT"))
	messagebox(oApp.oServer.cError)
	set step on
endif

use in __DATA

* READ part
lcSQL = 'select mData from test'
if !oApp.oServer.RunQuery(m.lcSQL,"TT")
	messagebox(oApp.oServer.cError)
	set step on
endif

select TT
* here we now have cursor - SQL result with general field
if file("F:\ttt.dbf")
	erase ("F:\ttt.dbf")
endif
if file("F:\ttt.fpt")
	erase ("F:\ttt.fpt")
endif

copy to "F:\ttt" && extra copy, but it is needed...
use

ll = fopen("F:\ttt.dbf",12)
fseek(ll,43)
fwrite(ll,'M')
fclose(ll)

use "F:\ttt" alias TT
* now have the same data, but in memo

lcFileName = "F:\qwerty.zip"
if file(lcFileName)
	erase (lcFileName)
endif

local lH
lH = FCREATE(lcFileName)
FWRITE(lH,TT.mData)
FCLOSE(lH)
* save to file qwerty.zip to check if whole above process not corrupted data

use in TT

[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]