| View previous topic :: View next topic |
| Author |
Message |
Dale Firmin
General User
Joined: 19 Feb 2012
Posts: 5
|
 Posted: Sun Feb 19, 2012 12:08 pm Post subject: BASE: running a query |
 |
|
Having trouble creating a macro to run a query. Here's what I have so far:
| Code: | REM ***** BASIC *****
Sub Booking_Date
Dim oQuery As Object
Dim oBooking_Date as object
Dim oexecuteQuery as object
oQuery = oQuery.getByName ("Booking_Date")
executeQuery = ("Booking_date")
end sub
|
Any ideas or suggestions.
Moderation probe1: set descriptive subject; set CODE tags |
|
| Back to top |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Wed Feb 22, 2012 10:57 am Post subject: |
|
|
Please look at the sample Macro code by Sliderule at the link below:
http://www.oooforum.org/forum/viewtopic.phtml?t=110660
Short answer, you did NOT first connect to the Registered database. 
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 |
|
|
Dale Firmin
General User
Joined: 19 Feb 2012
Posts: 5
|
| Posted: Thu Feb 23, 2012 4:50 am Post subject: Connect to Database |
|
|
| Thanks for the help as I am new to programming. Does the rest of my code look okay? For instance the oQuery and executeQuery commands? Again, thanks for the help. |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Thu Feb 23, 2012 6:21 am Post subject: Re: Connect to Database |
|
|
| Dale Firmin wrote: | | Thanks for the help as I am new to programming. Does the rest of my code look okay? For instance the oQuery and executeQuery commands? Again, thanks for the help. |
That is why I gave you sample code that does work . . . so . . . you could see the use of the executeQuery command in the link I gave you above.
For documentation on the executeQuery command . . . see:
http://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/XStatement.html#executeQuery
Pay particular look at how to run a query with the executeQuery command, and, how it returns a result set.
Sliderule
Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
Dale Firmin
General User
Joined: 19 Feb 2012
Posts: 5
|
| Posted: Fri Feb 24, 2012 5:35 am Post subject: Still at it |
|
|
Thanks for the help. This is what I have thus far......Let me know what you think.....
| Code: |
REM ***** BASIC *****
Sub Booking_Date
Dim oConnection as object
dim oQuery as object
dim oDBSource as object
dim oResultSet as object
dim oStatement as object
oConnection = oDBSource.GetConnection("Antenna_2.odb_1.odb_0.odb")
oQuery = oDBSource.QueryDefinitions
oResultSet = oStatement.executeQuery("Booking Date")
End Sub |
Tagged code - floris v, moderator |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Fri Feb 24, 2012 7:10 am Post subject: |
|
|
Dale Firmin:
I do not know what floris v "taggged" . . . or changed in your code . . . I do not know if what I see now is what you really wanted.
A problem I am having is, I have no idea what you want to do . . . I mean . . . after you run the Query ( I am assuming this is a Select Query . . . to just READ the database, not to make changes to any of the data in your database ). I prefer to make comments in my macros, so, I and others will know why statements ( lines of code ) are present.
| Code: | REM ***** BASIC *****
Sub Booking_Date
Dim oConnection as object
dim oQuery as object
dim oDBSource as object
dim oResultSet as object
dim oStatement as object
dim oDatabaseContext as object ' Added by Sliderule for consistency
oDBSource = createUnoService( "com.sun.star.sdb.DatabaseContext" ) ' Added by Sliderule
' the value between the parenthesis below is the REGISTERED NAME of the database
' as shown in the OpenOffice Menu: Tools -> Options... -> OpenOffice.org Base -> Databases
oConnection = oDBSource.GetConnection("Antenna_2.odb_1.odb_0.odb") ' << Should be changed by Dale
oStatement = oConnection.createStatement()
oQuery = oDBSource.QueryDefinitions
' About to run the Query below and put results in oResultSet
oResultSet = oStatement.executeQuery("Booking Date")
' This is where you code what you want to do with the OBJECT oResultSet
' This is just an example using StarBasic While Statement ( help file F1 )
'Loop through all Rows returned in the event more than one row returned
While oResultSet.next
MsgBox oResultSet.GetString(1) 'Since first column returned use number 1
' If you want to see more Fields from the Query
' . . . for each field use another statement like above
' For example:
' MsgBox oResultSet.GetString(2) 'Since second column returned use number 2
Wend '<<< End of While Loop for oResultSet
MsgBox "Finished looping through oResultSet"
End Sub |
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.
Last edited by Sliderule on Fri Feb 24, 2012 7:42 am; edited 1 time in total |
|
| Back to top |
|
|
Dale Firmin
General User
Joined: 19 Feb 2012
Posts: 5
|
| Posted: Thu Apr 05, 2012 8:55 am Post subject: Registered database name |
|
|
I'm sorry for not having replied before now. I do indeed appreciate your help. I'm stll getting an error message regarding the database name. I would like to copy and paste the code I currently have so someone could make any corrections needed. Thanks again.
REM ***** BASIC *****
Sub Booking_Date
Dim oConnection as object
dim oQuery as object
dim oDBSource as object
dim oResultSet as object
dim oStatement as object
dim oDatabaseContext as object ' Added by Sliderule for consistency
oDBSource = createUnoService( "com.sun.star.sdb.DatabaseContext" ) ' Added by Sliderule
' the value between the parenthesis below is the REGISTERED NAME of the database
' as shown in the OpenOffice Menu: Tools -> Options... -> OpenOffice.org Base -> Databases
oConnection = oDBSource.GetConnection("Firmin")
oStatement = oConnection.createStatement()
oQuery = oDBSource.QueryDefinitions
' About to run the Query below and put results in oResultSet
oResultSet = oStatement.executeQuery("Booking Date")
' This is where you code what you want to do with the OBJECT oResultSet
' This is just an example using StarBasic While Statement ( help file F1 )
'Loop through all Rows returned in the event more than one row returned
While oResultSet.next
MsgBox oResultSet.GetString(1) 'Since first column returned use number 1
' If you want to see more Fields from the Query
' . . . for each field use another statement like above
' For example:
' MsgBox oResultSet.GetString(2) 'Since second column returned use number 2
Wend '<<< End of While Loop for oResultSet
End Sub |
|
| Back to top |
|
|
|
