Re: Need ODBC Example Code

From: rockcogar <rock_cogarNOroSPAM_at_my-deja.com.invalid>
Date: Wed, 02 Feb 2000 13:19:01 -0800
Message-ID: <071dc2c4.ba060b07@usw-ex0103-019.remarq.com>

Well, I eventually solved this one on my own. What follows is a simple C version of an ODBC program to query all rows from a table called emp that is present in most Oracle 7.x and 8.x databases. I expect that it should also compile under LCC.

Rock.

// --------------------------------------------------------------
----------------------
// Program:      odbc
// Purpose:      C program to demo ODBC on an Oracle 8 database
table
// by:           Rock Cogar, Radian International LLC, OR TN
// date:         January 27, 2000
// Compiler:     Microsoft Visual C++ 5
// Environment:  Windows NT 4.0 on Pentium III
// Style:        Win32 Console application
// DBMS:         Assumes Schema SCOTT (password ="tiger") is
present with table emp
// ODBC:         Assumes ODBC alias called "oracle" points to

the oracle DBMS

// --------------------------------------------------------------
----------------------



#include <windows.h>


#include <stdio.h>


#include <stdlib.h>


#include <dos.h>


#include <errno.h>


#include <string.h>


#include <direct.h>


#include <conio.h>


#include <io.h>

#include <sql.h>
#include <SQLEXT.H>

#define SZLEN 16
#define SZDATELEN 24

void process(void);
void dbError( LPSTR lp, HENV henv,HDBC hdbc,HSTMT hstmt);

// --------------------------------------------------------------
----------------------------------------------

void main(void)
{
process();
}

// --------------------------------------------------------------
----------------------------------------------

// --------------------------------------------------------------
----------------------------------------------

void process(void)
{
SDWORD cbempno;
SDWORD cbename;
SDWORD cbjob;
SDWORD cbmgr;
SDWORD cbhiredate;
SDWORD cbsal;
SDWORD cbcomm;
SDWORD cbdeptno;
double dblempno;
double dblmgr;
double dblsal;
double dblcomm;
double dbldeptno;
char szename[SZLEN+1];
char szjob[SZLEN+1];
char szhiredate[SZDATELEN+1];
HSTMT hstmt;
SQLRETURN retcode;

HENV       henv;
HDBC       hdbc;
char       szSql[256];
char       szout[256];

TIMESTAMP_STRUCT ts;

retcode = SQLAllocEnv(&henv); /* Environment handle */

if (retcode != SQL_SUCCESS)

	{
	dbError( "SQLAllocEnv()",henv,hdbc,hstmt);
	return;
	}

retcode = SQLAllocConnect(henv, &hdbc); /* Connection handle */

if (retcode != SQL_SUCCESS)

	{
	dbError( "SQLAllocConnect()",henv,hdbc,hstmt);
	SQLFreeEnv(henv);
	return;
	}

SQLSetConnectOption(hdbc, SQL_LOGIN_TIMEOUT, 15); /* Set login timeout to 15 seconds. */
retcode = SQLConnect(hdbc, "oracle", SQL_NTS, "scott", SQL_NTS, "tiger", SQL_NTS); /* Connect to data source */

if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)

	{
	dbError( "SQLConnect()",henv,hdbc,hstmt);
	SQLFreeEnv(henv);
	return;
	}

retcode = SQLAllocStmt(hdbc, &hstmt); /* Statement handle */

if (retcode != SQL_SUCCESS)

	{
	dbError( "SQLAllocStmt()",henv,hdbc,hstmt);
	SQLFreeEnv(henv);
	return;
	}

lstrcpy( szSql,"select empno,ename,job,mgr,hiredate,sal,nvl (comm,0),deptno from emp order by empno asc");

retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);

if (retcode != SQL_SUCCESS)

	{
	dbError( " SQLExecDirect()",henv,hdbc,hstmt);
	}

if (retcode == SQL_SUCCESS)
	{

	while (TRUE)
		{
		retcode = SQLFetch(hstmt);

		if (retcode == SQL_ERROR || retcode ==
SQL_SUCCESS_WITH_INFO)
			{
			dbError( "SQLFetch()",henv,hdbc,hstmt);
			}

		if (retcode == SQL_SUCCESS || retcode ==
SQL_SUCCESS_WITH_INFO)
			{
			SQLGetData(hstmt, 1,SQL_C_DOUBLE,
&dblempno,    0,     &cbempno);
			SQLGetData(hstmt, 2,SQL_C_CHAR,
szename,      SZLEN, &cbename);
			SQLGetData(hstmt, 3,SQL_C_CHAR,
szjob,        SZLEN, &cbjob);
			SQLGetData(hstmt, 4,SQL_C_DOUBLE,
&dblmgr,      0,     &cbmgr);
			SQLGetData(hstmt, 5,SQL_C_DATE,   &ts,
16, &cbhiredate);
			SQLGetData(hstmt, 6,SQL_C_DOUBLE,
&dblsal,      0,     &cbsal);
			SQLGetData(hstmt, 7,SQL_C_DOUBLE,
&dblcomm,     0,     &cbcomm);
			SQLGetData(hstmt, 8,SQL_C_DOUBLE,
&dbldeptno,   0,     &cbdeptno);
			sprintf( szhiredate,"%d-%d-%
d",ts.month,ts.day,ts.year);
			sprintf(szout,"%6.0f\t%s\t%s\t%6.0f\t%

s\t%6.0f\t%6.0f\t%
6.0f\n",dblempno,szename,szjob,dblmgr,szhiredate,dblsal,dblcomm,d bldeptno);

			puts(szout);
			}
		else
			{
			break;
			}
		}
	}

SQLFreeStmt(hstmt, SQL_DROP);
SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
}

// --------------------------------------------------------------
----------------------------------------------

// --------------------------------------------------------------
----------------------------------------------

void dbError( LPSTR lp, HENV henv,HDBC hdbc,HSTMT hstmt) {
unsigned char buf[250];
unsigned char sqlstate[15];

SQLError( henv, hdbc, hstmt, sqlstate, NULL,buf, sizeof(buf), NULL);
fprintf(stderr, "%s. %s, SQLSTATE=%s\n",lp, buf, sqlstate); }

// --------------------------------------------------------------
-----------------------------------------------

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Wed Feb 02 2000 - 15:19:01 CST