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.odbNOTE 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 properForm, "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 subIn 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 functionThe 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
storefile.odb
Description: application/vnd.oasis.opendocument.database
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
