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

Help w SQL queryin Calc macro, querying Base

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
stuey
General User
General User


Joined: 28 Nov 2010
Posts: 8

PostPosted: Tue Oct 04, 2011 10:19 pm    Post subject: Help w SQL queryin Calc macro, querying Base Reply with quote

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
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Wed Oct 05, 2011 12:08 am    Post subject: Re: Help w SQL queryin Calc macro, querying Base Reply with quote

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
View user's profile Send private message Visit poster's website
stuey
General User
General User


Joined: 28 Nov 2010
Posts: 8

PostPosted: Wed Oct 05, 2011 3:31 pm    Post subject: Re: Help w SQL queryin Calc macro, querying Base Reply with quote

[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
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: Wed Oct 05, 2011 4:20 pm    Post subject: Reply with quote

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:
  1. 'Boston'
  2. '2011-10-15'
  3. '20:43:00'
  4. '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
View user's profile Send private message
enjoying28
General User
General User


Joined: 17 Dec 2010
Posts: 13

PostPosted: Sun Oct 23, 2011 4:31 pm    Post subject: Reply with quote

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
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: Sun Oct 23, 2011 4:56 pm    Post subject: Reply with quote

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"


  1. Save your Query, and, assign a name for it.
  2. Once in Calc, Press F4 key, OR, from the Menu: View -> Data Sources
  3. Click on your Registered Database Name
  4. Click on Queries
  5. Drag your Query to the the cell in your Calc Sheet you want the data to start
  6. To close the data sources, Press F4 key, OR, from the Menu: View -> Data Sources
  7. 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.. Smile

Sliderule
Back to top
View user's profile Send private message
enjoying28
General User
General User


Joined: 17 Dec 2010
Posts: 13

PostPosted: Sun Oct 23, 2011 7:40 pm    Post subject: Reply with quote

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
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: Sun Oct 23, 2011 8:02 pm    Post subject: Reply with quote

You could code it with doimport .

You can do a search on this forum for that keyword, and / or check the link below:

http://www.oooforum.org/forum/viewtopic.phtml?t=100544

Sliderule
Back to top
View user's profile Send private message
enjoying28
General User
General User


Joined: 17 Dec 2010
Posts: 13

PostPosted: Sun Oct 23, 2011 8:51 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API 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