| View previous topic :: View next topic |
| Author |
Message |
stuey
General User
Joined: 28 Nov 2010
Posts: 8
|
 Posted: Tue Oct 04, 2011 10:19 pm Post subject: Help w SQL queryin Calc macro, querying Base |
 |
|
Hello forum folx
I'm trying to get some practice querying a Base database from within a macro that runs in a Calc file. I set up a practice database (PracticeDB), registered it to openoffice so it is visible when I click "View Data Sources" in Calc. It has one table, BaseCounties, with 5 records and four fields (ID, Town, County, State)
1 Dover Norfolk MA
2 Boston Suffolk MA
3 Chicago Cook IL
4 Boise Ada ID
5 SiouxFalls Minnehaha SD
The example given in an "OpenOffice.org Basic Guide" pdf has a static query string, i.e. the value being queried is hard-coded. I need to set something up where that value is variable, but the first thing I need to do is learn the syntax of ooo's dialect of SQL. So, within Base, I created a query (just find Boston in the Town field) using the wizard and then opened it in SQL View. I copied the string straight from SQL view into my macro, and I got a weird error about parentheses being out of place (there were EXACTLY two pairs of parenz, nested, I triple-checked this; no mismatch). Then I noticed the example in the .pdf had two pairs of double quotes around things, so I tried that approach. I didn't get the parenz error anymore; I get a different one:
BASIC runtime error.
An exception occurred.
Type: com.sun.star.sdbc.SQLException
Message: Column not found: Boston in statement[SELECT "BaseCounties"."Town" AS "Town" FROM "BaseCounties" "BaseCounties" WHERE ( "BaseCounties"."Town" = "Boston" ) ORDER BY "BaseCounties"."Town"].
The code I have so far is appended below. Thanks in advance for any help anyone can offer.
| Code: | Sub test_query
Dim DatabaseContext as Object
Dim Datasource As Object
Dim Connecticus As Object
Dim Stm as Object
Dim Resultage
'Establish connection to Cities DB
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
Datasource = DatabaseContext.getByName("PracticeDB")
Connection = Datasource.GetConnection("","")
'Perform query
Stm = Connection.createStatement()
Resultage = Stm.executeQuery("SELECT ""BaseCounties"".""Town"" AS ""Town"" FROM ""BaseCounties"" ""BaseCounties"" WHERE (""BaseCounties"".""Town"" = ""Boston"") ORDER BY ""BaseCounties"".""Town""")
MsgBox Resultage.getString(3)
End Sub |
|
|
| Back to top |
|
 |
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2465
Location: Chonburi Thailand Asia
|
| Posted: Wed Oct 05, 2011 12:08 am Post subject: Re: Help w SQL queryin Calc macro, querying Base |
|
|
| stuey wrote: | | Message: Column not found: Boston |
Message says it all:
"Boston" treated as column name - try 'Boston'
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
stuey
General User
Joined: 28 Nov 2010
Posts: 8
|
| Posted: Wed Oct 05, 2011 3:31 pm Post subject: Re: Help w SQL queryin Calc macro, querying Base |
|
|
[quote="probe1"][quote="stuey"]Message: Column not found: Boston [/quote]
Message says it all:
[color=green]"Boston"[/color] treated as column name - try [color=green]'Boston'[/color][/quote]
Hmmm, okay thanks; I'll try that when I get home (have OOo on my home pc; not work). But, if it's taking "Boston" as a column name, then what does "BaseCounties"."Town" mean? How could it not mean that the Table name is "BaseCounties" and that the Column name is "Town?"
Is there a reference page somewhere on OOo's SQL dialect? It's a lot more complex than the one I'm used to... |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Wed Oct 05, 2011 4:20 pm Post subject: |
|
|
With regard to a reference to the HSQL database engine, see the following link:
http://www.hsqldb.org/doc/guide/ch09.html
As probe1 indicated, any TABLE, VIEW, or COLUMN ( field ) in HSQL is surrounded by double quotes. While strings AND date, time, timestamp data is surrounded by single quotes, for example:
- 'Boston'
- '2011-10-15'
- '20:43:00'
- '2011-10-15 20:43:00'
| HSQL Docuementation: http://www.hsqldb.org/doc/guide/ch09.html#expression-section wrote: |
name
The character set for quoted identifiers (names) in HSQLDB is Unicode.
A unquoted identifier (name) starts with a letter and is followed by any number of ASCII letters or digits. When an SQL statement is issued, any lowercase characters in unquoted identifiers are converted to uppercase. Because of this, unquoted names are in fact ALL UPPERCASE when used in SQL statements. An important implication of this is the for accessing columns names via JDBC DatabaseMetaData: the internal form, which is the ALL UPPERCASE must be used if the column name was not quoted in the CREATE TABLE statement.
Quoted identifiers can be used as names (for tables, columns, constraints or indexes). Quoted identifiers start and end with " (one doublequote). A quoted identifier can contain any Unicode character, including space. In a quoted identifier use "" (two doublequotes) to create a " (one doublequote). With quoted identifiers it is possible to create mixed-case table and column names.
string
Strings in HSQLDB are Unicode strings. A string starts and ends with a single ' (singlequote). In a string started with ' (singlequote) use '' (two singlequotes) to create a ' (singlequote).
String contatenation should be performed with the standard SQL operator || rather than the non-standard + operator.
values
* A DATE literal starts and ends with ' (singlequote), the format is yyyy-mm-dd (see java.sql.Date.
* A TIME liteal starts and ends with ' (singlequote), the format is hh:mm:ss (see java.sql.Time).
* A TIMESTAMP or DATETIME literal starts and ends with ' (singlequote), the format is yyyy-mm-dd hh:mm:ss.SSSSSSSSS (see java.sql.Timestamp).
|
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 |
|
|
enjoying28
General User
Joined: 17 Dec 2010
Posts: 13
|
| Posted: Sun Oct 23, 2011 4:31 pm Post subject: |
|
|
I had to make some small changes but I got this to work like this:
| Code: |
Sub test_query
Dim DatabaseContext as Object
Dim Datasource As Object
Dim Connecticus As Object
Dim Stm as Object
Dim Resultset as object
'Establish connection to Cities DB
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
Datasource = DatabaseContext.getByName("mydb.file")
Connection = Datasource.GetConnection("","")
'Perform query
Stm = Connection.createStatement()
Resultset = Stm.executeQuery("SELECT ""Main"".""LstStatus"" AS ""Status"" FROM ""Main"" ""Main"" WHERE (""Main"".""LstStatus"" = 'Sold') ORDER BY ""Main"".""LstStatus""")
If Not IsNull(ResultSet) Then
While ResultSet.next
MsgBox ResultSet.getString(1)
Wend
End If
End Sub
|
Any suggestions on how to output this query to a calc sheet instead of each record to a msgBox |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Sun Oct 23, 2011 4:56 pm Post subject: |
|
|
In your post above, this is the Query:
| Code: | SELECT
"Main"."LstStatus" AS "Status"
FROM "Main" "Main"
WHERE ("Main"."LstStatus" = 'Sold')
ORDER BY "Main"."LstStatus" |
- Save your Query, and, assign a name for it.
- Once in Calc, Press F4 key, OR, from the Menu: View -> Data Sources
- Click on your Registered Database Name
- Click on Queries
- Drag your Query to the the cell in your Calc Sheet you want the data to start
- To close the data sources, Press F4 key, OR, from the Menu: View -> Data Sources
- Smile and say: "Gee Sliderule, that was easy. Now, all I have to do is let the forum know how well this worked."
I hope this helps, please be sure to let me / us know.. 
Sliderule |
|
| Back to top |
|
|
enjoying28
General User
Joined: 17 Dec 2010
Posts: 13
|
| Posted: Sun Oct 23, 2011 7:40 pm Post subject: |
|
|
Thanks Sliderule,
But that is how to manually make a query and bring a query into a Calc sheet.
I was asking how with code in a macro in the calc sheet to take the RESULTSET object and output it into the Calc sheet. I have read a lot about setting position and range cell value for clac sheet with variables but a query I only know how wide the array is not how long. was looking for the function that would do this for automation reasons. I have searched but can't find anything on a function to do this. That is why I am asking in the Macro API forum. |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
|
| Back to top |
|
|
enjoying28
General User
Joined: 17 Dec 2010
Posts: 13
|
| Posted: Sun Oct 23, 2011 8:51 pm Post subject: |
|
|
Thanks a lot Sliderule for the great lead.
The Doimport is shorter code but much more involved, so from advice on that tread I went with this:
| Code: |
Dim sheet as Object
Dim cell as Object
Dim A as Integer
sheet = thisComponent.currentSelection.getSpreadsheet()
If Not IsNull(ResultSet) Then
A = 0
While ResultSet.next
cell = sheet.getCellByPosition( 0, A )
cell.String = ResultSet.GetString(1)
cell = sheet.getCellByPosition( 1, A )
cell.String = ResultSet.GetString(2)
cell = sheet.getCellByPosition( 2, A )
cell.String = ResultSet.GetString(3)
cell = sheet.getCellByPosition( 3, A )
cell.String = ResultSet.GetString(4)
A = A + 1
Wend
End If
|
Which replaced the if statement at the end of my code above
Thanks again for the direction Sliderule |
|
| Back to top |
|
|
|
|
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
|
