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

Execute arbitrary SQL for use in Calc spreadsheets

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
AndrewZ
Moderator
Moderator


Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA

PostPosted: Fri May 11, 2007 1:46 pm    Post subject: Execute arbitrary SQL for use in Calc spreadsheets Reply with quote

OpenOffice.org Calc and MySQL are a useful combination! It seems to work OK with small data sets. I have not tried a large query. If someone knows how to get the number of rows at the beginning, let me know. Smile

Code:

' A basic macro for use in Calc for performing arbitrary SQL SELECT queries
'
' Usage
' - You must register database connection.
' - Add this macro to Tools->Macros->Organize Macros->OpenOffice.org Basic
' - In a Calc cell, type something =OOOCalcSQL("select foo from bar", "mydsn")
' - Press CTRL+SHIFT+ENTER to make the function an array (to get all the data)
'
' Reference:
' http://codesnippets.services.openoffice.org/Database/Database.QueryDatabase.snip

function OOOCalcSQL(sSQLCommand, sDSN)
    RowSet = createUnoService("com.sun.star.sdb.RowSet")
    RowSet.DataSourceName = sDSN
    RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
    RowSet.Command = sSQLCommand
    RowSet.execute()
 
    nColumns = RowSet.GetColumns().GetCount()
    nRows = RowSet.RowCount()  ' I don't think this is right
    Dim result(1 to nRows, 1 to nColumns)
   
    nRow = 1
    while RowSet.next()
       if (nRow > nRows) then ' a hack because RowCount() is too low
          ReDim Preserve Result(1 to nRow, 1 to nColumns)
       endif
       for n = 1 to nColumns
          Result(nRow, n) = RowSet.GetString(n)
      next n
      nRow = nRow + 1
    wend
   
    OOOCalcSQL=Result()
End function

_________________
<signature>
* Did you solve your problem? Do others a favor: Post the solution
* OpenOffice.org Ninja
* BleachBit
</signature>
Back to top
View user's profile Send private message Visit poster's website
AndrewZ
Moderator
Moderator


Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA

PostPosted: Sat May 19, 2007 3:37 pm    Post subject: Reply with quote

This version has three major changes
1. Improved error handling in two places. First, it checks the DSN. Second, if the execution fails, it returns the error as a string (instead of potentially returning dozens/hundreds of dialog boxes).
2. Instead of returning a string for every column type, most column types are supported. however, I haven't worked much on NULL or date/time types. For dates, I just use the spreadsheet =datevalue(foo) to convert from a string.
3. You can specify the DSN username an dpassword. In some situations, it wasn't required (it seemed inconsistent), but it was easier to just add the values here than figure out why. Smile

Note: If you are getting multiple results using a spreadsheet array, OpenOffice.org doesn't seem to resize the array. In other words, if first run the query with 20 rows, but later it returns 21, OpenOffice.org will discard the last row. Whenever the array size changes, you have to erase and retype the function in the spreadsheet.

Code:

' A basic macro for use in Calc for performing arbitrary SQL queries as
' spreadsheet functions.
'
' Usage
' - You must register database connection in Base or through Tools->Options.
' - Add this macro to Tools->Macros->Organize Macros->OpenOffice.org Basic
' - In a Calc cell, type something =OOOCalcSQL("select foo from bar", "mydsn")
' - Press CTRL+SHIFT+ENTER to make the function an array (to get all the data)
'
' Reference:
' http://codesnippets.services.openoffice.org/Database/Database.QueryDatabase.snip


function OOOCalcSQL(sSQLCommand, sDSN)
   ' Ref: http://dba.openoffice.org/howto/IgnoreDriverPrivileges.html
   Dim aContext as Object
    aContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )

    If ( Not aContext.hasByName( sDSN ) ) Then
        OOOCalcSQL = "There is no data source named " + sDSN + "!"
        Exit Function
    Endif

    ' Create a row-set to query the database
    RowSet = createUnoService("com.sun.star.sdb.RowSet")
    RowSet.DataSourceName = sDSN
    RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
    RowSet.Command = sSQLCommand
' the following two statements may be removed under certain situations
    RowSet.User = "root"
    RowSet.Password = "secret"
    On Local Error Goto SQLError
    RowSet.execute()
    On Local Error Goto 0
 
    nColumns = RowSet.GetColumns().GetCount()
    nRows = RowSet.RowCount()  ' I don't think this is right
    Dim result(1 to nRows, 1 to nColumns)
   
    nRow = 1
    while RowSet.next()
       if (nRow > nRows) then ' a hack because RowCount() is too low
          ReDim Preserve Result(1 to nRow, 1 to nColumns)
       endif
       for n = 1 to nColumns
       
         ' Ref: http://api.openoffice.org/docs/DevelopersGuide/Database/DataTypesGetXXX.png
          Select Case RowSet.GetMetaData().GetColumnType(n)
         Case com.sun.star.sdbc.DataType.TINYINT
            Result(nRow, n) = RowSet.GetByte(n)
         Case com.sun.star.sdbc.DataType.SMALLINT               
            Result(nRow, n) = RowSet.GetShort(n)
         Case com.sun.star.sdbc.DataType.INTEGER         
            Result(nRow, n) = RowSet.GetInt(n)
         Case com.sun.star.sdbc.DataType.BIGINT
            Result(nRow, n) = RowSet.GetLong(n)
         Case com.sun.star.sdbc.DataType.REAL   
            Result(nRow, n) = RowSet.GetFloat(n)                  
         Case com.sun.star.sdbc.DataType.FLOAT            
         Case com.sun.star.sdbc.DataType.DOUBLE         
            Result(nRow, n) = RowSet.GetDouble(n)
         Case com.sun.star.sdbc.DataType.BIT      
            Result(nRow, n) = RowSet.GetBoolean(n)
         Case com.sun.star.sdbc.DataType.DECIMAL
            Result(nRow, n) = Val(RowSet.GetString(n))
'         Case com.sun.star.sdbc.DataType.SQLNULL
'            Result(nRow, n) = "NULL"         
            Case Else
               Result(nRow, n) = RowSet.GetString(n)
          End Select
      next n
      nRow = nRow + 1
    wend

'   RowSet.Dispose()
   
    OOOCalcSQL=Result()
   
SQLError:
   If Err <> 0 Then
'      MsgBox("Exception executing SQL")
       OOOCalcSQL=Error(Err)
   Endif
End function


_________________
<signature>
* Did you solve your problem? Do others a favor: Post the solution
* OpenOffice.org Ninja
* BleachBit
</signature>
Back to top
View user's profile Send private message Visit poster's website
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Mon May 21, 2007 2:09 am    Post subject: Reply with quote

Andrew, how the interface fetches row batches in the database depends on various factors, such as the type of database (HSQL, MySQL, SQLserver, ...), the access mode, whether your COMMAND is actually a just Table Name or a proper SQL Query and the various other parameters of the RowSet. The execution parser might decide to adopt a lazy strategy, and in any case to minimise the overhead of the RPC dialogue through the stacks to the database, the driver might decide to cache the row set into batches.

For example if the RowSet only returns a few rows from some complex query, then the RowCount will be final. If the query is going to return a million rows, then the RowCount will probably grow in the cache size jumps. One trick that you can do is to do a .last() followed by a .First() and then RowCount is correct, but this only works if the RowSet is scrollable.

However if the method IsRowCountFinal is true then you have got to the end. One obvious optimization is to replace
Code:
if (nRow > nRows) then ' a hack because RowCount() is too low
   ReDim Preserve Result(1 to nRow, 1 to nColumns)
endif
by
Code:
If (nRow > nRows) Then ' a hack because RowCount() is too low
   nRows = RowSet.RowCount()
   ReDim Preserve Result(1 to nRows, 1 to nColumns)
endif
because this will ReDim the array in batches rather than one row at a time. The alternative might be to use nRows+1000 or whatever, but in this case you'd need to ReDim Preserve the array back to the actual number of rows fetched when you exit the loop. Why do this? Because ReDim Preserve is a costly operation, so it makes sense to do it once per N rows rather than each row -- even if you need to do an extra one at the end to get the correct size.

The other optimisation that cries out for being done is because the column type is fixed for all rows in a result set (need to think about NULLs here), so wouldn't something like
Code:
   While RowSet.next()
      If (nRow > nRows) Then
         nRows = RowSet.RowCount()
         ReDim Preserve Result(1 to nRows, 1 to nColumns)
      EndIf
      Result(nRow, 1) = RowSet.GetString(1)
      Result(nRow, 2) = RowSet.GetByte(2)
      Result(nRow, 3) = RowSet.GetShort(3)
      Result(nRow, 4) = RowSet.GetString(4)
      Result(nRow, 5) = RowSet.GetDouble(5)
      Result(nRow, 6) = RowSet.GetDouble(6)
      nRow = nRow + 1
   Wend
run a lot more efficiently than the extra For loop and Case statement if you have anything other than a tiny number of rows to fetch. However this would require this code to be generated dynamically after the query has been executed, but this would require something like http://www.oooforum.org/forum/viewtopic.phtml?t=18781.

Thoughts?
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
Back to top
View user's profile Send private message Visit poster's website
AndrewZ
Moderator
Moderator


Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA

PostPosted: Mon May 21, 2007 7:48 am    Post subject: Reply with quote

Terry,

Thanks. I do like the first optimization with "nRows = RowSet.RowCount() " (which I merged into my code here), but for my needs, dynamic code generation is too complex to justify.
_________________
<signature>
* Did you solve your problem? Do others a favor: Post the solution
* OpenOffice.org Ninja
* BleachBit
</signature>
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu May 24, 2007 2:13 pm    Post subject: Reply with quote

Alternative method setting the import-descriptor of an existing db-range to an sql-string. Requires registered datasource and db-range.
Code:

REM  *****  BASIC  *****
REM to be called with appropriate arguments:
REM 1. Database range to be updated
REM 2. Name of registered datasource
REM 3. A complete SQL-Select statement in OOo-Base syntax, for instance:
REM SELECT "Field1"AS"Names","Field2"AS"Values" FROM "table" WHERE "Name" LIKE "A*"
REM If optional 4th param is True, the syntax must apply to the used db-engine. Ignored with OOo1.x
REM 5th optional param indicates a connection URL, which locates a database driver. (untested)
Option Explicit
Sub refreshDbRangeBySQL(oDBRange,dbSourceName$,sql$, _
      Optional bIsNative as Boolean, Optional sConnectionResource$)
'database range:
Dim oDesc(),i%,oPrp
'on error goto exitErr:
   if isMissing(bIsNative) then bIsNative = False
   if isMissing(sConnectionResource) then sConnectionResource = ""
   oDesc() = oDBRange.getImportDescriptor() 'array of com.sun.star.beans.PropertyValues
   For i = 0 to ubound(oDesc())
      oPrp = oDesc(i)
      If oPrp.Name = "DatabaseName" then
         oPrp.Value = dbSourceName
      elseIf oPrp.Name = "SourceType" then
         oPrp.Value = com.sun.star.sheet.DataImportMode.SQL
      elseIf oPrp.Name = "SourceObject" then
         oPrp.Value = sql
      elseif oPrp.Name = "isNative" then
         oPrp.Value = bIsNative
      elseif oPrp.Name = "ConnectionResource" then
         oPrp.Value = sConnectionResource
      Endif
      oDesc(i) = oPrp
   Next
   oDBRange.getReferredCells.doImport(oDesc())
exit sub
exitErr:
'raise API-error
   error err
End Sub

Example which gets all arguments from a set of named cells:
Code:

REM  *****  BASIC  *****
REM Document specific code:
Option Explicit
Sub RefreshDBQ()
Const cDBRange = "DBQ" 'named database range to refresh
Const cellSource = "DBQsource" 'absolute named cell-ref, specifying the data source name
Const cellSQL = "DBQsql" 'absolute named cell-ref, specifying the SQL
'cell values:
Dim sql$,src$
'database range:
Dim oDBR
'on error goto exitErr
   with thisComponent.NamedRanges
      sql = .getByName(cellSQL).getReferredCells.getCellByPosition(0,0).getString
      src = .getByName(cellSource).getReferredCells.getCellByPosition(0,0).getString
   end with
   oDBR = thisComponent.DataBaseRanges.getByName(cDBRange)
   refreshDbRangeBySQL(oDBR,src,sql)
end sub

If you don't like to concatenate an entire sql-string in a sheet-cell simply use something more hard-coded:
Code:

arg1 = cell1.getString
arg2="#"& cStr(Now())
sql = "SELECT * FROM ""Table"" WHERE ""Field1""="& arg1 &" AND ""Field2""="& arg2

EDIT: Updated example dbq.ods
http://www.mediafire.com/?ft4thvzoztx
Just in case it gets lost:
Added formula
=HYPERLINK($G$10;"Refresh By URL")&T(STYLE("Button"))
where G10 concatenates the url like this:
="vnd.sun.star.script:Standard.DBQ.RefreshDBQByURL?language=Basic&location=document&dbRange=DBQ&Source="&DBQsource&"&SQLCell=DBQsql"
which calls this macro with all required info that may be specific to your spreadsheet and data source:
Code:

Sub RefreshDBQByURL(byval sURL$)
'calls getArgumentFromURL, refreshDbRangeBySQL
'url-schema: protocol:Library.Module.Routine?arg1=value1&arg2=value2
'vnd.sun.star.script:Standard.DBQ.RefreshDBQByURL
'      ?language=Basic&location=document
'      &dbRange=myImportRange&Source=myDataSource&SQLCell=mySQLCell"
REM location=application if this code is saved in a global container
REM This macro evaluates 3 named arguments expected to be in sURL.
REM The names of these arguments are:
Const cDBR = "dbRange" 'name of database-range (target)
Const cSrc = "Source" 'name of registered data source
Const cSQL = "SQLCell"
REM SQLCell specifies a named cell in the current document which contains the entire SELECT-query.
REM Unlike "Sheet1.A1" named ranges keep working when you move cells or rename sheets.
REM We can't pass the SQL directly due to some special chars that might be in SQL.
REM Special chars like ' invalidate the calling url
Const cMsgTitle = "macro:RefreshDBQByURL"
'datasources and names in current workbook:
Dim oBaseContext, oDBRanges, oNames
'target name and object:
Dim sDBR$, oDBR
'source name, cell name, full sql-string of cell:
Dim src$, sCellName$, sql$
   oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
   oDBRanges = thisComponent.DatabaseRanges
   oNames = thisComponent.NamedRanges
   'getArgumentFromURL gets the value behind "=" from Name=Value
   src = getArgumentFromURL(sURL, cSrc)
   sDBR = getArgumentFromURL(sURL, cDBR)
   sCellName = getArgumentFromURL(sURL, cSQL)
   if oNames.hasByName(sCellName) then
      sql = oNames.getByName(sCellName).getReferredCells().getCellByPosition(0,0).getString()
      if oBaseContext.hasByName(src) then
         if oDBRanges.hasByName(sDBR) then
            oDBR = oDBRanges.getByName(sDBR)
            'call the working routine with a dbRange, a data-source name and sql:
            refreshDbRangeBySQL(oDBR,src,sql)
         else
            msgbox "No such database range: '"& sDBR &"'", 16, cMsgTitle
         endif
      else
         msgbox "Missing datasource: '"& src &"'", 16, cMsgTitle
      endif
   else
      msgbox "Named cell missing: '"& sCellName &"'", 16, cMsgTitle
   endif
End Sub
REM oversimplified. No url-decoding.
REM Gets named value from url protocol:test.bla/dir?Name1=Value2&Name2=Value2
Function getArgumentFromURL(sURL$,sName$) as String
on error goto exitErr:
Dim iStart%, i%, l%, sArgs$, a()
   iStart = instr(sURL, "?")
   l = len(sName)
   if (iStart = 0) or (l = 0) then exit function
   ' sArgs behind "?":
   sArgs = mid(sURL, iStart +1)
   a() = split(sArgs, "&")
   for i = 0 to uBound(a())
      ' not case sensitive:
      if instr(1, a(i), sName &"=", 1) = 1 then
         getArgumentFromURL = mid(a(i), l +2)
         exit for
      endif
   next
exitErr:
' return ""
End Function


Last edited by Villeroy on Fri Aug 10, 2007 7:17 am; edited 2 times in total
Back to top
View user's profile Send private message
AndrewZ
Moderator
Moderator


Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA

PostPosted: Thu May 24, 2007 7:09 pm    Post subject: Reply with quote

The working example is nice, and I saw a few things I never tried such as shorthand formula string concatenation and the =hyperlink() function.
_________________
<signature>
* Did you solve your problem? Do others a favor: Post the solution
* OpenOffice.org Ninja
* BleachBit
</signature>
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 25, 2007 10:52 am    Post subject: Reply with quote

Wink Yes, for me HYPERLINK and STYLE is the outstanding feature in Calc.
My first approach was similar to your's. I tried to create a simple wizzard or function which should have been able to import a parameter query by parameter substitution from cell values. Then I wanted to add a refresh listener to the database range, which should re-read the changed parameters on refresh. I got lost in the jungle of different datasources, positional and named parameters, translating doubles and strings from cell-values into correct field types. Finally the refresh listener of a db-range never gets called. It seems to be deaf.
Then I realized that that db-ranges provide an option to import by means of sql-strings rather than names of tables/queries. This option is not accessible in the GUI.
Until now I have tested this with hsqldb, dBase, mysql and someone in the Calc forum used it succesfully with Oracle. It used to fail with hsqldb and OOo older than 2.1 (lost connection). Seems to be fixed now.

Today I combined this stuff with a technique described here
http://www.oooforum.org/forum/viewtopic.phtml?t=56417
and more in general here
http://www.oooforum.org/forum/viewtopic.phtml?t=53903
Now it is possible to use this technique with any document. No more hard coded names in the Basic code. Everything is passed directly or indirectly by the URL, which calls the macro.
Udpated my previous post with new url of enhanced dbq.ods
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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 Code Snippets 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