datacopy.c

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

/* datacopy - Program to move database table between servers
 * Copyright (C) 2004-2005  Bill Thompson
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Library General Public
 * License as published by the Free Software Foundation; either
 * version 2 of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Library General Public License for more details.
 *
 * You should have received a copy of the GNU Library General Public
 * License along with this library; if not, write to the
 * Free Software Foundation, Inc., 59 Temple Place - Suite 330,
 * Boston, MA 02111-1307, USA.
 */

#if HAVE_CONFIG_H
#include <config.h>
#endif

#include <stdio.h>

#if HAVE_STDLIB_H
#include <stdlib.h>
#endif /* HAVE_STDLIB_H */

#if HAVE_STRING_H
#include <string.h>
#endif /* HAVE_STRING_H */

#if HAVE_STRINGS_H
#include <strings.h>
#endif /* HAVE_STRINGS_H */

#if HAVE_UNISTD_H
#include <unistd.h>
#endif

#if TIME_WITH_SYS_TIME
# include <sys/time.h>
# include <time.h>
#else
# if HAVE_SYS_TIME_H
#  include <sys/time.h>
# else
#  include <time.h>
# endif
#endif

#include <sybfront.h>
#include <sybdb.h>

#include "replacements.h"

enum states
{
	GET_NEXTARG,
	GET_BATCHSIZE,
	GET_PACKETSIZE,
	GET_OWNER,
	GET_SOURCE,
	GET_DEST
};

typedef struct pd
{
	int batchsize;
	int packetsize;
	char *suser;
	char *spass;
	char *sserver;
	char *sdb;
	char *sdbobject;
	char *duser;
	char *dpass;
	char *dserver;
	char *ddb;
	char *ddbobject;
	char *owner;
	int tflag;
	int aflag;
	int cflag;
	int Sflag;
	int Dflag;
	int bflag;
	int pflag;
	int vflag;
} BCPPARAMDATA;

static void pusage(void);
static int process_parameters(int, char **, struct pd *);
static int login_to_databases(BCPPARAMDATA * pdata, DBPROCESS ** dbsrc, DBPROCESS ** dbdest);
static int create_target_table(char *sobjname, char *owner, char *dobjname, DBPROCESS * dbsrc, DBPROCESS * dbdest);
static int check_table_structures(char *sobjname, char *dobjname, DBPROCESS * dbsrc, DBPROCESS * dbdest);
static int transfer_data(BCPPARAMDATA params, DBPROCESS * dbsrc, DBPROCESS * dbdest);

static int err_handler(DBPROCESS *, int, int, int, char *, char *);
static int msg_handler(DBPROCESS *, DBINT, int, int, char *, char *, char *, int);

int tdsdump_open(const char *filename);

int
main(int argc, char **argv)
{
	BCPPARAMDATA params;

	DBPROCESS *dbsrc;
	DBPROCESS *dbtarget;

	memset(&params, '\0', sizeof(params));

	if (process_parameters(argc, argv, &params) == FALSE) {
		pusage();
		return 1;
	}

	if (login_to_databases(&params, &dbsrc, &dbtarget) == FALSE)
		return 1;

	if (params.cflag) {
		if (create_target_table(params.sdbobject, params.owner, params.ddbobject, dbsrc, dbtarget) == FALSE) {
			printf("datacopy: could not create target table %s.%s . terminating\n", params.owner, params.ddbobject);
			dbclose(dbsrc);
			dbclose(dbtarget);
			return 1;
		}
	}

	if (check_table_structures(params.sdbobject, params.ddbobject, dbsrc, dbtarget) == FALSE) {
		printf("datacopy: table structures do not match. terminating\n");
		dbclose(dbsrc);
		dbclose(dbtarget);
		return 1;
	}

	if (transfer_data(params, dbsrc, dbtarget) == FALSE) {
		printf("datacopy: table copy failed.\n");
		printf("           the data may have been partially copied into the target database \n");
		dbclose(dbsrc);
		dbclose(dbtarget);
		return 1;
	}


	dbclose(dbsrc);
	dbclose(dbtarget);

	return 0;
}

static char *
gets_alloc(void)
{
	char reply[256];
	char *p;

	if (fgets(reply, sizeof(reply), stdin) == NULL)
		return NULL;
	p = strchr(reply, '\n');
	if (p)
		*p = 0;
	return strdup(reply);
}

