Base - Copy column and copy row functions

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Base - Copy column and copy row functions

Post by DrewJensen »

Someimes when entering data one needs to create a number of records that are almost identical - in this case a copy record function would be useful.

The following functions implement a simple way to do this.

Limitations
- the current copy row routine copies all columns in a result set - for example all rows in a table grid.
- the routine for all practicle purposes will work with only a single table.
- if some columns have default values set at the database these willbe ignored. ( an example might be a row for the current entry time )

How to use this routine.
The simplest way would be to add a button to a form and then assign the 'When initializing' event on the button to something like this

Sub onBtnClickCopyRow( oEvent as object )
copyRow( oEvent.Source.Model.Parent )
end sub

Code: Select all

function CopyRow( aRS as com.sun.star.sdb.ResultSet ) as boolean
    dim oCloneRS as variant
    dim ColCntr as integer    

    CopyRow = false
    
    if aRS.ResultSetConcurrency = com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY then
        msgBox( "ResultSet is Read Only" )
        exit function
    else
        if aRS.IsModified then
            if msgBox( "Save record before copy?",4,"Current Record Modified") <> 6 then
                exit function
            else
                if aRS.IsNew then
                    aRS.InsertRow
                else
                    aRS.UpdateRow
                end if
            end if
        else
            oCloneRS = aRS.createResultSet()
            aRS.moveToInsertRow()
            for ColCntr = 1 to aRS.Columns.Count -1
                CopyColumn( oCloneRS, aRS,  ColCntr )
            next
            CopyRow = True
        end if
    end if
    
end function

function CopyColumn( aRSSource as com.sun.star.sdb.ResultSet, _
                     aRSTarget as com.sun.star.sdb.ResultSet, _
                     aColNum as Integer ) as boolean
                     
    dim dType as integer


    CopyColumn = False

    ' DO NO copy an autoincrement field
    if aRSSource.Columns( aColNum ).isAutoIncrement then
        CopyColumn = True
    else

        dType = aRSSource.Columns( aColNum ).Type
        
        select case dType

            case com.sun.star.sdbc.DataType.BIT 
                aRSSource.Columns( aColNum ).getByte
                if not aRSSource.Columns( aColNum ).wasNull then
                    aRSTarget.Columns( aColNum ).UpdateByte( aRSSource.Columns( aColNum ).getByte )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif
            
            case com.sun.star.sdbc.DataType.TINYINT
                aRSSource.Columns( aColNum ).getByte
                if not aRSSource.Columns( aColNum ).wasNull then
                    aRSTarget.Columns( aColNum ).UpdateByte( aRSSource.Columns( aColNum ).getByte )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.SMALLINT 
                aRSSource.Columns( aColNum ).getShort
                if not aRSSource.Columns( aColNum ).wasNull then
                    aRSTarget.Columns( aColNum ).UpdateShort( aRSSource.Columns( aColNum ).getShort )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.INTEGER
                aRSSource.Columns( aColNum ).getInt
                if not aRSSource.Columns( aColNum ).wasNull then
                    aRSTarget.Columns( aColNum ).UpdateInt( aRSSource.Columns( aColNum ).getInt )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.BIGINT
                aRSSource.Columns( aColNum ).getLong
                if not aRSSource.Columns( aColNum ).wasNull then
                    aRSTarget.Columns( aColNum ).UpdateLong( aRSSource.Columns( aColNum ).getLong )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.FLOAT, _
                 com.sun.star.sdbc.DataType.REAL, _
                 com.sun.star.sdbc.DataType.DECIMAL, _
                 com.sun.star.sdbc.DataType.NUMERIC
                 
                aRSSource.Columns( aColNum ).getFloat
                if not aRSSource.Columns( aColNum ).wasNull then
                    aRSTarget.Columns( aColNum ).UpdateFloat( aRSSource.Columns( aColNum ).getFloat )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.CHAR, _
                 com.sun.star.sdbc.DataType.VARCHAR, _
                 com.sun.star.sdbc.DataType.LONGVARCHAR
                 
                aRSSource.Columns( aColNum ).getString
                if not aRSSource.Columns( aColNum ).wasNull then        
                    aRSTarget.Columns( aColNum ).UpdateString( aRSSource.Columns( aColNum ).getString )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.DATE
                aRSSource.Columns( aColNum ).getDate
                if not aRSSource.Columns( aColNum ).wasNull then        
                    aRSTarget.Columns( aColNum ).UpdateDate( aRSSource.Columns( aColNum ).getDate )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.TIME
                aRSSource.Columns( aColNum ).getTime
                if not aRSSource.Columns( aColNum ).wasNull then        
                    aRSTarget.Columns( aColNum ).UpdateTime( aRSSource.Columns( aColNum ).getTime )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.TIMESTAMP
                aRSSource.Columns( aColNum ).getTimeStamp
                if not aRSSource.Columns( aColNum ).wasNull then        
                    aRSTarget.Columns( aColNum ).UpdateTimeStamp( aRSSource.Columns( aColNum ).getTimeStamp )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.BOOLEAN
                aRSSource.Columns( aColNum ).getBoolean
                if not aRSSource.Columns( aColNum ).wasNull then        
                    aRSTarget.Columns( aColNum ).UpdateBoolean( aRSSource.Columns( aColNum ).getBoolean )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif

            case com.sun.star.sdbc.DataType.CLOB
                aRSSource.Columns( aColNum ).getClob
                if not aRSSource.Columns( aColNum ).wasNull then        
                    aRSTarget.Columns( aColNum ).UpdateClob( aRSSource.Columns( aColNum ).getClob )
                else
                    aRSTarget.Columns( aColNum ).UpdateNull
                endif    

            case com.sun.star.sdbc.DataType.SQLNULL
                    aRSTarget.Columns( aColNum ).UpdateNull

        end select

    end if 
    
    CopyColumn = True
    
