Unwanted insertion of "select * from" to the sql query string when using NotesSQL

HCL Domino Domino Forum
1

Dear All,

I need to create a report in ASP that pulls its data from the Notes DB. For this I use the NotesSQL 3.02g ODBC driver. I went through all the problems of permissions, IIS application protection, etc. so finally I am able to retrieve records. However I experience a strange problem: If my SQL query contains specific field names in the select statement, or e.g I use the ORDER BY clause somehow an extra “select * from” string is inserted in front of my query string therefore producing an invalid query.

This happens with my own local database replica, but does not happen with the NotesSQL sample database though…

This is what I see in the log file:

inetinfo 614-e08 ENTER SQLExecDirectW

	HSTMT               010523B8

	WCHAR *             0x017ACCF8 [      98] "select * from SELECT * FROM StandardActionsById WHERE ActionType='Internal' ORDER BY ActionID DESC"

	SDWORD                    98

inetinfo 614-e08 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)

	HSTMT               010523B8

	WCHAR *             0x017ACCF8 [      98] "select * from SELECT * FROM StandardActionsById WHERE ActionType='Internal' ORDER BY ActionID DESC"

	SDWORD                    98



	DIAG [37000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause (23063)

Interestingly, if I specify only the table name in the query string, this automatic insertion of “select * from” also happens.

If my query is built according to the pattern of: “select * from mytable where myfield1=XX” then the query is passed through without modification.

The web server is IIS 5.0. ASP code as follows:

Set oConn = Server.CreateObject(“ADODB.Connection”)

oConn.ConnectionString = “Driver={Lotus NotesSQL Driver (*.nsf)};” &_

                     "Server=local;" &_

                     "Database=data2\CTL\Requests.nsf;" &_

                     "UID=<usrname>" &_

                     "PWD=<pwd>"

oConn.Open

’ Create ADO Recordset Component, and associate it with ADO connection

Set oRs = Server.CreateObject(“ADODB.Recordset”)

Set oRs.ActiveConnection = oConn

SourceSQL = “SELECT * FROM StandardActionsById WHERE ActionType=‘Internal’ ORDER BY ActionID DESC”

oRs.Source = SourceSQL

oRs.Open

Anybody any idea how to correct this behaviour?

Thanks in advance,

Marcell