Kbase 21356: ODBC & ADO (ActiveX Data Object) access from Progress 4GL
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/7/2009 |
|
Status: Verified
GOAL:
How to use use the Progress 4GL against Microsoft?s ADO (ActiveX Data Object) technology via the ODBC protocol.
FACT(s) (Environment):
Progress 8.2x
Progress 8.3x
Progress 9.x
OpenEdge 10.x
Windows
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..