Connect to SQL Server from LN

Question

Connect to SQL Server from LN

Rofeu (TechnicalUser)

(OP)

3 May 07 11:43

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 (Programmer) · Answer 1 · 2007-05-03 12:18:46

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"

_{____________ 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}

lespaul (Programmer) · Answer 2 · 2007-05-03 13:34:46

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

CODE

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 (TechnicalUser) · Answer 3 · 2007-05-04 08:16:51

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)?

CODE

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:

CODE

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:

CODE

.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 (TechnicalUser) · Answer 4 · 2007-05-04 10:29:13

GOT IT!

CODE

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 (Programmer) · Answer 5 · 2007-05-04 10:35:23

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

_{____________ 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}

Come Join Us!

Posting Guidelines

Jobs

Connect to SQL Server from LN