Base - Copy column and copy row functions

Keyboard macros or custom scripts

Base - Copy column and copy row functions

Postby DrewJensen » Thu Dec 27, 2007 4:47 am

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   Expand viewCollapse view
function CopyRowaRS 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
oCloneRSaRS,  ColCntr )
            
next
            CopyRow 
True
        end 
if
    
end if
    
end function

function 
CopyColumnaRSSource 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
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Base - Copy column and copy row functions

Postby Villeroy » Sat Jan 05, 2008 7:43 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base - Copy column and copy row functions

Postby DrewJensen » Sat Jan 05, 2008 8:53 pm

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
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Base - Copy column and copy row functions

Postby Villeroy » Sat Jan 05, 2008 10:27 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base - Copy column and copy row functions

Postby Villeroy » Sun Jan 06, 2008 11:22 pm

OK, this version of CopyRow seems to work for me:
Code: Select all   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base - Copy column and copy row functions

Postby DrewJensen » Mon Jan 07, 2008 12:03 am

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
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

Postby bilbo123 » Wed Sep 17, 2008 10:01 am

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
bilbo123
 
Posts: 1
Joined: Wed Sep 17, 2008 7:51 am

Re: Base - Copy column and copy row functions

Postby Villeroy » Wed Sep 17, 2008 1:05 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base - Copy column and copy row functions

Postby tcamdg » Fri Nov 18, 2011 2:01 am

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
tcamdg
 
Posts: 5
Joined: Wed Nov 02, 2011 1:16 am

Re: Base - Copy column and copy row functions

Postby evinther » Fri Dec 23, 2011 8:20 pm

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
evinther
 
Posts: 6
Joined: Thu Sep 08, 2011 1:15 pm

Re: Base - Copy column and copy row functions

Postby papijo » Sun Dec 21, 2014 12:12 pm

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 5.2.3 on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 86
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: Google Feedfetcher and 2 guests