And attached is the simplest most generic procedure I could come up with.
So this routine just creates an SQL INSERT STATEMENT
No prepared statement.
No support for IMages or other varbinary data.
Everything is a string.
No use of a cataloge (schema) name
Ran it against a few forms I had for each
HSQLdb, MS Access, MySQL Native, ODBC/SQLite3
Note - I gave up trying to force a commit of the GUI controls to the
bound data columns.
To SLOW...
Drew
' Copyright (C) 2010 Andrew 'Drew' Jensen
' [email protected]
'
' This program is free software: you can redistribute it and/or modify
' it under the terms of the GNU General Public License as published by
' the Free Software Foundation, either version 3 of the License, or
' (at your option) any later version.
'
' This program is distributed in the hope that it will be useful,
' but WITHOUT ANY WARRANTY; without even the implied warranty of
' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
' GNU General Public License for more details.
'
' To review a full a copy of the GNU General Public License
' please see, <http://www.gnu.org/licenses/>.
'
'//
'// The library module was intended to be used as a aid in
'// working around an anomily in the 3.2 release of OpenOffice.org Base
'//
'// For reference see:
'// http://www.openoffice.org/issues/show_bug.cgi?id=108377
'//
'// The library includes 4 rooutines for use in these
'// Insert Data Only forms
'//
'// Two are suitable for use from menu, toolbar
'// or dialog displayed by data entry form
'//
'// insertdFistDataForm
'// reloadFistDataForm
'//
'// Two when called from a button owned by the dataform
'// that owns the controls to write to the database
'//
'// insertThisDataForm
'// reloadThisDataForm
option explicit
'// GLOBALS
'//
GLOBAL BoundFields as variant
'//
'// reloadFistDataForm
'//
'// can be called from menu or toolbar or HotKey
'// can be called from dialog displayed by data entry form
'//
'// will clear all controls of the first dataform
'// on the top most base form window
'//
sub reloadFistDataForm()
'//
'// only want to work with forms
'//
'// a Query/dataview window is
'// ActiveFrame = NULL
'//
if not isNull(
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model ) then
'//
'// Report Builder editor
'//
if not
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.supportsService("com.sun.star.sdb.ReportDesign")
then
'// ASSUME - ASSUME - ASSUME
'// this is a form
'//
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.DrawPage.Forms(0).reload
end if
end if
end sub
'//
'// insertFirstDataForm
'//
'// can be called from menu or toolbar or Kotkey
'// can be called from dialog displayed by data entry form
'//
'// will write the values from the bound data columns
controls
'// of the first dataform on the top most base form window
'//
sub insertFirstDataForm()
'//
'// only want to work with forms
'//
if not isNull(
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model ) then
'//
'// Report Builder editor
'//
if not
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.supportsService("com.sun.star.sdb.ReportDesign")
then
'//
'// ASSUME - ASSUME - ASSUME
'// this is a form
'//
InsertDataForm(
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.DrawPage.Forms(0)
)
end if
end if
end sub
'//
'// reloadThisDataForm
'//
'// can be called from a button that has a dataform as
parent
'//
sub reloadThisDataForm( oEvent as object )
oEvent.Source.Model.Parent.reload
end sub
'//
'// insertThisDataForm
'//
'// can be called from a button that has a dataform as
parent
'//
sub insertThisDataForm( oEvent as object )
InsertDataForm( oEvent.Source.Model.Parent )
end sub
'//
'// insertDataForm
'// do it
'//
sub InsertDataForm(oDataForm as object )
dim CurrentConnection
dim CurrentControl
dim cntr, fldcnt
dim strCmdFirst, strCmdLast, SQLCmd, QuoteString
dim QryComposer
dim CurrentColumn
dim strdata
'//
'// use the connection that is
'// used by the datatform control
'//
CurrentConnection = oDataForm.ActiveCOnnection
'//
'// only inerested in quote used for identifieers
'//
QuoteString = CurrentConnection.MetaData.IdentifierQuoteString
'//
'// use this for getting different
'// parts of the SQL command used
'// by the dataform control
'// in our case here
'// the table name for the SQL statement
'//
QryComposer = CurrentConnection.createQueryComposer
'//
'// populate the composer with the
'// current dataforms SQL command
'//
QryComposer.Query = oDataForm.ActiveCommand
'//
'// build the two parts of
'// of an SQL insert statement
'//
'//
'// iterate over the contols
'// to extract the column names
'// from the bound data controls
'//
'// makse sure variables as null
strCmdFirst = ""
strCmdLast = ""
'//
'// ensure that data in GUI conttol is
'// written to the bound data controls
'//
'// no commit for image controls?
'//
'//
'*************************************************
'
' This loop adds significant time ?
'
'*************************************************
' dim impId
' dim top
' top = oDataForm.Count
' cntr = 0
'
' do
' CurrentControl = oDataForm.ControlModels(cntr)
' impID = oDataForm.ControlModels(cntr).ImplementationId(0)
' if impID <> 14 AND impID <> 97 then
' if not isNull( CurrentControl.BoundField ) then
' CurrentControl.Commit
' end if
' end if
' cntr = cntr + 1
' loop while cntr < top
'
'*************************************************
for cntr = 0 to oDataForm.Columns.count - 1
CurrentColumn = oDataForm.Columns(cntr)
if CurrentColumn.Typename <> "longvarbinary" then
'//
'// include only those controls
types
'// that support bound data controls
'//
'//
'// reversed order
'// form can have control models
after the last
'// data control
'//
if not CurrentColumn.isAutoIncrement then
strCmdFirst = strCmdFirst + QuoteString
+ CurrentColumn.Name + QuoteString
strdata = CurrentColumn.getString
if CurrentColumn.isAutoIncrement OR
strdata = "" then
strCmdLast = strCmdLast + "
NULL "
else
strCmdLast = strCmdLast + " '"
+ strdata + " '"
end if
if cntr <> oDataForm.Columns.count - 1
then
strCmdFirst = strCmdFirst + +
", "
strCmdLast = strCmdLast + ", "
end if
end if
end if
next
SQLCmd = "INSERT INTO " + QuoteString +
QryComposer.Tables(0).Name + QuoteString + " ( " + strCmdFirst + " ) VALUES ( "
+ strCmdLast + " )"
dim oStatement
oStatement = CurrentConnection.createStatement
on error goto insertDataFormSQLite
oStatement.executeUpdate( SQLCmd )
'//
'// clean up the GUI controls
'// by reseting the dataform
'//
oDataForm.Reload
'//
'// and exit
exit sub
insertDataFormSQLite:
MsgBox "Error " & Err & ": " & Error$ + chr(13) + "At line : "
+ Erl + chr(13) + Now , 16 ,"<< insertDataForm >>"
end sub
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
