Adding records to SQL Server 2000 from Notes

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

mailto:andreas.hoster@herma.de

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

crigon@gmail.com