bsqlodbc.c

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

/* FreeTDS - Library of routines accessing Sybase and Microsoft databases
 * Copyright (C) 2004, 2005  James K. Lowden
 *
 * This library 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 library 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>
#include <assert.h>
#include <ctype.h>

#if HAVE_ERRNO_H
#include <errno.h>
#endif

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

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

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

#if HAVE_LIBGEN_H
#include <libgen.h>
#endif

#include "tds_sysdep_public.h"
#include <sql.h>
#include <sqlext.h>
#include "replacements.h"

static char software_version[] = "$Id: bsqlodbc.c,v 1.9.2.1 2008/02/12 15:41:51 freddy77 Exp $";
static void *no_unused_var_warn[] = { software_version, no_unused_var_warn };

static char * next_query(void);
static void print_results(SQLHSTMT hStmt);
/* static int get_printable_size(int type, int size); */
static void usage(const char invoked_as[]);

static SQLINTEGER print_error_message(SQLSMALLINT hType, SQLHANDLE handle);
static SQLRETURN odbc_herror(SQLSMALLINT hType, SQLHANDLE handle, SQLRETURN erc, const char func[], const char msg[]);
static SQLRETURN odbc_perror(SQLHSTMT hStmt, SQLRETURN erc, const char func[], const char msg[]);
static const char * prret(SQLRETURN erc);


struct METADATA
{
	char *name, *format_string;
	const char *source;
	SQLSMALLINT type;
	SQLULEN size, width;
};
struct DATA { char *buffer; SQLLEN len; int status; };

static int set_format_string(struct METADATA * meta, const char separator[]);

typedef struct _options 
{ 
	int 	fverbose, 
		fquiet;
	FILE 	*headers, 
		*verbose;
	char 	*servername, 
		*database, 
		*appname, 
		 hostname[128];
	const char *colsep;
	char	*input_filename, 
		*output_filename, 
		*error_filename; 
} OPTIONS;

typedef struct
{
	int connect_timeout, query_timeout;
	char 	*client_hostname,
		*username,
		*password,
		*client_charset;
} LOGINREC;

static LOGINREC* get_login(int argc, char *argv[], OPTIONS *poptions);

/* global variables */
OPTIONS options;
static const char default_colsep[] = "  ";
/* end global variables */


/**
 * The purpose of this program is threefold:
 *
 * 1.  To provide a generalized SQL processor suitable for testing the FreeTDS ODBC driver.
 * 2.  To offer a robust batch-oriented SQL processor suitable for use in a production environment.  
 * 3.  To serve as a model example of how to use ODBC functions.  
 *
 * These purposes may be somewhat impossible.  The original author is not an experienced ODBC programmer.  
 * Nevertheless the first objective can be met and is useful by itself.  
 * If others join in, perhaps the others can be met, too. 
 */


/**
 * When SQLExecute returns either SQL_ERROR or SQL_SUCCESS_WITH_INFO, 
 * an associated SQLSTATE value can be obtained by calling SQLGetDiagRec 
 * with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle.  
 */
static SQLINTEGER
print_error_message(SQLSMALLINT hType, SQLHANDLE handle) {
	int i;
	SQLINTEGER ndiag=0;
	SQLRETURN ret;
	SQLCHAR state[6];
	SQLINTEGER error, maxerror=0;
	SQLCHAR text[1024];
	SQLSMALLINT len;

	ret = SQLGetDiagField(hType, handle, 0, SQL_DIAG_NUMBER, &ndiag, sizeof(ndiag), NULL);
	assert(ret == SQL_SUCCESS);

	for(i=1; i <= ndiag; i++) {
		memset(text, '\0', sizeof(text));
		ret = SQLGetDiagRec(hType, handle, i, state, &error, text, sizeof(text), &len);
		
		if (ret == SQL_SUCCESS && error == 0) {
			fprintf(stdout, "\"%s\"\n", text);
			continue;
		}
		
		fprintf(stderr, "%s: error %d: %s: %s\n", options.appname, (int)error, state, text);
		assert(ret == SQL_SUCCESS);
		
		if (error > maxerror)
			maxerror = error;
	}
	return maxerror;
}

