×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Connect to SQL Server from LN

thread245-1363674

Connect to SQL Server from LN

Connect to SQL Server from LN

(OP)
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

RE: Connect to SQL Server from LN

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

RE: Connect to SQL Server from LN

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

RE: Connect to SQL Server from LN

(OP)
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

RE: Connect to SQL Server from LN

(OP)
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

RE: Connect to SQL Server from LN

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 smile

____________ 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close