How to Save a Visual Components Formula One WorkBook to a Database BLOB field

Of course you could do this by saving the F1 workbook to a temporary file on disk, and then loading this file
into the database BLOB field. But this will be Slooowww due to all the extra hard disk access. A much faster
way is to save the f1 workbook to memory and then into the BLOB field, or vice versa.

I wanted to save entire formula one workbooks as BLOB fields in a database. BLOBs can be
any binary data, not just memo or graphic fields. Saving spreadsheets into a database lets you organise
unstructured (spreadsheet) data in an organised way, together with other fields containing information about
the spreadsheet in each record for example. In my application, I am using the F1 grid to enter and edit data,
and thought I might as well use F1 to save the data also.

If you look in the F1 help file, there are "writetoblob" and "readfromblob" methods that will allow you to do
this. Unfortunately there is not enough detail in the help file to tell you how to do it. It turns out that a
few simple Windows 95/NT API calls will solve the problem. I only figured this out after a lot of hard
thought, research on Microsoft's web site about win32 memory management, and in the win32 developers guide
help that comes with delphi. Here is the code I wrote if you are interested.

It assumes that a database "mdlActuary.tblClaims" has been setup with a BLOB field called "triangle", and that
it is open for insertion. The workbook to be written from or to is passed to these routines, calling by name
not value.

The postworkbooktoblob routine first calls the F1 WriteToBlob method which copies the F1 workbook in binary
form in memory and passes back a handle to that memory (a long integer which just acts as a label for the
chunk of memory). The key API calls (described in the Delphi online help) are GlobalLock, which locks the
chunk of memory, reserving it for my program, and returns a pointer to the start of the memory block. I then
use another API call GlobalSize, which just returns a long integer value equal to the size of the memory block
(a multiple of 4 Kbytes on intel machines). I then use a Delphi BLOBstream to get the data from memory to the
database BLOB field, and finally call API routine GlobalFree to free up the reserved memory. I also save the
size of the BLOB in another database field.

The CopyBLOBtoworkbook routine does exactly the opposite, gets the saved blob from the database back into
memory and finally calls F1's readfromblob method to get it into a F1 workbook on a delphi form. The only new
API call is GlobalAlloc, which allocates a memory handle to a block of memory of size equal to the database
field "size".

I figured this out all by myself just by reading the sources I mentioned above and a bit of guesswork!!!!
I previously knew nothing about calling the Windows API!!

Most of the API calls are defined in the Windows.Pas file which comes with Delphi. It is fascinating to look
through. It shows a lot about how Delphi creates a wrapper around the Windows API. Using these functions, you
can create code that executes as fast as a C++ program, using Delphi. Delphi really IS THE GREATEST. I just
love it!!!!

Cheers to Delphi users!!!!
If anyone finds this helpful, please email me with you comments.

unit BLOBRoutines;

{contains a few win32 API calls to save and load VCF1 workbooks as Blobs in database}

interface

uses windows, vcfrmla1,DBTables,Classes;

Procedure PostWorkBookToBLOB (Var Workbook : TVCFormulaOne);
Procedure CopyBLOBToWorkBook(Var WorkBook:TVCFormulaOne);

implementation

Uses ActuaryDataModule;

Procedure PostWorkBookToBLOB (Var Workbook : TVCFormulaOne);

var
  Ole_handle:Integer;
  Size:dword;
  ptrWorkBook:Pointer ;
  BS : tBlobStream;

begin
Workbook.WriteToBlob(Ole_handle,0);
ptrWorkBook:=GlobalLock(Ole_handle);
Size:=GlobalSize(Ole_handle);
BS:=tBlobStream.Create(mdlActuary.tblClaimsTriangle,bmReadWrite);
BS.Write(ptrWorkBook^,Size);
mdlActuary.tblClaims['BLOBSize']:=Size;
Ole_handle:=GlobalFree(Ole_Handle);
BS.Free;
end;

Procedure CopyBLOBToWorkBook(Var WorkBook:TVCFormulaOne);

var
  Ole_handle:Integer;
  ptrWorkBook:Pointer ;
  BS : tBlobStream;
  Size:DWord;

begin
Size:=mdlActuary.tblClaimsBLOBSize.AsInteger;
Ole_handle:=GlobalAlloc(GMEM_Moveable,Size);
ptrWorkBook:=GlobalLock(Ole_Handle);
BS:=tBlobStream.Create(mdlActuary.tblClaimsTriangle,bmRead);
BS.Seek(0,soFromBeginning);
BS.Read(ptrWorkBook^,Size);
WorkBook.ReadFromBlob(Ole_handle,0);
Ole_handle:=GlobalFree(Ole_handle);
BS.Free;
end;

end.
--
=======================================================================
IronRod  :)

Rodney Hoskinson
Actuary
FAI Insurance
77 Pacific Highway
North Sydney NSW 2065
AUSTRALIA

+61 2 9936 2369
+61 2 9929 7486
+61 041 2142 875

rodn...@magna.com.au
http://www.magna.com.au/~rodneyh

========================================================================