bsqlodbc.c

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

{
	int c;
	
	for (c=0; c < ncols; c++) {
		free(metadata[c].format_string);
		free(data[c].buffer);
	}
	free(metadata);
	free(data);
}

static void
print_results(SQLHSTMT hStmt) 
{
	static const char dashes[] = "----------------------------------------------------------------" /* each line is 64 */
				     "----------------------------------------------------------------"
				     "----------------------------------------------------------------"
				     "----------------------------------------------------------------";
	
	struct METADATA *metadata = NULL;
	
	struct DATA *data = NULL;
	
	SQLSMALLINT ncols = 0;
	RETCODE erc;
	int c, ret;

	/* 
	 * Process each resultset
	 */
	do {
		/* free metadata, in case it was previously allocated */
		free_metadata(metadata, data, ncols);
		metadata = NULL;
		data = NULL;
		ncols = 0;
		
		/* 
		 * Allocate memory for metadata and bound columns 
		 */
		if ((erc = SQLNumResultCols(hStmt, &ncols)) != SQL_SUCCESS){
			odbc_perror(hStmt, erc, "SQLNumResultCols", "failed");
			exit(EXIT_FAILURE);
		} 
		
		metadata = (struct METADATA*) calloc(ncols, sizeof(struct METADATA));
		assert(metadata);

		data = (struct DATA*) calloc(ncols, sizeof(struct DATA));
		assert(data);
		
		/* 
		 * For each column, get its name, type, and size. 
		 * Allocate a buffer to hold the data, and bind the buffer to the column.
		 * "bind" here means to give the address of the buffer we want filled as each row is fetched.
		 */

		fprintf(options.verbose, "Metadata\n");
		fprintf(options.verbose, "%-6s  %-30s  %-30s  %-15s  %-6s  %-6s  \n", "col", "name", "source", "type", "size", "varies");
		fprintf(options.verbose, "%.6s  %.30s  %.30s  %.15s  %.6s  %.6s  \n", dashes, dashes, dashes, dashes, dashes, dashes);
		for (c=0; c < ncols; c++) {
			/* Get and print the metadata.  Optional: get only what you need. */
			SQLCHAR name[512];
			SQLSMALLINT namelen, ndigits, fnullable;

			if ((erc = SQLDescribeCol(hStmt, c+1, name, sizeof(name), &namelen, 
							&metadata[c].type, &metadata[c].size,
							&ndigits, &fnullable)) != SQL_SUCCESS) {
				odbc_perror(hStmt, erc, "SQLDescribeCol", "failed");
				exit(EXIT_FAILURE);
			} 
			assert(namelen < sizeof(name));
			name[namelen] = '\0';
			metadata[c].name = strdup((char *) name);
			metadata[c].width = (ndigits > metadata[c].size)? ndigits : metadata[c].size;

			fprintf(options.verbose, "%6d  %30s  %30s  %15s  %6lu  %6d  \n", 
				c+1, metadata[c].name, metadata[c].source, "(todo)", 
				(long unsigned int) metadata[c].size,  -1);

#if 0
			fprintf(options.verbose, "%6d  %30s  %30s  %15s  %6d  %6d  \n", 
				c+1, metadata[c].name, metadata[c].source, dbprtype(metadata[c].type), 
				metadata[c].size,  dbvarylen(dbproc, c+1));

			metadata[c].width = get_printable_size(metadata[c].type, metadata[c].size);
			if (metadata[c].width < strlen(metadata[c].name))
				metadata[c].width = strlen(metadata[c].name);
#endif				
			/* 
			 * Build the column header format string, based on the column width. 
			 * This is just one solution to the question, "How wide should my columns be when I print them out?"
			 */
			ret = set_format_string(&metadata[c], (c+1 < ncols)? options.colsep : "\n");
			if (ret <= 0) {
				fprintf(stderr, "%s:%d: asprintf(), column %d failed\n", options.appname, __LINE__, c+1);
				return;
			}

			/* 
			 * Bind the column to our variable.
			 * We bind everything to strings, because we want to convert everything to strings for us.
			 * If you're performing calculations on the data in your application, you'd bind the numeric data
			 * to C integers and floats, etc. instead. 
			 * 
			 * It is not necessary to bind to every column returned by the query.  
			 * Data in unbound columns are simply never copied to the user's buffers and are thus 
			 * inaccesible to the application.  
			 */

			data[c].buffer = calloc(1, metadata[c].width);
			assert(data[c].buffer);

			if ((erc = SQLBindCol(hStmt, c+1, SQL_C_CHAR, (SQLPOINTER)data[c].buffer, 
						metadata[c].width, &data[c].len)) != SQL_SUCCESS){
				odbc_perror(hStmt, erc, "SQLBindCol", "failed");
				exit(EXIT_FAILURE);
			} 

		}
		
		if (!options.fquiet) {
			/* Print the column headers to stderr to keep them separate from the data.  */
			for (c=0; c < ncols; c++) {
				fprintf(options.headers, metadata[c].format_string, metadata[c].name);
			}

			/* Underline the column headers.  */
			for (c=0; c < ncols; c++) {
				fprintf(options.headers, metadata[c].format_string, dashes);
			}
		}
		/* 
		 * Print the data to stdout.  
		 */
		while (ncols > 0 && (erc = SQLFetch(hStmt)) != SQL_NO_DATA) {
			switch(erc) {
			case SQL_SUCCESS:
				break;
			case SQL_SUCCESS_WITH_INFO:
				print_error_message(SQL_HANDLE_STMT, hStmt);
				continue;
			default:
				odbc_perror(hStmt, erc, "SQLFetch", "failed");
				exit(EXIT_FAILURE);
			}
			for (c=0; c < ncols; c++) {
				char *s;
				switch (data[c].len) { /* handle nulls */
				case SQL_NULL_DATA: /* is null */
					fprintf(stdout, metadata[c].format_string, "NULL");
					break;
				default:
					assert(data[c].len > 0);
					s = calloc(1, 1 + data[c].len);
					assert(s);
					memcpy(s, data[c].buffer, data[c].len);
					fprintf(stdout, metadata[c].format_string, s);
					free(s);
					break;
				}
			}
		}
		if (ncols > 0 && erc == SQL_NO_DATA)
			print_error_message(SQL_HANDLE_STMT, hStmt);

		erc = SQLMoreResults(hStmt);
		fprintf(options.verbose, "SQLMoreResults returned %s\n", prret(erc));
		switch (erc) {
		case SQL_NO_DATA:
			print_error_message(SQL_HANDLE_STMT, hStmt);
			break;
		case SQL_SUCCESS_WITH_INFO:
			print_error_message(SQL_HANDLE_STMT, hStmt);
		case SQL_SUCCESS:
			continue;
		default:
			odbc_perror(hStmt, erc, "SQLMoreResults", "failed");
			exit(EXIT_FAILURE);
		}
	} while (erc != SQL_NO_DATA);
	
	if (erc != SQL_NO_DATA) {
		assert(erc != SQL_STILL_EXECUTING);
		odbc_perror(hStmt, erc, "SQLMoreResults", "failed");
		exit(EXIT_FAILURE);
	} 
}

