Accessing SQL Server from LN 8.5

HCL Domino Domino Forum
1

Hi Everyone,

I am trying to access a table on a SQL Server using ODBC Connection. I am running both Client and Server on 8.5 version. I am able to execute the query with no problem. But when I try to access a column value on a record it returns “FALSE” instead of the value in the column. I am not sure if I am missing out something or there is some other issue. The code I use is as below:

Uselsx “*LSXODBC”

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet

Set qry.Connection = con

Set result.Query = qry

con.ConnectTo("SystemDSN")

qry.SQL = "SELECT * FROM EMPLOYEE"

result.Execute

	Do

	result.NextRow

	empno = result.GetValue("EmpNo", empno)

	

	

Loop Until result.IsEndOfData



result.Close(DB_CLOSE)

con.Disconnect

Here the string variable empno contains “False” after being executed.

Awaiting your response soon. Also let me know if there is any patch required to be installed for LN 8.5.

Thanks in advance.

Yusuf.

2

Subject: SQL connectivity from Domino 8.5 server

Hi Yusuf,

Sorry I am being no help for the solution you are looking for. Instead I need some help and information from you.

I am unable to connect to SQL server from Domino 8.5. I have added the SQL driver to agent Project and trying to use the JDBC. the error I receive is:

java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver

            at java.lang.Class.forName(Class.java:136)

            at NotificationObserver.getConnection(NotificationObserver.java:162)

            at NotificationObserver.createMessage(NotificationObserver.java:266)

            at NotificationObserver.NotesMain(NotificationObserver.java:126)

            at lotus.domino.AgentBase.runNotes(Unknown Source)

            at lotus.domino.NotesThread.run(Unknown Source)

This code is working fine with Domino 6.5 server. Can you please let me know how are you connecting to SQL from your Domino server?

Any help is highly appreciated.

Thanks,

Rohan

3

Subject: SQL Connectivity from LN 8.5

Hi Rohan,

I am using the LS Extension scripts in Lotus Notes to connect to the SQL Database using ODBC. I have posted the basic code needs to connect to the database and retrieve data from a table.

In case of the issue that I was facing I am not able to understand the reason that I found for it not working. The reason is LN is not able to get data from a nvarchar field in a SQL table. I tried changing the field from nvarchar to varchar and it worked as it should had been doing. Luckly for me that the columns that I changed from nvarchar to varchar does not affect the SQL database and no data loss occurs. So this is a temparory fix that I got but still looking for the correct fix.

I would request everyone accessing this forums to help me understand why the code is not working for nvarchar.

Rohan, if you can post the basic code for jdbc connectivity that you are using I will try to figure out if I can help you.

4

Subject: Problem with finding jar files for compilation

Hi Yusuf,

I am using the basic jdbc code for SQL connection:

Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”);

	String url = "jdbc:microsoft:sqlserver://" + ipAdd + ";databaseName="

			+ sDbName;

	return DriverManager.getConnection(url, userName, password);

I am able to connect to the SQL server (runtime) now but the problem has been reduced to not being able to find a couple of jar files the classes from which are being imported into the agent code.

When I try to compile the agent code, it throws an error that says the class doesn’t exist. I have placed these jar files under the ext folder where the SQL driver files are also placed.

However, at comile time, the agent fails to find the jar files. Can you give me an idea where should I set the classpath (server/client machine) and how? Does it have to be a system variable? Or in Notes.ini? Or Eclipse classpath (I have tried this option w/o any success)

Any help is appreciated,

Thanks,

Rohan