Re: [libreoffice-users] Base & PostgreSQL Transactions

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.

On 07/19/2013 04:31 PM, Don Parris wrote:
> 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).
> 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
>
>
> On Fri, Jul 19, 2013 at 8:56 AM, Andrew Douglas Pitonyak <
> andrew@pitonyak.org> wrote:
>
> > 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<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#Transaction_Handling>
> > http://wiki.openoffice.org/**wiki/Documentation/DevGuide/**
> > Database/Using_DBMS_Features<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<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<http://www.baseprogramming.com/OOBasicDatabaseDev.pdf>
> >
> > I do not remember if I discuss transactions at all in AndrewBase.odt
> >
> >
> > On 07/18/2013 04:05 PM, Don Parris wrote:
> >
> > > 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
> > --
> > Andrew Pitonyak
> > My Macro Document: 
> > http://www.pitonyak.org/**AndrewMacro.odt<http://www.pitonyak.org/AndrewMacro.odt>
> > Info:  http://www.pitonyak.org/oo.php
> >
> >
> >
> > --
> > To unsubscribe e-mail to: 
> > users+unsubscribe@global.**libreoffice.org<users%2Bunsubscribe@global.libreoffice.org>
> > Problems? http://www.libreoffice.org/**get-help/mailing-lists/how-to-**
> > unsubscribe/<http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/>
> > Posting guidelines + more: http://wiki.**documentfoundation.org/**
> > Netiquette <http://wiki.documentfoundation.org/Netiquette>
> > List archive: 
> > http://listarchives.**libreoffice.org/global/users/<http://listarchives.libreoffice.org/global/users/>
> > All messages sent to this list will be publicly archived and cannot be
> > deleted

--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
Info:  http://www.pitonyak.org/oo.php


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted