[Solved] Help need macro for Base

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
garyatabi
Posts: 4
Joined: Thu Aug 06, 2009 11:53 pm

[Solved] Help need macro for Base

Post by garyatabi »

Hi would be very nice if someone could get me started with a macro for base. I am trying to use a button on a form to add the time and date that when pushed to write to a data field. ( when button1 pushed getdate to field date_and_time)
I do not know how to get the data to write to the field from the macro or were to find the right labels to use in the program

update
I got computer to get the time with "NOW" with button push but I can not find any syntax to write data to table and do not know where to look for this info


Thank you for any help you can give
Last edited by garyatabi on Tue Aug 11, 2009 2:26 am, edited 1 time in total.
OpenOffice 3.1 on Windows xp
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: help need macro for base

Post by DrewJensen »

You have two ways to go:

1) use a little sql statement in your basic macro and write the data directly to the table

2) user a little sql in your macro, or straight basic, and write the data to the form control. Then let the user save the data at their convienence.

Any preference
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
garyatabi
Posts: 4
Joined: Thu Aug 06, 2009 11:53 pm

Re: Help need macro for Base

Post by garyatabi »

Could Someone post some code that would work.I am lost on how to get sql (INSERT INTO) to work in open office
thanks for the help so far it
gave me places to look to learn myself
OpenOffice 3.1 on Windows xp
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Post by DrewJensen »

Hi,

I will put some examples up for you tonight - one more question first. Is the latest post you made related to the first, do you really want to insert a new record or update a field in an existing record?
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
garyatabi
Posts: 4
Joined: Thu Aug 06, 2009 11:53 pm

Re: Help need macro for Base

Post by garyatabi »

sorry
I want to click a button then the macro will get time and date using "now" then add that (time and date) to a table every time it is clicked.
I got the program to do the hello word thing and display the time and date but can not get it to save to table.

example

Table1
ID,time,time2,time3
1,10:30,11:00,12:00
2, add here,11:35,12:05

thanks :D
OpenOffice 3.1 on Windows xp
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Post by DrewJensen »

Well I'm going start pretty much from scratch
[although you may not need some of what I'll hit on, others might so please bear with me,
and since I will use SQL in the macro it is good to see what is really being automated]
..and make a small change to your question.

Finally - I'll attach the database, with the macros embedded and the form to this post.
( Note I will be using the 3.1.1 version of OO.o for this example.)

Create a new Base file (embedded database) named TimeKeeper.odb
It has one table, Times3 with the fields
ID, DT1, DT2, DT3
Table editor
Table editor
Let's look at some basic SQL statements to insert and update the data

I can insert a record into the table by using an INSERT SQL statment.
To do this I open the SQL window from the Tools>SQL menu and enter the command:
INSERT INTO "Times3" ("DT1")
VALUES( CURRENT_TIMESTAMP)
Insert record 0
Insert record 0
Notice that in 3.1.1 the SQL window supports syntax highlighting for the SQL statement.

CURRENT_TIMESTAMP is a macro buit into the HSQLdb databse engine ( it is standard on most RDBMS )
and will return the date & time according to the system the database is running on.
For an embedded database this is of course the PC, but it would work just as well with MySQL
and would be the clock on the server.

Since this is the first record written to the table I know that the value in ID is 0.
(but after this I should not make an assumption about an AUTO_INCREMENT value again)
Opening the data view for the table (double click the table name Times) I can see this.
Table data view after INSERT statment for record 0
Table data view after INSERT statment for record 0
Now back in the SQL window I will update the fields DT2 and DT3. I do this by entering standard SQL UPDATE commands.
UPDATE "Times3"
SET "DT2" = CURRENT_TIMESTAMP
WHERE "ID" = 0

Followed by
UPDATE "Times3"
SET "DT3" = CURRENT_TIMESTAMP
WHERE "ID" = 0

Great - at this point I want to point out that the changes to the record are NOT reflected in the table's data view window - Why?
The data view, and any form attached to this table, got a snapshot of the data when the record set was retrieved for display.
In order to see the changes to the actual data then you will need to reload the record set - click the "Refresh" tool button.

Alright - now at this point I think to myself..in my table definition I set the field DT1 as required.
ID is also required but it is auto generated for me whenever a new record is entered. Could I do that for the DT1 field also?
The answer is yes. DO NOT be followed into using the "Default Value" text box back on the table design editor
- that property is for static strings only, and is only used by form controls. What I want is a vlaue written to field DT1
by the data engine anytime a new record is inserted and I want it to be the current date and time for when that happens.

Again I can do this with a standard SQL command, in the SQL window - the ALTER TABLE command.
ALTER TABLE "Times3" ALTER COLUMN "DT1"
SET DEFAULT CURRENT_TIMESTAMP

Excellent - now if I want to insert a new record via SQL I need not worry about the DT1 field rather I use a statement like this:
INSERT INTO "Times3" ( "ID" )
VALUES ( NULL )

Since the ID field is an Auto_Increment field it is a special case when a NULL is in the INSERT statement
- it triggers the data engine to get the next value for this field
- and that triggers the DEFAULT setting on the DT1 field.

(see next post)
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Post by DrewJensen »

and here is what the data view window looks like after another refresh
table data view after second SQL insert statement
table data view after second SQL insert statement
Note here that the current record in the data view is once again record 0
- this is a consequence of executing the "Refresh" (reload) of the record set.
- because I am going to use SQL in my macro this is significant and I will need to account for this

At this point I'll use the Form wizard to make a forms.
The form will have a grid control on the top and a sub-form of the same table but displaying individual controls for a single record on the bottom.
It will also have three buttons, New Time Record, Enter DateTime 2, Enter DateTime DT3.

Here is the form as the wizard finishes:
Form as built by form wizard
Form as built by form wizard
Notice here that Base doesn't actually have a DateTime control.
- The wizard splits our datatime field into separate Date and Time controls, both in the grid and individualy.
- Both controls in this case are bound to the same field in the table however.

Well I want to change this.
- I select and delee each of the indivudal Time controls in the sub-form
- I replace the Date controls with Fomratted controls
- and set the format for each to what I want, which is a date time string.

Since I'm not actually entering data in the controls this will cause no problems at all.
I then added the three buttons
Replace date controls w/ Formatted
Replace date controls w/ Formatted

(see next post - the macros )
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Post by DrewJensen »

Created a new basic library emeded in the TimeKeeper.odb file
I like to start by creating a skeleton that I can bind my buttons to
- Module 1 at this point is simply this

Code: Select all

OPTION EXPLICIT

'
' Click event for
' button New Time Record
'
sub onClickNewDT1( oEvent as OBJECT )
	NewTimeRecord()
end sub


'
' Click event for
' button Enter DateTime 2
'
sub onClickUpdateDT2( oEvent as OBJECT )
	UpdateTimeStamp( "DT2" )
end sub


'
' Click event for
' button Enter DateTime 2
'
sub onClickUpdateDT3( oEvent as OBJECT )
	UpdateTimeStamp( "DT3" )
end sub


'
' Insert new record into the
' Times3 table
'
' INSERT INTO "Times3" ( "ID" )
' VALUES ( NULL )
'
sub NewTimeRecord()

end sub


'
' Update the field <aFieldName> in the 
' Times3 table with the current
' system date and time
'
' UPDATE "Times3"
' SET "DT3" = CURRENT_TIMESTAMP
' WHERE "ID" = 0
'
sub UpdateTimeStamp( aFieldName as string )

end sub
With this simple skeleton of 5 sub procedures three are actually fnished
- the three that will be assinged to the buttons
- so I just do that now

IMO the nice thing about doing it this way is that given Basic is an interrpruted language
you could at this pont run the form and open the Basic IDE and then as you start to build
the two worker procedures NewTimeRecord and UpdateTimeStamp test them simply by
clicking the buttons on the form.

So here is a copy of the database as it stands at the current date and time for you to do just that.
TimeKeeper_skeleton.odb
Base file with skeleton Basic library
(13.61 KiB) Downloaded 627 times
(see next post - Insert record )
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Post by DrewJensen »

First things first - inserting a new record.

OO.o Basic as you may have already figured out is pretty slim when it comes to functions - because what it does is offer a gateway to the OO.o API.
In the case here I will use a STATEMENT to issue the SQL command.
You can find the nitty gritty documentation for a STATEMENT here:
http://api.openoffice.org/docs/common/r ... ement.html

TIP - in OO.o most (all) sevices such as this have a counterpart, that appends an X to the name,
for instance you should look at this documentation page:
http://api.openoffice.org/docs/common/r ... ement.html

It is at this second reference that you find how to actually execute the SQL.

Also - if you read over the docs you might find yourself asking:
"But how do I create a statement in the first place:"
LOL - they did give a clue as where to look, the last entry on the XSTATEMENT page getConnection reads:


"returns the Connection object that produced this Statement object. "

So off we go to with the hperlink to the docs on CONNECTIONS - to find once again that this is a Service and
what we kind of want is the interface methods...and once again that is the XCONNECTION
http://api.openoffice.org/docs/common/r ... ction.html

Hurray - there is the interface CreateStatement.

New question then:
"Where do I get the connection from?:

*chuckle*...

Let's just cut the bull here..

In version 3.1 OO.o introduced a psuedo variable ThisDataBaseDocument
- it is available ONLY when your basic code is embedded in a Base file and returns...a reference to the Base file.
Makes sense.....

ThisDataBaseDocument is actually an OfficeDatabaseDocument, as defined in the API documents. ( you should be able to find that now using the INDEX at the site)

The connection that the current Base file is using can be retrieved then with
ThisDataBaseDocument.CurrentController.ActiveConnection

LOL - see how I found that.

and with that knowledge the macro starts to look like this:

Code: Select all

'
' Insert new record into the
' Times3 table
'
' INSERT INTO "Times3" ( "ID" )
' VALUES ( NULL )
'
sub NewTimeRecord()
	dim oStatement
	oStatement = ThisDataBaseDocument.CurrentController.ActiveConnection.CreateStatement
	oStatement.ExecuteUpdate( "INSERT INTO ""Times3"" (""ID"") VALUES ( NULL )" )