static int
process_parameters(int argc, char **argv, BCPPARAMDATA * pdata)
{
	int state;
	int i;

	char arg[FILENAME_MAX + 1];

	char connection[256];
	char owner[256];
	char *tok;


	/* set some defaults */

	pdata->batchsize = 1000;

	/* get the rest of the arguments */

	state = GET_NEXTARG;

	for (i = 1; i < argc; i++) {
		strcpy(arg, argv[i]);

		switch (state) {

		case GET_NEXTARG:

			if (arg[0] != '-')
				return FALSE;

			switch (arg[1]) {

			case 'b':
				pdata->bflag++;
				if (strlen(arg) > 2)
					pdata->batchsize = atoi(&arg[2]);
				else
					state = GET_BATCHSIZE;
				break;
			case 'p':
				pdata->pflag++;
				if (strlen(arg) > 2)
					pdata->packetsize = atoi(&arg[2]);
				else
					state = GET_PACKETSIZE;
				break;
			case 't':
				pdata->tflag++;
				break;
			case 'a':
				pdata->aflag++;
				break;
			case 'c':
				pdata->cflag++;
				if (strlen(arg) > 2) {
					strcpy(owner, &arg[2]);
					pdata->owner = strdup(owner);
				} else
					state = GET_OWNER;
				break;
			case 'd':
				tdsdump_open(NULL);
				break;
			case 'S':
				pdata->Sflag++;
				if (strlen(arg) > 2) {
					strcpy(connection, &arg[2]);

					tok = strtok(connection, "/");
					if (!tok)
						return FALSE;
					pdata->sserver = strdup(tok);

					tok = strtok(NULL, "/");
					if (!tok)
						return FALSE;
					pdata->suser = strdup(tok);

					tok = strtok(NULL, "/");
					if (!tok)
						return FALSE;
					pdata->spass = strdup(tok);

					tok = strtok(NULL, "/");
					if (!tok)
						return FALSE;
					pdata->sdb = strdup(tok);

					tok = strtok(NULL, "/");
					if (!tok)
						return FALSE;
					pdata->sdbobject = strdup(tok);

				} else
					state = GET_SOURCE;
				break;
			case 'D':
				pdata->Dflag++;
				if (strlen(arg) > 2) {
					strcpy(connection, &arg[2]);

					tok = strtok(connection, "/");
					if (!tok)
						return FALSE;
					pdata->dserver = strdup(tok);

					tok = strtok(NULL, "/");
					if (!tok)
						return FALSE;
					pdata->duser = strdup(tok);

					tok = strtok(NULL, "/");
					if (!tok)
						return FALSE;
					pdata->dpass = strdup(tok);

					tok = strtok(NULL, "/");
					if (!tok)
						return FALSE;
					pdata->ddb = strdup(tok);

					tok = strtok(NULL, "/");
					if (!tok)
						return FALSE;
					pdata->ddbobject = strdup(tok);

				} else
					state = GET_DEST;
				break;
			case 'v':
				pdata->vflag++;
				break;
			default:
				return FALSE;

			}
			break;
		case GET_BATCHSIZE:
			pdata->batchsize = atoi(arg);
			state = GET_NEXTARG;
			break;
		case GET_PACKETSIZE:
			pdata->packetsize = atoi(arg);
			state = GET_NEXTARG;
			break;
		case GET_OWNER:
			if (arg[0] == '-') {
				fprintf(stderr, "If -c is specified an owner for the table must be provided.\n");
				return FALSE;
			}
			strcpy(owner, arg);
			pdata->owner = strdup(owner);
			state = GET_NEXTARG;
			break;
		case GET_SOURCE:
			strcpy(connection, arg);

			tok = strtok(connection, "/");
			if (!tok)
				return FALSE;
			pdata->sserver = strdup(tok);

			tok = strtok(NULL, "/");
			if (!tok)
				return FALSE;
			pdata->suser = strdup(tok);

			tok = strtok(NULL, "/");
			if (!tok)
				return FALSE;
			pdata->spass = strdup(tok);

			tok = strtok(NULL, "/");
			if (!tok)
				return FALSE;
			pdata->sdb = strdup(tok);

			tok = strtok(NULL, "/");
			if (!tok)
				return FALSE;
			pdata->sdbobject = strdup(tok);

			state = GET_NEXTARG;
			break;

		case GET_DEST:
			strcpy(connection, arg);

			tok = strtok(connection, "/");
			if (!tok)
				return FALSE;
			pdata->dserver = strdup(tok);

			tok = strtok(NULL, "/");
			if (!tok)
				return FALSE;
			pdata->duser = strdup(tok);

			tok = strtok(NULL, "/");
			if (!tok)
				return FALSE;
			pdata->dpass = strdup(tok);

			tok = strtok(NULL, "/");
			if (!tok)
				return FALSE;
			pdata->ddb = strdup(tok);

			tok = strtok(NULL, "/");
			if (!tok)
				return FALSE;
			pdata->ddbobject = strdup(tok);

			state = GET_NEXTARG;
			break;

		default:
			break;

		}
	}
	/* one of these must be specified */

	if ((pdata->tflag + pdata->aflag + pdata->cflag) != 1) {
		fprintf(stderr, "one (and only one) of -t, -a or -c must be specified\n");
		return FALSE;
	}

	if (!pdata->Sflag) {

		printf("\nNo [-S]ource information supplied.\n\n");
		printf("Enter Server   : ");
		pdata->sserver = gets_alloc();

		printf("Enter Login    : ");
		pdata->suser = gets_alloc();

		printf("Enter Password : ");
		pdata->spass = gets_alloc();

		printf("Enter Database : ");
		pdata->sdb = gets_alloc();

		printf("Enter Table    : ");
		pdata->sdbobject = gets_alloc();
	}

	if (!pdata->Dflag) {

		printf("\nNo [-D]estination information supplied.\n\n");
		printf("Enter Server   : ");
		pdata->dserver = gets_alloc();

		printf("Enter Login    : ");
		pdata->duser = gets_alloc();

		printf("Enter Password : ");
		pdata->dpass = gets_alloc();

		printf("Enter Database : ");
		pdata->ddb = gets_alloc();

		printf("Enter Table    : ");
		pdata->ddbobject = gets_alloc();
	}

	return TRUE;

}