#if 0 
static int
get_printable_size(int type, int size)	/* adapted from src/dblib/dblib.c */
{
	switch (type) {
	case SYBINTN:
		switch (size) {
		case 1:
			return 3;
		case 2:
			return 6;
		case 4:
			return 11;
		case 8:
			return 21;
		}
	case SYBINT1:
		return 3;
	case SYBINT2:
		return 6;
	case SYBINT4:
		return 11;
	case SYBINT8:
		return 21;
	case SYBVARCHAR:
	case SYBCHAR:
		return size;
	case SYBFLT8:
		return 11;	/* FIX ME -- we do not track precision */
	case SYBREAL:
		return 11;	/* FIX ME -- we do not track precision */
	case SYBMONEY:
		return 12;	/* FIX ME */
	case SYBMONEY4:
		return 12;	/* FIX ME */
	case SYBDATETIME:
		return 26;	/* FIX ME */
	case SYBDATETIME4:
		return 26;	/* FIX ME */
#if 0	/* seems not to be exported to sybdb.h */
	case SYBBITN:
#endif
	case SYBBIT:
		return 1;
		/* FIX ME -- not all types present */
	default:
		return 0;
	}

}
#endif /* 0, not used */

/** 
 * Build the column header format string, based on the column width. 
 * This is just one solution to the question, "How wide should my columns be when I print them out?"
 */
