Hi Drew, Thank you so much for the detailed explanation. I'll give it a try in the coming days, and rest assured, if I can't make it work, I'll come back crying for help. :-)
Kind regards, Dmitri --- Productivity Sauce: www.linuxpromagazine.com/productivitysauce Writer's Tools: writertools.googlecode.com On Wed, Dec 31, 2008 at 6:01 AM, Drew Jensen <[email protected]> wrote: > Alexandro Colorado wrote: >> >> On Tue, 30 Dec 2008 16:34:47 -0600, Drew Jensen >> <[email protected]> wrote: >> >>> Dmitri Popov wrote: >>>> >>>> Hello, >>>> >>>> I'm toying with the idea of creating a simple document management >>>> solution based on OpenOffice.org Base. The only problem is that I >>>> can't figure out how to write a simple OpenOffice.org Basic macro that >>>> inserts a file into a LONGVARBINARY field. I tried to find the answer >>>> in Andrew Pitonyak's database document, but since I'm a complete >>>> dummy, I couldn't figure it out. Could anyone help me with that? >>>> >>> >> >> I wouldnt mind knowing suck a macro. Probably something similar to the >> question on storing images in Base. Which I remember being asked (and >> answered) in the past. >> > > > Ok > > See the attached odb file, storefile.odb > > NOTE this is an OO.o 3.0.1 RC1 created database, versions of OO.o older then > 2.4 will not open this file. > > Table, "Table1" with the following fields: > ID IDENTITY -- PK > FileName VARCHAR(128) --Full URL with file name and extension > FileObj VARBINARY( 2147483647 ) -- the BLOB field to hold the file proper > > Form, "Table1". This form has some macros embedded in it that will insert or > update a record in the table, storing any type of binary file you would > like. > > Open the form and you will see that it has a File Selection control. Use the > browse button on this control to select the file you want to add into the > database. > The macros will set the fields FileName = the full URL of the file and > FileObj = the binary data. > > The basic code works without a flaw (hmmm, well it works as tested) and does > just what was intended - *BUT* - there is an error in this code -one of the > more common errors I see on the forums. > > Here is the bug in all it's glory. > > Open the database - it is empty of records. > Open the form, click the FileSelect browse button, select a file. > Now click the Add File button to the left. > AH HA - nothing happened....*WRONG* > > Close the database and reopen it, now open the form again. > There is a record in that table after all. > > TIP - you don't need to close and reopen the database. You can just click on > the refresh datasource button after the Add File function to see the changes > to the data table. > > Fine - I'll come back to this a little later. > > Here are the two procedures that do the work on the table: > > sub InsertRecord( aConn as object,_ > aFileName as string,_ > aInputStream as variant ) > > dim prp_stmt as variant > prp_stmt = aConn.prepareStatement( "INSERT INTO ""Table1"" (""FileName"", > ""FileObj"") VALUES( :fname, :fObj ) ") > prp_stmt.setString( 1, aFileName ) > prp_stmt.setBinaryStream( 2, aInputStream, > aInputStream.getInputStream.length ) > prp_stmt.executeupdate > > end sub > > sub UpdateRecord( aConn as object,_ > aFileName as string,_ > aInputStream as variant,_ > aID as integer ) > dim prp_stmt as variant > prp_stmt = aConn.prepareStatement( "UPDATE ""Table1"" SET ""FileName"" = > :fname, ""FileObj"" = :fObj WHERE ""ID"" = :id" ) > prp_stmt.setString( 1, aFileName ) > prp_stmt.setBinaryStream( 2, aInputStream, > aInputStream.getInputStream.length ) > prp_stmt.setInt( 3, aID ) > prp_stmt.executeupdate > end sub > > > In both procedures the aConn parameter is an active connection to the > database, FileName is the full URL to the file and aInputStream is a > DataInputStream returned by this rather generic function: > > function fnOpenInputStream( sFileName as String ) as object > > dim oInputStream as object > dim sURL as String > dim oSFA as object > dim oInpDataStream as object > > sUrl = ConvertToUrl(sFileName) > oSFA = createUNOService ("com.sun.star.ucb.SimpleFileAccess") > oInputStream = oSFA.openFileRead(sFileName) oInpDataStream = > createUNOService ("com.sun.star.io.DataInputStream") > oInpDataStream.setInputStream(oInputStream) fnOpenInputStream = > oInpDataStream > > end function > > > The procedure assigned to the button click only plays the role of checking > that our file selector is not null, fills a couple of variables and decides > which of the work procedures to call - insert or update: > > sub onClickAddFile( oEv as object ) dim conn as variant > dim oDataForm as variant > dim currentID as integer > dim fileName as string > dim inputStream as variant > > oDataForm = oEv.Source.Model.Parent > conn = oev.source.model.parent.activeconnection > > if oDataForm.getByName("FileSelection").Text <> "" then > FileName = oDataForm.getByName("FileSelection").Text > inputStream = fnOpenInputStream( FileName ) > if oDataForm.isNew then > InsertRecord( conn, fileName, inputStream ) > else > currentID = oDataForm.getByName("fmtID").BoundField.getInt > UpdateRecord( conn, fileName, inputStream, currentID ) > end if > oDataForm.getByName("FileSelection").Text = "" > end if > > end sub > > > > The code should be pretty straight forward, but ask away if you have > questions or maybe someone has a better way using "Embedded SQL Commands"... > > Which brings us back to the bug - here it is. > > It is a case of impedance if you will, there is a GUI form with code called > from events on controls...but then the code turns around and uses SQL > commands to perform an update to the table...behind the forms back so to > speak. > Well, I could add a line at the end to reload the DataForm controls result > set and that would fix it...NOT. > Why do I say 'NOT' - because the result set would return to the first > record. > OK - I can save a bookmark and then move back to the book mark... > Well, that would work, now what about when the code did an insert. How do I > get the correct bookmark for that again? Anyway - it is a total kludge and > quite inefficient. > > Still the "embedded INSERT and UPDATE commands" using prepared statements > works and there are times for sure to do it that way...but usually not on a > Form, IMO. > > And that's it for me tonight...tomorrow round two, and the other way to code > this so that the form displays properly. > > Till then, > > Drew > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
