cockji.cpp

嵌入式ODBC和SQL应用 开发环境,VC++6.0

#include <stdio.h>
#include <iostream.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>

#define MAXBUFLEN   255	
#define MaxNameLen  20
//#define SQLGETDATA

#define SQLBINDCOL

SQLHENV      henv = SQL_NULL_HENV;
SQLHDBC      hdbc1 = SQL_NULL_HDBC;     
SQLHSTMT      hstmt1 = SQL_NULL_HSTMT;

int main() 
{
	
	RETCODE retcode;
	
    // Allocate the ODBC Environment and save handle.
	retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);	
	if(retcode < 0 )
	{
		cout<<"allocate ODBC Environment handle errors."<<endl;
		printf("%d\n",retcode);
		return -1;
	}

	// Notify ODBC that this is an ODBC 3.0 application.
	retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
		(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
	if(retcode < 0 )
	{
		cout<<"the  ODBC is not version3.0 "<<endl;
		printf("%d\n",retcode);
		return -1;
	}
	
	// Allocate an ODBC connection and connect.
	retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
	if(retcode < 0 )
	{
		cout<<"allocate ODBC connection handle errors."<<endl;
		printf("%d\n",retcode);
		return -1;
	}
	

	//Data Source Name must be of type User DSN or System DSN
	char* szDSN = "coco";
	char* szUID = "coco";//log name
	char* szAuthStr = "coco";//passward

	//connect to the Data Source
	retcode = SQLConnect(hdbc1, (SQLCHAR*)szDSN, (SWORD)strlen(szDSN),(SQLCHAR*)szUID, 
				(SWORD)strlen(szUID),(SQLCHAR*)szAuthStr, (SWORD)strlen(szAuthStr));
	if(retcode < 0 )
	{		
		cout<<"connect to  ODBC datasource errors."<<endl;
		printf("%d\n",retcode);	
		return -1;
	}

	// Allocate a statement handle.
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
	if(retcode < 0 )
	{
		cout<<"allocate ODBC statement handle errors."<<endl;
		printf("%d\n",retcode);
		return -1;
	}
    retcode = SQLExecDirect(hstmt1,(SQLCHAR*)"if exists (select name from sysobjects where name='provider') drop table coco.provider", SQL_NTS);
	if(retcode < 0 )
	{
		cout<<"Drop table failed.\n"<<endl;

		printf("%d\n",retcode);
		return -1;
	}
	retcode = SQLExecDirect(hstmt1,(SQLCHAR*)"CREATE TABLE provider (SNO char(5)   primary key,SNAME char(10)  not null,STATUS  int null,CITY  char(10)	null)", SQL_NTS);
	if(retcode < 0 )
	{
		cout<<"Create table failed.\n"<<endl;

		printf("%d\n",retcode);
		return -1;
	}
	retcode = SQLExecDirect(hstmt1,(SQLCHAR*)"INSERT INTO provider values ('S1','精益','20','天津') ", SQL_NTS);
	if(retcode < 0 )
	{
		cout<<"insert failed.\n"<<endl;
		printf("%d\n",retcode);
		return -1;
	}
	retcode = SQLExecDirect(hstmt1,(SQLCHAR*)"insert into provider values ('S2','胜锡','10','北京') ", SQL_NTS);
	if(retcode < 0 )
	{
		cout<<"insert failed.\n"<<endl;
		printf("%d\n",retcode);
		return -1;
	}
	retcode = SQLExecDirect(hstmt1,(SQLCHAR*)"insert into provider values ('S3','东方红','30','天津') ", SQL_NTS);
	if(retcode < 0 )
	{
		cout<<"insert failed.\n"<<endl;
		printf("%d\n",retcode);
		return -1;
	}
	retcode = SQLExecDirect(hstmt1,(SQLCHAR*)"insert into provider values ('S4','丰泰盛','20','天津')", SQL_NTS);
	if(retcode < 0 )
	{
		cout<<"insert failed.\n"<<endl;
		printf("%d\n",retcode);
		return -1;
	}
	retcode = SQLExecDirect(hstmt1,(SQLCHAR*)"insert into provider values ('S5','为民','30','上海' ) ", SQL_NTS);
	if(retcode < 0 )
	{
		cout<<"insert failed.\n"<<endl;
		printf("%d\n",retcode);
		return -1;
	}
	retcode = SQLExecDirect(hstmt1,(SQLCHAR*)"insert into provider values ('S6','通天','25',NULL) ", SQL_NTS);
	if(retcode < 0 )
	{
		cout<<"insert failed.\n"<<endl;
		printf("%d\n",retcode);
		return -1;
	}


	// Execute an SQL statement directly on the statement handle.
	retcode = SQLExecDirect(hstmt1,(SQLCHAR*)"SElECT sname,city  FROM provider ", SQL_NTS);
	if(retcode < 0 )
	{
		cout<<"Executing statement  throught ODBC  errors.\n"<<endl;
		printf("%d\n",retcode);
		return -1;
	}

	/*
	** 处理查询结果
	** SQL_NO_DATA指示到达结果缓冲末尾

	** 可以通过SQLBindCol把某个结果属性列绑定到变量上
	   --这种方式，每次只能绑定一个属性列；只须绑定一次，就可以多次使用；
	   --绑定之后，通过SQLFetch来缓冲区中current tuple对应的值拷贝到变量中；
	     同时，SQLFectch把下一个元组置为current tuple

	** 也可以通过SQLGetData来读取数据
	*/
	
	// SQLBindCol variables
	SQLCHAR      city[MaxNameLen + 1];	
	SQLCHAR		 name[MaxNameLen + 1];
	SQLINTEGER   columnLen = 0;//数据库定义中该属性列的长度

//通过宏SQLBINDCOL来选择用SQLBindCol还是SQLGetData
#ifdef SQLBINDCOL
	retcode = SQLBindCol(hstmt1, 1, SQL_C_CHAR,name,MaxNameLen , &columnLen);
	retcode = SQLBindCol(hstmt1, 2, SQL_C_CHAR, city, MaxNameLen, &columnLen);

	/*printf("************************************************************\n");
	while ( (retcode = SQLFetch(hstmt1) ) != SQL_NO_DATA ) 
	{ if(columnLen<0)		
		printf("              name : %s        city: null\n", name);
	 else printf("              name : %s        city: %s\n", name,city);
	}
	printf("************************************************************\n");*/
	//retcode=SQLSetPos(hstmt1,1,SQL_POSITION,SQL_LOCK_NO_CHANGE);

	printf("************************************************************\n");
	while ( (	retcode = SQLExtendedFetch(hstmt1,SQL_FETCH_ABSOLUTE,0,NULL,NULL) ) != SQL_NO_DATA ) 
	{ if(columnLen<0)		
		printf("              name : %s        city: null\n", name);
	 else printf("              name : %s        city: %s\n", name,city);
	}
	printf("************************************************************\n");
/*#else
	
	while(1)
	{
		retcode = SQLFetch(hstmt1);
		if(retcode == SQL_NO_DATA)
			break;
		
		retcode = SQLGetData(hstmt1, 2, SQL_C_CHAR, city, MaxNameLen, &columnLen);	

		printf("city = %s\n", city);*/
		

#endif

	/* Clean up.*/
	SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
	SQLDisconnect(hdbc1);
	SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
	SQLFreeHandle(SQL_HANDLE_ENV, henv);
	
	return(0);
}