Insert ( copy record ) routine -
Drew Jensen <[email protected]> Thu, 11 Feb 2010 09:09:20 -0500
| Newsgroups | gmane.comp.openoffice.dba.user,gmane.comp.openoffice.devel.general |
|---|---|
| Message-ID | <[email protected]> |
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 --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
i108377-all.bas
(text/plain, 14 KB)
' 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 '// '// 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