static int
login_to_databases(BCPPARAMDATA * pdata, DBPROCESS ** dbsrc, DBPROCESS ** dbdest)
{
	LOGINREC *slogin;
	LOGINREC *dlogin;

	/* Initialize DB-Library. */

	if (dbinit() == FAIL)
		return FALSE;

	/*
	 * Install the user-supplied error-handling and message-handling
	 * routines. They are defined at the bottom of this source file.
	 */

	dberrhandle(err_handler);
	dbmsghandle(msg_handler);

	/*
	 * Allocate and initialize the LOGINREC structure to be used
	 * to open a connection to SQL Server.
	 */

	slogin = dblogin();

	DBSETLUSER(slogin, pdata->suser);
	DBSETLPWD(slogin, pdata->spass);
	DBSETLAPP(slogin, "Migrate Data");

	/* if packet size specified, set in login record */

	if (pdata->pflag && pdata->packetsize > 0) {
		DBSETLPACKET(slogin, pdata->packetsize);
	}

	/*
	 * Get a connection to the database.
	 */

	if ((*dbsrc = dbopen(slogin, pdata->sserver)) == (DBPROCESS *) NULL) {
		fprintf(stderr, "Can't connect to source server.\n");
		return FALSE;
	}

	if (dbuse(*dbsrc, pdata->sdb) == FAIL) {
		fprintf(stderr, "Can't change database to %s .\n", pdata->sdb);
		return FALSE;
	}

	dlogin = dblogin();

	DBSETLUSER(dlogin, pdata->duser);
	DBSETLPWD(dlogin, pdata->dpass);
	DBSETLAPP(dlogin, "Migrate Data");

	/* Enable bulk copy for this connection. */

	BCP_SETL(dlogin, TRUE);

	/* if packet size specified, set in login record */

	if (pdata->pflag && pdata->packetsize > 0) {
		DBSETLPACKET(dlogin, pdata->packetsize);
	}

	/*
	 * Get a connection to the database.
	 */

	if ((*dbdest = dbopen(dlogin, pdata->dserver)) == (DBPROCESS *) NULL) {
		fprintf(stderr, "Can't connect to destination server.\n");
		return FALSE;
	}

	if (dbuse(*dbdest, pdata->ddb) == FAIL) {
		fprintf(stderr, "Can't change database to %s .\n", pdata->sdb);
		return FALSE;
	}

	return TRUE;

}

static int
create_target_table(char *sobjname, char *owner, char *dobjname, DBPROCESS * dbsrc, DBPROCESS * dbdest)
{
	char ls_command[2048];
	int i;

	DBINT num_cols;
	DBCHAR column_type[33];
	DBCOL colinfo;


	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));

	sprintf(ls_command, "CREATE TABLE %s.%s ", owner, dobjname);

	num_cols = dbnumcols(dbsrc);

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

		if (dbtablecolinfo(dbsrc, i, &colinfo) != SUCCEED)
			return FALSE;

		if (i == 1)
			strcat(ls_command, "( ");
		else
			strcat(ls_command, ", ");

		strcat(ls_command, colinfo.Name);
		strcat(ls_command, " ");

		switch (colinfo.Type) {
		case SYBINT1:
			strcat(ls_command, "tinyint");
			break;
		case SYBBIT:
			strcat(ls_command, "bit");
			break;
		case SYBINT2:
			strcat(ls_command, "smallint");
			break;
		case SYBINT4:
			strcat(ls_command, "int");
			break;
		case SYBDATETIME:
			strcat(ls_command, "datetime");
			break;
		case SYBDATETIME4:
			strcat(ls_command, "smalldatetime");
			break;
		case SYBREAL:
			strcat(ls_command, "real");
			break;
		case SYBMONEY:
			strcat(ls_command, "money");
			break;
		case SYBMONEY4: