Consuming XML web services from Calc

[Fallen]

I'm writing a simple macro to consume a webservice from Calc. It works fine in Excel, but I've found VERY little useful documentation on doing similar processes from OpenOffice. Anyway here is the VBA code for consuming the service; it works:

Dim scAs New MSSOAPLib.SoapClient
sc.mssoapinit ("http://localhost/MathServices/AddServices.asmx?wsdl")
MsgBox (sc.AddTen(5))

This service simply adds ten to the number passed to it, then posts the result to the user. Works fine. Now the StarBasic version:

proxyFac = createUNOService("com.sun.star.webservices.proxy.ProxyFactory")
proxyFac.initialize(Array("http://localhost/MathServices/AddServices.asmx?wsdl"))
proxy = proxyFac.getProxy("AddServices")
propertySet = proxy.AddTen(5)
resultSet = propertySet.resultElements
MsgBox resultsSet(0)

Granted, I don't know too much about what this code REALLY does; it came from the only website I could find with any information about this subject.

http://udk.openoffice.org/java/examples/wsproxy/component_description.html

I adapted it as best I know how, not knowing what the parameters really are for or whether or not they are required. The StarBasic version blows up at line 2 saying:

"Basic runtime Error. Object Variable not set"

I believe that this is because the UNO service specified doesn't really exist. Anyway, does anyone have some answers for me? Any bit of help would be greatly appreciated.

Thanks

[DannyB]

Does this help?

Access external COM/OLE server from OOo Basic
http://www.oooforum.org/forum/viewtopic.php?t=5918
http://www.oooforum.org/forum/viewtopic.php?t=6381

I'm wondering if the solution would be something like this....

oObjFactory = createUnoService( "com.sun.star.bridge.OleObjectFactory" )
sc = oObjFactory.createInstance( "MSSOAPLib.SoapClient" )

sc.mssoapinit ("http://localhost/MathServices/AddServices.asmx?wsdl")

Since you don't provide a publicly available URL for a web service, I have no way to test. I can only guess.
_________________
Want to make OOo Drawings like the colored flower design to the left?

[Fallen]

On paper it looks great, but in reality it still blows up, this time at the third line

sc.mssoapinit ("http://localhost/MathServices/AddServices.asmx?wsdl")

Same error too, "Object variable not set"
I imagine that this is a blanket error description, so I put little faith in it. In the end, that wouldn't work anyway. I need the call to be a native or Java reference because the OpenOffice version of this macro will be running on a Linux ThinClient. Could the problem be somewhere other than the code?

[DannyB]

If it is to consume web services from a Linux system, then you should pursue non-Microsoft solutions.

I have not yet ventured into consuming web services from Java. If you can do that, you can package your code as a component, and call it from within a macro.

Building a component will be a real learning curve.

You can also build a component in Python. Still you'll have the same learning curve issues to climb. (i.e. the learning issues are not language specific, but are OOo specific.)

You can build a Java or Python program that runs external to OOo, and connects to OOo. This is easier, and could be thought of as a prerequisite for building a component.

Perhaps you can find Python libraries that consume web services. If so, building a Python component might be the way to go. I have posted some example Python component's source code here before. I have also posted the source to one Java component, which happens to be a Calc spreadsheet AddIn function. But being a component, it does everything that a component needs to do.

One obstacle is that you have to obtain and install the SDK so that you can use the command line tools to compile your IDL files into RDB files, and generate Java and C++ header files for your IDL definitions.
_________________
Want to make OOo Drawings like the colored flower design to the left?

[Fallen]

Yeah, the trick is that I have to support Excel on WinXP systems and Calc in the Linux environment. I was hoping to just mimic the functionallity of the Excel SOAP calls natively from OpenOffice, but that doesn't seem like it is going to happen. Thanks for your help.

[Fallen]

I obtained a google API key today to test the example provided, and it didn't work. I imagine that my Oo is just broken. Thanks anyway.

[biaco]

i need some useful sample about how code (starbasic or python) a calc spreadsheet
to consume webservice that interact with a database Sql Server 2000.
The idea is to have a spreadsheet with a button that connect to a SQL string of a webservice and populate with data returned the cells. The web service is a simple db connection and quering like this:


'Alok Mehta
'February 2005
'MSDN VBA WebService Example

Option Explicit On

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

<WebService(Namespace:="http://myCollege.edu/")> _
Public Class Service
Inherits System.Web.Services.WebService

'Provide grades for all homework, exams, and so on. Based on
'professor and course
<WebMethod()> Public Function Get_Grades(ByVal nProfessorID As Long, ByVal lngCourseID As Long) As String

'Make sure its a valid request
If bValidUser(nProfessorID, lngCourseID) Then

'Make connection to the Grades database; the connection
'string would be in a config file in real application
Dim GradesConn As SqlConnection = New SqlConnection("Data source=localhost;Initial Catalog=Grades;Uid=sa;Pwd=mypassword;")
Dim GradesDataSet As DataSet = New DataSet

Try
'SQL to get the students grades for a particular professor and course
Dim strSQL As String = "SELECT tbl_Student.Student_Name, tbl_Grades.Quiz_Grade, tbl_Grades.HomeWork, tbl_Grades.MidTerm, tbl_Grades.Participation, " & _
"tbl_Grades.FinalExam FROM tbl_Student INNER JOIN (tbl_Professor INNER JOIN (tbl_Course INNER JOIN " & _
"tbl_Grades ON tbl_Course.Course_ID=tbl_Grades.Course_ID) ON tbl_Professor.Professor_ID=tbl_Grades.Professor_ID) " & _
"ON tbl_Student.Student_ID=tbl_Grades.Student_ID WHERE tbl_Professor.Professor_ID=@profID AND tbl_Course.Course_ID=@courseID"

'Create the command to execute
Dim Comm As SqlCommand = New SqlCommand(strSQL, GradesConn)
Comm.Parameters.Add("@profID", nProfessorID)
Comm.Parameters.Add("@courseID", lngCourseID)

'Use a data adapter to fill the DataSet, then get as XML
Dim dataGrades As SqlDataAdapter = New SqlDataAdapter(Comm)
dataGrades.Fill(GradesDataSet)
Get_Grades = GradesDataSet.GetXml

Catch e As Exception
'Return any error messages
Get_Grades = e.Message
Finally
GradesConn.Close()
End Try
Else
'In case its not a valid user
Get_Grades = "Invalid user"
End If
End Function

Private Function bValidUser(ByVal nProfessorid, ByVal lngCourseid) As Boolean
'This function should be replaced by your security standards
'such as a Web service token etc. For simplicity we just allow
'a specific professor ID and course ID
bValidUser = (nProfessorid = 1 And lngCourseid = 1)
End Function
End Class

Can you help me?