Connect to SQL Server from LN 1

[Rofeu]

Hi,

I hope some one can point me in the right direction here.

I'd like to update a table on a SQL Server with data in a LN document, but I've no idea how to connect to it.

I've read something about LN being able to connect with OLE DB, but how I'm to put that in code, I could not find.

Cheers,

Roel

 

[onpnt]

Yes you can use the oledb provider along with gaining access to most database servers.

look in the help files of designer under "LCConnection Class Methods"

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[lespaul]

Here's a LotusScript function that updates a DB2, but the concepts should be the same:

Function Update(rdate As String, rtime As String, rjudge As String, odate As String, otime As String) As Variant
	Dim con As New ODBCConnection
	Dim qry As New ODBCQuery
	Dim result As New ODBCResultSet
	
	Set qry.Connection = con
	Set result.Query = qry
	
	rdate = Format(rdate, "YYYYMMDD")
	rtime = Format(rtime, "HHMM")
	odate = Format(odate, "YYYYMMDD")
	otime = Format(otime, "HHMM")
	
	con.ConnectTo("iseries")	
	
	qry.SQL = "SELECT JUDCOD FROM CMLIB.CMPJUDNM WHERE JUDNAM = '" + rjudge + "'"
	result.Execute
	rjudge = result.GetValue("JUDCOD")
	     		' Update jury slot record to AS/400 jury calendaring
	If rjudge <> 5 And rjudge <> 15 Then
		qry.SQL = "SELECT CASNUM, CASPRE FROM CMLIB.CMPHERMF WHERE HERNGDAT = " + odate + " AND HERTIM = " + otime + " AND HERTYP = 'SJ' AND (STSCOD = 0 OR STSCOD = 1)"
	'qry.SQL = "SELECT CASNUM, CASPRE FROM Y2K.CMPHERMF WHERE HERNGDAT = " + odate + " AND HERTIM = " + otime + " AND HERTYP = 'SJ' AND (STSCOD = 0 OR STSCOD = 1)"
		result.Execute
		If result.NumRows = 0 Then
			qry.SQL = "UPDATE CMLIB.CMPSCHCT SET HERNGDAT = " + rdate + ", HERTIM = " + rtime + " WHERE JUDCOD = " + rjudge + _ 
			" AND HERNGDAT = " + odate + " AND HERTIM = " + otime + " AND HERTYP = 'SJ'"
			
		'qry.SQL = "UPDATE Y2K.CMPSCHCT SET HERNGDAT = " + rdate + ", HERTIM = " + rtime + " WHERE JUDCOD = " + rjudge + _ 
		'" AND HERNGDAT = " + odate + " AND HERTIM = " + otime + " AND HERTYP = 'SJ'"
			result.Execute	
			result.Close(DB_CLOSE)
			con.Disconnect
			Update = True
			
		End If
	Else
		qry.SQL = "UPDATE CVLIB.CVPSCHCTA SET DKTDATE = " + rdate + ", DKTTIM = " + rtime + " WHERE JUDCOD = " + rjudge + _ 
		" AND DKTDATE = " + odate + " AND DKTTIM = " + otime + " AND DKTTYP = 'SJ'"
	End If
	
	
End Function

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Rofeu]

Hi,

thanks for the replies. I've tried both tactics, unfortunately without success.

Regarding the ODBC, I'm not sure where I need to reference what exactly.

For the ConnectTo statement, should I put in the name of the server or database (maybe both)?

Sub Click(Source As Button)
	
	Dim con As New ODBCConnection
	Dim qry As New ODBCQuery
	Dim result As New ODBCResultSet
	Dim MyResult As String
	
	Set qry.Connection = con
	Set result.Query = qry
	con.ConnectTo(SQLServerName)    
	
	qry.SQL = "SELECT CURRENCY FROM SQLServerDatabase.SourceTable"
	result.Execute
	myresult = result.GetValue("CURRENCY")
	Messagebox myresult
	
	result.Close(DB_CLOSE)
	con.Disconnect	
End Sub

When I run the above, MyResult returns FALSE.

As to the LCConnection, I've tried the following:

Sub Click(Source As Button)
	
	Dim con As  New LCConnection  ("oledb")
	
	With con
		.Provider = "sqloledb" 
		.Server = "EUNLROTDB007"
		.Database = "MJU_Validation"
		.Metadata = "tbl_CURRENCY"
	End With
	
	On Error Resume Next
	con.Connect
	If Err = False Then	
		Messagebox "Successfully connected to SQLServer."
	Else
		Messagebox  "Connection failed with error " & Err & ": " & Error
	End If
	
End Sub

This seems at least to be trying to access the db, but it tells me that there is 'Invalid authorization specification'. I've added the lines:

.UserID = "MyUserID" 'also tried "Domain\MyUserID"
		.Password = "MyWindowsPassword"

but then it says that 'Login Failed for user MyUserID'

I've have ownership of the database I'm trying to connect to. I'm not sure what UserID and/or Password it is expecting. (i.e. I can just access the db through SQL Server Enterprise Manager without ever having to provide a password as it should use my windows login).

I've the feeling that I'm close, but close is never quite good enough.

Does anyone know what I'm missing?

Cheers,

Roel

 

[Rofeu]

GOT IT!

Sub Click(Source As Button)
	
	Dim ses As New LCSession
	ses.ClearStatus 	
	
	Dim con As  New LCConnection  ("oledb")
	
	With con
		.Provider = "SQLOLEDB" 
		.Server = "SQLServerName"
		.Database = "DatabaseName" 
		.Metadata = "tbl_CURRENCY"
		.Auth_Integrated = "SSPI"
	End With
	
	On Error Resume Next
	con.Connect
	If Err = False Then	
		Messagebox "Successfully connected to SQLServer."
	Else
		Messagebox  "Connection failed with error " & Err & ": " & Error
	End If
	
	Dim MyFldList As New LCFieldList
	Dim MyFld As LCField	
	Dim SQL As String
	Dim MyResult As String
	
	SQL = "SELECT CURRENCY FROM tbl_CURRENCY"
	con.Execute sql,myfldlist
	
	Set myfld = myfldlist.GetField(1)
	
	While (con.Fetch(myfldlist) > 0)
		myresult = myresult &  myfld.Text(0) & Chr(13)
	Wend
	
	Messagebox myresult
	
	con.Disconnect
	Set myfldlist = Nothing
	Set myfld = Nothing
	Set con = Nothing
	Set ses = Nothing
	
	
End Sub

Thanks for pointing me to those LCConnection Classes, ONPNT, star for you!

Cheers,

Roel

 

[onpnt]

Well done Roel and thank you for posting your solution. I'm sure it will help many others

And thank you for the * as well

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]