Last week I wrote a nice little OpenOffice data access macro. Having a normal OpenOffice data source,

=DBSELECT(table, primary_key, field)

will return the corrensponding value from it.

When announcing this on the user mailing list, reactions were quite positive so I decided to release my code snippet here under the GPL.

        <br /><!--more-->If you decide to use it, I would be glad of a comment under this article. Maybe you even feel free to take a look at my <a href="" title="" onmouseover="window.status='';return true;" onmouseout="window.status='';return true;">amazon wish list</a> :)

On any errors or if you improved the code please contact me so that I can change it here.

Now you can either download the source or copy and paste it from here:

'Author: Frédéric Wenzel (fwenzel at gmx dot net) 'Macro is licensed under the GNU GPL: ' 'Data access macro for / StarOffice. '-- version 0.1 -- 'Searches a specific data source for a row matching the given primary key number 'and then returns the value of the given column (field). 'Known issues: '- providing the password here is quite unsafe. Think of alternatives. '- error handling could be more verbose. '- SQL select statement is quite MySQL specific. Needs to be rewritten to meet other DBMSes. function dbSelect(table As String, idNumber As String, fieldName As String, Optional idField As String) as String Dim oDatabase as Object Dim oConnection as Object Dim oStatement as Object Dim oResultSet as Object '**** Set up things here **** Const dataSourceName as String = "datasource" 'OpenOffice data source to connect to Const dbUser as String = "oouser" 'username for data source 'CAUTION: Use an unprivileged user here as providing a privileged password will be a severe security risk! Const dbPass as String = "mypass" 'password for data source Const idFieldDef as String = "id" 'Default (unique) primary key field to be searched if none is given '**** End of Setup **** 'some error handling On Error Goto ErrorHandler 'Set default primary key row: If isMissing(idField) Then idField = idFieldDef oDatabase = CreateUnoService("") oConnection= oDatabase.GetByName(dataSourceName).GetConnection(dbUser, dbPass) oStatement = oConnection.createStatement() oResultSet = oStatement.executeQuery("SELECT `" & idField & "`, `" & fieldName & "` FROM `" & table & "` WHERE `" & idField & "` = '" & idNumber & "' LIMIT 1;") If Not IsNull(oResultSet) Then 'now return selected value if resultset is not empty If Not oResultSet.isAfterLast() Then dbSelect = oResultSet.getString(2) Exit Function End If 'otherwise: return empty string dbSelect = "" Exit Function '***** 'minor error handling ErrorHandler: dbSelect = "## Error! ##" End Function

Have fun! :)

Was this helpful? Buy me a coffee with Bitcoin! (What is this?)

Updating Adobe Flash Without Restarting Firefox

No reason for a Flash upgrade to shut down your entire browser, even if it claims so.It's 2015, and the love-hate relationship of the Web...… Continue reading

Reddit's Fail-Alien (or "Fail-ien?")

Published on January 15, 2015