Consultor Eletrônico

Status: Verified

GOAL:

How to set up and test the ODBC driver on UNIX?

GOAL:

How to install and configure the ODBC driver on UNIX?

GOAL:

How do I set up ODBC on UNIX?

GOAL:

How do I setup ODBC DSN on UNIX?

GOAL:

How to prepare for a UNIX to UNIX ODBC connection?

GOAL:

How do I configure ODBC on UNIX?

GOAL:

How to use ODBC on UNIX?

FACT(s) (Environment):

Progress 9.x
OpenEdge 10.x
UNIX

FIX:

Here is an outline of the general steps needed to use the ODBC driver on UNIX:
1. ensure the database is started
2. ensure the Progress/OpenEdge ODBC driver is installed
3. configure a data source
4. compile / run the program
Note that the ODBC API is standardized and there are a number of ODBC frameworks available on UNIX which allow to mix ODBC drivers from different vendors. Progress provides both a specific ODBC driver for the Progress and OpenEdge databases, and an ODBC framework to use this driver. It may be possible to use the ODBC driver within a third-party framework - although this has not been specifically tested by Progress.
The ODBC driver to use for the data source is located in $DLC/odbc/lib and is named after the following:
- pgpro9XX.so - Progress 9 driver
- pgoe10xx.so - OpenEdge 10 driver
The drivers depends on other libraries in $DLC/odbc/lib and $DLC/lib; programs using this driver should be able to find shared libraries in these locations. Check below for details.
Here are detailed steps to test the ODBC driver with an example program:
1. A test database should be started, and listening on a network port using parameter -S.

2. In the UNIX environment, there is no standard ODBC administration utility. To configure a data source, you must edit the system information file, a plain text file that is usually called odbc.ini. The system information file is maintained using any text editor to define data source entries.

Therefore, create an odbc.ini file with the following information under $DLC/odbc. Replace the settings as needed - check out the comments inside the file for more information.

[ODBC Data Sources]
;mystest is the Data Source Name (DSN) and could be any name you choose. Each DSN should be unique.
;you can have multiple data sources. For each one you should create a configuration section below.
mytest=Progress_SQL92_Driver

[mytest]
; Driver is the path to the shared library containing the Progress driver.
; Replace with the complete path to your version of the ODBC driver in $DLC/odbc/lib.
; The shared library is named after the following pattern:
; pgpro9XX.so - Progress 9 driver
; pgoe10XX.so - OpenEdge 10 driver
Driver=/usr1/dlc91c/odbc/lib/pgpro915.so

DatabaseName=odbctest
; PortNumber is the port number where the SQL broker is listening. If you don't use separate brokers
; for 4GL and SQL, this is the same port number as the -S parameter.
PortNumber=7077
; HostName is the name of the host where the database server is running. Use "localhost" if
; the database is running locally. This setting should match database parameter -H.
HostName=mr-sniffles
; LogonID can be an account set up for the database if security is enabled.
; You may also use your logon account with the password blank.
; The default SQL dba is the user who created the database.
; This account should be used to gain full access to tables and users for the SQL part of the database.
LogonID=sysprogress
Password=sysprogress

; The default values for the following options are acceptable in most configurations. Refer
; to the documentation if you need to change them.
APILevel=1
ConnectFunctions=YYN
CPTimeout=60
DriverODBCVer=03.60
FileUsage=0
SQLLevel=0
UsageCount=1
ArraySize=50
DefaultLongDataBuffLen=2048
DefaultIsolationLevel=REPEATABLE READ
StaticCursorLongColBuffLen=4096

[ODBC]
; the ODBC section is generic and not specific to Progress/OpenEdge. It pertains to
; the standard ODBC framework (shared among ODBC drivers).
; A copy of the standard ODBC framework libraries is included with Progress/OpenEdge;
; use th.is if you do not have another ODBC environment already in use on the system.

