database.cpp

将SQL Server备份到文本文件以及还原到数据库。

#include <windows.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <share.h>
#include <math.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>

SQLHENV  henv1=SQL_NULL_HENV;
SQLHENV  henv2=SQL_NULL_HENV;
SQLHDBC  hdbc1=SQL_NULL_HDBC;
SQLHDBC  hdbc2=SQL_NULL_HDBC;
SQLHSTMT hstmt1=SQL_NULL_HSTMT;
SQLHSTMT hstmt2=SQL_NULL_HSTMT;

int connect_db();
int disconnect_db();
int test_odbcapi();
int recover();
void ErrorProcess(SQLSMALLINT,SQLHANDLE);


char *DataSource="lqh";
//char *DataSource="LocalServer";
char *UserName="sa";
char *Password="]]]]]]]";
char *dbname="haha";

char *FileName="odbcapi.txt";

union  {
  SQLCHAR szBuf[513];
  SQLINTEGER dwBuf;
  SQLSMALLINT wBuf;
  SQLFLOAT fBuf;
  SQLDOUBLE dBuf;
//  SQLTINYINT bitBuf;
}all_type[255];

char szData[1024*64];


int main() 
{

	 if(connect_db()!=0) 
	 {
	   printf("connect_db error!\n");
	   return(-1);
	 }

	 if(recover()!=0) 
	
	//if(test_odbcapi()!=0) 
	 {
		 printf("Test ODBCAPI error\n");
		 return (-1);
	 }

	 disconnect_db();
	 return(0);
}

int test_odbcapi()
{
 SQLRETURN sqlreturn;
 SQLCHAR table_cat[81],table_schem[81],table_name[80],table_type[81];
 SQLCHAR column_name[81],type_name[81],szType[80][81],szName[80][81];
 SQLINTEGER column_size,dwDBbuf[80],dwSize[80];
 SQLSMALLINT data_type,smType[80];
 char szText[2048];
 SQLCHAR szSelect[2048];
 FILE *fp1;
 int i,n,size_flag;
 strcpy(szText,"");
 while((fp1=_fsopen(FileName,"w+",_SH_DENYRW))==NULL) {
   printf("Open Text File Error\n");
   return(-1);
 }
 sqlreturn=SQLAllocHandle(SQL_HANDLE_STMT,hdbc1,&hstmt1);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLAllocHandle(hstmt1) error!\n");
   return(-1);
 }
 sqlreturn=SQLAllocHandle(SQL_HANDLE_STMT,hdbc2,&hstmt2);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLAllocHandle(hstmt2) error!\n");
   return(-1);
 }

 sqlreturn=SQLTables(hstmt1,(unsigned char *)dbname,SQL_NTS,NULL,SQL_NTS,NULL,SQL_NTS,(unsigned char *)"TABLE",SQL_NTS);
 	
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLTables error!\n");
   ErrorProcess(SQL_HANDLE_STMT,hstmt1); 
   return(-1);
 }

 SQLBindCol(hstmt1,1,SQL_C_CHAR,table_cat,80,&dwDBbuf[0]);
 SQLBindCol(hstmt1,2,SQL_C_CHAR,table_schem,80,&dwDBbuf[1]);
 SQLBindCol(hstmt1,3,SQL_C_CHAR,table_name,80,&dwDBbuf[2]);
 SQLBindCol(hstmt1,4,SQL_C_CHAR,table_type,80,&dwDBbuf[3]);
 while((sqlreturn=SQLFetch(hstmt1))==SQL_SUCCESS||sqlreturn==SQL_SUCCESS_WITH_INFO) {
   sprintf(szText,"%s %s %s %s\n",table_type,table_name,table_cat,table_schem);
   fputs(szText,fp1);
   sqlreturn=SQLColumns(hstmt2,(unsigned char *)dbname,SQL_NTS,table_schem,SQL_NTS,table_name,SQL_NTS,NULL,SQL_NTS);
   if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) 
   {
     printf("SQLColumns error! table_cat=%s,table_schem=%s,table_name=%s\n",table_cat,table_schem,table_name);
     ErrorProcess(SQL_HANDLE_STMT,hstmt2); 
     continue;
   }
   SQLBindCol(hstmt2,4,SQL_C_CHAR,column_name,80,&dwDBbuf[4]);
   SQLBindCol(hstmt2,5,SQL_C_SHORT,&data_type,2,&dwDBbuf[5]);
   SQLBindCol(hstmt2,6,SQL_C_CHAR,type_name,80,&dwDBbuf[6]);
   SQLBindCol(hstmt2,8,SQL_C_LONG,&column_size,4,&dwDBbuf[7]);
   n=0;size_flag=0;
   
   while((sqlreturn=SQLFetch(hstmt2))==SQL_SUCCESS||sqlreturn==SQL_SUCCESS_WITH_INFO)
   {
     sprintf(szText,"<> %s %s %d %d\n",column_name,type_name,column_size,data_type);
     fputs(szText,fp1);
	 strcpy((char *)szName[n],(const char *)column_name);
	 smType[n]=data_type;
	 strcpy((char *)szType[n],(const char *)type_name);
	 dwSize[n]=column_size;
     if(column_size>512) size_flag++;
	 n++;
	 if(n>254) break;
   }
   SQLFreeStmt(hstmt2,SQL_UNBIND);
   SQLCloseCursor(hstmt2);
   strcpy(szText,"");
   for(i=0;i<n;i++) {
	 strcat(szText,(const char *)szName[i]);
	 if(i<n-1) strcat(szText,",");
   }
   if(strlen(szText)==0) continue;
  sprintf((char *)szSelect,"select %s from %s.%s",szText,table_schem,table_name);
    // sprintf((char *)szSelect,"select %s from %s",szText,table_name);
   sprintf((char *)szText,"%s\n",szSelect);
   if(size_flag!=0) continue;
   SQLSetStmtAttr(hstmt2,SQL_ATTR_CURSOR_TYPE,SQL_CURSOR_FORWARD_ONLY,0);
 
   sqlreturn=SQLExecDirect(hstmt2,szSelect,SQL_NTS);
   if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
     printf("SQLExecDirect 1 error!\n stmt=%s\n",szSelect);
     ErrorProcess(SQL_HANDLE_STMT,hstmt2); 
     SQLCloseCursor(hstmt2);
     continue;
   }
   for(i=0;i<n;i++) 
   {
	 switch(smType[i])
	 {
	   case SQL_CHAR: case SQL_VARCHAR: case SQL_DATETIME: case SQL_DECIMAL:
         SQLBindCol(hstmt2,(unsigned short)i+1,SQL_C_CHAR,all_type[i].szBuf,255,&dwDBbuf[i]);
		 break;
	   case SQL_INTEGER:
         SQLBindCol(hstmt2,(unsigned short)i+1,SQL_C_LONG,&all_type[i].dwBuf,4,&dwDBbuf[i]);
		 break;
	   case SQL_SMALLINT:
         SQLBindCol(hstmt2,(unsigned short)i+1,SQL_C_SHORT,&all_type[i].wBuf,2,&dwDBbuf[i]);
		 break;
	   case SQL_FLOAT:
         SQLBindCol(hstmt2,(unsigned short)i+1,SQL_C_DOUBLE,&all_type[i].fBuf,8,&dwDBbuf[i]);
		 break;
	   case SQL_REAL: case SQL_DOUBLE:
         SQLBindCol(hstmt2,(unsigned short)i+1,SQL_C_FLOAT,&all_type[i].szBuf,8,&dwDBbuf[i]);
		 break;
	   default:
		 break;
	 }
   }
   strcpy(szData,"// ");
   while((sqlreturn=SQLFetch(hstmt2))==SQL_SUCCESS||sqlreturn==SQL_SUCCESS_WITH_INFO)
   {
	 for(i=0;i<n-1;i++)
	 {
	   switch(smType[i]) 
	   {
	   case SQL_CHAR: case SQL_VARCHAR: case SQL_DATETIME: case SQL_DECIMAL:
		   sprintf(szText,"\'%s\',",all_type[i].szBuf);
		   strcat(szData,szText);
		 break;
	   case SQL_INTEGER:
		   sprintf(szText,"\'%ld\',",all_type[i].dwBuf);
		   strcat(szData,szText);
		 break;
	   case SQL_SMALLINT:
		   sprintf(szText,"\'%d\',",all_type[i].wBuf);
		   strcat(szData,szText);
		 break;
	   case SQL_FLOAT:
		   sprintf(szText,"\'%f\',",all_type[i].fBuf);
		   strcat(szData,szText);
		 break;
	   case SQL_REAL:case SQL_DOUBLE:
		   sprintf(szText,"\'%lf\',",all_type[i].dBuf);
		   strcat(szData,szText);
		 break;
	   default:
		 sprintf(szText,"\'%d\',",all_type[i].szBuf);
		 strcat(szData,szText);
		 break;
	   }
	 }
	 switch(smType[n-1]) 
	   {
	   case SQL_CHAR: case SQL_VARCHAR: case SQL_DATETIME: case SQL_DECIMAL:
		   sprintf(szText,"\'%s\'",all_type[i].szBuf);
		   strcat(szData,szText);
		 break;
	   case SQL_INTEGER:
		   sprintf(szText,"\'%ld\'",all_type[i].dwBuf);
		   strcat(szData,szText);
		 break;
	   case SQL_SMALLINT:
		   sprintf(szText,"\'%d\'",all_type[i].wBuf);
		   strcat(szData,szText);
		 break;
	   case SQL_FLOAT:
		   sprintf(szText,"\'%f\'",all_type[i].fBuf);
		   strcat(szData,szText);
		 break;
	   case SQL_REAL:case SQL_DOUBLE:
		   sprintf(szText,"\'%lf\'",all_type[i].dBuf);
		   strcat(szData,szText);
		 break;
	   default:
		   sprintf(szText,"\'%d\'",all_type[i].wBuf);
		   strcat(szData,szText);
		 break;
	   }
	 strcat(szData,"  end\n");
	 printf("%s\n",szData);
     fputs(szData,fp1);
	 strcpy(szData,"// ");
   }
   SQLFreeStmt(hstmt2,SQL_UNBIND);
   SQLCloseCursor(hstmt2);
 }
 SQLFreeHandle(SQL_HANDLE_STMT,hstmt2);
 SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);
 fclose(fp1);
 return(0);
}

