Hi,
Attached is a small basic file, intended to be embedded in an ODB file.
The file has two main routines:
InsertThisDataForm
InsertFirstDataForm
Datasources tested and comments
dBase - no problems found
HSQLdb - no problems found
MS Access - Image controls will generate error
MySQL - Image controlsl do not write the data to disk, but no error is
reported
SQLite3 (odbc) - no support for Image Controls when prepared statements
not supported
This started out with trying to help someone with Issue 108377 - this
works pretty well for that, even with the problems.
Turns out this is turning into a pretty nice copy record function though...
If you try it (the comments should show you how to use it) let me know
if you find any other problems..
Later,
Drew
' Copyright (C) 2010 Andrew 'Drew' Jensen
' atjen...@openoffice.org
'
' 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
'//
'// 0.1
'// 2010-02-11
'//
'// two routines do the actual insert
'//
'// insertDataForm
'// SQLite - uses SQL insert statement directly
'//
'// insertDataFormPS
'// all other drivers - uses PreparedStatement component
'//
'
option explicit
'//
'// reloadFistDataForm
'//
'// can be called from menu or toolbar
'// 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
'
if InStr( thisDataBaseDocument.dataSource.URL,
"sdbc:odbc:SQLite3" ) = 1 then
'//
'// ODBC/SQLite does not support prepared
statements
'
InsertDataForm(
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.DrawPage.Forms(0)
)
else
'//
'// everything elese does ?
'
InsertDataFormPS(
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.DrawPage.Forms(0)
)
end if
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
'//
'// insertThisDataFormPS
'//
'// can be called from a button that has a dataform as parent
'
sub insertThisDataForm( oEvent as object )
'//
'// ODBC/SQLite does not support prepared statements
'
if InStr( thisDataBaseDocument.dataSource.URL, "sdbc:odbc:SQLite3" ) =
1 then
InsertDataForm( oEvent.Source.Model.Parent )
else
'//
'// everything elese does ?
'
InsertDataFormPS( oEvent.Source.Model.Parent )
end if
end sub
'//
'// insertDataForm
'// do it
'
sub InsertDataFormPS(oDataForm as object )
dim CurrentConnection
dim CurrentControl
dim cntr, fldcnt
dim strCmdFirst, strCmdLast, strTableName, SQLCmd, QuoteString
dim QryComposer
dim CurrentColumn
dim strdata
dim prepStatement
dim bStream
dim cntUsed
'//
'// use the connection that is
'// used by the datatform control
'
CurrentConnection = oDataForm.ActiveCOnnection
'//
'// only inerested in quote used for identifieers
'
QuoteString = CurrentConnection.MetaData.IdentifierQuoteString
'//
'// SingleSelectQueryComposer
'// replaces the old query composer
'//
'// the old composer is still available
'// with
'// CurrentConnection.CreateComposer
'//
'// The old composer however will
'// not allow us to work with queries
'// which Alias column names
'//
'
QryComposer = CurrentConnection.createInstance(
"com.sun.star.sdb.SingleSelectQueryComposer" )
'//
'// populate the composer with the
'// current dataforms SQL command
'
QryComposer.Query = oDataForm.ActiveCommand
'*************************************************
'
' TODO -
'
'*************************************************
'
'
' ensure that the control with focus
' at the time of a menu/toolbar event
' does a commit to push the data from
' the GUI control to the bound data
' control
'
'
'*************************************************
cntUsed = 0
'//
'// build the two parts of
'// of an SQL insert statement
'//
'
for cntr = 0 to QryComposer.Columns.count - 1
if NOT QryComposer.Columns(cntr).isAutoIncrement _
AND NOT QryComposer.Columns(cntr).AggregateFunction _
AND NOT QryComposer.Columns(cntr).Function then
'//
'// a secondary counter
'// parameter count may be less then
'// composer column count
'
cntUsed = cntUsed + 1
'// use the singleQueryComposer
'// for the column's real names
'
strCmdFirst = strCmdFirst + QuoteString +
QryComposer.Columns(cntr).RealName + QuoteString
'// with parameters
'
strCmdLast = strCmdLast + " ? "
if cntr <> QryComposer.Columns.count - 1 then
strCmdFirst = strCmdFirst + + ", "
strCmdLast = strCmdLast + ", "
end if
end if
next
'//
'// if not all columns will be sent
'// to the database engine
'// remove the last comma
'
if cntUsed < QryComposer.Columns.count -1 then
strCmdFirst = left( strCmdFirst , len( strCmdFirst ) - 2 )
strCmdLast = left( strCmdLast, len( strCmdLast ) - 2 )
end if
'//
'// build the final SQL statement
'//
'// MySQL native connector requires schema
(catalog) name along with table name
'
if InStr( thisDataBaseDocument.DataSource.URL, "sdbc:mysql" ) = 1 then
'//
'//
'// requited when using a prepared statements
'// with the native connector
'//
'// dataform.updateSchema returns ""
'// so instead the catalog set in the connection
'
strTableName = QuoteString + CurrentConnection.Catalog +
QuoteString + "." + QuoteString + QryComposer.Tables(0).Name + QuoteString
else
'//
'// otherwise no catalog (schema) required
'// True for embedded HSQLdb, dBase, MS Access
'
strTableName = QuoteString + QryComposer.Tables(0).Name +
QuoteString
end if
'//
'// assemble the sql
'
SQLCmd = "INSERT INTO " + strTableName + " ( " + strCmdFirst + " )
VALUES ( " + strCmdLast + " )"
'//
'// pass it to the database engine
'
prepStatement = CurrentConnection.prepareStatement( SQLCmd )
'//
'// prepared statements start counting at 1
'// go figure
'
cntUsed = 0
for cntr = 0 to QryComposer.Columns.count -1 ' oDataForm.Columns.count
-1
CurrentColumn = oDataForm.Columns(cntr)
'//
'// don't include auto, aggreg, func fields
'
if NOT QryComposer.Columns(cntr).isAutoIncrement _
and NOT QryComposer.Columns(cntr).AggregateFunction _
and NOT QryComposer.Columns(cntr).Function then
'//
'// a secondary counter
'// parameter count may be less then
'// composer column count
'
cntUsed = cntUsed + 1
'//
'// binary data gets special treatment
'
if CurrentColumn.Type =
com.sun.star.sdbc.DataType.LONGVARBINARY _
or CurrentColumn.Type =
com.sun.star.sdbc.DataType.VARBINARY _
or CurrentColumn.Type =
com.sun.star.sdbc.DataType.BLOB then
'//
'// MySQL native connector requires
setBlob
'
if InStr( thisDataBaseDocument.DataSource.URL,
"sdbc:mysql" ) = 1 then
bStream = CurrentColumn.getBlob
if not CurrentColumn.wasNULL then
prepStatement.setBlob( cntUsed,
bStream )
else
'//
'// different types
have different nulls
'
prepStatement.setNull( cntUsed,
CurrentColumn.Type )
end if
else
'// HSQLdb use
setBinaryStream
'//
bStream = CurrentColumn.getBinaryStream
if not CurrentColumn.wasNULL then
prepStatement.setBinaryStream(
cntUsed, bStream, bStream.length )
else
'//
'// different types
have different nulls
'
prepStatement.setNull( cntUsed,
CurrentColumn.Type )
end if
end if '// not a binary type
else
'//
'// everything else pass as string
'
strdata = CurrentColumn.getString
if not CurrentColumn.wasNULL then
prepStatement.setString( cntUsed,
strdata )
else
prepStatement.setNull( cntUsed,
CurrentColumn.Type )
end if
end if
end if
next
'//
'// set error trap
'
on error goto insertDataFormSQLite
'//
'// execute the insert
'
prepStatement.executeUpdate
'//
'// 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
'//
'// insertDataForm
'// execute SQL command
'// binary data not supported
'// SQLite3
'//
sub InsertDataForm(oDataForm as object )
dim CurrentConnection
dim CurrentControl
dim cntr, fldcnt
dim strCmdFirst, strCmdLast, SQLCmd, QuoteString
dim QryComposer
dim CurrentColumn
dim strdata
dim cntUsed
'//
'// 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.createInstance(
"com.sun.star.sdb.SingleSelectQueryComposer" )
'//
'// 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.Type <>
com.sun.star.sdbc.DataType.LONGVARBINARY _
and CurrentColumn.Type <>
com.sun.star.sdbc.DataType.VARBINARY _
and CurrentColumn.Type <>
com.sun.star.sdbc.DataType.BLOB _
and NOT
QryComposer.Columns(cntr).isAutoIncrement _
and NOT
QryComposer.Columns(cntr).AggregateFunction _
and NOT QryComposer.Columns(cntr).Function then
'//
'// include only those controls
types
'// that support bound data controls
'//
'//
'// reversed order
'// form can have control models
after the last
'// data control
'
cntUsed = cntUsed + 1
strCmdFirst = strCmdFirst + QuoteString +
QryComposer.Columns(cntr).RealName + QuoteString
strdata = CurrentColumn.getString
if NOT CurrentColumn.wasNull then
strCmdLast = strCmdLast + " '" +
strdata + " '"
else
strCmdLast = strCmdLast + " NULL "
end if
if cntr <> oDataForm.Columns.count - 1 then
strCmdFirst = strCmdFirst + + ", "
strCmdLast = strCmdLast + ", "
end if
end if
next
'//
'// if not all columns will be sent
'// to the database engine
'// remove the last comma
'
if cntUsed < QryComposer.Columns.count -1 then
strCmdFirst = left( strCmdFirst , len( strCmdFirst ) -
2 )
strCmdLast = left( strCmdLast, len( strCmdLast ) - 2 )
end if
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: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