; InstallDir is the root path to the ODBC framework. $DLC/odbc is not an acceptable path, use the absolute path..
InstallDir=/usr1/dlc91c/odbc
; Use Trace=1 to enable an ODBC trace. This requires a path to a log file (TraceFile)
; and a path to a tracing library (included with Progress/OpenEdge in $DLC/lib/odbctrac.so, be sure to use the absolute path in the configuration)
Trace=1
TraceFile=/tmp/odbctrace.out
TraceDll=/usr1/dlc91c/odbc/lib/odbctrac.so
UseCursorLib=0

In this test file, the "sysprogress" account is used with password "sysprogress". It is a special account with DBA privileges, but it must first be activated. To do this, access the Data Administration utility, go to Security. Add a user named sysprogress with password sysprogress. After you validate the account is ready to use.
Note: you cannot reference environment variables in odbc.ini! Path names should be written in full.
If you do not have an ODBCINST environment variable set yet that is pointing to the odbcinst.ini file containing a list of installed ODBC drivers, then you need to to this as well.
An example of the contents for an odbcinst.ini using the OpenEdge version 10.1C03 ODBC driver could look like this:
[ODBC Drivers]
Progress OpenEdge 10.1C Driver=Installed
[Progress OpenEdge 10.1C Driver]
Driver=
as01/oe/linuxx86/v101C03/dlc/odbc/lib/pgoe1023.so]

3. Create a environment variable called ODBCINI that points to the odbc.ini file. For example:
ODBCINI=$DLC/odbc/odbc.ini; export ODBCINI
and ODBCINST for the case not there yet as well
ODBCINST=$DLC/odbc/odbcinst.ini; export ODBCINST


4. Add the directories $DLC/odbc/lib and $DLC/lib directory to the shared library search path. This search path is usually set using environment variables: LD_LIBRARY_PATH on Solaris and Linux, SHLIB_PATH on HP/UX, or LIBPATH on AIX. This enables the ODBC application to locate the ODBC core components and drivers at runtime. For example:
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DLC/lib:$DLC/odbc/lib; export LD_LIBRARY_PATH


Note: A test application called "demo" may be already installed with Progress/OpenEdge in $DLC/odbc/demo. This test application requires you to use a custom database, therefore it's recommended you use the sample code below which is based on a Sports2000 database.
5. Create directory named "demo" in $DLC/odbc

6 . Create a demoodbc.c file from the information below.

/*
** File: demoodbc.c
**
** Purpose: To demonstrate some of the ODBC function calls used to
** fetch some simple data from a SQL database.
**
** MERANT, Inc. 1993-1999
**
** ODBC Functions Used:
**
** SQLAllocConnect
** SQLAllocEnv
** SQLAllocStmt
** SQLBindCol
** SQLConnect
** SQLDisconnect
** SQLError
** SQLExecute
** SQLFetch
** SQLFreeConnect
** SQLFreeEnv
** SQLFreeStmt
** SQLPrepare
** SQLSetConnectOption
*/

/*
** The Header File qeodbc.h when included before the MS ODBC header
files
** define data types used by the ODBC Header files.
*/
#include <stdio.h>
#include <stdlib.h>
#include <strings.h>
#include <string.h>
#include <sqlext.h>

/*
** Define Some useful defines
*/
#if !defined (NULL)
#define NULL 0
#endif


/*
** function: ODBC_error
**
** Purpose: Display to stdout current ODBC Errors
**
** Arguments: henv _ ODBC Environment handle.
** hdbc - ODBC Connection Handle error generated on.>** hstmt - ODBC SQL Handle error generated on.
**
** Returns: void
**
*/

void ODBC_error ( /* Get and print ODBC error messages */
HENV henv, /* ODBC Environment */
HDBC hdbc, /* ODBC Connection Handle */
HSTMT hstmt) /* ODBC SQL Handle */
{
UCHAR sqlstate[10];
UCHAR errmsg[SQL_MAX_MESSAGE_LENGTH];
SDWORD nativeerr;
SWORD actualmsglen;
RETCODE rc;

loop: rc = SQLError(henv, hdbc, hstmt,
sqlstate, &nativeerr, errmsg,
SQL_MAX_MESSAGE_LENGTH - 1, &actualmsglen);

if (rc == SQL_ERROR) {
printf ("SQLError failed!\n");
return;
}

if (rc != SQL_NO_DATA_FOUND) {
printf ("SQLSTATE = %s\n",sqlstate);
printf ("NATIVE ERROR = %d\n",nativeerr);
errmsg[actualmsglen] = '\0';
printf ("MSG = %s\n\n",errmsg);
goto loop;
}
}

/*
** function: ODBC_Connect
**
** Purpose: Allocates ODBC HENV and HDBC.
**
** Arguments: henv _ Pointer to environment handle
** hdbc - Pointer to connection handle
**
** Returns: RETCODE - Return status from last ODBC Function.
**
*/

RETCODE ODBC_Connect( /* Perform Driver Connection */
HENV henv, /* ODBC Environment Handle */
HDBC hdbc, /* ODBC Connection Handle */
UCHAR *driver, /* Data Source Name */
UCHAR *uid, /* User ID */
UCHAR *pwd) /* User Password */
{
RETCODE rc;
int retries;

#if defined(TRACING)
rc = SQLSetConnectOption(hdbc, SQL_OPT_TRACE, 1);
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
return (rc);
#endif
/*
** try connecting up to 3 times
*/
for (retries = 1; retries <= 3; retries++) {
rc = SQLConnect (hdbc, driver, SQL_NTS,
uid, SQL_NTS, pwd, SQL_NTS);
if ((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO))
return (rc);
else {
printf("SQLConnect: Failed...\n");
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
printf("SQLConnect: Retrying Connect.\n");
}
}
/*
** Attempt to obtain a meaningful error as to why connect failed.
*/
printf("No connection could be established.\n");
ODBC_error (henv, hdbc, SQL_NULL_HSTMT);
return (SQL_INVALID_HANDLE);
}

/*
** function: EnvInit
**
** Purpose: Allocates ODBC HENV and HDBC.
**
** Arguments: henv _ Pointer to environment handle
** hdbc - Pointer to connection handle
** Returns: RETCODE status from ODBC Functions.
*/
RETCODE EnvInit(HENV *henv, HDBC *hdbc)
{
RETCODE rc;

rc = SQLAllocEnv (henv);
if ((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO))
rc = SQLAllocConnect (*henv, hdbc);
return (rc);
}

/*
** function: EnvClose
**
** Purpose: Frees environment and connection handles.
**
** Arguments: henv _ environment handle
** hdbc - connection to handle
*/
void EnvClose(HENV henv, HDBC hdbc)
{
SQLDisconnect (hdbc);
SQLFreeConnect (hdbc);
SQLFreeEnv (henv);

}
/*
** Defines Used by ArgParse and Parent.
*/
#define PWD_ARG 1
#define UID_ARG 2
#define DSN_ARG 3
#define HELP_ARG 4

/*
** function: ArgParse
**
** Purpose: To parse runtime arguments.
**
** Arguments: argv which is a character string to be parsed.
**
*/
int ArgParse(char *argv)
{
if (!strcasecmp(argv, "-help"))
return (HELP_ARG);
if (!strcasecmp(argv, "-pwd"))
return (PWD_ARG);
if (!strcasecmp(argv, "-uid"))
return (UID_ARG);
else
return (DSN_ARG);
}

/*
** Defines used by main program.
*/
#define PWD_LEN 32
#define UID_LEN 32
#define OPT1_LEN 255
#define OPT2_LEN 255
#define DSN_LEN 32
#define PWD_MSG1 "Requested password exceeds compiled limit of %d.\n"
#define PWD_ERR1 "Password not found after keyword -pwd on command line.\n"
#define UID_MSG1 "Requested username exceeds compiled limit of %d.\n"
#define UID_ERR1 "Username not found after keyword -uid on command line.\n"
#define USAGE_MSG1 "Usage: %s [-uid. username] [-pwd password] DSN.\n"
#define BANNER "%s MERANT, Inc. ODBC Sample Application.\n"

/*
** Program: demo
**
** Purpose: Demo ODBC routine using demo data EMP.
*/
int main(int argc, char * argv[])
{
HDBC hdbc;
HENV henv;
HSTMT hstmt;
RETCODE rc;
UCHAR uid[UID_LEN]={0};
UCHAR pwd[PWD_LEN]={0};
UCHAR driver[DSN_LEN];
UCHAR sql[255];
UCHAR table[255];
UCHAR empnum[15],
firstname[15],
lastname[15],
startdate[30],
deptcode[15];
SDWORD val_empnum,
val_firstname,
val_lastname,
val_startdate,
val_deptcode;

/*
** Define Table and Driver
*/
printf(BANNER, argv[0]);
/*
uid[0] = NULL;
pwd[0] = NULL;
*/
if (argc > 1) {
int argIndex; /* Input argument index */
int argCount; /* Running count of arguments */

for (argCount=argc-1, argIndex=1; argCount > 0;
argIndex++, argCount--) {
switch (ArgParse(argv[argIndex])) {
case HELP_ARG:
printf(USAGE_MSG1, argv[0]);
return(1);
case PWD_ARG:
argIndex++;
if (argCount <= 1) {
printf(PWD_ERR1);
printf(USAGE_MSG1, argv[0]);
return(1);
}
if (strlen(argv[argIndex]) > PWD_LEN) {
printf(PWD_MSG1, PWD_LEN);
return(1);
}
strcpy((char*)pwd, argv[argIndex]);
argCount--;
break;
case UID_ARG:
argIndex++;
if (argCount <= 1) {
printf(UID_ERR1);
printf(USAGE_MSG1, argv[0]);
return(1);
}
if (strlen(argv[argIndex]) > UID_LEN) {
printf(UID_MSG1, UID_LEN);
return(1);
}
strcpy((char*)uid, argv[argIndex]);
argCount--;
break;
case DSN_ARG:
strcpy((char*)driver, argv[argIndex]);
break;
}
}
}
else {
printf(USAGE_MSG1, argv[0]);
return(1);
}

if (strlen((char*)uid) == 0)
strcpy ((char*)uid, "odbc01");
if (strlen((char*)pwd) == 0)
strcpy ((char*)pwd, (char*)uid);

printf ("%s: will connect to data source '%s' as user '%s/%s'.\n",
argv[0], driver, uid, pwd);

#if !defined (__cplusplus) && defined (hppa)
/*
** C programs must call the HP C++ Object initializer function.
*/
_main ();
#endif
EnvInit (&henv, &hdbc);

#ifdef USE_CURSORS
/*
** Use cursor library
*/
rc = SQLSetConnectAttr (hdbc, SQL_ATTR_ODBC_CURSORS,
SQL_CUR_USE_ODBC,0);
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
printf ("Unable to use cursor library!\n");
ODBC_error (henv, hdbc, hstmt);
}
#endif

rc = ODBC_Connect (henv, hdbc, driver, uid, pwd);
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
exit(255); /* Exit with failure */