int connect_db()
{
 SQLRETURN sqlreturn;
 sqlreturn=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv1);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLAllocHandle(henv) error!\n");
   return(-1);
 }
 sqlreturn=SQLSetEnvAttr(henv1,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLSetEnvAttr error!\n");
   return(-1);
 }
 sqlreturn=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv2);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLAllocHandle(henv) error!\n");
   return(-1);
 }
 sqlreturn=SQLSetEnvAttr(henv2,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLSetEnvAttr error!\n");
   return(-1);
 }
 sqlreturn=SQLAllocHandle(SQL_HANDLE_DBC,henv1,&hdbc1);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLAllocHandle(hdbc1) error!\n");
   return(-1);
 }
 sqlreturn=SQLConnect(hdbc1,(SQLCHAR *)DataSource,SQL_NTS,(unsigned char *)UserName,SQL_NTS,(unsigned char *)Password,SQL_NTS);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLConnect 1 error!\n");
   return(-1);
 }
 sqlreturn=SQLAllocHandle(SQL_HANDLE_DBC,henv2,&hdbc2);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLAllocHandle(hdbc2) error!\n");
   return(-1);
 }
 sqlreturn=SQLConnect(hdbc2,(SQLCHAR *)DataSource,SQL_NTS,(unsigned char *)UserName,SQL_NTS,(unsigned char *)Password,SQL_NTS);
 if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) {
   printf("SQLConnect 2 error!\n");
   return(-1);
 }
 return(0);
}

int disconnect_db()
{
 SQLDisconnect(hdbc1);
 SQLDisconnect(hdbc2);
 SQLFreeHandle(SQL_HANDLE_DBC,hdbc1);
 SQLFreeHandle(SQL_HANDLE_DBC,hdbc2);
 SQLFreeHandle(SQL_HANDLE_ENV,henv1);
 SQLFreeHandle(SQL_HANDLE_ENV,henv2);
 return(0);
}

void ErrorProcess(SQLSMALLINT handle_type,SQLHANDLE handle) 
{
 SQLRETURN sqlreturn=SQL_SUCCESS;
 UCHAR szSqlState[6]="",szErrorMsg[1024]="";
 SDWORD pfNativeError=0L;
 SWORD pcbErrorMsg=0,i;
 SDWORD SS_MsgState=0,SS_Severity=0;
 SQLINTEGER RecordNum;
 
 SQLGetDiagField(handle_type,handle,0,SQL_DIAG_NUMBER,&RecordNum,SQL_IS_INTEGER,NULL);
 for(i=1;i<=RecordNum;i++) {
   if(SQLGetDiagRec(handle_type,handle,i,szSqlState,&pfNativeError,szErrorMsg,1023,&pcbErrorMsg)!=SQL_NO_DATA) {
     printf("ODBC Error Number = %s\n",szSqlState);
     printf("ODBC Locat Error Number = %d\n",pfNativeError);
     printf("ODBC Error Msg = %s\n",szErrorMsg);
   }
 }
}

int recover()
{
	SQLRETURN sqlreturn;
	char szText[2048],temp[400],table[20];
	SQLCHAR szUpdate[2048];
	FILE *fp,*fp1;
	strcpy(szText,"");
	fp = fopen("odbcapi.txt","r");
	
	if(fp == NULL)
	{
		printf("备份文件不存在！\n");
		return 0;
	}
	fp1= fopen("errbackup.txt","w");
	sqlreturn=SQLAllocHandle(SQL_HANDLE_STMT,hdbc1,&hstmt1);
	if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS)) 
	{
		printf("SQLAllocHandle(hstmt1) error!\n");
		return (-1);
	}
	SQLSetStmtAttr(hstmt1,SQL_ATTR_CURSOR_TYPE,SQL_CURSOR_FORWARD_ONLY,0);
	fscanf(fp,"%s",temp);
	while(!feof(fp))
	{
		
		if(strcmp(temp,"TABLE")==0)
		{
		//	SQLFreeStmt(hstmt1,SQL_UNBIND);
		//	SQLCloseCursor(hstmt1);
		//  SQLSetStmtAttr(hstmt1,SQL_ATTR_CURSOR_TYPE,SQL_CURSOR_FORWARD_ONLY,0);
			fscanf(fp,"%s",table);
			strcpy(szText,"");
			strcat(szText,"create table ");
			strcat(szText,table);
			strcat(szText,"(");
			fscanf(fp,"%s",temp);
			fscanf(fp,"%s",temp);
			fscanf(fp,"%s",temp);
			while(strcmp(temp,"<>")==0)
			{
				fscanf(fp,"%s",temp);
				strcat(szText,temp);
			
				strcat(szText," ");
				fscanf(fp,"%s",temp);
				if(strcmp(temp,"id")==0 || strcmp(temp,"tid")==0 || strcmp(temp,"empid")==0)
				{
					strcat(szText,"varchar");
					strcat(szText,"(");
					fscanf(fp,"%s",temp);
					if(strcmp(temp,"identity")==0)
						fscanf(fp,"%s",temp);
					strcat(szText,temp);
					strcat(szText,")");
					fscanf(fp,"%s",temp);
				}
				else
				if(strcmp(temp,"char")==0 ||strcmp(temp,"varchar")==0)
				{
					strcat(szText,temp);
					strcat(szText,"(");
					fscanf(fp,"%s",temp);
					if(strcmp(temp,"identity")==0)
						fscanf(fp,"%s",temp);
					strcat(szText,temp);
					strcat(szText,")");
					fscanf(fp,"%s",temp);					
				}
				else
				{
					strcat(szText,temp);
					fscanf(fp,"%s",temp);
					if(strcmp(temp,"identity")==0)
						fscanf(fp,"%s",temp);
					fscanf(fp,"%s",temp);
				}
					fscanf(fp,"%s",temp);
				if(strcmp(temp,"<>")==0)
				{
					strcat(szText,",");
				}
				else 
				{
					strcat(szText,");");
				}
			}
			if(strlen(szText)==0) continue;
			sprintf((char*)szUpdate,"%s",szText);
		//	printf("%s\n",szText);
			sqlreturn = SQLExecDirect(hstmt1,(SQLCHAR *)szUpdate,SQL_NTS);
			if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS))
			{
			//	printf("SQLExec \"%s\" error!\n",szUpdate);
				fputs(szText,fp1);
				fputs("\n",fp1);
			//	 return(-1);
			}
			while(strcmp(temp,"//") ==0)
			{
				strcpy(szText,"");
				strcat(szText,"INSERT INTO ");
				strcat(szText,table);
				strcat(szText," VALUES( ");
				fscanf(fp,"%s",temp);
				while(strcmp(temp,"end")!=0 )
				{	
					strcat(szText,temp);
					strcat(szText," ");						
					fscanf(fp,"%s",temp);
				}
				strcat(szText,")");
				sprintf((char*)szUpdate,"%s",szText);
				//printf("%s\n",szText);
				sqlreturn = SQLExecDirect(hstmt1,(SQLCHAR *)szUpdate,SQL_NTS);
				if((sqlreturn!=SQL_SUCCESS_WITH_INFO)&&(sqlreturn!=SQL_SUCCESS))
				{
					//printf("SQLEXEC \"%s\" error!\n",szText);
					fputs(szText,fp1);
					fputs("\n",fp1);
					// return(-1);
				}
				fscanf(fp,"%s",temp);
			}
			
		
		}
		else
		{
			 printf("数据错误！\n");
			 exit(0);
		}
	}
	SQLFreeHandle(SQL_HANDLE_STMT,hstmt2);
	SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);
	fclose(fp);
	fclose(fp1);
	return 0;
}