#define is_character_data(x)   (x==SQL_CHAR	    || \
				x==SQL_VARCHAR	    || \
				x==SQL_LONGVARCHAR  || \
				x==SQL_WCHAR	    || \
				x==SQL_WVARCHAR     || \
				x==SQL_WLONGVARCHAR)
static int
set_format_string(struct METADATA * meta, const char separator[])
{
	int width, ret;
	const char *size_and_width;
	assert(meta);

	if(0 == strcmp(options.colsep, default_colsep)) { 
		/* right justify numbers, left justify strings */
		size_and_width = is_character_data(meta->type)? "%%-%d.%ds%s" : "%%%d.%ds%s";
		
		width = meta->width; /* get_printable_size(meta->type, meta->size); */
		if (width < strlen(meta->name))
			width = strlen(meta->name);

		ret = asprintf(&meta->format_string, size_and_width, width, width, separator);
	} else {
		/* For anything except the default two-space separator, don't justify the strings. */
		ret = asprintf(&meta->format_string, "%%s%s", separator);
	}
		       
	return ret;
}

static void
usage(const char invoked_as[])
{
	fprintf(stderr, "usage:  %s \n"
			"        [-U username] [-P password]\n"
			"        [-S servername] [-D database]\n"
			"        [-i input filename] [-o output filename] [-e error filename]\n"
			, invoked_as);
}

static void
unescape(char arg[])
{
	char *p = arg;
	char escaped = '1'; /* any digit will do for an initial value */
	while ((p = strchr(p, '\\')) != NULL) {
	
		switch (p[1]) {
		case '0':
			/* FIXME we use strlen() of field/row terminators, which obviously won't work here */
			fprintf(stderr, "bsqlodbc, line %d: NULL terminators ('\\0') not yet supported.\n", __LINE__);
			escaped = '\0';
			break;
		case 't':
			escaped = '\t';
			break;
		case 'r':
			escaped = '\r';
			break;
		case 'n':
			escaped = '\n';
			break;
		case '\\':
			escaped = '\\';
			break;
		default:
			break;
		}
			
		/* Overwrite the backslash with the intended character, and shift everything down one */
		if (!isdigit((unsigned char) escaped)) {
			*p++ = escaped;
			memmove(p, p+1, 1 + strlen(p+1));
			escaped = '1';
		}
	}
}

static LOGINREC *
get_login(int argc, char *argv[], OPTIONS *options)
{
	LOGINREC *login;
	int ch;

	extern char *optarg;

	assert(options && argv);
	
	options->appname = tds_basename(argv[0]);
	options->colsep = default_colsep; /* may be overridden by -t */
	
	login = calloc(1, sizeof(LOGINREC));
	
	if (!login) {
		fprintf(stderr, "%s: unable to allocate login structure\n", options->appname);
		exit(1);
	}
	
	if (-1 == gethostname(options->hostname, sizeof(options->hostname))) {
		perror("unable to get hostname");
	} 

	while ((ch = getopt(argc, argv, "U:P:S:dD:i:o:e:t:hqv")) != -1) {
		switch (ch) {
		case 'U':
			login->username = strdup(optarg);
			break;
		case 'P':
			login->password = strdup(optarg);
			break;
		case 'S':
			options->servername = strdup(optarg);
			break;
		case 'd':
		case 'D':
			options->database = strdup(optarg);
			break;
		case 'i':
			options->input_filename = strdup(optarg);
			break;
		case 'o':
			options->output_filename = strdup(optarg);
			break;
		case 'e':
			options->error_filename = strdup(optarg);
			break;
		case 't':
			unescape(optarg);
			options->colsep = strdup(optarg);
			break;
		case 'h':
			options->headers = stdout;
			break;
		case 'q':
			options->fquiet = 1;
			break;
		case 'v':
			options->fverbose = 1;
			break;
		case '?':
		default:
			usage(options->appname);
			exit(1);
		}
	}
	
	if (!options->servername) {
		usage(options->appname);
		exit(1);
	}
	
	return login;
}