I am trying to emulate some functionality I saw in Excel where a database is queried to return a value from the general ledger given a period reference and gl account code - this would enable scalc to be used as a GL report writer for webERP - an open source php web based ERP system. This would be a great reason to adopt open office for a lot of people.
I have read and searched widely on the use of the new connector. It seems relatively new so there is not much to see apart from descriptions of how to connect using the GUI. I have used Andrew Pitonyak's tutorial on OO Basic and accessing mysql - as modified for the URL identified from the OO wiki for the mysql connector. I have come up with the following code:
Code: Select all
Sub GetGLValue
'Propose to send these parameters with the function call
Dim AccountCode%
Dim PeriodFrom%
Dim PeriodTo%
Dim BudgetOrActual%
Dim sDatabaseName$
Dim sUserName$
Dim sUserPassword$
Dim sDatabaseHost$
sDatabaseName = "weberpdemo"
sUserName = "theMySQLUserName"
sUserPassword = "theMySQLUserPassword"
sDatabaseHost = "localhost"
AccountCode = 1380 'an account code in the demo
PeriodTo = 22 'a period number to inquire on
BudgetOrActual = 1
Dim oStatement
Dim oStaetment1
Dim oResult
Dim oResult1
Dim oParams () As New com.sun.star.beans.PropertyValue
Dim oManager
Dim sURL
Dim sSQL
Dim sMessage
oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
AppendProperty(oParams(), "user", sUserName)
AppendProperty(oParams(), "password", sUserPassword)
sURL = "sdbc:mysqlc:://" & sDatabaseHost & ":3306/" & sDatabaseName
oCon = oManager.getConnectionwithInfo(sURL, oParams())
oStatement = oCon.CreateStatement()
oResult1 = oStatement.executeQuery("USE " & DBQuoteName(sDatabaseName, oCon))
sSQL = "SELECT " & DBQuoteName("bfwd", oCon) & "+" & _
DBQuoteName("actual", oCon) & " AS TotValue FROM " &_
DBQuoteName("chartdetails", oCon) & " WHERE " & _
DBQuoteName("period", oCon) & "=" & PeriodTo & _
" AND " & DBQuoteName("accountcode", oCon) & "=" & AccountCode & _
" AND " & DBQuoteName("budgetoractual", oCon) & "=" & BudgetOrActual
oStatement1 = oCon.CreateStatement()
oResult1 = oStatement.executeQuery(sSQL)
Do While oResult1.next()
sMessage = sMessage & oResult1.getString(1) & CHR$(10)
Loop
MsgBox s, 0, "Value of Account: " & AccountCode & " for Period " & PeriodTo
oCon.close()
End Sub
Function DBQuoteName(sName As String, oCon) As String
Dim sQuote As String
sQuote = oCon.getMetaData().getIdentifierQuoteString()
DBQuoteName = sQuote & sName & sQuote
End Function
Function CreateProperty(sName$, oValue) As com.sun.star.beans.PropertyValue
Dim oProperty As New com.sun.star.beans.PropertyValue
oProperty.Name = sName
oProperty.Value = oValue
CreateProperty() = oProperty
End Function
Sub AppendProperty(oProperties(), sName As String, ByVal oValue)
AppendToArray(oProperties(), CreateProperty(sName, oValue))
End Sub
Sub AppendToArray(oData(), ByVal x)
Dim iUB As Integer 'The upper bound of the array.
Dim iLB As Integer 'The lower bound of the array.
iUB = UBound(oData()) + 1
iLB = LBound(oData())
ReDim Preserve oData(iLB To iUB)
oData(iUB) = x
End Sub
Also when using the URL "sdbc:mysqlc:://" & sDatabaseHost & ":3306/" & sDatabaseName
I am specifying the database name - weberpdemo - why then do I have to send SQL to the connection to get it to use the weberpdemo database? Is this a known bug with the connector.
I am using version 1.0.0 of the connector
I am using version 3.1.1 of OpenOffice OOO310m19 (Build: 9420)
I am using puppy linux 4.3.1 and mysql Ver 14.14 Distrib 5.1.35, for pc-linux-gnu (i686)
I may of course be reporting a bug but am assuming in the first instance that I have stuffed up somewhere... I would appreciate any help anyone can offer and in return I promise to write up a nice tutorial if I can get it going!!
What I really wanted to do was call the webERP API using XML-RPC this would side step any requirement for a data connector and would seem to be a more generic solution for getting data from web-services. However, I can't see any XML-RPC client code class available for OO Basic and coding such a thing is WAY beyond me!! If there is such a class available that would allow a value to be retrieved from an XML-RPC web-service and returned to an scalc function please could you point me to it.
Many thanks for reading!
