Rafael,
With regards to getting the info back off the server:
Note that on the server in the documents table you need
to hold the original file name as you will need to recreate
this file when you pull the data back.
The methodology is to create an input cursor to receive the data first then
Load up this cursor and then convert the data back to memo and then
Proper "File" format with StrtoFile()
nPrimary_Key = <Key to retrieve document record from SQL Server>>
* Create temporary cursor to hold the output from SQL Server
Use In Select("curImage")
*
Create Cursor curImage (Primary_Key I, Binary_Data Blob)
Select curImage
CURSORSETPROP("MapBinary",.T.,0)
cSQL="Select all " ;
+"Primary_Key, " ;
+"File_Name, " ;
+"Binary_Data " ;
+"from Documents " ;
+"where Primary_Key=?nPrimary_Key ";
<<Go get the data from the server>>
* convert from SQL format into Memo format
cData=Cast(curImage->Binary_Data AS Memo)
* Create temp file as an image of the document retrieved
cTemp_Name=curImage->File_Name && or a temp file name here eg "Word.doc"
nReturn=Strtofile(cData, cTemp_Name)
* Put a preview in a picture box on the
* form if it is a picture etc.
Thisform.imgImage.Picture=cTemp_Name
Dave C
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf
Of Rafael Copquin
Sent: 03 June 2010 00:24
To: ProFox Email List
Subject: Re: VFP and SQL Server + Word files
Wow Richard!
This is precisely what I need!
Since it is 8:30 PM here, I'll adapt your code to my form tomorrow
morning and see how it goes.
Thank you very much
Rafael Copquin
El 02/06/2010 19:49, Richard Kaye escribió:
> For some reason, I got interested in this. Here's a little proof of
concept. I swear it works on my system...<vbg> If the list wraps it too
much, let me know and I'll email the PRG to you.
>
> #DEFINE CRLF CHR(13)+CHR(10)
> **************************************
> * Program: SAVEDOCTOSQL1.PRG
> * Date: 06/02/2010 06:45:21 PM
> * VFP Version: Visual FoxPro 09.00.0000.7423 for Windows
> * Notes:
> **************************************
>
> #DEFINE MB_OK 0
> #DEFINE MB_ICONSTOP 16
> #DEFINE MB_ICONINFORMATION 64
> TRY
> ACTIVATE SCREEN
> CLEAR
> TEXT
> CREATE a table in SQL server with the following structure:
> pkey int identity not null
> filename varchar(50) null
> doc varbinary(max) null
> CREATE a connection to the DB that contains the table.
> ENDTEXT
> WAIT WINDOW
> CLEAR
> LOCAL m.cDoc AS String, m.lcCaption AS Character, m.lnHandle AS
Integer
> m.lcCaption=[There& Back Again...]
> m.lcProblem=[Something's not quote right...]
> m.lnHandle=0
> m.lcDocument=GETFILE('doc',[Get document],[Select])
> m.cDoc=FILETOSTR(m.lcDocument)
> m.cDocBlob=CAST(m.cDOC AS BLOB)
> TEXT TO cCmd TEXTMERGE NOSHOW FLAGS 1 PRETEXT 1+2+4+8
> INSERT INTO test (filename)
> VALUES ('<<JUSTFNAME(m.lcDocument)>>')
> ENDTEXT
> m.lnHandle=SQLCONNECT() && assumes connection you want is available
> IF m.lnHandle>0
> IF SQLExec(m.lnHandle, m.cCmd)>0 && record was
inserted
> MESSAGEBOX([Record
inserted],MB_OK+MB_ICONINFORMATION, m.lcCaption)
> TEXT TO cCmd TEXTMERGE NOSHOW FLAGS 1 PRETEXT 1+2
> UPDATE test SET doc=?cDocBlob WHERE
filename='<<JUSTFNAME(m.lcDocument)>>'
> ENDTEXT
> IF SQLEXEC(m.lnHandle, m.cCmd)>0 && query
did not fail (although that doesn't mean it actually changed data)
> MESSAGEBOX([Record
updated],MB_OK+MB_ICONINFORMATION, m.lcCaption)
> ELSE
> AERROR(aSQLError)
>
MESSAGEBOX(asqlerror[2]+CRLF+asqlerror[3],MB_OK+MB_ICONSTOP, m.lcProblem)
> ENDIF
> TEXT TO cCmd TEXTMERGE NOSHOW FLAGS 1 PRETEXT
1+2+4+8
> SELECT TOP 1 filename, doc FROM test
> WHERE filename='<<JUSTFNAME(m.lcDocument)>>'
> ORDER BY pkey DESC
> ENDTEXT
> IF SQLEXEC(m.lnHandle, m.cCmd, [retrieved])>0 &&
record was retreived
> MESSAGEBOX([Record
retrieved],MB_OK+MB_ICONINFORMATION, m.lcCaption)
> SELECT CAST(retrieved.doc AS blob) doc,
filename FROM retrieved INTO CURSOR temp
> SCATTER MEMVAR MEMO FIELDS doc, filename
>
m.filename=JUSTSTEM(m.filename)+[test.]+JUSTEXT(m.filename)
> ?STRTOFILE(m.doc, m.filename) && echoes
number of bytes to screen
> IF FILE(FULLPATH(m.filename))
> LOCAL m.loWord AS word.application
>
m.loWord=NEWOBJECT([word.application])
>
m.loWord.Documents.Add(FULLPATH(m.filename))
> ENDIF
> ELSE
> AERROR(aSQLError)
>
MESSAGEBOX(asqlerror[2]+CRLF+asqlerror[3],MB_OK+MB_ICONSTOP, m.lcProblem)
> ENDIF
> ELSE
> AERROR(aSQLError)
>
MESSAGEBOX(asqlerror[2]+CRLF+asqlerror[3],MB_OK+MB_ICONSTOP, m.lcProblem)
> ENDIF
> ENDIF
> CATCH TO m.loError
> LOCAL m.lcError AS Character
> m.lcError=[ Error: ] + STR(m.loError.ErrorNo) + CRLF
> m.lcError=m.lcError+[ LineNo: ] + STR(m.loError.LineNo) + CRLF
> m.lcError=m.lcError+[ Message: ] + m.loError.Message + CRLF
> m.lcError=m.lcError+[ Procedure: ] + m.loError.Procedure + CRLF
> m.lcError=m.lcError+[ Details: ] + m.loError.Details + CRLF
> m.lcError=m.lcError+[ StackLevel: ] + STR(m.loError.StackLevel) +
CRLF
> m.lcError=m.lcError+[ LineContents: ] + m.loError.LineContents
> MESSAGEBOX(m.lcError,MB_OK+MB_ICONSTOP,[*ERROR*])
> FINALLY
> USE IN SELECT([temp])
> USE IN SELECT([retrieved])
> IF m.lnHandle>0
> SQLDISCONNECT(m.lnHandle)
> ENDIF
> IF VARTYPE(m.loWord)=[O]
> m.loWord.Visible=.t.
> ENDIF
> ENDTRY
>
> rk
>
>
> -----Original Message-----
> From: [email protected] [mailto:[email protected]]
On Behalf Of Rafael Copquin
> Sent: Wednesday, June 02, 2010 6:05 PM
> To: [email protected]
> Subject: VFP and SQL Server + Word files
>
> OK Dave and Steve
>
> I followed Dave's instructions, ie, use the cast function after the
> filetostr function and
>
> IT WORKED !!!
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
