library.cpp

用c的API编写的图书管理系统

	strcat(stmt_buf,bobooknum);
	strcat(stmt_buf,",");
	strcat(stmt_buf,boborrowernum);
	strcat(stmt_buf,",current_timestamp");

	strcat(stmt_buf,",dateadd(day,60,current_timestamp)) ");


	strcat(stmt_buf,"update bookinf set remain=remain-1 where booknum=\'");
	strcat(stmt_buf,bobooknum);
	strcat(stmt_buf,"\' update borrower set remain=remain-1 where borrowernum=\'");
	strcat(stmt_buf,boborrowernum);
	strcat(stmt_buf,"\' commit tran haode");

	r = SQLExecDirect( hstmt1, (SQLCHAR *)stmt_buf, strlen(stmt_buf) ); 
	if ( r != SQL_SUCCESS )
	{ 
	printf("SQLExecDirect error!\n\n" ) ; 
	return; 
	} 

	printf("borrow book successfully\n\n");
SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);
SQLDisconnect(hdbc1);


SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV,henv1);
return  ;
	
}

void returnbook()
{
	SQLHENV henv1;
	SQLHDBC hdbc1;
	SQLHSTMT hstmt1;
	char *databasename="lib";
	char *username="sa";
	char *userpswd="loveshe";
	SQLRETURN r;
	char stmt_buf[512];
	char rebooknum[8];
	char reborrowernum[8];
	char prestring[64]="delete from borrow where booknum=";

	r=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv1);
	if ( r != SQL_SUCCESS ) 
	{ 
		printf( "SQLAllocHandle error1!\n") ; 
		exit(1); 
	} 
	
	r = SQLSetEnvAttr ( henv1, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0 ); 
	if ( r != SQL_SUCCESS ) 
	{ 
	printf( "SQLSetEnvAttr error!" ); 
	exit(1); 
	} 

	r=SQLAllocHandle(SQL_HANDLE_DBC,henv1,&hdbc1);

	if ( r != SQL_SUCCESS ) { 
		printf("SQLSetEnvAttr error2!\n") ; 
		exit(1); }
	
	r=SQLConnect(hdbc1,	(SQLCHAR*)databasename,	SQL_NTS,(SQLCHAR*)username,SQL_NTS,(SQLCHAR*)userpswd,SQL_NTS);
	if(r!=SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO )
	{ 
	printf( "SQLAllocHandle4 error!\n") ;
	exit(1); 
	}


	r = SQLAllocHandle ( SQL_HANDLE_STMT, hdbc1, &hstmt1); 
	if ( r != SQL_SUCCESS ) 
	{ 

		printf("SQLAllocHandle3 error!\n"); 

		exit(1); 
	} 

//输入信息
	printf("put in the book number which you want to return\n");
	scanf("%s",rebooknum);
	printf("put in the borrower number of you\n");
	scanf("%s",reborrowernum);
	strcpy(stmt_buf,prestring);
	strcat(stmt_buf,rebooknum);
	strcat(stmt_buf," and borrowernum=");
	strcat(stmt_buf,reborrowernum);
	strcat(stmt_buf," update bookinf set remain=remain+1 where booknum=");
	strcat(stmt_buf,rebooknum);
	strcat(stmt_buf," update borrower set remain=remain+1 where borrowernum=");
	strcat(stmt_buf,reborrowernum);

	//执行sql

	r = SQLExecDirect( hstmt1, (SQLCHAR *)stmt_buf, strlen(stmt_buf) ); 
	if ( r != SQL_SUCCESS )
	{ 
	printf("SQLExecDirect error!\n\n" ) ; 
	return ; 
	} 
	printf("return successfully!\n\n");

SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);
SQLDisconnect(hdbc1);


SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV,henv1);
return  ;
}

void findbook()
{
	SQLHENV henv1;
	SQLHDBC hdbc1;
	SQLHSTMT hstmt1;
	char *databasename="lib";
	char *username="sa";
	char *userpswd="loveshe";
	SQLRETURN r;
	long c1;
	long c2;
	long c3;
	long c4;
	long c5;
	long c6;
	long c7;
	char stmt_buf[512];
	char fibookname[20];
	char booknum[20];
	char bookname[30];
	char author[20];
	char publish[30];
	float price;
	int amount;
	int remain;
	int j=0;
	char prestring[128]="select * from bookinf where bookname like \'%";

	r=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv1);
	if ( r != SQL_SUCCESS ) 
	{ 
		printf( "SQLAllocHandle error1!\n") ; 
		exit(1); 
	} 
	
	r = SQLSetEnvAttr ( henv1, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0 ); 
	if ( r != SQL_SUCCESS ) 
	{ 
	printf( "SQLSetEnvAttr error!" ); 
	exit(1); 
	} 

	r=SQLAllocHandle(SQL_HANDLE_DBC,henv1,&hdbc1);

	if ( r != SQL_SUCCESS ) { 
		printf("SQLSetEnvAttr error2!\n") ; 
		exit(1); }
	
	r=SQLConnect(hdbc1,	(SQLCHAR*)databasename,	SQL_NTS,(SQLCHAR*)username,SQL_NTS,(SQLCHAR*)userpswd,SQL_NTS);
	if(r!=SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO )
	{ 
	printf( "SQLAllocHandle4 error!\n") ;
	exit(1); 
	}


	r = SQLAllocHandle ( SQL_HANDLE_STMT, hdbc1, &hstmt1); 
	if ( r != SQL_SUCCESS ) 
	{ 

		printf("SQLAllocHandle3 error!\n"); 

		exit(1); 
	} 

//输入信息
	printf("put in the book name\n");
	scanf("%s",fibookname);
	strcpy(stmt_buf,prestring);
	strcat(stmt_buf,fibookname);
	strcat(stmt_buf,"%\'");
//执行sql

	r = SQLExecDirect( hstmt1, (SQLCHAR *)stmt_buf, strlen(stmt_buf) ); 
	if ( r != SQL_SUCCESS )
	{ 
	printf("SQLExecDirect error!\n" ) ; 
	return ; 
	} 
//获取图书信息
	SQLBindCol ( hstmt1,1, SQL_C_CHAR, (SQLPOINTER)booknum,20, &c1);
	SQLBindCol ( hstmt1,2, SQL_C_CHAR, (SQLPOINTER)bookname,30, &c2);
	SQLBindCol ( hstmt1,3, SQL_C_CHAR, (SQLPOINTER)author,20, &c3);
	SQLBindCol ( hstmt1,4, SQL_C_CHAR, (SQLPOINTER)publish,30, &c4);
	SQLBindCol ( hstmt1, 5, SQL_C_FLOAT, (SQLPOINTER)&price,0, &c5);
	SQLBindCol ( hstmt1, 6, SQL_C_LONG, (SQLPOINTER)&amount,0, &c6);
	SQLBindCol ( hstmt1, 7, SQL_C_LONG, (SQLPOINTER)&remain,0, &c7);

	while (1)
	{ 
		r = SQLFetch ( hstmt1 ); 
			 
		if ( r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO)
		{ 
			j=1;
			printf("book-number  book-name          author    publish          price  amount  remain");
			printf("\n\n%s         %s %s %s %.2f    %d      %d\n\n",booknum,bookname,author,publish,price,amount,remain );
		} 
		else
		{
			if(j==0)
			printf("there is no book with the name\n\n");
			break;
		}

	} 
SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);
SQLDisconnect(hdbc1);


SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV,henv1);
return  ;


}

void findborrowbook()
{
	SQLHENV henv1;
	SQLHDBC hdbc1;
	SQLHSTMT hstmt1;
	char *databasename="lib";
	char *username="sa";
	char *userpswd="loveshe";
	SQLRETURN r;
	long c1;
	long c2;
	long c3;
	char returndate[30];
	char stmt_buf[512];
	char fiborrownum[8];
	char bookname[30];
	char borrowername[30];
	char check[128];
	int j=0;
	char prestring[128]="select bookname,returndate from bookinf,borrow where bookinf.booknum=borrow.booknum and borrow.borrowernum=\'";



	r=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv1);
	if ( r != SQL_SUCCESS ) 
	{ 
		printf( "SQLAllocHandle error1!\n") ; 
		exit(1); 
	} 
	
	r = SQLSetEnvAttr ( henv1, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0 ); 
	if ( r != SQL_SUCCESS ) 
	{ 
	printf( "SQLSetEnvAttr error!" ); 
	exit(1); 
	} 

	r=SQLAllocHandle(SQL_HANDLE_DBC,henv1,&hdbc1);

	if ( r != SQL_SUCCESS ) { 
		printf("SQLSetEnvAttr error2!\n") ; 
		exit(1); }
	
	r=SQLConnect(hdbc1,	(SQLCHAR*)databasename,	SQL_NTS,(SQLCHAR*)username,SQL_NTS,(SQLCHAR*)userpswd,SQL_NTS);
	if(r!=SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO )
	{ 
	printf( "SQLAllocHandle4 error!\n") ;
	exit(1); 
	}


	r = SQLAllocHandle ( SQL_HANDLE_STMT, hdbc1, &hstmt1); 
	if ( r != SQL_SUCCESS ) 
	{ 

		printf("SQLAllocHandle3 error!\n"); 

		exit(1); 
	} 


