| View previous topic :: View next topic |
| Author |
Message |
AndrewZ
Moderator
Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA
|
 Posted: Fri May 11, 2007 1:46 pm Post subject: Execute arbitrary SQL for use in Calc spreadsheets |
 |
|
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. 
| 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 |
|
 |
AndrewZ
Moderator
Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA
|
| Posted: Sat May 19, 2007 3:37 pm Post subject: |
|
|
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.
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 |
|
|
TerryE
Super User
Joined: 16 Jul 2006
Posts: 550
Location: UK
|
| Posted: Mon May 21, 2007 2:09 am Post subject: |
|
|
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 |
|
|
AndrewZ
Moderator
Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA
|
| Posted: Mon May 21, 2007 7:48 am Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu May 24, 2007 2:13 pm Post subject: |
|
|
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 |
|
|
AndrewZ
Moderator
Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA
|
| Posted: Thu May 24, 2007 7:09 pm Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri May 25, 2007 10:52 am Post subject: |
|
|
 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 http://forum.openoffice.org |
|
| 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
|
