Acessando banco SQL através de driver ODBC

Boa Tarde Claudia, não cheguei a testar mas achei um exemplo veja abaixo

Código:

Status: Verified

GOAL:

How to use use the Progress 4GL against Microsoft’s ADO (ActiveX Data
Object) technology via the ODBC protocol.

FIX:

Any Windows based Progress Client (Version 8.2 and above) can be used
to access ODBC compliant databases such as MS SQL Server, DB2 and
AS/400. 

(NOTE: Microsoft’s ADO and ODBC technologies are complex subjects and
fall outside of the scope of this Knowledge Base.)

The Progress DataServer technology allows you to execute your existing
Progress 4GL code against foreign DBMS’s, with little or no source
code modification. Any 4GL modifications that *are* needed would be
primarily to compensate for the differences between the Progress and
the target DBMS. For instance, SQL Databases such as MS SQL Server do
not recognize the Progress SHARE-LOCK; this is not a deficiency of the
Progress DataServer technology, but rather a strength of the Progress
4GL and RDBMS. In situations when the Progress DataServer technology
is not the right mechanism for your development or deployment, there
are alternative ways to access foreign DBMS's.

The following source code is written against the Progress Sports2000
Database which has been migrated to a non-Progress source DBMS via the
Progress DataServer. The code populates a Progress Temp-Table with
data from the Sports2000.Customer table. 

If you are not connecting to a Database that was migrated from the
Progress Sports2000 Database, these four code sections will need to be
modified in order to run the sample against your particular database
and DSN configuration.

1) Temp table definition: DEFINE TEMP-TABLE tt ...
2) Displayed fields in Browser: DEFINE BROWSE ...
3) Data assignment: ASSIGN tt.cName
4) All DSN field definitions: Assign ODBC-DSN = "MyRemoteODBCDB" 


/* A sample procedure to test an ADO connection */

Def var ObjRecordSet as com-handle no-undo.
Def var ObjConnection as com-handle no-undo.
Def var ObjCommand as com-handle no-undo.
Def var ODBC-DSN as character no-undo.
Def var ODBC-SERVER as character no-undo.
Def var ODBC-USERID as character no-undo.
Def var ODBC-PASSWD as character no-undo.
Def var ODBC-QUERY as character no-undo.
Def var ODBC-STATUS as character no-undo.
Def var ODBC-RECCOUNT as integer no-undo.
Def var ODBC-NULL as character no-undo.
Def var ODBC-CURSOR as integer no-undo.

/* If not executing against a sports2000 like database this temp table
will need to be redefined */ 
DEFINE TEMP-TABLE tt 
FIELD CustNum AS integer 
FIELD cName AS character 
FIELD address AS character 
FIELD address2 AS character 
FIELD City AS character 
FIELD State AS character 
FIELD Country AS character 
FIELD Phone AS character 
FIELD Contact AS character 
FIELD SalesRep AS character 
FIELD Comments AS character 
FIELD CreditLimit AS decimal 
FIELD Balance AS decimal 
FIELD Terms AS character 
FIELD Discount AS integer 
FIELD PostalCode AS character 
FIELD Fax AS character 
FIELD EmailAddress AS character .

DEFINE QUERY q1 FOR tt SCROLLING.
DEFINE BROWSE b1 QUERY q1 NO-LOCK 
DISPLAY custnum cname address address2 City State Country 
Phone Contact SalesRep Comments CreditLimit Balance 
Terms Discount PostalCode Fax EmailAddress 
WITH NO-ROW-MARKERS SEPARATORS SIZE 70 BY 12.62 EXPANDABLE.

DEFINE FRAME f1 b1 WITH NO-BOX.

/* Create the connection object for the link to SQL */
Create "ADODB.Connection" ObjConnection.
/* Create a recordset object ready to return the data */
Create "ADODB.RecordSet" ObjRecordSet.
/* Create a command object for sending the SQL statement */
Create "ADODB.Command" ObjCommand.

