Base & PostgreSQL Transactions

users
#1

Hi all,

I have a 4-table DB in PostgreSQL to which I connect using LibreOffice
Base. I have a form with 2 subforms in place that allows me to:
Select an existing entity (from the ENTITY table)
Add a new financial transaction (to the TRANSREC table)
Add new line items for each transaction (to the TRANSDETAILS table).

I would like to take advantage of PostGRESQL's underlying transaction
(start, rollback, commit, etc.) to ensure that a given transaction and its
line items are entered as one single transaction. I just don't know how to
implement that using Base as a front-end.

Maybe I really need something like macros and BASIC or Python to make such
a thing work? If anyone knows a good tutorial on this subject, I can
certainly read - just haven't really found anything yet.

Thanks!
Don

#2

Hi :slight_smile:
If you are looking for a good guide about Macros then Andrew Pitonyak's guide is supposedly the best
https://wiki.documentfoundation.org/Documentation/Other_Documentation_and_Resources#Programmers
Apparently it is well worth the money but you might want to have a quick look at the official guides for free first.  Andrew wrote most of the chapters about Macros in those too as a first step into understanding Macros

So, start with Chapter 9 of the Getting Started Guide
https://wiki.documentfoundation.org/Documentation/Publications#Getting_Started_with_LibreOffice
Maybe have a look at Chapter 12 in the Calc Guide
https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Calc_Guide
because spreadsheets and databases share some common ground i suspect.  Also this whole handbook might be helpful!!
https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Handbook

Note that the entire Base Handbook has been updated for the 4.0.x branch of LibreOffice already even though individual chapters were not done separately.  If you want the separate chapters you can always get the almost identical ones for the 3.5-3.6 branches.

I'm not entirely sure any of this helps you but hopefully others will appear later to let you know if you really do need macros.  Obviously Python is a more useful language once you have gotten to grips with it but Basic might be easier to learn
Regards from
Tom :slight_smile:

#3

really found anything pertaining to my question so far, hence my asking
here.

#4

Have you read this document?

http://www.baseprogramming.com/OOBasicDatabaseDev.pdf

Iain

#5

Hi :slight_smile:
Ahhh, i usually try to ask first and then start reading through documentation.  If i find the answer first then i post it back to the list.  It doesn't always work out that way as a google search and an initial "quick glance" through documentation can sometimes become too absorbing and time drifts. 
Regards from
Tom :slight_smile:

#6

The ODF Authors are working on a DB guide, I do not know the current state of it, have you looked for it (or at it)?
https://wiki.documentfoundation.org/images/5/50/BH40-BaseHandbook.odt

These may also be of interest:

http://wiki.openoffice.org/wiki/Documentation/DevGuide/Database/Using_DBMS_Features#Transaction_Handling
http://wiki.openoffice.org/wiki/Documentation/DevGuide/Database/Using_DBMS_Features
http://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/TransactionIsolation.html

This document mentions transactions in passing
http://www.baseprogramming.com/OOBasicDatabaseDev.pdf

I do not remember if I discuss transactions at all in AndrewBase.odt

#7

Thanks Andrew,

So far, none of the links really strike me as helpful. Sorry, but this is
fairly new territory to me.

I had hoped I would find a tutorial something along the lines of:
We're going to create a way for users to enter/update data across a few
tables in a single transaction.
Here's how you do it using macros/BASIC... Here's how you do it using the
DB's native capabilities...
In order to stop our form from entering the data into each field as we
type, we need to do so and so...
Now to collect all the data and click submit...

How does one prevent the form "inserting" the data "on the fly" - do I use
a macro? Should I write a function or procedure on my PGSQL server to run
when the user clicks "Submit" in the base form? And how do I get the
"Submit" action down to Postgres to run the transaction procedure?

I get the commit() function (
http://wiki.openoffice.org/wiki/Documentation/DevGuide/Database/Using_DBMS_Features).
..

But isn't there a "BEGIN" statement?

I think what I will do is move my DB forward at its current level and read
up on transactions, triggers and procedures - as well as on LO Basic &
Macros - so I can implement the transaction stuff later. I bet it's a lot
simpler to understand than I think. But having to look in 20 different
locations for little pieces of info - and then figure which pieces go where
in my problem is more than I really have time for right now.

If & when I do figure it out, I'll have to write a tutorial on it for sure.

Regards,
Don

#8

Hey Don,

I've not bought one yet, but this is the best reference I've found out about for LO Base. It's based on OOo Base, but that's most likely not a real issue. The one biggest problem with LO Base is the decided lack of documentation.

http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Dstripbooks&field-keywords=Database+Programming+with+OpenOffice.org+Base+%26+Basic+

#9

Thanks Mark, that certainly looks interesting. I may just check it out.
Even so, that puts a damper on a short-term solution.

#10

Let me start by saying that I am not a DB expert and, although I have done significant work using DB and using transactions, I have not done so using Base or LO in general.

More comments in-line.

I had hoped I would find a tutorial something along the lines of:
We're going to create a way for users to enter/update data across a few
tables in a single transaction.
Here's how you do it using macros/BASIC... Here's how you do it using the
DB's native capabilities...
In order to stop our form from entering the data into each field as we
type, we need to do so and so...
Now to collect all the data and click submit...

For all of my use cases, none of this has mattered, so I never considered it. I assume that you need to set "autocommit" to off on the connection.

setAutoCommit(False):

Enable (True) or disable (False) a connection's auto-commit mode. In auto-commit mode, all SQL statements are executed and committed as individual transactions. Otherwise, SQL statements are grouped into transactions that are terminated by a call to either commit() or rollback(). A commit occurs when a statement completes or the next execute occurs, whichever comes first. If a result set is obtained, the statement completes when the last row from the result set is retrieved or the result set is closed.

So, I assume that you would simply set auto commit to false, and then, when you are finished, call "commit()" on the transaction. Sadly, I have no idea at all if the Form's behavior will over-ride what you do and issue a commit as you fill in each sub-form.

I have no idea if you need to consider "Deferrability" in all this (deferrability is related to foreign constraints).

#11

Hi Don,

One possible way is to set up your form (which after all, is only a Writer or Calc document) as a dummy interface that is not bound to any data fields, and then use Basic to manage the data entered by the user as an array, construct a SQL statement, connect to the db, and then commit that statement.

I haven't tried this, but in theory, it should work.

Alex

#12

You know Alex, when you put it that way, it seems so obvious.... Most great ideas seem so AFTER someone thinks of them. I tip my hat to you sir!

#13

Thanks for the help. This gives me some idea of the general approach I can
take. I very much appreciate it. It probably will still take some time
to work out the details, as I am not much of a programmer (though I do
dabble).