datacopy.c

在Linux/Unix下面访问WINDOWS SQLSERVER 的ODBC驱动程序

			strcat(ls_command, "smallmoney");
			break;
		case SYBFLT8:
			strcat(ls_command, "float");
			break;
		case SYBDECIMAL:
			sprintf(column_type, "decimal(%d,%d)", colinfo.Precision, colinfo.Scale);
			strcat(ls_command, column_type);
			break;
		case SYBNUMERIC:
			sprintf(column_type, "numeric(%d,%d)", colinfo.Precision, colinfo.Scale);
			strcat(ls_command, column_type);
			break;
		case SYBCHAR:
			sprintf(column_type, "char(%d)", colinfo.MaxLength);
			strcat(ls_command, column_type);
			break;
		case SYBVARCHAR:
			sprintf(column_type, "varchar(%d)", colinfo.MaxLength);
			strcat(ls_command, column_type);
			break;
		case SYBTEXT:
			strcat(ls_command, "text");
			break;
		case SYBIMAGE:
			strcat(ls_command, "image");
			break;
		}

		if (colinfo.Null == TRUE) {
			strcat(ls_command, " NULL");
		} else {
			strcat(ls_command, " NOT NULL");
		}
	}
	strcat(ls_command, " ) ");

	if (dbcmd(dbdest, ls_command) == FAIL) {
		printf("dbcmd failed\n");
		return FALSE;
	}

	if (dbsqlexec(dbdest) == FAIL) {
		printf("create table on DESTINATION failed\n");
		return FALSE;
	}

	while (NO_MORE_RESULTS != dbresults(dbdest));

	return TRUE;
}

static int
check_table_structures(char *sobjname, char *dobjname, DBPROCESS * dbsrc, DBPROCESS * dbdest)
{
	char ls_command[256];
	int i;

	DBINT src_numcols = 0;
	DBINT dest_numcols = 0;

	DBINT src_coltype, dest_coltype;
	DBINT src_collen, dest_collen;


	sprintf(ls_command, "SET FMTONLY ON select * from %s SET FMTONLY OFF", sobjname);

	if (dbcmd(dbsrc, ls_command) == FAIL) {
		printf("dbcmd failed\n");
		return FALSE;
	}

	if (dbsqlexec(dbsrc) == FAIL) {
		printf("table %s not found on SOURCE\n", sobjname);
		return FALSE;
	}

	while (NO_MORE_RESULTS != dbresults(dbsrc));
	{

		if (0 == (src_numcols = dbnumcols(dbsrc))) {
			printf("Error in dbnumcols\n");
			return FALSE;
		}
	}

	sprintf(ls_command, "SET FMTONLY ON select * from %s SET FMTONLY OFF", dobjname);

	if (dbcmd(dbdest, ls_command) == FAIL) {
		printf("dbcmd failed\n");
		return FALSE;
	}

	if (dbsqlexec(dbdest) == FAIL) {
		printf("table %s not found on DEST\n", sobjname);
		return FALSE;
	}

	while (NO_MORE_RESULTS != dbresults(dbdest));
	{

		if (0 == (dest_numcols = dbnumcols(dbdest))) {
			printf("Error in dbnumcols\n");
			return FALSE;
		}
	}

	if (src_numcols != dest_numcols) {
		printf("number of columns do not match. source : %d , dest: %d\n", src_numcols, dest_numcols);
		return FALSE;
	}

	for (i = 1; i <= src_numcols; i++) {

		src_coltype = dbcoltype(dbsrc, i);
		src_collen = dbcollen(dbsrc, i);
		dest_coltype = dbcoltype(dbdest, i);
		dest_collen = dbcollen(dbdest, i);

		if ((src_coltype == SYBNUMERIC && dest_coltype == SYBNUMERIC) ||
		    (src_coltype == SYBDECIMAL && dest_coltype == SYBDECIMAL)
			) {
			continue;
		}

		if (src_coltype != dest_coltype || src_collen != dest_collen) {
			printf("COLUMN TYPE MISMATCH: column %d\n", i);
			printf("source: type %d, length %d\n", src_coltype, src_collen);
			printf("dest  : type %d, length %d\n", dest_coltype, dest_collen);
			return FALSE;
		}
	}
	return TRUE;
}

static int
transfer_data(BCPPARAMDATA params, DBPROCESS * dbsrc, DBPROCESS * dbdest)
{
	char ls_command[256];
	int col;

	DBINT src_numcols = 0;

	DBINT src_datlen;

	typedef struct migcoldata
	{
		DBINT coltype;
		DBINT collen;
		DBINT nullind;
		DBCHAR *data;
	} MIGCOLDATA;

	MIGCOLDATA **srcdata;

	DBINT rows_read = 0;
	DBINT rows_sent = 0;
	DBINT rows_done = 0;
	DBINT ret;

	struct timeval start_time;
	struct timeval end_time;
	double elapsed_time;
	struct timeval batch_start;
	struct timeval batch_end;
	double elapsed_batch = 0.0;

	if (params.vflag) {
		printf("\nStarting copy...\n");
	}

	if (params.tflag) {

		sprintf(ls_command, "truncate table %s", params.ddbobject);

		if (dbcmd(dbdest, ls_command) == FAIL) {
			printf("dbcmd failed\n");
			return FALSE;
		}

		if (dbsqlexec(dbdest) == FAIL) {
			printf("dbsqlexec failed\n");
			return FALSE;
		}

		if (dbresults(dbdest) == FAIL) {
			printf("Error in dbresults\n");
			return FALSE;
		}
	}


	sprintf(ls_command, "select * from %s", params.sdbobject);

	if (dbcmd(dbsrc, ls_command) == FAIL) {
		printf("dbcmd failed\n");
		return FALSE;
	}

	if (dbsqlexec(dbsrc) == FAIL) {
		printf("dbsqlexec failed\n");
		return FALSE;
	}

	if (NO_MORE_RESULTS != dbresults(dbsrc));
	{
		if (0 == (src_numcols = dbnumcols(dbsrc))) {
			printf("Error in dbnumcols\n");
			return FALSE;
		}
	}



	if (bcp_init(dbdest, params.ddbobject, (char *) NULL, (char *) NULL, DB_IN) == FAIL) {
		printf("Error in bcp_init\n");
		return FALSE;
	}

	srcdata = (MIGCOLDATA **) malloc(sizeof(MIGCOLDATA *) * src_numcols);

	for (col = 0; col < src_numcols; col++) {

		srcdata[col] = (MIGCOLDATA *) malloc(sizeof(MIGCOLDATA));
		memset(srcdata[col], '\0', sizeof(MIGCOLDATA));
		srcdata[col]->coltype = dbcoltype(dbsrc, col + 1);
		srcdata[col]->collen = dbcollen(dbsrc, col + 1);

		switch (srcdata[col]->coltype) {
		case SYBBIT:
			srcdata[col]->data = malloc(sizeof(DBBIT));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, BITBIND, sizeof(DBBIT), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBBIT, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;
		case SYBINT1:
			srcdata[col]->data = malloc(sizeof(DBTINYINT));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, TINYBIND, sizeof(DBTINYINT), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBINT1, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBINT2:
			srcdata[col]->data = malloc(sizeof(DBSMALLINT));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, SMALLBIND, sizeof(DBSMALLINT), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBINT2, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBINT4:
			srcdata[col]->data = malloc(sizeof(DBINT));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, INTBIND, sizeof(DBINT), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBINT4, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBFLT8:
			srcdata[col]->data = malloc(sizeof(DBFLT8));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, FLT8BIND, sizeof(DBFLT8), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBFLT8, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBREAL:
			srcdata[col]->data = malloc(sizeof(DBREAL));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, REALBIND, sizeof(DBREAL), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBREAL, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBMONEY:
			srcdata[col]->data = malloc(sizeof(DBMONEY));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, MONEYBIND, sizeof(DBMONEY), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBMONEY, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBMONEY4:
			srcdata[col]->data = malloc(sizeof(DBMONEY4));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, SMALLMONEYBIND, sizeof(DBMONEY4), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBMONEY4, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBDATETIME:
			srcdata[col]->data = malloc(sizeof(DBDATETIME));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, DATETIMEBIND, sizeof(DBDATETIME), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBDATETIME, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBDATETIME4:
			srcdata[col]->data = malloc(sizeof(DBDATETIME4));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, SMALLDATETIMEBIND, sizeof(DBDATETIME), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBDATETIME4, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBNUMERIC:
			srcdata[col]->data = malloc(sizeof(DBNUMERIC));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, NUMERICBIND, sizeof(DBNUMERIC), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, sizeof(DBNUMERIC), NULL, 0, SYBNUMERIC, col + 1) ==
			    FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;

		case SYBDECIMAL:
			srcdata[col]->data = malloc(sizeof(DBDECIMAL));
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, DECIMALBIND, sizeof(DBDECIMAL), (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, sizeof(DBDECIMAL), NULL, 0, SYBDECIMAL, col + 1) ==
			    FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;
		case SYBTEXT:
		case SYBCHAR:
			srcdata[col]->data = malloc(srcdata[col]->collen + 1);
			if (srcdata[col]->data == (char *) NULL) {
				printf("allocation error\n");
				return FALSE;
			}
			dbbind(dbsrc, col + 1, NTBSTRINGBIND, srcdata[col]->collen + 1, (BYTE *) srcdata[col]->data);
			dbnullbind(dbsrc, col + 1, &(srcdata[col]->nullind));
			if (bcp_bind(dbdest, (BYTE *) srcdata[col]->data, 0, -1, NULL, 0, SYBCHAR, col + 1) == FAIL) {
				printf("bcp_bind error\n");
				return FALSE;
			}
			break;
		}
	}

	gettimeofday(&start_time, 0);

	while (dbnextrow(dbsrc) != NO_MORE_ROWS) {
		rows_read++;
		for (col = 0; col < src_numcols; col++) {
			switch (srcdata[col]->coltype) {
			case SYBBIT:
			case SYBINT1:
			case SYBINT2:
			case SYBINT4:
			case SYBFLT8:
			case SYBREAL:
			case SYBDATETIME:
			case SYBDATETIME4:
			case SYBMONEY:
			case SYBMONEY4:
				if (srcdata[col]->nullind == -1) {	/* NULL data retrieved from source */
					bcp_collen(dbdest, 0, col + 1);
				} else {
					bcp_collen(dbdest, -1, col + 1);
				}
				break;
			case SYBNUMERIC:
				if (srcdata[col]->nullind == -1) {	/* NULL data retrieved from source */
					bcp_collen(dbdest, 0, col + 1);
				} else {
					bcp_collen(dbdest, sizeof(DBNUMERIC), col + 1);
				}
				break;
			case SYBDECIMAL:
				if (srcdata[col]->nullind == -1) {	/* NULL data retrieved from source */
					bcp_collen(dbdest, 0, col + 1);
				} else {
					bcp_collen(dbdest, sizeof(DBDECIMAL), col + 1);
				}
				break;
			case SYBTEXT:
			case SYBCHAR:
				if (srcdata[col]->nullind == -1) {	/* NULL data retrieved from source */
					bcp_collen(dbdest, 0, col + 1);
				} else {

					/*
					 * if there is zero length data, then the
					 * input data MUST have been all blanks,
					 * trimmed down to nothing by the bind
					 * type of NTBSTRINGBIND.
					 * so find out the source data length and
					 * re-set the data accordingly...
					 */

					if (strlen(srcdata[col]->data) == 0) {
						src_datlen = dbdatlen(dbsrc, col + 1);
						memset(srcdata[col]->data, ' ', src_datlen);
						srcdata[col]->data[src_datlen] = '\0';
					}
					bcp_collen(dbdest, strlen(srcdata[col]->data), col + 1);
				}
				break;
			}
		}
		if (bcp_sendrow(dbdest) == FAIL) {
			fprintf(stderr, "bcp_sendrow failed.  \n");
			return FALSE;
		} else {
			rows_sent++;
			if (rows_sent == params.batchsize) {
				gettimeofday(&batch_start, 0);
				ret = bcp_batch(dbdest);
				gettimeofday(&batch_end, 0);
				elapsed_batch = elapsed_batch +
					((double) (batch_end.tv_sec - batch_start.tv_sec) +
					 ((double) (batch_end.tv_usec - batch_start.tv_usec) / 1000000.00)
					);
				if (ret == -1) {
					printf("bcp_batch error\n");
					return FALSE;
				} else {
					rows_done += ret;
					printf("%d rows successfully copied (total %d)\n", ret, rows_done);
					rows_sent = 0;
				}
			}
		}
	}

	if (rows_read) {
		gettimeofday(&batch_start, 0);
		ret = bcp_done(dbdest);
		gettimeofday(&batch_end, 0);
		elapsed_batch = elapsed_batch +
			((double) (batch_end.tv_sec - batch_start.tv_sec) +
			 ((double) (batch_end.tv_usec - batch_start.tv_usec) / 1000000.00)
			);
		if (ret == -1) {
			fprintf(stderr, "bcp_done failed.  \n");
			return FALSE;
		} else {
			rows_done += ret;
		}
	}

	gettimeofday(&end_time, 0);


	elapsed_time = (double) (end_time.tv_sec - start_time.tv_sec) +
		((double) (end_time.tv_usec - start_time.tv_usec) / 1000000.00);

	if (params.vflag) {
		printf("\n");
		printf("rows read            : %d\n", rows_read);
		printf("rows written         : %d\n", rows_done);
		printf("elapsed time (secs)  : %f\n", elapsed_time);
		printf("rows per second      : %f\n", rows_done / elapsed_time);
	}

	return TRUE;


}

static void
pusage(void)
{
	fprintf(stderr, "usage: datacopy [-t | -a | -c owner] [-b batchsize] [-p packetsize] [-v] [-d]\n");
	fprintf(stderr, "       [-S server/username/password/database/table]\n");
	fprintf(stderr, "       [-D server/username/password/database/table]\n");
	fprintf(stderr, "       -t : truncate target table before loading data\n");
	fprintf(stderr, "       -a : append data to target table\n");
	fprintf(stderr, "       -c : create table owner.table before loading data\n");
	fprintf(stderr, "       -b : alter the number of records in each bcp batch\n");
	fprintf(stderr, "       (larger batch size = faster)\n");
	fprintf(stderr, "       -p : alter the default TDS packet size from the default\n");
	fprintf(stderr, "       (larger packet size = faster)\n");
	fprintf(stderr, "       -v : produce verbose output (timings etc.)\n");
	fprintf(stderr, "       -d : produce TDS DUMP log (serious debug only!)\n");
}

static int
err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
{
	if (dberr) {
		fprintf(stderr, "Msg %d, Level %d\n", dberr, severity);
		fprintf(stderr, "%s\n\n", dberrstr);
	} else {
		fprintf(stderr, "DB-LIBRARY error:\n\t");
		fprintf(stderr, "%s\n", dberrstr);
	}

	return INT_CANCEL;
}

static int
msg_handler(DBPROCESS * dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line)
{
	/*
	 * If it's a database change message, we'll ignore it.
	 * Also ignore language change message.
	 */
	if (msgno == 5701 || msgno == 5703)
		return 0;

	printf("Msg %ld, Level %d, State %d\n", (long int) msgno, severity, msgstate);

	if (strlen(srvname) > 0)
		printf("Server '%s', ", srvname);
	if (strlen(procname) > 0)
		printf("Procedure '%s', ", procname);
	if (line > 0)
		printf("Line %d", line);

	printf("\n\t%s\n", msgtext);

	return 0;
}