Consultor Eletrônico

Status: Verified

GOAL:

How to install and configure the ODBC driver on UNIX

GOAL:

How do I set up ODBC on UNIX?

GOAL:

How do I configure ODBC on UNIX?

FACT(s) (Environment):

Progress 9.1x
UNIX

FIX:

On UNIX, the Progress SQL-92 ODBC Driver is a series shared libraries supplied by MERANT . In order to configure the ODBC Driver on a UNIX system, the Progress SQL-92 ODBC Driver libraries and executable files must have been installed, as described in the Progress Installation and Configuration Guide Version 9 for UNIX.

To setup and test the driver configuration perform the following:

1. Start the database with the -S parameter. Pass the -S parameter the port number the database will listening on.

2. In the UNIX environment, there is no ODBC Administrator. To configure a data source 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.

3. Create an environment variable called ODBCINI that points to the odbc.ini file. Example: ODBCINI=$DLC/odbc/odbc.ini; export ODBCINI

4. Create an odbc.ini file with the following information under $DLC/odbc. Do not use environment variables in either the Makefile or the odbc.ini file.


[ODBC Data Sources]
mytest=Progress_SQL92_Driver #Mytest is the Data Source Name (DSN) and can be any name choosen. Each DSN should be unique.

[mytest]
Driver=Progress install directory/odbc/lib/pgpro915.so
DatabaseName=odbctest # Name of the database. The database must be started in multi-user mode with -S and -H (see the db log file to confirm parameters are set)
PortNumber=7077
HostName=mr-sniffles # The -H value is the hostname the database was started on.
LogonID=userid #A database account if security is enabled. The default SQL dba is the user who created the database. A Logon account with a blank password blank can be used.
Password=password
APILevel=1
ConnectFunctions=YYN
CPTimeout=60
DriverODBCVer=03.60
FileUsage=0
SQLLevel=0
UsageCount=1
ArraySize=50
DefaultLongDataBuffLen=2048
DefaultIsolationLevel=REPEATABLE READ #Check Progress Documentation to change options such as DefaultIsolationLevel. The defaults should work okay in most cases.
LogonID=userid
StaticCursorLongColBuffLen=4096

[ODBC]
InstallDir=Progress install directory/odbc
Trace=1
TraceFile=/tmp/odbctrace.out
TraceDll=Progress install directory/odbc/lib/odbctrac.so
UseCursorLib=0


5. Add the $DLC/odbc/lib and the $DLC/lib directory to the operating systems library search path environment variable. Setting the library search path environment variable enables the ODBC application to locate the ODBC core components and drivers at the time of execution. The environment variable set depends on the flavor of UNIX being used.


For Solaris set $LD_LIBRARY_PATH environment variable
For HP-UX set $SHLIB_PATH environment variable
For AIX set $LIBPATH environment variable.
For other platforms use LD_LIBRARY_PATH. Example: LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DLC/lib:$DLC/odbc/lib; export LD_LIBRARY_PATH

6. Create a demo directory under $DLC/odbc and create a demoodbc.c file in it from the information below. If the ODBC directory already exists, use the demoodbc.c file in the existing demo directory.


/*
** 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 <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 EM.PNUM, 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)sizeof(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 the following contents.


all: demoodbc
demoodbc: demoodbc.c
cc -o demoodbc -I../include demoodbc.c -L../lib -lodbc -lodbcinst -lpgicu19
NOTE: A tabbed space must be included before the line beginning with cc. Do not use the space bar.
NOTE: For Linux, use gcc instead of cc


8.Use the Makefile to compile the demoodbc.c and generate the object file


Example: make all

9.Launch the demoodbc file specifying the DSN in the odbc.ini file.


Example: demoodbc -uid sysprogress -pwd sysprogress mytest
.