ODBC LotusScript problems

HCL Domino Domino Forum
1

Hi,

I’m sorry if this is covering old ground, but this is my first time using LotusScript to connect to an external data source and I’m having a couple of problems creating Notes documents to match the rows in the tables.

I can access the tables on our SQL server ok, although I’m not sure how to handle UNICODE columns or how I can create an attachment from an OLE Object in the table.

I’ve included a sample of my code below, any ideas would be very much appreciated.

Thanks,

Gareth

Option Public

Uselsx “*LSXODBC”

Sub Initialize

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet



Dim field1 As String



Dim s As New NotesSession

Dim db As NotesDatabase

Dim doc As NotesDocument



Set qry.Connection = con

Set result.Query = qry

con.ConnectTo("fidb")

qry.SQL = "SELECT * FROM Company_Report"

result.Execute



Set db = s.CurrentDatabase



If result.IsResultSetAvailable Then

	Do

		result.NextRow

		Set doc = db.CreateDocument

		doc.Form = "Fixed Report"

		doc.Company = result.GetValue(1)

		doc.Files = result.GetValue(4)

		Call doc.Save(True, True)

	Loop Until result.IsEndOfData

	result.Close(DB_CLOSE)

Else

	Messagebox "Cannot get result set"

	Exit Sub

End If

con.Disconnect

End Sub

2

Subject: ODBC LotusScript problems

Ditto what Dallas said. If you are conecting the SQL Server from Notes, either from the server or from the client, there is no reason at all to use the ODBC extension. The Lotus connectors are simpler to use - because you don’t have to set up a dsn - easier to debug and give better performance.

Look in Lotus Connector Lotusscript extension guide under \Help.

Also look under my postings in this forum under Author\Threaded, there should be some good code examples there.

Also look in the EI forum, eiforum.nsf on this server.

3

Subject: ODBC LotusScript problems

I need to do the same thing as you. So I hope someone else answers this. Whenever I tried to use the rich text options for attachments/objects, it would blow up lotus notes. The only way I got it to work, which is a cludge, was to have the images in a windows directory, and have the path in the image column. But I much rather be able to copy the object from the one datasource, to a lotus notes database.

4

Subject: RE: ODBC LotusScript problems

Hi Greg,

Have you experienced any UNICODE problems too? Have you got any ideas as to how I can convert to a string? It just returns the values as FALSE.

Thanks,

Gareth

5

Subject: ODBC LotusScript problems

If you’re connecting to SQL Server and you must use LotusScript, you should consider using the LCLSX before investing too much time in LSDO. The learning curve is a little steeper so it may take a little more time, but it’s also more flexible/powerful than LSDO. Specifically, I’d suggest having a look at the LCStream class – it should help you out. The OLE DB connector supports SQL Server 7/2000 - start there if you can, otherwise, try the ODBC connector.dgg