?? database.cpp
字號:
#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;
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -