OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Run queriess iteratively from within a macro or [Solved]

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
DennisW
Newbie
Newbie


Joined: 20 Nov 2010
Posts: 3
Location: Canada

PostPosted: Sat Nov 20, 2010 6:08 am    Post subject: Run queriess iteratively from within a macro or [Solved] Reply with quote

I want to run a set of queries iteratively from within a macro or Basic program.

I have a set of queries that where originally created in MS Access. I moved my Access database to Base in OO. The queries all function well in Base (they provide valid results so there is no problem there).

I run an initiating query that performs calculations based on data from 3 source data tables and enters (appends) the result into results table1. After several iterations of the initiating query, a second query appends the data from results table1 into results table2. The data in results table1 is then deleted. and the process is re-initiated only this time a 3rd query is used. The 3rd query uses the data from the 3 source data tables plus the results table2 to perform calculations, and the results are then entered (appended) into results table1. As previously specified, after a given number of iterations of this process, the data from results table1 are appended to results table2. The data in results table1 is deleted, and the process is started again using the 3rd query.

The process is repeated (iterated) a specified number of times.

I want to automate the process, so I would like to simply run the existing queries from within a macro or program that would automatically perform the specified number of iterations.

I need to know the syntax for launching (running) queries from within a macro or Basic program that would control the iterative process.

I have read the beginners guide for Base, and I have read the Developer Guide.

In visual basic there is the command "DoCmd.OpenQuery" like this

DoCmd.OpenQuery "queryname", acViewNormal, acEdit
DoCmd.Close

This command allows me to launch, from within visual basic, a query that was created and saved in the Access database.

I have not been able to find or identify a corresponding command in Basic, and I don't know how create macros without a form.


Dennis W
_________________
Dennis W


Last edited by DennisW on Sun Nov 21, 2010 4:51 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Nov 20, 2010 9:04 am    Post subject: Reply with quote

Hello

Study pdf of Benitez.
http://www.baseprogramming.com/resources.html
The last two lines

I think study also this post.
http://www.oooforum.org/forum/viewtopic.phtml?t=102514&highlight=

I think also look for the programming language tool make by the HSQLDB team. That can maybe more easy then learning macros in OOo.

Romke
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Nov 20, 2010 11:44 am    Post subject: Reply with quote

Dennis:

Welcome to OpenOffice Base.

This may be more information compared to what you asked, but, thought I would post it anyway . . . for you and others reading this.

The code below, gets a Query defined in your "Registered Data Name" . . . and . . . runs it.

For documentation of the command:
  1. API commands by name

    http://api.openoffice.org/docs/common/ref/index-files/index-1.html

  2. Get Query By Name ( Get the SQL defined for the Query )

    http://api.openoffice.org/docs/common/ref/com/sun/star/container/XNameAccess.html#getByName

  3. Execute Query ( Run It )

    http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XPreparedStatement.html#executeQuery


SAMPLE MACRO CODE to run a Query stored in the "registered database". Includes some 'error messages'. Smile

Code:
Sub GetRunQuery

   Dim oStatement, oDBSource, oConnection, oQueries, oResultSet As Object

   sQuery = "MyQueryName"     '<< name of Query to run . . . Name is CASE sensitive
   sDBName = "MyDatabaseName" '<< registered datasource . . . Change for YOUR DB name name is CASE sensitive

   oDatabaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )

   If oDatabaseContext.hasByName(sDBName) Then
      oDBSource = oDatabaseContext.GetByName(sDBName)

      oConnection = oDBSource.GetConnection("", "")   
      oStatement = oConnection.createStatement()
   Else
      MsgBox "No Registered DB Name:   " + sDBName,16,"Please Correct DB Name"
      Exit Sub
   End If

   oQueries = oDBSource.QueryDefinitions   '<<queries in datasource
   
   If ( oQueries.hasByName( sQuery ) ) Then
        oQuery = oQueries.getByName( sQuery )   '<< the query in question
        sQuery = oQuery.Command                 '<< content of query
   Else
      BEEP
      MsgBox "No query found by name:   " + Chr$(13) + Chr$(13) + sQuery _
             + Chr$(13) + Chr$(13) + "Query names are CASE ( UPPER / lower ) sensitive.",16,"Please Correct Query Name"
      Exit Sub
   End If

   oResultSet = oStatement.executeQuery( sQuery )     '<<  Execute the Query and send data to oResultSet

   
End Sub   'GetRunQuery

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
DennisW
Newbie
Newbie


Joined: 20 Nov 2010
Posts: 3
Location: Canada

PostPosted: Sun Nov 21, 2010 4:53 am    Post subject: Reply with quote

Great response.

I appreciate the help.

Thanks
_________________
Dennis W
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group