end sub
NOTE - I had to double quote the double quotes in the string literal...

Go ahead - open the file, and update the procedure - open the form and click on the 'New Time Record' button.

It works, but you need to click that "Refresh" toolbutton again in order to see the new record ...so lets fix that.

To do that we need to change the NewTimeRecord procedure to this:

Code: Select all

sub NewTimeRecord( aResultSet as object)
and then change the button click handling procedure to call it with this.

Code: Select all

sub onClickNewDT1( oEvent as OBJECT )
		NewTimeRecord( oEvent.Source.Model.Parent )
end sub
Every event that you can assign to a basic procedure passes an object of type EventObject, you can find the docs fo that at...
http://api.openoffice.org/docs/common/r ... bject.html


One final change then, add a line to reload the datat

Code: Select all

aResultSet.Reload
While I'm at it, seems like I want to be on the newly inserted record at the form
- add one line and the NewTimeRecord( procedure and it looks like:
- finally I created a statement, so I should dispose of it

Code: Select all

sub NewTimeRecord( aResultSet as object)
	dim oStatement
	oStatement = ThisDataBaseDocument.CurrentController.ActiveConnection.CreateStatement
	oStatement.ExecuteUpdate( "INSERT INTO ""Times3"" (""ID"") VALUES ( NULL )" )
	aResultSet.Reload
	aResultSet.Last
	oStatement.dispose
end sub
With the baic macro in place and having clicked it a few times my form on screen now looks like:
Form with Insert macro coded
Form with Insert macro coded
I really tried to give you lots of links so that you can start to find your own answers...
But - on to the next macro then...

(see next post - Update macro)
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Post by DrewJensen »

Wel it is late so let me just do this.

http://api.openoffice.org/docs/common/r ... le-ix.html

There you can find all you need to follow the last macro:

I added the extra parameter, as I did with the insert procedure for the recordset object
I want to get the current bookmark
- so that I can find my way back here
Used the same basic structure with a statment
- also used the result set to find the current records ID value
Finally - after reloading the record set moved to the saved book mark

The final macro is:

Code: Select all

sub UpdateTimeStamp( aFieldName as string, aResultSet as object )
	dim oStatement
	dim oBkMark
	oBkMark = aResultSet.getBookmark
	oStatement = ThisDataBaseDocument.CurrentController.ActiveConnection.CreateStatement
	oStatement.ExecuteUpdate( "UPDATE ""Times3"" SET """ & aFieldName & """ = CURRENT_TIMESTAMP WHERE ""ID"" = " & aResultSet.getInt( aResultSet.FindColumn( "ID" ) )
	aResultSet.Reload
	aResultSet.movetobookmark( oBkMark )
	oStatement.dispose
end sub
and the Base file
TimeKeeper.odb
File using all embedded SQL in basic
(13.15 KiB) Downloaded 641 times
What this shows is only one way to do this - if you want to look at an alternative...this database can be the exmaple and that can happen tomorrow...
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
garyatabi
Posts: 4
Joined: Thu Aug 06, 2009 11:53 pm

Re: Help need macro for Base

Post by garyatabi »

Thanks a lot for the help

I think I can get it now :bravo:
OpenOffice 3.1 on Windows xp
TonyRogers
Posts: 1
Joined: Wed Aug 19, 2009 1:11 pm

Re: [Solved] Help need macro for Base

Post by TonyRogers »

Hello Drew,

I've just found this post/tutorial, and it seems to be just what I need to help me create my first OOCalc 'application'.

Many thanks for taking your time to help out with this kind of stuff.

Having done a bit of Copy -> Paste -> Edit on the code you've presented above, I've run in to a bit of a problem.

I use v. 2.4 of calc, and I guess that I should have paid more attention to the clue in your post about v. 3.1 having the feature to enable the easy creation of the db connection!

Do you have any clues for me re. how to do this kind of thing with 2.4?

I find that Google is not very helpful with a lot of OO stuff. Maybe I'm not attuned to OO enough to make the best searches?

Thanks in advance,

Tony Rogers


EDIT The following worked for me after some more Googling and experimenting.

Statement=event.Source.Model.Parent.ActiveConnection.CreateStatement
OpenOffice 2.4 on Ubuntu 8.04
andyfff
Posts: 1
Joined: Tue Sep 08, 2009 4:03 pm

Re: [Solved] Help need macro for Base

Post by andyfff »

Hi,
I'm trying to move an access 95 ( yes really!) to Open Office. My bakcground is IT as far back as bing a mainframe programmer in Cobol, but i;ve done a degree using java c++ etc, but thats a bit rusty and have used databases for 20 odd years.
my point is that i've read your posts - but is there any way to attach the SQL to a button simply rather than adding all the dim newrecord stuff
Thanks for your help - my apologies for the gap between what you know and i know!
If i can attach add and delete buttons to a form i'll be on my way to completing this project
All i'll need then is a nice front-end!

cheers
Andyfff.
open office 3.1 on windows xp
Post Reply