[Solved] Help need macro for Base

Creating a macro - Writing a Script - Using the API

[Solved] Help need macro for Base

Postby garyatabi » Fri Aug 07, 2009 12:08 am

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
garyatabi
 
Posts: 4
Joined: Thu Aug 06, 2009 11:53 pm

Re: help need macro for base

Postby DrewJensen » Fri Aug 07, 2009 3:36 am

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
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Postby garyatabi » Sat Aug 08, 2009 2:09 am

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
garyatabi
 
Posts: 4
Joined: Thu Aug 06, 2009 11:53 pm

Re: Help need macro for Base

Postby DrewJensen » Sat Aug 08, 2009 2:24 am

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?
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Postby garyatabi » Sat Aug 08, 2009 4:55 am

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
garyatabi
 
Posts: 4
Joined: Thu Aug 06, 2009 11:53 pm

Re: Help need macro for Base

Postby DrewJensen » Sat Aug 08, 2009 6:52 am

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 Design.png
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)
SQL_insert.png
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.png
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)
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Postby DrewJensen » Sat Aug 08, 2009 7:20 am

and here is what the data view window looks like after another refresh
Table Data View-2.png
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:
Times3 Form.png
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
Change Controls.png
Replace date controls w/ Formatted



(see next post - the macros )
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Postby DrewJensen » Sat Aug 08, 2009 8:10 am

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   Expand viewCollapse view
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 271 times


(see next post - Insert record )
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Postby DrewJensen » Sat Aug 08, 2009 9:40 am

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   Expand viewCollapse view
'
' 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   Expand viewCollapse view
sub NewTimeRecord( aResultSet as object)


and then change the button click handling procedure to call it with this.
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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_insert_macro.png
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)
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Postby DrewJensen » Sat Aug 08, 2009 10:06 am

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   Expand viewCollapse view
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 307 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...
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Help need macro for Base

Postby garyatabi » Tue Aug 11, 2009 2:23 am

Thanks a lot for the help

I think I can get it now :bravo:
OpenOffice 3.1 on Windows xp
garyatabi
 
Posts: 4
Joined: Thu Aug 06, 2009 11:53 pm

Re: [Solved] Help need macro for Base

Postby TonyRogers » Wed Aug 19, 2009 1:25 pm

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
TonyRogers
 
Posts: 1
Joined: Wed Aug 19, 2009 1:11 pm

Re: [Solved] Help need macro for Base

Postby andyfff » Tue Sep 08, 2009 4:13 pm

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
andyfff
 
Posts: 1
Joined: Tue Sep 08, 2009 4:03 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests