I have been trying to play around with attaching to an SQL database and retieve data. I’m able to connect(that was a big step), I’m able to build a list and cycle through each record. But I can’t figure how to write the info, to a notes db. I want to copy the data to a notes field called LName, sql field name is name. I want to do this from an Agent in the notes db and not LEI activity. I have read the red book and I’m more confused then when I started. Thank in Advance.
If(con.Execute(|select * from testserver|,keylist) = 0) Then
Print (“Not Connected”)
Else
Print (“Connected”)
End If
Set fld = keylist.Lookup(“Name”)
While(con.fetch(keylist,) >0)
count = count +1
Messagebox(“name” & Cstr(count) & “=” & fld.Value(0))
Subject: RE: LSXLC coding Question
That’s not the effect the redbook is supposed to have…
All right – saying you want to write the value into a Notes field still leaves a lot of questions unanswered. The biggest ones are:
in which Notes document – new or existing?
if existing, how do you decide which one you want?
if new, what other fields do you need to set?
did you want to write all the values into the same document or a different document for each row?
There are basically two ways to update the Notes information… using the NotesXxx built-in classes, or with the Notes connector of the lc lsx. Which one is easier and more efficient depends on the answers to the above.
Doing it with the LC LSX would be a good way to learn how this is done. Basically, you use “notes” as the connector type argument to the LCConnection.New method, specify which Notes database you want by setting the Server and Database arguments to the server and filepath of the Notes database. Call Connect just as you would for a relational DB. Instead of a relational view, use a Notes form for your Metadata property.
Otherwise, you would work pretty much just like for a relational database. What that means specifically again, depends on the details of your problem.
Subject: LSXLC coding Question
below is how I did it in JAVAmaybe it will give you a hit on how to do it in Lotus Script. If this does not help you, please feel free to e-mail me and I will see if I can give you a better answer…
Session session = getSession();
AgentContext agentContext = session.getAgentContext();
Document doc = agentContext.getDocumentContext( );
PrintWriter pw = getAgentOutput();
String url = "jdbc:db2:S101B6AD;translate binary=true";
DriverManager.registerDriver(new com.ibm.db2.jdbc.app.DB2Driver());
Connection dbConnect = DriverManager.getConnection(url, gLogin, gPassword);
Statement stmt = dbConnect.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM <table name goes here> WHERE CUSNO =" + gNumber + "'");
rs.next();
doc.replaceItemValue("Form", "wSaveLost");
doc.replaceItemValue("Customer", rs.getString("CNAME"));
doc.replaceItemValue("ADD1", rs.getString("CADD1"));
doc.replaceItemValue("ADD2", rs.getString("CADD2"));
doc.replaceItemValue("CITY", rs.getString("CADD3"));
doc.replaceItemValue("STATE", rs.getString("CSTTE"));
doc.replaceItemValue("ZIP", rs.getString("CZIPC"));
doc.replaceItemValue("PHONE", rs.getString("CPHOA"));
doc.save();
stmt.close();
rs.close();
dbConnect.close();
Subject: LSXLC coding Question
I have to give lotus a lot of credit on there documentation in most cases. I don’t know why I’m having a problem with LC LSX stuff, maybe because I don’t know SQL.
I would like to use LC LSX (as I trying to learn it)I’m creating a new document for each row. I thought for coping the info to notes the codes would go something like this:
Thanks for responding
snippet:
Set doc = dc.GetFirstDocument
While Not doc Is Nothing
Set fld = fldlst.Lookup(“Name”)
While(con.fetch(fldlst) >0)
Call doc.ReplaceItemValue(“LName”,fld.value(0))
End If
doc.Save True,True
Set doc=dc.GetNextDocument(doc)
Wend
End
Subject: RE: LSXLC coding Question
would you object to using JDBC (JAVA)?
Subject: RE: LSXLC coding Question
Hi Michael D Marca!, I don’t have a problem converting to JAVA, just haven’t tried it yet, but when I saw your relpy I did think I would give it a go and try it. Thanks for your help.
Subject: RE: LSXLC coding Question
below is what I used to create a Notes doc from data in a DB2 table based on a value that the user inputed.You will have to change the JDBC driver to a SQL one and you will have to enter your information where you see the <> tags
otherwise have fun
also, I found next to no help when I was trying to learn this stuff too…
//Get Customer Information
import lotus.domino.*;
import java.sql.*;
import java.lang.*;
import java.io.*;
import java.net.*;
import java.util.Vector;
import java.util.*;
public class JavaAgent extends AgentBase {
String UNID;
public void NotesMain() {
try {
Session session = getSession();
AgentContext agentContext = session.getAgentContext();
Document doc = agentContext.getDocumentContext( );
PrintWriter pw = getAgentOutput();
DateTime dt = session.createDateTime("Today");
dt.setNow();
String gNum = doc.getItemValueString("clNumber");
String hServer = "<your server>";
String hLogin = "<login>";
String hPassword = "<password>";
getCustomer(hLogin, hPassword, gNum);
}
catch(Exception e) {
e.printStackTrace();
}
}
public void getCustomer(String gLogin, String gPassword, String gNumber) {
try {
Session session = getSession();
AgentContext agentContext = session.getAgentContext();
Document doc = agentContext.getDocumentContext( );
PrintWriter pw = getAgentOutput();
String url = "jdbc:db2:" + <your server> + ";translate binary=true";
DriverManager.registerDriver(new com.ibm.db2.jdbc.app.DB2Driver());
Connection dbConnect = DriverManager.getConnection(url, gLogin, gPassword);
Statement stmt = dbConnect.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM <table name> WHERE CUSNO =" + gNumber + "'");
rs.next();
doc.replaceItemValue("Form", "wSaveLost");
doc.replaceItemValue("Customer", rs.getString("CNAME"));
doc.replaceItemValue("ADD1", rs.getString("CADD1"));
doc.replaceItemValue("ADD2", rs.getString("CADD2"));
doc.replaceItemValue("CITY", rs.getString("CADD3"));
doc.replaceItemValue("STATE", rs.getString("CSTTE"));
doc.replaceItemValue("ZIP", rs.getString("CZIPC"));
doc.replaceItemValue("PHONE", rs.getString("CPHOA"));
doc.save();
stmt.close();
rs.close();
dbConnect.close();
}
catch(Exception e) {
e.printStackTrace();
}
}
}
Subject: RE: LSXLC coding Question
So you’re assuming that there are exactly as many Notes documents as there are rows in the relational database, and that whatever method you’re using to create a NotesDocumentCollection will produce a collection that’s in an order that matches the order of the rows in your result set.
Given those assumptions, your code should work; but I think the assumptions are probably wrong.
It’s usually the case that there is some key field that is used to identify a Notes document with a particular relational row. You don’t want to copy the value from the relational database into just any Notes document – you want to copy it into the document with the matching key field.
The easiest way to accomplish this, is to use a Notes view that’s sorted by that key field, and use GetDocumentByKey to find the right Notes document to update.
However, this is not the highest-performance way. Plus, if you’re attempting to synchronize deletions, it’s not easy or efficient to detect which Notes documents have keys that are no longer in the relational database so that you can delete those Notes documents.
For a more efficient technique, see this download, which contains an agent (5. Replicate Pirates) that shows how to use the LC LSX to do a one-way keyed synchronization between two databases – either or both may be a Notes database.
Subject: LSXLC coding Question
Another resource is this redpaper. It is several years old but I believe it is still useful. It was written to help people convert from LS:DO to LSXLC.http://publib-b.boulder.ibm.com/Redbooks.nsf/3c7330a3359c75a68525698b007bbec9/e4de4d3f6bbbac7885256a020083336c?OpenDocument