end function
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base - Copy column and copy row functions

Post by Villeroy »

I tried this macro and I found that it silently fails to insert a new row if the cursor has not been moved to the end. Instead it overwrites an existing record below the visibly loaded records.
I created a most simple form, containing just one table grid linked to a test table. Currently the table has 37 records, the grid shows 10 of them at a time.
I added a copy button and assigned your suggested "onBtnClickCopyRow" to event "Before commencing".
Loading the form, I'm at record 1 of *11.
Pushing the button, I get the 12th record overwritten with a copy of the first one.
Before:

Code: Select all

0	Forname ZERO	Surname ZERO	01.01.70	0
1 ...
2 ...
...
11	Forname 29	Surname 29	29.11.66	0
12 ...
...
36 last record
After:

Code: Select all

0	Forname ZERO	Surname ZERO	01.01.70	0
1 ...
2 ...
...
11	Forname ZERO	Surname ZERO	01.01.70
12 ...
...
36 last record
Obviously aRS.moveToInsertRow() does not reach behind very last record. It goes behind the last currently visible record where the existing record gets overwritten. The macro copies to a new record as expected if I manually move to the very last record before pushing the button.

Minor problem:
Sub copyRow handles a dirty record by message box "Save record before copy?". If you answer "Yes", the record gets saved but nothing will be copied unless you push the button another time.

Code: Select all

if aRS.IsModified then
    <ask message box>
    <if "No" then exit>
else
    <do action>
endif
I replaced the else with the endif, so the record gets copied in any case except for answer "No".

Code: Select all

if aRS.IsModified then
    <ask message box>
    <if "No" then exit>
endif
<do action>
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Base - Copy column and copy row functions

Post by DrewJensen »

Thanks for the feedback and the catch on the If isModified, I'll update the listing.

Regarding the problem with the record being over written I can't reproduce it. Before I posted this it was tested on XP - 2.3.1 and Kubuntu64 7.1 - 2.3.1 ( distro ). Just now I also tested it on 2.2.1-04 i586 under Mandriva - using pretty the scenario you outlined but can't get the problem to happen.

