Method directive calling an external database

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

So, this is weird. I know.

 

I need to put a method directive in place that will be triggered whenever a new revision is approved. The method directive will query an external database to see if it can find any records associated with that part number. If it does find at least one record, the directive will send an email out. If it does not, it will do nothing.

 

The external database is a SQL database on the Epicor server.

 

Any ABL gurus got a chunk of code that will let me query a sql server?

http://knowledgebase.progress.com/articles/Article/P10561


Thank you in advance,
Calvin Dekker
[cid:image006.jpg@...4496B0]<http://www.codabears.com/>
630-672-7688 x1484
[cid:image002.png@...3FB4B0] <https://www.facebook.com/CodaBearsInc> [cid:image003.png@...3FB4B0] <http://www.linkedin.com/company/codabears-inc> [cid:image004.png@...3FB4B0] <https://twitter.com/CodaBearsInc> [cid:image005.png@...3FB4B0] <http://google.com/+CodaBearsIncRoselle>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Tom J. Christie
Sent: Wednesday, May 7, 2014 3:11 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Method directive calling an external database


So, this is weird. I know.

I need to put a method directive in place that will be triggered whenever a new revision is approved. The method directive will query an external database to see if it can find any records associated with that part number. If it does find at least one record, the directive will send an email out. If it does not, it will do nothing.

The external database is a SQL database on the Epicor server.

Any ABL gurus got a chunk of code that will let me query a sql server?



[Non-text portions of this message have been removed]