/* Change the below values as necessary */ 
Assign ODBC-DSN = "MyRemoteODBCDB" /* The ODBC DSN */
ODBC-SERVER = "localhost" /* The name of the server hosting the SQL DB
and DSN */
ODBC-USERID = "sa" /* The user id for access to the SQL Database */
ODBC-PASSWD = "" /* Password required by above user-id */
ODBC-QUERY = "SELECT * from customer".
/* Open up the connecti.on to the ODBC Layer */
ObjConnection:Open ( "data source=" + ODBC-DSN + ";server=" +
ODBC-SERVER, ODBC-USERID, ODBC-PASSWD, 0 ) no-error. 
/* Check for connection errors */
If ( error-status:num-messages > 0 ) then 
ODBC-STATUS = "Error: Could not establish connection.".

Else 
DO:
Assign ObjCommand:ActiveConnection = ObjConnection
ObjCommand:CommandText = ODBC-QUERY
ObjCommand:CommandType = 1 /* adCmdText */
ObjConnection:CursorLocation = 3 /* adUseClient */
ObjRecordSet:CursorType = 3 /* adOpenStatic */
ObjRecordSet = ObjCommand:Execute ( output ODBC-NULL, "", 32 )
ODBC-RECCOUNT = ObjRecordSet:RecordCount.

/* Have we returned any rows ? */
If ( ODBC-RECCOUNT > 0 ) and not ( ODBC-RECCOUNT = ? ) then 
Do:
ObjRecordSet:MoveFirst no-error.

Do while ODBC-CURSOR < ODBC-RECCOUNT:
/* Display the data from the query (or create a Progress temp-table
for future use) */
/* Display ObjRecordSet:Fields ("name"):Value format "x(20)". */
CREATE tt.
ASSIGN tt.cName = ObjRecordSet:Fields ("name"):Value
tt.custnum = ObjRecordSet:Fields ("custnum"):VALUE
tt.address = ObjRecordSet:Fields ("address"):Value
tt.address2 = ObjRecordSet:Fields ("address2"):Value 
tt.City = ObjRecordSet:Fields ("City"):Value
tt.State = ObjRecordSet:Fields ("State"):Value
tt.Country = ObjRecordSet:Fields ("Country"):Value
tt.Phone = ObjRecordSet:Fields ("Phone"):Value
tt.Contact = ObjRecordSet:Fields ("Contact"):Value
tt.SalesRep = ObjRecordSet:Fields ("SalesRep"):Value
tt.Comments = ObjRecordSet:Fields ("Comments"):Value 
tt.CreditLimit = ObjRecordSet:Fields ("CreditLimit"):Value
tt.Balance = ObjRecordSet:Fields ("Balance"):Value
tt.Terms = ObjRecordSet:Fields ("Terms"):Value
tt.Discount = ObjRecordSet:Fields ("Discount"):Value
tt.PostalCode = ObjRecordSet:Fields ("PostalCode"):Value
tt.Fax = ObjRecordSet:Fields ("Fax"):Value
tt.EmailAddress = ObjRecordSet:Fields ("EmailAddress"):Value. 

Assign ODBC-CURSOR = ODBC-CURSOR + 1.
ObjRecordSet:MoveNext no-error.
End. /* retrieved a single data row */

End. /* retrieved all data rows */
Else
Assign ODBC-STATUS = "No records found.".

/* Close the ADO connection */
ObjConnection:Close no-error.

End. /* The connection opened correctly */


/* Don't forget to release the memory!! */
Release object ObjConnection no-error.
Release object ObjCommand no-error.
Release object ObjRecordSet no-error.

Assign ObjConnection = ? ObjCommand = ? ObjRecordSet = ?.

OPEN QUERY q1 FOR EACH tt.
ENABLE ALL WITH FRAME f1.

WAIT-FOR WINDOW-CLOSE OF CURRENT-WINDOW..