Saving documents to SQL
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
; saves the name of the file if you want to
Do FileOps.$readentirefile(ImageFileName,iList.FileBinary) Returns lError
; in windows if a file is open, it won’t read properly
Calculate Message as con(“FileOps error reading attachment – “,ImageFileName,” – error code : “,lError)
Quit method kFalse
; reads the file into the binary column
; save the file extension – if you want to
Getting the ‘file’ back is a reversal of the process :
; Create the file.
If pos(‘ / ‘,AttachmentList.FileName)>0
Calculate FileName as con(mid(AttachmentList. FileName,1,pos(‘ / ‘,AttachmentList. FileName)-1),AttachmentList.FileExtn)
Calculate FileName as con(‘tbsattachment’,AttachmentList.FileExtn)
; Create a new file. (Automatically deletes the old file if it exists.)
Do oFileOpsExt.$createfile(FilePath) Returns FlagOK
Calculate Mssg as con(“File error occurred when attempting to create the file, “,FileName,”.”)
Calculate Dtls as con(“File Path: “,FilePath)
; Write the encrypted string to the file.
; Close the file.
Hope that helps
> On Jun 3, 2017, at 5:25 AM, Dawid Mocke <email@example.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
> 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
> 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