Is DECS only used to “retrieve” values from an external data source? Is it possible to actually add values to an external data source from a Notes application? If I can’t use DECS, would you recommend?
Subject: JDBC if you use a java agent
You can use JDBC, if your agent is a java agent.Just import the Jar files from the SQL2000 JDBC driver in a notes script library and you can use them like an jdbc driver in a normal java agent.
public class SQL_EXPORT extends AgentBase {
public void NotesMain() {
java.sql.Connection con;
try {
// create domino objects
lotus.domino.Session s = getSession();
lotus.domino.AgentContext agentContex = s.getAgentContext();
// create jdbc connection
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection("jdbc:microsoft:sqlserver:servername,...);
This is probably not the fastet method, but if you know java, it’s fairly easy.
You just need an pure java jdbc driver.
Andreas Hoster
Subject: Adding records to SQL Server 2000 from Notes
Checkout the http://www-12.lotus.com/ldd/doc/decs/6.0.1a/decsdoc6.nsf
Lookup virtual fields. I’m not sure if that’s what you are looking for…
Also, I use LSXODBC and LCLSX for importing and exporting data in bulk to\from notes databases.
HTH,
Kelvin Johnson
Subject: RE: Adding records to SQL Server 2000 from Notes
Thanks.
Do you have any examples of using LSXODBC? The tables I’m sending data to will be pretty basic.
Subject: RE: Adding records to SQL Server 2000 from Notes
You can do this via LSYou must make an ODBC conenction to your SQL server I’ve made this script library to do this:
##############################à
'ODBCconnection:
Option Public
Uselsx “*lsxodbc”
Dim nomeODBCdsn As String
Dim userODBC As String
Dim passwordODBC As String
Dim sqlserver As String
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim querysql As String
Dim result As ODBCResultSet
Sub parODBC(coddb)
' this sub if you need to have this parameter variable.
nomeODBCdsn="<DSNc onfigured on server>"
userODBC="XXX"
passwordODBC="YYY"
sqlserver="SSS"
End Sub
Function connessione() As String
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set result = New ODBCResultSet
Set qry.Connection = con
Set result.Query = qry
con.Threadsafedriver=True
status = con.ConnectTo(nomeODBCdsn,userODBC,passwordODBC)
If Not con.isconnected Then
If con.Getextendederrormessage<>"" Then
connessione=con.Getextendederrormessage
Else
connessione= "Connection error"
Exit Function
End If
End If
connessione=status
End Function
Sub Initialize
End Sub
Function setAttivaSQL() As Integer
setAttivaSQL=True
End Function
Function EseguiQuery(strquery As String,intbatchsize As Integer ,incachelimit As Integer, inttimeout As Integer)
'strquery = this is the QUERY
If inttimeout>0 Then
qry.Queryexecutetimeout=inttimeout
End If
qry.SQL = strquery
result.fetchbatchsize=intbatchsize
result.cachelimit=incachelimit
If Not result.execute Then
EseguiQuery=result.Getextendederrormessage
EseguiQuery=EseguiQuery & Chr(10) & Chr(13) & result.Geterrormessage
EseguiQuery=EseguiQuery & Chr(10) & Chr(13) & result.Geterror
EseguiQuery=EseguiQuery & Chr(10) & Chr(13) & querysql
’ 720 TimeOut
Else
EseguiQuery="True"
End If
End Function
Sub Terminate
End Sub
###################################
then I use it in my agent as show belove:
Call parODBC(coddb)
If Connessione() = “True” Then
querySQL=componiQRY(tmpPIVA)'this function to write the SQL command
test=EseguiQRY()
If result.isresultsetavailable Then
MyData=result.getValue("<SQL-Column Name as in the query",MyData)
End If
End If
Function ComponiQRY(tmpPIVA) As String
'HERE YOUR SQL QUERY
querysql=|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
[...],
FROM
[...]
WHERE
[...]
|
ComponiQRY = querysql
End Function
###############
Naturally you must make this conforme to what you need, but it runs quite good
By
Cristiano
Subject: RE: Adding records to SQL Server 2000 from Notes
Thanks so much for your response.
Quick question though… the following line seems to be incomplete:
Call parODBC(coddb)
If Connessione() = “True” Then
querySQL=componiQRY(tmpPIVA)'this function to write the SQL command
test=EseguiQRY()
If result.isresultsetavailable Then
MyData=result.getValue(“<SQL-Column Name as in the query”,MyData)
End If
End If
Should this go in the Initialize? If so, what parameters do I pass the EseguiQRY function.
Thanks!
Subject: RE: Adding records to SQL Server 2000 from Notes
hi, sorry, I’m on late …my cut & paste was not so good… you’ve found something more of what you need.
The parameter “tmpPIVA” is mine, and you do not need it!! The EseguiQRY function uses the querysql string as parameter, that is a global param and so you do not need to pass it to the function.
querySQL is a string made by the ComponyQRY function that contains the SQL command!
Let me know if it runs!!!
by