I'll install a copy of 2.3 a little later under - although I suppose it wouldn't hurt to put a test in, just to be sure that we landed on the insertRow. ( I kind of hate to do that as I was going to use this for the guts of a programmable import / export function that doesn't rely on the Copy Table wizard , for that any extra check is going to slow things down a good deal, but if I need to do that for 2.3 so be it )
 Edit: Ah ha - just made the change for the isModified logic and as soon as I did - I can reproduce the data problem you report, just as you report it. A bit more thinking then about handling this isModified state is in order apparently. 
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base - Copy column and copy row functions

Post by Villeroy »

I upgraded to 2.3.1. It makes no difference.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base - Copy column and copy row functions

Post by Villeroy »

OK, this version of CopyRow seems to work for me:

Code: Select all

function CopyRow( aRS as com.sun.star.sdb.ResultSet ) as boolean
dim oCloneRS as variant
dim ColCntr as integer    
	CopyRow = false
	if aRS.ResultSetConcurrency = com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY then
		msgBox( "ResultSet is Read Only" )
		exit function
	else
		if aRS.IsModified then
			if msgBox( "Save record before copy?",4,"Current Record Modified") <> 6 then
				exit function
			else
				if aRS.IsNew then
					aRS.InsertRow
				else
					aRS.UpdateRow
				end if
			end if
		endif
		oCloneRS = aRS.createResultSet()
		if aRS.last() then 'returns True on success
			aRS.moveToInsertRow()
			for ColCntr = 1 to aRS.Columns.Count -1
				CopyColumn( oCloneRS, aRS,  ColCntr )
			next
			CopyRow = True
		else
			msgBox( "Could not move to end of recordset!",16 , "macro: CopyRow"
		endif
	end if
end function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Base - Copy column and copy row functions

Post by DrewJensen »

Alright I see where that would work, but it should not be necessary to move to the last record.

Move to insertRow is not the same as LastRecord + 1.

OK - I wrote that above and before I hit submit I tested that statement - I'm wrong - at least when the GUI is being used. The table grid component does want to move to the last record.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
bilbo123
Posts: 1
Joined: Wed Sep 17, 2008 7:51 am

Re: Base - Copy column and copy row functions

Post by bilbo123 »

Hi!

I just tried the above macro but if I run it in a form, I receive an error message "wrong number of arguments". I have a database with one table and one form containing all of the fields.

At the macro editor, in the CopyRow function the "run step-by-step" stops at the row

"if aRS.ResultSetConcurrency = com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY then"

and if I delete this IF commend, it stops also at the row
"if aRS.IsModified then"

it says both time that the "argument cannot be omitted"

I'm stuck, I don't know what went wrong :( Do you have a hint perhaps??

Thank you!!

Chris (Bilbo123)
OOo 2.3.X on Ms Windows W2k
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base - Copy column and copy row functions

Post by Villeroy »

Do not simply run it. Read the instructions what it does and how it is supposed to be invoked.
 Edit: bilbo123's problem seems to be solved: http://www.oooforum.org/forum/viewtopic ... 391#298391 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tcamdg
Posts: 5
Joined: Wed Nov 02, 2011 1:16 am

Re: Base - Copy column and copy row functions

Post by tcamdg »

Will this macro work with more recent versions of OpenOffice?

There does not seem to be a "When initializing" event now...

And when I try to assign this macro to some other event, I get an error about ResultSetConcurrency "property or method not found."
LibreOffice 3.4 / Ubuntu 11.10 / HSQLDB 2.2.5
evinther
Posts: 6
Joined: Thu Sep 08, 2011 1:15 pm

Re: Base - Copy column and copy row functions

Post by evinther »

I just copied Drew code an applied it in OO 3.3 Base and it works perfectly. Thanks Drew.

Erik
OpenOffice 3.3.0 on Windows 7
papijo
Posts: 90
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: Base - Copy column and copy row functions

Post by papijo »

evinther wrote:I just copied Drew code an applied it in OO 3.3 Base and it works perfectly. Thanks Drew.
Erik
Same for me. Drew's macro code works fine on Apache OpenOffice 4.1.1 and LO: LibreOffice 4.3.3.2. Many thanks, Drew! I am of opinion that this "duplicate record" feature ought to be part of AOO/LO standard functions. ;)
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
Post Reply