Problems with ODBC Select statement in Notes

HCL Domino Domino Forum
1

I am attempting to retreive a range of records from an Oracle database to convert to Notes documents using an ODBC connection. I have a Select statement that retrieves all records between two dates. If I cut and paste my Select statement into Toad and run it against the data base, I get all the records as I would expect. When I run it in my Notes procedure, I get only today’s records, if today’s date is in the search, else I get zero records. Here is my Select statement:

OraQuery.SQL = “SELECT * FROM ORACLE_TABLE WHERE TRUNC(MY_DATE) > TO_DATE('” + sDATE.DateOnly + “‘,‘MM/DD/YYYY’) - 1 AND TRUNC(MY_DATE) < TO_DATE(’” + eDATE.DateOnly + “',‘MM/DD/YYYY’) + 1”

Does anyone have any idea why Notes doesn’t like this?

Thanks,

MJ

2

Subject: Problems with ODBC Select statement in Notes

Notes version is 6.0.1

MJ

3

Subject: Problems with ODBC Select statement in Notes

Try placing the select into a string first like:

qryString = “SELECT * FROM ORACLE_TABLE WHERE TRUNC(MY_DATE) > TO_DATE('” + sDATE.DateOnly + “‘,‘MM/DD/YYYY’) - 1 AND TRUNC(MY_DATE) < TO_DATE(’” + eDATE.DateOnly + “',‘MM/DD/YYYY’) + 1”

OraQuery.SQL = qryString

+++++++++

That method usually works for me. Remember to make sure your quotes are escaped. Hard to tell here if they are, but they may cause you problems as well.