Run external SQL via a button

English
#1

My system info is :

Version: 7.5.5.2 / LibreOffice Community
Build: caf8fe7424262805f223b9a233
Environment: CPU Threads: 4;OS: Windows 10.0 Build 19405
User Interface: UI render: Skia/Raster; VCL:win
Locale: en.GB (en_GB); UI: en-GB
Misc Calc: threaded
Database HSQLDB Embedded

Another question for you folks. First, let me describe what my manual process is. Each month I download a CSV file, save it as a calc file, change the format of each row to suit my db, then appended the data to a certain table. (I have recorded a Macro to do most of this for me automatically). I then call up and run some Update SQL code, which adds values to certain fields in that table, based on the contents of other fields. I make changes to the SQL code from time to time, so that it is up to date for when I run it. The SQL code is in a text file, created using Notepad, saved in the Documents folder of my laptop. All well and good. Now I’m wondering if I can automate things a bit by having a button on a form which, on being clicked, calls up the file containing the SQL and runs the code in it automatically. It would be best if the SQL code remains as an external file, making it easy to make modifications from time to time. So the button would have to be programmed with the name of the SQL file and it’s location on my laptop, then be able to read the contained SQL and execute it. I would make sure the file contains only the SQL code that would be required for the update operation. Is that possible? Thanks.

#2

Forget the spreadsheet. Just import your csv directly into your database. This requires some careful consideration and preparation depending on the exact structure of your incoming csv files. However, it will work as long as the csv structure remains the same.
https://forum.openoffice.org/en/forum/viewtopic.php?t=108073
Try my second appropach of January 2023 with function ImportCSV. It is most versatile and customizable. The conversion of text values into valid database types is just a matter of relatively simple SQL.

1 Like
#3

Thanks but the importing of the CSV data isn’t the subject of my question - I’ve sorted that to my satisfaction using a recorded macro. My question is about running SQL code, via a button, that’s saved in an external txt file.

#6

Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet may be?

1 Like
#4

Yes and No. No, because a button will not execute directly any external file, neither as a macro, nor SQL.
.
Yes, because you can use a macro to do this. You will find several macros in the guide for Base, wich send SQL to a database. You could quite easily extend this macros to load from a text-file on your system. (Or alter the SQL inside the macro, instead of using an external file, but that was not your question.)
.
Security concerns…

1 Like
#5

Thank you. I’ll check that out.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.