static const char *
prret(SQLRETURN erc)
{
	switch(erc) {
	case SQL_SUCCESS: 		return "SQL_SUCCESS";
	case SQL_SUCCESS_WITH_INFO: 	return "SQL_SUCCESS_WITH_INFO";
	case SQL_ERROR: 		return "SQL_ERROR";
	case SQL_STILL_EXECUTING: 	return "SQL_STILL_EXECUTING";
	case SQL_INVALID_HANDLE: 	return "SQL_INVALID_HANDLE";
	case SQL_NO_DATA: 		return "SQL_NO_DATA";
	}
	fprintf(stderr, "error:%d: prret cannot interpret SQLRETURN code %d\n", __LINE__, erc);
	return "unknown";
}

static SQLRETURN
odbc_herror(SQLSMALLINT hType, SQLHANDLE handle, SQLRETURN erc, const char func[], const char msg[])
{
	char * ercstr;

	assert(func && msg);
	
	switch(erc) {
	case SQL_SUCCESS:
		return erc;
	case SQL_SUCCESS_WITH_INFO:
		ercstr = "SQL_SUCCESS_WITH_INFO";
		break;
	case SQL_ERROR:
		ercstr = "SQL_ERROR";
		break;
	case SQL_STILL_EXECUTING:
		ercstr = "SQL_STILL_EXECUTING";
		break;
	case SQL_INVALID_HANDLE:
		fprintf(stderr, "%s: error %d: %s: invalid handle: %s\n", options.appname, (int)erc, func, msg);
		exit(EXIT_FAILURE);
	default:
		fprintf(stderr, "%s: error: %d is an unknown return code for %s\n", options.appname, (int)erc, func);
		exit(EXIT_FAILURE);
	}
	
	fprintf(stderr, "%s: error %d: %s: %s: %s\n", options.appname, (int)erc, func, ercstr, msg);

	print_error_message(hType, handle); 
	
	return erc;
}

static SQLRETURN
odbc_perror(SQLHSTMT hStmt, SQLRETURN erc, const char func[], const char msg[])
{
	return odbc_herror(SQL_HANDLE_STMT, hStmt, erc, func, msg);
}

int
main(int argc, char *argv[])
{
	LOGINREC *login;
	SQLHENV hEnv = 0;
	SQLHDBC hDbc = 0;
	SQLRETURN erc;
	const char *sql;

	memset(&options, 0, sizeof(options));
	options.headers = stderr;
	login = get_login(argc, argv, &options); /* get command-line parameters and call dblogin() */
	assert(login != NULL);

	/* 
	 * Override stdin, stdout, and stderr, as required 
	 */
	if (options.input_filename) {
		if (freopen(options.input_filename, "r", stdin) == NULL) {
			fprintf(stderr, "%s: unable to open %s: %s\n", options.appname, options.input_filename, strerror(errno));
			exit(1);
		}
	}

	if (options.output_filename) {
		if (freopen(options.output_filename, "w", stdout) == NULL) {
			fprintf(stderr, "%s: unable to open %s: %s\n", options.appname, options.output_filename, strerror(errno));
			exit(1);
		}
	}
	
	if (options.error_filename) {
		if (freopen(options.error_filename, "w", stderr) == NULL) {
			fprintf(stderr, "%s: unable to open %s: %s\n", options.appname, options.error_filename, strerror(errno));
			exit(1);
		}
	}

	if (options.fverbose) {
		options.verbose = stderr;
	} else {
		static const char null_device[] = "/dev/null";
		options.verbose = fopen(null_device, "w");
		if (options.verbose == NULL) {
			fprintf(stderr, "%s:%d unable to open %s for verbose operation: %s\n", 
					options.appname, __LINE__, null_device, strerror(errno));
			exit(1);
		}
	}

	fprintf(options.verbose, "%s:%d: Verbose operation enabled\n", options.appname, __LINE__);
	
	/* 
	 * Connect to the server 
	 */
	if ((erc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv)) != SQL_SUCCESS) {
		odbc_herror(SQL_HANDLE_ENV, hEnv, erc, "SQLAllocHandle", "failed to allocate an environment");
		exit(EXIT_FAILURE);
	}
	assert(hEnv);

	if ((erc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER)) != SQL_SUCCESS) {
		odbc_herror(SQL_HANDLE_DBC, hDbc, erc, "SQLSetEnvAttr", "failed to set SQL_OV_ODBC3");
		exit(EXIT_FAILURE);
	}

	if ((erc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc)) != SQL_SUCCESS) {
		odbc_herror(SQL_HANDLE_DBC, hDbc, erc, "SQLAllocHandle", "failed to allocate a connection");
		exit(EXIT_FAILURE);
	}
	assert(hDbc);

	if ((erc = SQLConnect(hDbc, 	(SQLCHAR *) options.servername, SQL_NTS, 
					(SQLCHAR *) login->username, SQL_NTS, 
					(SQLCHAR *) login->password, SQL_NTS)) != SQL_SUCCESS) {
		odbc_herror(SQL_HANDLE_DBC, hDbc, erc, "SQLConnect", "failed");
		exit(EXIT_FAILURE);
	}