//输入讯息
	printf("put in your borrower-number\n");
	scanf("%s",fiborrownum);

	strcpy(check,"select borrowername from borrower where borrowernum=\'");
	strcat(check,fiborrownum);
	strcat(check,"\'");
//执行sql

	r = SQLExecDirect( hstmt1, (SQLCHAR *)check, strlen(check) ); 
	if ( r != SQL_SUCCESS )
	{ 
	printf("SQLExecDirect error!\n" ) ; 
	return ; 
	} 

//取出借书人名
	SQLBindCol ( hstmt1,1, SQL_C_CHAR, (SQLPOINTER)borrowername,30, &c2);

	r = SQLFetch ( hstmt1 ); 
		
	if ( r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO)
	{ }		
 //该借书号不存在则退出
	else
		{
			
			printf("there is no the borrower \n\n");
			return;
		}

	




//否则继续


	r=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv1);
	if ( r != SQL_SUCCESS ) 
	{ 
		printf( "SQLAllocHandle error1!\n") ; 
		exit(1); 
	} 
	
	r = SQLSetEnvAttr ( henv1, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0 ); 
	if ( r != SQL_SUCCESS ) 
	{ 
	printf( "SQLSetEnvAttr error!" ); 
	exit(1); 
	} 

	r=SQLAllocHandle(SQL_HANDLE_DBC,henv1,&hdbc1);

	if ( r != SQL_SUCCESS ) { 
		printf("SQLSetEnvAttr error2!\n") ; 
		exit(1); }
	
	r=SQLConnect(hdbc1,	(SQLCHAR*)databasename,	SQL_NTS,(SQLCHAR*)username,SQL_NTS,(SQLCHAR*)userpswd,SQL_NTS);
	if(r!=SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO )
	{ 
	printf( "SQLAllocHandle4 error!\n") ;
	exit(1); 
	}


	r = SQLAllocHandle ( SQL_HANDLE_STMT, hdbc1, &hstmt1); 
	if ( r != SQL_SUCCESS ) 
	{ 

		printf("SQLAllocHandle3 error!\n"); 

		exit(1); 
	} 

	strcpy(stmt_buf,prestring);
	strcat(stmt_buf,fiborrownum);
	strcat(stmt_buf,"\'");
//执行sql

	r = SQLExecDirect( hstmt1, (SQLCHAR *)stmt_buf, strlen(stmt_buf) ); 
	if ( r != SQL_SUCCESS )
	{ 
	printf("SQLExecDirect error!\n" ) ; 
	return ; 
	} 
//获取信息	
	SQLBindCol ( hstmt1,1, SQL_C_CHAR, (SQLPOINTER)bookname,30, &c1);
	SQLBindCol ( hstmt1,2, SQL_C_CHAR, (SQLPOINTER)returndate,30, &c3);
	while (1)
	{ 
		r = SQLFetch ( hstmt1 ); 
		
		if ( r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO)
		{ 
			
			if(j==0)
				printf("book-name              return-date\n\n");
			j=1;

			printf("%s      %s\n\n",bookname,returndate);
		} 
		else
		{
			if(j==0)
			printf("there is no book \n\n");
				break;
		}

	} 
SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);
SQLDisconnect(hdbc1);


SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV,henv1);
return  ;

}

void addborrower()
{
	SQLHENV henv1;
	SQLHDBC hdbc1;
	SQLHSTMT hstmt1;
	SQLRETURN r;
	char *databasename="lib";
	char *username="sa";
	char *userpswd="loveshe";

	char stmt_buf[512];
	char borrowernum[20];
	char borrowername[20];
	char type[20];
	char getamount[256];
	long tremain;
	long c;

	char remain[5];
	char prestring[128]="insert into borrower(borrowernum,borrowername,type,registerdate,validdate,remain)values( \'";
//输入信息
	printf("put in borrower-number\n");
		scanf("%s",borrowernum);
	printf("put in borrower-name\n");
		scanf("%s",borrowername);
	printf("put in type\n");
		scanf("%s",type);



	r=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv1);
	if ( r != SQL_SUCCESS ) 
	{ 
		printf( "SQLAllocHandle error1!\n") ; 
		exit(1); 
	} 
	
	r = SQLSetEnvAttr ( henv1, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0 ); 
	if ( r != SQL_SUCCESS ) 
	{ 
	printf( "SQLSetEnvAttr error!" ); 
	exit(1); 
	} 

	r=SQLAllocHandle(SQL_HANDLE_DBC,henv1,&hdbc1);

	if ( r != SQL_SUCCESS ) { 
		printf("SQLSetEnvAttr error2!\n") ; 
		exit(1); }
	
	r=SQLConnect(hdbc1,	(SQLCHAR*)databasename,	SQL_NTS,(SQLCHAR*)username,SQL_NTS,(SQLCHAR*)userpswd,SQL_NTS);
	if(r!=SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO )
	{ 
	printf( "SQLAllocHandle4 error!\n") ;
	exit(1); 
	}


	r = SQLAllocHandle ( SQL_HANDLE_STMT, hdbc1, &hstmt1); 
	if ( r != SQL_SUCCESS ) 
	{ 

		printf("SQLAllocHandle3 error!\n"); 

		exit(1); 
	} 


	strcpy(getamount,"select borrowamount from type where type=\'");
	strcat(getamount,type);
	strcat(getamount,"\'");
//执行sql

	r = SQLExecDirect( hstmt1, (SQLCHAR *)getamount, strlen(getamount) ); 
	if ( r != SQL_SUCCESS )
	{ 
	printf("SQLExecDirect error!\n\n" ) ; 
	return; 
	} 
//取出type中借书量	
	SQLBindCol ( hstmt1,1, SQL_C_LONG, (SQLPOINTER)&tremain,0, &c);
	r = SQLFetch ( hstmt1 ); 
	if ( r == SQL_ERROR || r == SQL_SUCCESS_WITH_INFO) 
	{ 
		printf("FETCH ERROE!\n" ); 
		 
		} 
	printf("%d\n",tremain);
	itoa(tremain,remain,10);


	r=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv1);
	if ( r != SQL_SUCCESS ) 
	{ 
		printf( "SQLAllocHandle error1!\n") ; 
		exit(1); 
	} 
	
	r = SQLSetEnvAttr ( henv1, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0 ); 
	if ( r != SQL_SUCCESS ) 
	{ 
	printf( "SQLSetEnvAttr error!" ); 
	exit(1); 
	} 

	r=SQLAllocHandle(SQL_HANDLE_DBC,henv1,&hdbc1);

	if ( r != SQL_SUCCESS ) { 
		printf("SQLSetEnvAttr error2!\n") ; 
		exit(1); }
	
	r=SQLConnect(hdbc1,	(SQLCHAR*)databasename,	SQL_NTS,(SQLCHAR*)username,SQL_NTS,(SQLCHAR*)userpswd,SQL_NTS);
	if(r!=SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO )
	{ 
	printf( "SQLAllocHandle4 error!\n") ;
	exit(1); 
	}


	r = SQLAllocHandle ( SQL_HANDLE_STMT, hdbc1, &hstmt1); 
	if ( r != SQL_SUCCESS ) 
	{ 

		printf("SQLAllocHandle3 error!\n"); 

		exit(1); 
	} 





	strcpy(stmt_buf,prestring);
	strcat(stmt_buf,borrowernum);
	strcat(stmt_buf,"\',\'");
	strcat(stmt_buf,borrowername);
	strcat(stmt_buf,"\',\'");
	strcat(stmt_buf,type);
	strcat(stmt_buf,"\',");
	strcat(stmt_buf,"current_timestamp");
	strcat(stmt_buf,",");
	strcat(stmt_buf,"dateadd(year,4,current_timestamp),");
	strcat(stmt_buf,remain);
	strcat(stmt_buf,")");

	//execute sql
	r = SQLExecDirect( hstmt1, (SQLCHAR *)stmt_buf, strlen(stmt_buf) ); 
	if ( r != SQL_SUCCESS ) { 
	printf("SQLExecDirect error!\n\n" ) ; 
	return; 
	} 
	printf("insert successfully!\n\n");
	SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);
	SQLDisconnect(hdbc1);
	SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);
	SQLFreeHandle(SQL_HANDLE_ENV,henv1);
return  ;

}