VFP, ADO stream object, write to sql server

[Jim Erwin]

I've been experimenting the last couple of days with
inserting, updating and retrieving MS word documents from
vfp to sql server using ado and the stream object. While
I have no problems retrieving documents, or other binary
images, from the sql server via ado, getting them inserted
or updated from vfp has been unattainable to this point.

MS has a great kb article (Q272338) about retrieving the
binary images from vfp via ado.

It has a link to another article (Q258038) about Accessing
and modifying BLOB data on SQL server, but all of the
examples are in VB. Now, I've gotten pretty good at
translating vb'isms to vfp'isms over the last 2 years (due
to the lack of specific vfp examples in most documentation
and kb articles), but I can't seem to get vfp to properly
store the documents to the sql server. I can however do
it in VB without any problems, but I'd rather do this
native to VFP and NOT have to build a COM object in VB to
broker the transaction.

Here's the code I've tried, and the results

******************************************************

#DEFINE C_ConnStr "driver={SQL
Server};Server=myserver;UID=user;PWD=password;database=foxt
est"

local loRS
local loConn
local loStream
local lcSql
local lcfilename

lcfilename = "f:\"

loRs = createobject('adodb.recordset')
loConn = createobject('adodb.connection')
loStream = createobject('adodb.stream')

loConn.open(C_ConnStr)

loStream.type = 1 && binary type
loStream.open
loStream.LoadFromFile("f:\loadtest1.doc")

loRs.activeconnection = loConn
loRs.cursortype = 1 && keyset cursor
loRs.locktype = 3 && optimistic lock
loRs.source = 'doctest1'
loRs.open
loRs.addnew
loRs.fields("docname") = "Test Document.doc"
loRs.fields("docimage") = loStream.read() && errors on this
loRs.update
loRs.close
loConn.close
loStream.close

wait window "It should be done"

********************************************

When it hits the line to read the stream into the docimage
field (an image field on the sql server) I get the
following error:

Function argument value, type, or count is invalid.

I've also attempted this all from the command window, and
from there, I get the error on the same line, but if I
execute the line a second time, it does not error, but...
it also does not read the stream into the field either.

I've done the exact same thing in vb with vb syntax and it
works flawlessly.

Any ideas on how I can get this to work from VFP would be
greatly appreciated... in fact... it would be nice to see
it added to the first kb article I mentioned in case
somebody else would like to do the same thing.

regards,

JE

[Vlad Grynchyshyn]

Hi!

This is a problem in VFP. When using the ADO Stream object, you can get
data, but cannot write them. Fortunately, DO binary field in the ADO
Recordset object has special methods - AppendChunk and GetChunk. While
GetChunk do not works for getting data, AppendChunk works ok when saving
data. So, the solution is - get by stream, wite by AppendChunk.

Hope this helps.

"Jim Erwin" <jerwin@centurykcDOTcom> wrote in message
news:1e5d01c0fb30$10d3eca0$9be62ecf@tkmsftngxa03...

[Jim Erwin]

Vlad,

Thanks much for the information. I've used appendchunk in the past with asp
and vbscript in the past before the stream object was added to ADO. I have
not yet used it with binary objects, just large quantities of text going
into a sql text field. Would you by chance have an example of using it with
VFP with a binary object?

regards,

Jim Erwin
jerwin@centurykcDOTcom

"Vlad Grynchyshyn" <vg...@qwe.qwe.qwe> wrote in message
news:eWVHUOV$AHA.1312@tkmsftngp07...