Using OLEDB is pretty straight forward once you get the connection string figured out. I don't live and breathe SQL Server so getting the connection string right is always a refresher course for me.
This example connects to a SQL Server database on my system so the connection and table name will need to be changed. The output goes to the Server log via the Message statement.
/*******************************/
/* constants */
&GLOBAL-DEFINE adLockReadOnly 1
&GLOBAL-DEFINE adLockOptimistic 3
&GLOBAL-DEFINE adLockBatchOptimistic 4
&GLOBAL-DEFINE adUseClient 3
&GLOBAL-DEFINE adOpenForwardOnly 0
DEFINE VARIABLE strSql AS CHARACTER NO-UNDO.
DEFINE VARIABLE objCon AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vcConString AS CHARACTER NO-UNDO.
CREATE "ADODB.Connection" objCon.
objCon:ConnectionString =
"Provider=SQLOLEDB;Password=epicor;" +
"Integrated Security=SSPI;User ID=Administrator;" +
"Initial Catalog=Sample;Data Source=EPICORSI\SQL2008".
objCon:OPEN(,,,). /* makes the connection to the datasource */
DEFINE VARIABLE objRS AS COM-HANDLE NO-UNDO.
CREATE "ADODB.RecordSet" objRS.
objRS:CursorLocation = {&adUseClient}.
objRS:CursorType = {&adOpenForwardOnly}.
objRS:locktype = {&adLockReadOnly}.
strSql =
"SELECT * " +
" FROM DS_Client".
objRS:OPEN (strSql,objCon,,,).
DO WHILE NOT objRS:eof:
MESSAGE
/* just as easy could load a dynamic string list */
objRS:FIELDS("Description"):VALUE .
objRS:MoveNext.
END.
RELEASE OBJECT objRS.
RELEASE OBJECT objCon.
/******************************/
Jim Kinneman
Encompass Solutions, Inc