/*
** Allocate a HSTMT to communicate with ODBC DB Driver.
*/
rc = SQLAllocStmt (hdbc, &hstmt);
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
printf ("Unable to Allocate a HSTMT:\n");
ODBC_error (henv, hdbc, hstmt);
EnvClose (henv, hdbc);
exit (255);
}
/*
** Build the SQL statement
*/
strcpy ((char*)table, "pub.EMPLOYEE");
strcpy ((char*)sql, "SELECT EMPNUM, FIRSTNAME, LASTNAME, ");
strcat ((char*)sql, "STARTDATE, DEPTCODE FROM ");
strcat ((char*)sql, (char*)table);
/*
** Prepare our SQL Statement for Executions.
*/
rc = SQLPrepare (hstmt, sql, (SDWORD)strlen((char*)sql));
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
printf ("SQLPrepare has Failed. RC=%d\n", rc);
ODBC_error (henv, hdbc, hstmt);
EnvClose (henv, hdbc);
exit (255);
}
/*
** Bind variables to SQL Columns
*/
rc = SQLBindCol (hstmt, 1, SQL_C_CHAR,
&empnum,
(SDWORD)sizeof(empnum),
&val_empnum);
rc = SQLBindCol (hstmt, 2, SQL_C_CHAR,
&firstname,
(SDWORD)sizeof(firstname),
&val_firstname);
rc = SQLBindCol (hstmt, 3, SQL_C_CHAR,
&lastname,
(SDWORD)sizeof(lastname),
&val_lastname);
rc = SQLBindCol (hstmt, 4, SQL_C_CHAR,
&startdate,
(SDWORD)siz.eof(startdate),
&val_startdate);
rc = SQLBindCol (hstmt, 5, SQL_C_CHAR,
&deptcode,
(SDWORD)sizeof(deptcode),
&val_deptcode);
/*
** Execute Prepared SQL
*/
rc = SQLExecute (hstmt);
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
printf ("......SQLExcute has Failed. RC=%d\n", rc);
ODBC_error (henv, hdbc, hstmt);
EnvClose (henv, hdbc);
exit (255);
}
/*
** Fetch Data
*/
printf("\n");
printf("%-15s %-15s %-20s %-15s\n",
"First Name", "Last Name", "Hire Date", "Dept");
printf("%-15s %-15s %-20s %-15s\n",
"----------", "---------", "---------", "----");
for (;;) {
rc = SQLFetch (hstmt);
if (rc == SQL_NO_DATA_FOUND) {
printf ("SQLFetch returns: SQL_NO_DATA_FOUND\n");
break;
}
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
printf ("SQLFetch has Failed. RC=%d\n", rc);
ODBC_error (henv, hdbc, hstmt);
break;
}
printf("%-15s %-15s %-20s %-15s\n",
firstname, lastname, startdate, deptcode);
}
printf("\n");
/*
** Free Bind Buffers
*/
rc = SQLFreeStmt (hstmt, SQL_UNBIND);
EnvClose(henv, hdbc);
}


7. Create a Makefile under $DLC/odbc/demo with build rules for the previous program. Files should be compiled and linked with the following libraries: -lodbc -lodbcinst. For some versions of Progress 9 -lpgicuXX is required as well (XX depends on the Progress/OpenEdge version in use, check in $DLC/odbc/lib).
Here is an example Makefile on Linux:
all: demoodbc
demoodbc: demoodbc.c
gcc -o demoodbc -I../include demoodbc.c -L../lib -lodbc -lodbcinst -lpgicu19

For 32 bit AIX use this syntax for the third line: xlC_r -o demoodbc -I../include demoodbc.c -L../lib -lc_r -lC_r -lodbc

For 64 bit AIX use this syntax for the third line: xlC_r -DODBC64 -q64 -qlonglong -qlongdouble -qvftable -o demoodbc -I../include demoodbc.c -L../lib -lc_r -lC_r -lodbc

For HP-UX use the following syntax for the third line when linking library files:

aCC -o demoodbc -I../include demoodbc.c -L../lib -lodbc -lodbcinst -lpgicu19
Please refer to HP documentation for a complete explanation: http://docs.hp.com/en/8/commandsyntax.htm

Note: A tabbed space must be included before the line beginning with gcc (or xlC_r). Do not use the space bar.

8. Use the Makefile to compile the sample program, for example:
make all
The operation should create an executable named "demoodbc".
9. Launch demoodbc specifying the data source name (DSN) on the command line. For example:

demoodbc -uid sysprogress -pwd sysprogress mytest
The program should run without errors and demonstrates that the ODBC driver is in use and accessing the database..