How do I connect an SQL database to Lotus Domino Designer?

Question

I am creating a Lotus Notes application which has to have dynamic combo boxes. The choices for the combo boxes need to be retrieved by selecting from a SQL database.

I am new to Lotus Notes / Domino, I would love to know how to connect my SQL database for use in the domino designer. Thanks.

Edit: this is the client, not the web

Sub  Initialize

    On Error GoTo e
    Dim pw As String,user As String,odbc As String
    Dim i As Integer
    Dim conn As ODBCConnection,query As ODBCQuery,rs As ODBCResultSet
    Dim db As NotesDatabase
    Dim session As NotesSession
    Dim view As NotesView
    Dim doc As NotesDocument
    Dim newDoc As NotesDocument
    Set session = New NotesSession  
    Set db = session.CurrentDatabase
    Set view = db.GetView("Reports")
    Set doc = view.GetFirstDocument 
    Set conn = New ODBCConnection
    Set query = New ODBCQuery
    Set rs = New ODBCResultSet
    Set query.Connection = conn 
    Set rs.Query = query

    odbc =  "server"  
    user =  "user" 
    pw =  "pass"
    Call conn.ConnectTo( odbc , user , pw ) 
        i = 0
        query.SQL =  "SELECT * FROM table" 
        rs.Execute 
        rs.FirstRow
        Do  While  Not rs.IsEndOfData
            i = i + 1
            rs.NextRow
        Loop
    conn.Disconnect
    Exit  Sub 
e : 
    MessageBox "Error " & Err & " line " & Erl & ": " & _
    Error        
    Exit Sub

End  Sub

Please update your question to specify if this is a notes client application or a web based application. — Declan Lynch, Jan 31 '12 at 16:09

score 4 · Accepted Answer · answered Jan 31 '12 at 19:41

4

The questions is tagged Lotusscript so I assume that this is Lotusscript related (and not XPages related).

Have a look at the ODBCConnection, ODBCQuery, and ODBCResultSet Lotusscript classes in the Domino Designer Help database.

answered Jan 31 '12 at 19:41

Per Henrik Lausten

21,331
3
29
76

Is there something I have to do to make sure it recognizes my db? It always comes back with some sort of "Named Product Does Not Exist" error. My credentials are correct. – PRNDL Dev Feb 01 '12 at 20:48
Can you show some of your code? Alternatively see if you can find Lotusscript code examples using ODBCConnection - such as this post: http://stackoverflow.com/questions/4923236/lotuscript-some-questions-about-connecting-to-a-sql-db – Per Henrik Lausten Feb 01 '12 at 21:17
Updated main post with what i have. It's pretty straightforward. I have no idea why it wont connect. – PRNDL Dev Feb 02 '12 at 14:14
I assume your "call.con.ConnectTo" is a type (it should be Call con.ConnectTo). Try "If con.ConnectTo(...) Then" instead of your "While Not". Also has ODBC been set up on the server (in the ODBC Driver Manager on the OS level), and can you manually connect using that ODBC connection on the server? – Per Henrik Lausten Feb 02 '12 at 14:51
Okay I got the connection working and now I have the data. How do I assign the data to the combo boxes? Also, is it possible to assign one value from the sql as the combobox value and another value from the sql as the alias? – PRNDL Dev Feb 02 '12 at 18:43
You could place your ODBC logic in e.g. the QueryOpen event and have that code populate a computed multi-value field. You can then refer to this field in the Combobox field in "Use formula for choices". This will reload the data using ODBC on every form open so you should consider loading and storing data elsewhere - e.g. using an agent to store data in a profile document and then refer to that in the combobox. – Per Henrik Lausten Feb 02 '12 at 22:24

score 4 · Answer 2 · answered Jan 31 '12 at 19:54

4

If you're not able to use any XPages components, you could try the ODBC variant of @DBLookup in the 'Use formula for choices' part of your combobox.

answered Jan 31 '12 at 19:54

Jon McAuliffe

3,137
1
20
10

I tried this and all that I get is: " Could not execute @db function" – PRNDL Dev Feb 01 '12 at 19:23
Sounds like an ODBC setup problem (as Per refers to above). – Jon McAuliffe Feb 02 '12 at 22:19
+1 Although the op asks for a LotusScript solution, using the @Formula ODBC variant of '@DBlookup' is probably the easiest and most efficient solution to populate a combobox, as its a Notes client app. The error he gets is just a ODBC config error. An example is to put this in the combo/dialog values source: @dblookup("ODBC"; "ConnectionName"; "userid"; "userpw"; "table"; "field"; "key" [;options]) - see the Notes help for details. – andora Jul 24 '14 at 09:19

score 2 · Answer 3 · answered Feb 02 '12 at 14:48

The code you have added to the question is going to cause an infinite loop due to the while/wend

Depending on how often the choices for the dropdown boxes change you could also create a scheduled agent that connections to the SQL server. I do this a lot for some of my own internal applications as it cuts down on unnecessary traffic to the SQL server if the values being returned are always the same.

Your scheduled agent would need to use the LSXLC extensions by adding UseLSX "*lsxlc" to the options section of the Lotusscript agent.

The LSXLC has a LOT of options which would be beyond the scope of this question so I would recommend looking at the Domino Designer Help files and searching for lsxlc. There are lots of examples in the help files.

score 1 · Answer 4 · answered Jan 31 '12 at 14:49

1

Have a look at extlib on OpenNTF. It has an XPages component that allows you to connect to make SQL calls.

http://extlib.openntf.org

answered Jan 31 '12 at 14:49

Simon O'Doherty

9,259
3
26
54

I just started Lotus like 2 days ago, I don't even know how to implement xPages. – PRNDL Dev Feb 01 '12 at 19:29

score 1 · Answer 5 · answered Jan 31 '12 at 17:23

1

if you are using an xpages application, you can use a managed bean or static java method to get the data you want and bind it to the select values of the of combobox control.

answered Jan 31 '12 at 17:23

Toby Samples

2,168
14
15

How do I connect an SQL database to Lotus Domino Designer?

5 Answers5