Macro to copy data between MariaBD database tables

julianj · November 17, 2022, 6:23pm

MariaDB 10.11.0
Version: 7.4.2.3 (x64) / LibreOffice Community
Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf
CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: CL
Windows 11
|Processor|AMD Ryzen 9 5900HX with Radeon Graphics 3.30 GHz|
|Installed RAM|32.0 GB (31.4 GB usable)|
|Device ID|83E8E3B7-1CFE-404B-8A70-4C7F14FDFBCE|
|Product ID|00330-50955-86349-AAOEM|
|System type|64-bit operating system, x64-based processor|

New poster, not a DBA by any means and a real novice at macros.

I have a simple database consisting of 2 tables.
From a form I am able to put data into one of the tables but when I have all the data (4 fields) I would lie to copy it from on table to the other.
The source table is just used to gather the data and not to store it.
The following code runs fine from the Execute SQL Statment window, but I can’t get it to work in a macro.
INSERT INTO tbl_shooting_record (membership, calibre,type,serial_number) SELECT membership_number, calibre,type,serial_number FROM tbl_filter_members WHERE ID = 1 AND membership_number != 0

I have looked at:
https://ask.libreoffice.org/t/run-sql-in-the-current-base-database/47965

but I can’t get it to work, I tried al options, the line I used there was:
oStatement.execute(“INSERT INTO ““tbl_shooting_record”” (”“membership”", ““calibre””,"“type”","“serial_number”") SELECT ““membership_number””, ““calibre””,"“type”","“serial_number”" FROM ““tbl_filter_members”” WHERE ID = 1 AND ““membership_number”” != 0 ")

The layout of my form is shown in the attached photo. I’m using Push Button 2 to run the macro.

When I press the button the mouse does the spinning thing, but I can’t see anything happening and if I look into the destination table it hasn’t been updated.

Thanks for any help.

EDIT

I’ve just seen a post saying that I should be using back tick and not double quotes so I updated my line to:
oStatement.execute("INSERT INTO tbl_shooting_record (membership,calibre,type,serial_number) SELECT membership_number,calibre,type,serial_number FROM tbl_filter_members WHERE ID = 1 AND membership_number != 0 ")
But still nit updating.

Villeroy · November 17, 2022, 6:34pm

Forget about macros.
Having a (sub-)form with a table control showing the record set to be copied, you can click the grey upper left corner, right-click>Copy, then select the icon of the target table and paste.

julianj · November 17, 2022, 6:42pm

For a user input form that won’t be very convinient.
M y users will only have access to a form, I will end up having LO hidden from them so they can’t easily see the tables.
The data shown in the SubForm_Grid contains more data than I need and is created from a query of the form I want to add the data into.
I need to have this as simple as possible, but I will look into this as I may be able to use this for myself.

Thanks for your reply

Ratslinger · November 17, 2022, 9:41pm

That indicates there are likely issues with your code. Need to see all the applicable code and not just the SQL line.

julianj · November 17, 2022, 10:02pm

THis is what I’ve tried, only one version in at a time so no duplicate “Sub CopyData” entries:

Sub CopyData
if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
    ThisDatabaseDocument.CurrentController.connect
endif
Dim oStatement As Object
oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
oStatement.execute("INSERT INTO `tbl_shooting_record` (`membership`,`calibre`,`type`,`serial_number`) SELECT `membership_number`,`calibre`,`type`,`serial_number` FROM `tbl_filter_members` WHERE ID = 1 AND `membership_number` != 0 ")
End Sub

Sub CopyData 
if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
    ThisDatabaseDocument.CurrentController.connect
endif
Dim oStatement As Object
oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
oStatement.execute("INSERT INTO `tbl_shooting_record` (`membership`,`calibre`,`type`,`serial_number`) SELECT `membership_number`,`calibre`,`type`,`serial_number` FROM `tbl_filter_members` WHERE ID = 1 AND `membership_number` != 0 ")
End Sub

Sub CopyData 
if IsNull(ThisComponent.CurrentController.ActiveConnection) then
    ThisComponent.CurrentController.connect
endif
Dim oStatement As Object
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
oStatement.execute("INSERT INTO `tbl_shooting_record` (`membership`,`calibre`,`type`,`serial_number`) SELECT `membership_number`,`calibre`,`type`,`serial_number` FROM `tbl_filter_members` WHERE ID = 1 AND `membership_number` != 0 ")
End Sub

Ratslinger · November 17, 2022, 10:20pm

@julianj
Please read This is the guide - How to use the Ask site? which will help in proper formatting.

Ratslinger · November 17, 2022, 10:49pm

@julianj
This works for me from form button (using MySQL 8.x & LO v7.4.2.3) IF the record is saved first:

Sub CopyData
if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
    ThisDatabaseDocument.CurrentController.connect
endif
Dim oStatement As Object
oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
oStatement.execute("INSERT INTO `tbl_shooting_record` (`membership`,`calibre`,`type`,`serial_number`) SELECT `membership_number`,`calibre`,`type`,`serial_number` FROM `tbl_filter_members` WHERE ID = 1 AND `membership_number` != 0 ")
End Sub

Not saving first gives the result you have. If the record is not saved first, then it is not in the table to b copied to another table.

julianj · November 18, 2022, 6:16pm

Well in my testing last night it wouldn’t work, tonight I come back and created a blank form with just the button and the macro works.

I just need to find out whot the issue with my original form is, something for me to work on.

@Ratslinger many thanks for your help and not just on this question on plenty of others I’ve had before.