I have more a small db from mysql to base. I have a procedure which i run to balance all my accounts/ledgers.
In oracle and mysql - i have used procedures. These procedures will have cursors and looping to do the stuff…
How is it done in librebase… Can macros loop through tables and run sql in between.
Hello,
Can macros loop through tables and run sql in between.
The answer is yes. However, learning to use macros in LO (Base or any other module) is certainly challenging. There are many aspects to cover. The real question is if using macros to accomplish your task is needed at all. The majority of cases do NOT need macros. You need to understand Base and how it works first.
A place to start is on this post - To learn LibreOffice Base are there introductions or tutorials?
Edit: This post may also be of interest - Database procedures and callable Statements
If this answers your question please tick the 
(upper left area of answer). It helps others to know there was an accepted answer.
Can macros loop through tables and run sql in between.
Yes, certainly. There are quite a few examples on this site and other places. The exact implementation depends on the details of the problem.
how to write stored procedures in libreoffice base
The embedded engine is HSQLDB 1.8. This version doesn’t allow stored procedures except for static Java methods. See Chapter 9. SQL Syntax. Note that using an embedded database is not recommended.
HSQLDB 2.0 and higher does support stored procedures: Chapter 9. SQL-Invoked Routines.
Base can use mysql or oracle as the engine as well, and then you can use stored procedures as normal.
EDIT:
You mentioned macro can do loops… can you send any link.
- In base Convert Query to macro - #3 by Ratslinger
- Calc macro select two or more fields from MYSQL
- Using Basic macro functions in SQL statements? - #8 by Ratslinger
When you mention embedded is not recommended what is the way?
Use a split database setup as explained in the “not recommended” link in the original answer.
My standard recommendation is MySQL since there are many examples for Base. When creating a new Base file, specify Connect to an existing database → MySQL. Another good choice for Base is the most recent version of HSQLDB, currently 2.4.0.
thanks. yes the embedded db is 1.80. so procedures wont run!.. I got that. You mentioned macro can do loops… can you send any link. I have 3 cursors and 3 loops… what is a best way?
when you mention embedded is not recommended what is the way? use hsqldb and again a jdk with front end and all that? i tried moving to librebase… to avoid html/css/php etc for getting a few master details forms,queries and reports… the schema itself is half a dozen tables with general ledger and investments ledger with a bunch of views/reports and charts… did not want to run an Oracle Apex/12g for a small app. but for the ledger procedure (which is key) the rest is addressed in Libre…