Question Progress to SQL

[jmac13]

Hi All,

I'm using 10.2b and we've got a project coming up where we will be connecting to a SQL db and updating a table/tables and maybe getting data back. When we have done this in the past we have used the com-handles adodb.connection,adodb.Command & adodb.Recordset. Using a ODBC connection to connect to the SQL DB and thenWe would do the following example (See below) we would say do a Select then go round recordset object and create a temp-table or populate a db table or a Update depending on what we are doing.

Is there a better way of doing this or should I just stick with doing this? thanks

  define variable comConn            as com-handle  no-undo.
  define variable comCmd              as com-handle  no-undo.
  define variable comRS              as com-handle  no-undo. 
   
   create "adodb.connection"  comConn.
   create "adodb.Command"      comCmd.
   create "adodb.Recordset"    comRS.
 
 
   comConn :Open("{&DSN}","{&UID}","{&PSW}",-1) no-error.
 
    if not valid-handle(comConn) then return.
 
    comCmd  :ActiveConnection  = comConn no-error.   
    comCmd  :CommandText        = ipChrQuery no-error.
    comRS  :Open(comCmd, , 3,2,) no-error.

 

[GregTomkins]

Interesting, never seen com handles used like that before. Now I want to try it

I guess you could use a DataServer, no?, but that is muchos dineros and probably way way way overkill for what you are doing.

Or you could do something more entertaining like write a little server in C#, Java whatever and communicate with Progress via sockets or proxies ... also probably overkill.

 

[RealHeavyDude]

Nowadays you should think about a service. Accessing a "foreign" database directly or letting a "foreign" application directly access your database is not recommended for several reasons. And by "foreign" I do not only mean a different technology stack - it might very well be two Progress OpenEdge applications that for some reason need to exchange data.

If there is any possibility then the other software ( I think that it most likely won't be just a database ) should offer a service - maybe a web service that you just call without needing to know their gory implementation details. The same is true for the other system - you should offer them a service so that they do not need to know the gory implementation details of your application.

Just some thoughts.

Heavy Regards, RealHeavyDude.

 

[jmac13]

scrap what I said before.. looks like we will be having the DB as a SQL Schema.. not sure if thats better or worse..Was trying to find some docs on one of the online courses on how to do this.. I have done it before but once again I've been shown by someone else and not sure if the correct method anyone got some good links?

thanks all

 

[jmac13]

I'm currently looking at this:

http://documentation.progress.com/output/OpenEdge102b/pdfs/dmsql/dmsql.pdf

I may have more questions but I've add the link for anyone else looking for the same info