Hi to all,I’ve set up a LC LSX connection to a SQL Server. I successfully execute queries and fetch result sets. Now I want to retrieve XML using the new FOR XML clause of SQL Server 2000, that ouputs an XML document instead of a recordset. The trouble is, the result I get from SQLServer seems to have character set problems: data and fields names are mixed with black squares, no carets are displayed. LS Debugger shows that the text is returned this way. Have anybody met this behaviour?
TIA
I enclude full code and a sample of output:
Lotus Script Agent:
Sub Initialize
Dim session As New LCSession
Dim con As New LCConnection("oledb")
Dim getField As LCField
Dim count As Integer, i As Integer
Dim cs As New LCField(LCTYPE_TEXT,1)
Dim tokenId As Long
Dim outputFieldList As New LCFieldList
Dim msg$
con.provider = "sqloledb"
con.server = "MyServer"
con.Userid = "MyUser"
con.Password = "MyPassword"
con.Database = "Northwind"
con.Procedure = "MyProcedure"
con.Connect
count = con.Call (Nothing, 1, outputFieldList)
Do While (con.Fetch (outputFieldList, 1, 1) > 0)
For i = 1 To outputFieldList.FieldCount
Set getField = outputFieldList.getField(i)
msg$ = msg$ & getField.Text(0) & Space$(1)
Next
msg$ = msg$ & Chr$(10)
Loop
Msgbox msg$,, "CALL"
End Sub
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
SQL Server Stored Procedure:
ALTER PROCEDURE LCSimpleSelect AS
SELECT CustomerID, CompanyName, City, Country, Phone
FROM Customers
WHERE Country IN (‘Italy’, ‘Germany’)
FOR XML AUTO
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Output from agent:
CustomerIDୄCompanyNameфCity݄CountryՄPhoneॄCustomersĂ䄀䰀䘀䬀䤀Ȁᄂ&Alfreds Futterkistê䈀攀爀氀椀渀ȀᄄGermanyԂᘑ ㌀ ⴀ 㜀㐀㌀㈀䌀Ă䈀䰀䄀唀匀Ȁᄂ.Blauer See Delikatessen̂⸮.
[… more follow …]
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<