Saving documents to SQL

Dawid

Your friend here will be FileOps and binary columns

Essentially you simply need a binary column in your table, and then something like :

; get a file
Do FileOps.$getfilename(ImageFileName,’Select attachment’,’*.*’)
Do FileOps.$splitpathname(ImageFileName,lDrive,lDirName,lFilename,lFileExtn) Returns lError
If ImageFileName>”
If iList.FileName=‘’
; saves the name of the file if you want to
Do iList.FileName.$assign(lFilename)
End If
If lFileExtn=’.pdf’
Do FileOps.$readentirefile(ImageFileName,iList.FileBinary) Returns lError
; in windows if a file is open, it won’t read properly
If lError<>0
Calculate Message as con(“FileOps error reading attachment – “,ImageFileName,” – error code : “,lError)
Do prmpts.$promptOK(Message)
Quit method kFalse
End If
Else
Do oFileOpsObj.$openfile(ImageFileName)
; reads the file into the binary column
Do oFileOpsObj.$readfile(iList.FileBinary)
Do oFileOpsObj.$closefile()
End If
; save the file extension – if you want to
Do iList.FileExtn.$assign(lFileExtn)
End If

Getting the ‘file’ back is a reversal of the process :

If not(isnull(AttachmentList.FileBinary))
; Create the file.
If pos(‘ / ‘,AttachmentList.FileName)>0
Calculate FileName as con(mid(AttachmentList. FileName,1,pos(‘ / ‘,AttachmentList. FileName)-1),AttachmentList.FileExtn)
Else
Calculate FileName as con(‘tbsattachment’,AttachmentList.FileExtn)
End If

; Create a new file. (Automatically deletes the old file if it exists.)
Do oFileOpsExt.$createfile(FilePath) Returns FlagOK
If not(FlagOK)
Calculate Mssg as con(“File error occurred when attempting to create the file, “,FileName,”.”)
Calculate Dtls as con(“File Path: “,FilePath)
Do errhndlr.$logError($cmethod,Mssg,Dtls,-300)
Else

; Write the encrypted string to the file.
Do oFileOpsExt.$writefile(AttachmentList.FileBinary)
; Close the file.
Do oFileOpsExt.$closefile()
End If
End If

Hope that helps

Andy

> On Jun 3, 2017, at 5:25 AM, Dawid Mocke <dawidmocke@gmail.com> wrote:
>
> Hello $Listers,
> I hope that you are keeping well!
>
> I am no longer as active as I should be on Omnis. I do however need to
> support a handful of clients from time to time. So it has come that I need
> to potentially develop new functionality for one client.
>
> Here is what led to the request from the client. For years now they have
> used the system to ‘attach’ documents to accounts. These attachments where
> effectively a copy of the selected document, copying that document over to
> a pre-define directory on a file server and saving that location to a SQL
> database.
> So if the client wanted to view the document, the system would find the
> location in the DB, open that location on the server and then open the
> document.
> This worked fine for several years. However it seems that a document in a
> directory on a file server is not as reliable as one wants it to be and
> files gets deleted (malicious or otherwise) and this in turn breaks the
> link in the DB. (PS: the backup strategy is not great).
>
> Now the request. Instead of saving this document to disk, I need to now
> save it to a SQL DB.
> The client is using $tudio 5.1 on Windows with MS SQL Backend.
> I know it is possible to do it, but I have no idea how to :
>
> 1. Insert it into SQL with Omnis
> 2. ‘Read’ it from SQL with Omnis and then to open it with whatever default
> reader is on the client pc, i.e. MSWord, Excel, PDF, etc.
>
> Any pointers (even to previous posts) will be highly appreciated!
>
> thank you
>
> —
> Dawid Mocke
> This message (and any associated files) is intended only for the use of the
> individual or entity to which it is addressed and may contain information
> that is confidential, subject to copyright or constitutes a trade secret.
> If you are not the intended recipient you are hereby notified that any
> dissemination, copying or distribution of this message, or files associated
> with this message, is strictly prohibited. If you have received this
> message in error, please notify us immediately by replying to the message
> and deleting it from your computer.
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com