#if 0
	/* Switch to the specified database, if any */
	if (options.database)
		dbuse(dbproc, options.database);
#endif

	/* 
	 * Read the queries and write the results
	 */
	while ((sql = next_query()) != NULL ) {
		SQLHSTMT hStmt;

		if ((erc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS) {
			odbc_perror(hStmt, erc, "SQLAllocHandle", "failed to allocate a statement handle");
			exit(EXIT_FAILURE);
		}

		/* "Prepare" the query and send it to the server */
		if ((erc = SQLPrepare(hStmt, (SQLCHAR *) sql, SQL_NTS)) != SQL_SUCCESS) {
			odbc_perror(hStmt, erc, "SQLPrepare", "failed");
			exit(EXIT_FAILURE);
		}

		if((erc = SQLExecute(hStmt)) != SQL_SUCCESS) {
			switch(erc) {
			case SQL_NEED_DATA: 
				goto FreeStatement;
			case SQL_SUCCESS_WITH_INFO:
				if (0 != print_error_message(SQL_HANDLE_STMT, hStmt)) {
					fprintf(stderr, "SQLExecute: continuing...\n");
				}
				break;
			default:
				odbc_perror(hStmt, erc, "SQLExecute", "failed");
				exit(EXIT_FAILURE);
			}
		}

		/* Write the output */
		print_results(hStmt);
		
		FreeStatement:
		if ((erc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt)) != SQL_SUCCESS){
			odbc_perror(hStmt, erc, "SQLFreeHandle", "failed");
			exit(EXIT_FAILURE);
		} 
	}

	return 0;
}

static char *
next_query()
{
	char query_line[4096];
	static char *sql = NULL;
	
	if (feof(stdin))
		return NULL;
			
	fprintf(options.verbose, "%s:%d: Query:\n", options.appname, __LINE__);
	
	free(sql);
	sql = NULL;

	while (fgets(query_line, sizeof(query_line), stdin)) {
		/* 'go' or 'GO' separates command batches */
		char *p = query_line;
		if (strncasecmp(p, "go", 2) == 0) {
			for (p+=2; isspace((unsigned char) *p); p++) {
				if (*p == '\n')
					return sql;
			}
		}

		fprintf(options.verbose, "\t%s", query_line);
		
		/* Add the query line to the command to be sent to the server */
		if (!strlen(query_line))
			continue;
		p = realloc(sql, 1 + (sql? strlen(sql) : 0) + strlen(query_line));
		if (!p) {
			fprintf(stderr, "%s:%d: could not allocate memory\n", options.appname, __LINE__);
			return NULL;
		}
		if (!sql) {
			strcpy(p, query_line);
		} else {
			strcat(p, query_line);
		}
		sql = p;
	}
	
	if (feof(stdin))
		return sql;
			
	if (ferror(stdin)) {
		fprintf(stderr, "%s:%d: next_query() failed\n", options.appname, __LINE__);
		perror(NULL);
		return NULL;
	}
	
	return sql;
}

static void
free_metadata(struct METADATA *metadata, struct DATA *data, int ncols)