Ask Your Question
0

how to write stored procedures in libreoffice base

asked 2018-02-14 15:13:45 +0200

sree gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-02-14 20:41:41 +0200

Jim K gravatar image

updated 2018-02-15 14:40:59 +0200

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 http://www.hsqldb.org/doc/1.8/guide/c.... Note that using an embedded database is not recommended.

HSQLDB 2.0 and higher does support stored procedures: http://hsqldb.org/doc/2.0/guide/sqlro....

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.

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.

edit flag offensive delete link more

Comments

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?

sree gravatar imagesree ( 2018-02-15 08:28:15 +0200 )edit

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

sree gravatar imagesree ( 2018-02-15 08:35:45 +0200 )edit
0

answered 2018-02-14 16:59:18 +0200

Ratslinger gravatar image

updated 2018-02-14 20:10:22 +0200

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-14 15:13:45 +0200

Seen: 108 times

Last updated: Feb 15