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 %-10s %-18s %-6s %-6s \n", "col", "name", "type value", "type name", "size", "varies"); fprintf(options.verbose, "%.6s %.30s %.10s %.18s %.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; if (is_character_data(metadata[c].type)) { SQLHDESC hDesc; SQLINTEGER buflen; metadata[c].nchars = metadata[c].size; if ((erc = SQLAllocHandle(SQL_HANDLE_DESC, hStmt, &hDesc)) != SQL_SUCCESS) { odbc_perror(hStmt, erc, "SQLAllocHandle", "failed"); exit(EXIT_FAILURE); } if ((erc = SQLGetDescField(hDesc, c+1, SQL_DESC_OCTET_LENGTH, &metadata[c].size, sizeof(metadata[c].size), &buflen)) != SQL_SUCCESS) { odbc_perror(hStmt, erc, "SQLGetDescField", "failed"); exit(EXIT_FAILURE); } if ((erc = SQLFreeHandle(SQL_HANDLE_DESC, hStmt)) != SQL_SUCCESS) { odbc_perror(hStmt, erc, "SQLFreeHandle", "failed"); exit(EXIT_FAILURE); } } fprintf(options.verbose, "%6d %30s %10d %18s %6lu %6d \n", c+1, metadata[c].name, (int)metadata[c].type, prtype(metadata[c].type), (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, bufsize(&metadata[c])); assert(data[c].buffer); if ((erc = SQLBindCol(hStmt, c+1, SQL_C_CHAR, (SQLPOINTER)data[c].buffer, bufsize(&metadata[c]), &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_WITH_INFO: print_error_message(SQL_HANDLE_STMT, hStmt); case SQL_SUCCESS: break; 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); } }
static void print_results(DBPROCESS *dbproc) { static const char empty_string[] = ""; static const char dashes[] = "----------------------------------------------------------------" /* each line is 64 */ "----------------------------------------------------------------" "----------------------------------------------------------------" "----------------------------------------------------------------"; struct METADATA *metadata = NULL, return_status; struct DATA { char *buffer; int status; } *data = NULL; struct METACOMP { int numalts; struct METADATA *meta; struct DATA *data; } **metacompute = NULL; RETCODE erc; int row_code; int i, c, ret; int iresultset; int ncomputeids = 0, ncols = 0; /* * If using default column separator, we want columns to line up vertically, * so we use blank padding (STRINGBIND). * For any other separator, we use no padding. */ const int bindtype = (0 == strcmp(options.colsep, default_colsep))? STRINGBIND : NTBSTRINGBIND; /* * Set up each result set with dbresults() * This is more commonly implemented as a while() loop, but we're counting the result sets. */ fprintf(options.verbose, "%s:%d: calling dbresults: OK\n", options.appname, __LINE__); for (iresultset=1; (erc = dbresults(dbproc)) != NO_MORE_RESULTS; iresultset++) { if (erc == FAIL) { fprintf(stderr, "%s:%d: dbresults(), result set %d failed\n", options.appname, __LINE__, iresultset); return; } if (options.pivot.func) { const struct key_t *rk = &options.pivot.row_key, *ck = &options.pivot.col_key; erc = dbpivot(dbproc, rk->nkeys, rk->keys, ck->nkeys, ck->keys, options.pivot.func, options.pivot.val_col); } fprintf(options.verbose, "Result set %d\n", iresultset); /* Free prior allocations, if any. */ for (c=0; c < ncols; c++) { free(metadata[c].format_string); free(data[c].buffer); } free(metadata); metadata = NULL; free(data); data = NULL; ncols = 0; for (i=0; i < ncomputeids; i++) { for (c=0; c < metacompute[i]->numalts; c++) { free(metacompute[i]->meta[c].name); free(metacompute[i]->meta[c].format_string); } free(metacompute[i]->meta); free(metacompute[i]->data); free(metacompute[i]); } free(metacompute); metacompute = NULL; ncomputeids = 0; /* * Allocate memory for metadata and bound columns */ fprintf(options.verbose, "Allocating buffers\n"); ncols = dbnumcols(dbproc); metadata = (struct METADATA*) calloc(ncols, sizeof(struct METADATA)); assert(metadata); data = (struct DATA*) calloc(ncols, sizeof(struct DATA)); assert(data); /* metadata is more complicated only because there may be several compute ids for each result set */ fprintf(options.verbose, "Allocating compute buffers\n"); ncomputeids = dbnumcompute(dbproc); if (ncomputeids > 0) { metacompute = (struct METACOMP**) calloc(ncomputeids, sizeof(struct METACOMP*)); assert(metacompute); } for (i=0; i < ncomputeids; i++) { metacompute[i] = (struct METACOMP*) calloc(ncomputeids, sizeof(struct METACOMP)); assert(metacompute[i]); metacompute[i]->numalts = dbnumalts(dbproc, 1+i); fprintf(options.verbose, "%d columns found in computeid %d\n", metacompute[i]->numalts, 1+i); if (metacompute[i]->numalts > 0) { fprintf(options.verbose, "allocating column %d\n", 1+i); metacompute[i]->meta = (struct METADATA*) calloc(metacompute[i]->numalts, sizeof(struct METADATA)); assert(metacompute[i]->meta); metacompute[i]->data = (struct DATA*) calloc(metacompute[i]->numalts, sizeof(struct DATA)); assert(metacompute[i]->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 db-lib the address of the buffer we want filled as each row is fetched. * TODO: Implement dbcoltypeinfo() for numeric/decimal datatypes. */ 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. */ char *name = dbcolname(dbproc, c+1); metadata[c].name = strdup(name ? (const char *) name : empty_string); name = dbcolsource(dbproc, c+1); metadata[c].source = (name)? name : empty_string; metadata[c].type = dbcoltype(dbproc, c+1); metadata[c].size = dbcollen(dbproc, c+1); assert(metadata[c].size != -1); /* -1 means indicates an out-of-range request*/ 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)); /* * 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?" */ 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); 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 db-lib 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. */ if (metadata[c].width < INT_MAX) { data[c].buffer = calloc(1, 1 + metadata[c].width); /* allow for null terminator */ assert(data[c].buffer); erc = dbbind(dbproc, c+1, bindtype, 0, (BYTE *) data[c].buffer); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbbind(), column %d failed\n", options.appname, __LINE__, c+1); return; } erc = dbnullbind(dbproc, c+1, &data[c].status); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbnullbind(), column %d failed\n", options.appname, __LINE__, c+1); return; } } else { /* We don't bind text buffers, but use dbreadtext instead. */ data[c].buffer = NULL; } } /* * Get metadata and bind the columns for any compute rows. */ for (i=0; i < ncomputeids; i++) { fprintf(options.verbose, "For computeid %d:\n", 1+i); for (c=0; c < metacompute[i]->numalts; c++) { /* read metadata */ struct METADATA *meta = &metacompute[i]->meta[c]; int nby, iby; BYTE *bylist; char *colname, *bynames; int altcolid = dbaltcolid(dbproc, i+1, c+1); metacompute[i]->meta[c].type = dbalttype(dbproc, i+1, c+1); metacompute[i]->meta[c].size = dbaltlen(dbproc, i+1, c+1); /* * Jump through hoops to determine a useful name for the computed column * If the query says "compute count(c) by a,b", we get a "by list" indicating a & b. */ bylist = dbbylist(dbproc, c+1, &nby); bynames = strdup("by ("); for (iby=0; iby < nby; iby++) { char *s = NULL; int ret = asprintf(&s, "%s%s%s", bynames, dbcolname(dbproc, bylist[iby]), (iby+1 < nby)? ", " : ")"); if (ret < 0) { fprintf(options.verbose, "Insufficient room to create name for column %d:\n", 1+c); break; } free(bynames); bynames = s; } if( altcolid == -1 ) { colname = "*"; } else { assert(0 < altcolid && altcolid <= dbnumcols(dbproc)); colname = metadata[--altcolid].name; } asprintf(&metacompute[i]->meta[c].name, "%s(%s)", dbprtype(dbaltop(dbproc, i+1, c+1)), colname); assert(metacompute[i]->meta[c].name); metacompute[i]->meta[c].width = get_printable_size(metacompute[i]->meta[c].type, metacompute[i]->meta[c].size); if (metacompute[i]->meta[c].width < strlen(metacompute[i]->meta[c].name)) metacompute[i]->meta[c].width = strlen(metacompute[i]->meta[c].name); ret = set_format_string(meta, (c+1 < metacompute[i]->numalts)? options.colsep : "\n"); if (ret <= 0) { free(bynames); fprintf(stderr, "%s:%d: asprintf(), column %d failed\n", options.appname, __LINE__, c+1); return; } fprintf(options.verbose, "\tcolumn %d is %s, type %s, size %d %s\n", c+1, metacompute[i]->meta[c].name, dbprtype(metacompute[i]->meta[c].type), metacompute[i]->meta[c].size, (nby > 0)? bynames : ""); free(bynames); /* allocate buffer */ assert(metacompute[i]->data); metacompute[i]->data[c].buffer = calloc(1, metacompute[i]->meta[c].width); assert(metacompute[i]->data[c].buffer); /* bind */ erc = dbaltbind(dbproc, i+1, c+1, bindtype, -1, (BYTE*) metacompute[i]->data[c].buffer); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbaltbind(), column %d failed\n", options.appname, __LINE__, c+1); return; } } } fprintf(options.verbose, "\n"); fprintf(options.verbose, "Data\n"); 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 ((row_code = dbnextrow(dbproc)) != NO_MORE_ROWS) { switch (row_code) { case REG_ROW: for (c=0; c < ncols; c++) { if (metadata[c].width == INT_MAX) { /* TEXT/IMAGE */ BYTE *p = dbdata(dbproc, c+1); size_t len = dbdatlen(dbproc, c+1); if (len == 0) { fputs("NULL", stdout); } else { BYTE *pend = p + len; switch(dbcoltype(dbproc, c+1)) { case SYBTEXT: if (fwrite(p, len, 1, stdout) != 1) { perror("could not write to output file"); exit(EXIT_FAILURE); } break; default: /* image, binary */ fprintf(stdout, "0x"); for (; p < pend; p++) { printf("%02hx", (unsigned short int)*p); } break; } } fprintf(stdout, metadata[c].format_string, ""); /* col/row separator */ continue; } switch (data[c].status) { /* handle nulls */ case -1: /* is null */ /* TODO: FreeTDS 0.62 does not support dbsetnull() */ fprintf(stdout, metadata[c].format_string, "NULL"); break; case 0: /* case >1 is datlen when buffer is too small */ default: fprintf(stdout, metadata[c].format_string, data[c].buffer); break; } } break; case BUF_FULL: assert(row_code != BUF_FULL); break; case FAIL: fprintf(stderr, "bsqldb: fatal error: dbnextrow returned FAIL\n"); assert(row_code != FAIL); exit(EXIT_FAILURE); break; default: /* computeid */ fprintf(options.verbose, "Data for computeid %d\n", row_code); for (c=0; c < metacompute[row_code-1]->numalts; c++) { char fmt[256] = "%-"; struct METADATA *meta = &metacompute[row_code-1]->meta[c]; /* left justify the names */ strcat(fmt, &meta->format_string[1]); fprintf(options.headers, fmt, meta->name); } /* Underline the column headers. */ for (c=0; c < metacompute[row_code-1]->numalts; c++) { fprintf(options.headers, metacompute[row_code-1]->meta[c].format_string, dashes); } for (c=0; c < metacompute[row_code-1]->numalts; c++) { struct METADATA *meta = &metacompute[row_code-1]->meta[c]; struct DATA *data = &metacompute[row_code-1]->data[c]; switch (data->status) { /* handle nulls */ case -1: /* is null */ /* TODO: FreeTDS 0.62 does not support dbsetnull() */ fprintf(stdout, meta->format_string, "NULL"); break; case 0: /* case >1 is datlen when buffer is too small */ default: fprintf(stdout, meta->format_string, data->buffer); break; } } } } /* Check return status */ if (!options.fquiet) { fprintf(options.verbose, "Retrieving return status... "); if (dbhasretstat(dbproc) == TRUE) { fprintf(stderr, "Procedure returned %d\n", dbretstatus(dbproc)); } else { fprintf(options.verbose, "none\n"); } } /* * Get row count, if available. */ if (!options.fquiet) { if (DBCOUNT(dbproc) > -1) fprintf(stderr, "%d rows affected\n", DBCOUNT(dbproc)); else fprintf(stderr, "@@rowcount not available\n"); } /* * Check return parameter values */ fprintf(options.verbose, "Retrieving output parameters... "); if (dbnumrets(dbproc) > 0) { for (i = 1; i <= dbnumrets(dbproc); i++) { char parameter_string[1024]; return_status.name = dbretname(dbproc, i); fprintf(stderr, "ret name %d is %s\n", i, return_status.name); return_status.type = dbrettype(dbproc, i); fprintf(options.verbose, "\n\tret type %d is %d", i, return_status.type); return_status.size = dbretlen(dbproc, i); fprintf(options.verbose, "\n\tret len %d is %d\n", i, return_status.size); dbconvert(dbproc, return_status.type, dbretdata(dbproc, i), return_status.size, SYBVARCHAR, (BYTE *) parameter_string, -1); fprintf(stderr, "ret data %d is %s\n", i, parameter_string); } } else { fprintf(options.verbose, "none\n"); } } /* wend dbresults */ fprintf(options.verbose, "%s:%d: dbresults() returned NO_MORE_RESULTS (%d):\n", options.appname, __LINE__, erc); }
int /* return count of SQL text rows */ print_results(DBPROCESS *dbproc) { static const char empty_string[] = ""; struct METADATA *metadata = NULL; struct DATA { char *buffer; int status; } *data = NULL; RETCODE erc; int row_code; int c, ret; int iresultset; int nrows=0, ncols=0, ncomputeids; /* * Set up each result set with dbresults() */ for (iresultset=1; (erc = dbresults(dbproc)) != NO_MORE_RESULTS; iresultset++) { if (erc == FAIL) { fprintf(stderr, "%s:%d: dbresults(), result set %d failed\n", options.appname, __LINE__, iresultset); return -1; } if (SUCCEED != dbrows(dbproc)) { return 0; } /* Free prior allocations, if any. */ for (c=0; c < ncols; c++) { free(metadata[c].format_string); free(data[c].buffer); } free(metadata); metadata = NULL; free(data); data = NULL; ncols = 0; /* * Allocate memory for metadata and bound columns */ ncols = dbnumcols(dbproc); metadata = (struct METADATA*) calloc(ncols, sizeof(struct METADATA)); assert(metadata); data = (struct DATA*) calloc(ncols, sizeof(struct DATA)); assert(data); /* The hard-coded queries don't generate compute rows. */ ncomputeids = dbnumcompute(dbproc); assert(0 == ncomputeids); /* * 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 db-lib the address of the buffer we want filled as each row is fetched. */ for (c=0; c < ncols; c++) { int width; /* Get and print the metadata. Optional: get only what you need. */ char *name = dbcolname(dbproc, c+1); metadata[c].name = (name)? name : (char*) empty_string; name = dbcolsource(dbproc, c+1); metadata[c].source = (name)? name : (char*) empty_string; metadata[c].type = dbcoltype(dbproc, c+1); metadata[c].size = dbcollen(dbproc, c+1); assert(metadata[c].size != -1); /* -1 means indicates an out-of-range request*/ #if 0 fprintf(stderr, "%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)); #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?" */ width = get_printable_size(metadata[c].type, metadata[c].size); if (width < strlen(metadata[c].name)) width = strlen(metadata[c].name); ret = set_format_string(&metadata[c], (c+1 < ncols)? " " : "\n"); if (ret <= 0) { fprintf(stderr, "%s:%d: asprintf(), column %d failed\n", options.appname, __LINE__, c+1); return -1; } /* * Bind the column to our variable. * We bind everything to strings, because we want db-lib 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].size); assert(data[c].buffer); erc = dbbind(dbproc, c+1, STRINGBIND, -1, (BYTE *) data[c].buffer); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbbind(), column %d failed\n", options.appname, __LINE__, c+1); return -1; } erc = dbnullbind(dbproc, c+1, &data[c].status); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbnullbind(), column %d failed\n", options.appname, __LINE__, c+1); return -1; } } /* * Print the data to stdout. */ for (;(row_code = dbnextrow(dbproc)) != NO_MORE_ROWS; nrows++) { switch (row_code) { case REG_ROW: for (c=0; c < ncols; c++) { switch (data[c].status) { /* handle nulls */ case -1: /* is null */ fprintf(stderr, "defncopy: error: unexpected NULL row in SQL text\n"); break; case 0: /* OK */ default: /* >1 is datlen when buffer is too small */ fprintf(stdout, "%s", data[c].buffer); break; } } break; case BUF_FULL: default: fprintf(stderr, "defncopy: error: expected REG_ROW (%d), got %d instead\n", REG_ROW, row_code); assert(row_code == REG_ROW); break; } /* row_code */ } /* wend dbnextrow */ fprintf(stdout, "\n"); } /* wend dbresults */ return nrows; }
void print_results(DBPROCESS *dbproc) { static const char empty_string[] = ""; static const char dashes[] = "----------------------------------------------------------------" /* each line is 64 */ "----------------------------------------------------------------" "----------------------------------------------------------------" "----------------------------------------------------------------"; struct METADATA *metadata = NULL, return_status; struct DATA { char *buffer; int status; } *data = NULL; struct METACOMP { int numalts; struct METADATA *meta; struct DATA *data; } **metacompute = NULL; RETCODE erc; int row_code; int i, c, ret; int iresultset; int ncomputeids = 0, ncols = 0; /* * Set up each result set with dbresults() * This is more commonly implemented as a while() loop, but we're counting the result sets. */ fprintf(options.verbose, "%s:%d: calling dbresults OK:\n", options.appname, __LINE__); for (iresultset=1; (erc = dbresults(dbproc)) != NO_MORE_RESULTS; iresultset++) { if (erc == FAIL) { fprintf(stderr, "%s:%d: dbresults(), result set %d failed\n", options.appname, __LINE__, iresultset); return; } fprintf(options.verbose, "Result set %d\n", iresultset); /* Free prior allocations, if any. */ fprintf(options.verbose, "Freeing prior allocations\n", iresultset); for (c=0; c < ncols; c++) { free(metadata[c].format_string); free(data[c].buffer); } free(metadata); metadata = NULL; free(data); data = NULL; ncols = 0; for (i=0; i < ncomputeids; i++) { for (c=0; c < metacompute[i]->numalts; c++) { free(metacompute[i]->meta[c].name); free(metacompute[i]->meta[c].format_string); } free(metacompute[i]->meta); free(metacompute[i]->data); free(metacompute[i]); } free(metacompute); metacompute = NULL; ncomputeids = 0; /* * Allocate memory for metadata and bound columns */ fprintf(options.verbose, "Allocating buffers\n", iresultset); ncols = dbnumcols(dbproc); metadata = (struct METADATA*) calloc(ncols, sizeof(struct METADATA)); assert(metadata); data = (struct DATA*) calloc(ncols, sizeof(struct DATA)); assert(data); /* metadata is more complicated only because there may be several compute ids for each result set */ fprintf(options.verbose, "Allocating compute buffers\n", iresultset); ncomputeids = dbnumcompute(dbproc); if (ncomputeids > 0) { metacompute = (struct METACOMP**) calloc(ncomputeids, sizeof(struct METACOMP*)); assert(metacompute); } for (i=0; i < ncomputeids; i++) { metacompute[i] = (struct METACOMP*) calloc(ncomputeids, sizeof(struct METACOMP)); assert(metacompute[i]); metacompute[i]->numalts = dbnumalts(dbproc, 1+i); fprintf(options.verbose, "%d columns found in computeid %d\n", metacompute[i]->numalts, 1+i); if (metacompute[i]->numalts > 0) { fprintf(options.verbose, "allocating column %d\n", 1+i); metacompute[i]->meta = (struct METADATA*) calloc(metacompute[i]->numalts, sizeof(struct METADATA)); assert(metacompute[i]->meta); metacompute[i]->data = (struct DATA*) calloc(metacompute[i]->numalts, sizeof(struct DATA)); assert(metacompute[i]->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 db-lib the address of the buffer we want filled as each row is fetched. * TODO: Implement dbcoltypeinfo() for numeric/decimal datatypes. */ fprintf(options.verbose, "Metadata\n", iresultset); fprintf(options.verbose, "%-6s %-30s %-30s %-15s %-6s %-6s \n", "col", "name", "source", "type", "size", "varys"); fprintf(options.verbose, "%.6s %.30s %.30s %.15s %.6s %.6s \n", dashes, dashes, dashes, dashes, dashes, dashes); for (c=0; c < ncols; c++) { int width; /* Get and print the metadata. Optional: get only what you need. */ char *name = dbcolname(dbproc, c+1); metadata[c].name = (name)? name : empty_string; name = dbcolsource(dbproc, c+1); metadata[c].source = (name)? name : empty_string; metadata[c].type = dbcoltype(dbproc, c+1); metadata[c].size = dbcollen(dbproc, c+1); assert(metadata[c].size != -1); /* -1 means indicates an out-of-range request*/ 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)); /* * 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?" */ width = get_printable_size(metadata[c].type, metadata[c].size); if (width < strlen(metadata[c].name)) width = strlen(metadata[c].name); ret = set_format_string(&metadata[c], (c+1 < ncols)? " " : "\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 db-lib 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].size); assert(data[c].buffer); erc = dbbind(dbproc, c+1, STRINGBIND, -1, (BYTE *) data[c].buffer); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbbind(), column %d failed\n", options.appname, __LINE__, c+1); return; } erc = dbnullbind(dbproc, c+1, &data[c].status); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbnullbind(), column %d failed\n", options.appname, __LINE__, c+1); return; } } /* * Get metadata and bind the columns for any compute rows. */ for (i=0; i < ncomputeids; i++) { fprintf(options.verbose, "For computeid %d:\n", 1+i); for (c=0; c < metacompute[i]->numalts; c++) { /* read metadata */ struct METADATA *meta = &metacompute[i]->meta[c]; int nbylist, ibylist; BYTE *bylist; char *colname, bynames[256] = "by ("; int altcolid = dbaltcolid(dbproc, i+1, c+1); metacompute[i]->meta[c].type = dbalttype(dbproc, i+1, c+1); metacompute[i]->meta[c].size = dbaltlen(dbproc, i+1, c+1); /* * Jump through hoops to determine a useful name for the computed column * If the query says "compute count(c) by a,b", we get a "by list" indicating a & b. */ bylist = dbbylist(dbproc, c+1, &nbylist); for (ibylist=0; ibylist < nbylist; ibylist++) { int ret; char *s = strchr(bynames, '\0'); int remaining = bynames + sizeof(bynames) - s; assert(remaining > 0); ret = snprintf(s, remaining, "%s%s", dbcolname(dbproc, bylist[ibylist]), (ibylist+1 < nbylist)? ", " : ")"); if (ret <= 0) { fprintf(options.verbose, "Insufficient room to create name for column %d:\n", 1+c); break; } } if( altcolid == -1 ) { colname = "*"; } else { colname = metadata[altcolid].name; } asprintf(&metacompute[i]->meta[c].name, "%s(%s)", dbprtype(dbaltop(dbproc, i+1, c+1)), colname); assert(metacompute[i]->meta[c].name); ret = set_format_string(meta, (c+1 < metacompute[i]->numalts)? " " : "\n"); if (ret <= 0) { fprintf(stderr, "%s:%d: asprintf(), column %d failed\n", options.appname, __LINE__, c+1); return; } fprintf(options.verbose, "\tcolumn %d is %s, type %s, size %d %s\n", c+1, metacompute[i]->meta[c].name, dbprtype(metacompute[i]->meta[c].type), metacompute[i]->meta[c].size, (nbylist > 0)? bynames : ""); /* allocate buffer */ assert(metacompute[i]->data); metacompute[i]->data[c].buffer = calloc(1, metacompute[i]->meta[c].size); assert(metacompute[i]->data[c].buffer); /* bind */ erc = dbaltbind(dbproc, i+1, c+1, STRINGBIND, -1, metacompute[i]->data[c].buffer); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbaltbind(), column %d failed\n", options.appname, __LINE__, c+1); return; } } } fprintf(options.verbose, "\n"); fprintf(options.verbose, "Data\n", iresultset); /* Print the column headers to stderr to keep them separate from the data. */ for (c=0; c < ncols; c++) { char fmt[256] = "%-"; /* left justify the names */ strcat(fmt, &metadata[c].format_string[1]); fprintf(stderr, fmt, metadata[c].name); } /* Underline the column headers. */ for (c=0; c < ncols; c++) { fprintf(stderr, metadata[c].format_string, dashes); } /* * Print the data to stdout. */ while ((row_code = dbnextrow(dbproc)) != NO_MORE_ROWS) { switch (row_code) { case REG_ROW: for (c=0; c < ncols; c++) { switch (data[c].status) { /* handle nulls */ case -1: /* is null */ /* TODO: FreeTDS 0.62 does not support dbsetnull() */ fprintf(stdout, metadata[c].format_string, "NULL"); break; case 0: /* case >1 is datlen when buffer is too small */ default: fprintf(stdout, metadata[c].format_string, data[c].buffer); break; } } break; case BUF_FULL: assert(row_code != BUF_FULL); break; default: /* computeid */ fprintf(options.verbose, "Data for computeid %d\n", row_code); for (c=0; c < metacompute[row_code-1]->numalts; c++) { char fmt[256] = "%-"; struct METADATA *meta = &metacompute[row_code-1]->meta[c]; /* left justify the names */ strcat(fmt, &meta->format_string[1]); fprintf(stderr, fmt, meta->name); } /* Underline the column headers. */ for (c=0; c < metacompute[row_code-1]->numalts; c++) { fprintf(stderr, metacompute[row_code-1]->meta[c].format_string, dashes); } for (c=0; c < metacompute[row_code-1]->numalts; c++) { struct METADATA *meta = &metacompute[row_code-1]->meta[c]; struct DATA *data = &metacompute[row_code-1]->data[c]; switch (data->status) { /* handle nulls */ case -1: /* is null */ /* TODO: FreeTDS 0.62 does not support dbsetnull() */ fprintf(stdout, meta->format_string, "NULL"); break; case 0: /* case >1 is datlen when buffer is too small */ default: fprintf(stdout, meta->format_string, data->buffer); break; } } } } /* Check return status */ fprintf(options.verbose, "Retrieving return status... "); if (dbhasretstat(dbproc) == TRUE) { fprintf(stderr, "Procedure returned %d\n", dbretstatus(dbproc)); } else { fprintf(options.verbose, "none\n"); } /* * Get row count, if available. */ if (DBCOUNT(dbproc) > -1) fprintf(stderr, "%d rows affected\n", DBCOUNT(dbproc)); /* * Check return parameter values */ fprintf(options.verbose, "Retrieving output parameters... "); if (dbnumrets(dbproc) > 0) { for (i = 1; i <= dbnumrets(dbproc); i++) { char parameter_string[1024]; return_status.name = dbretname(dbproc, i); fprintf(stderr, "ret name %d is %s\n", i, return_status.name); return_status.type = dbrettype(dbproc, i); fprintf(options.verbose, "\n\tret type %d is %d", i, return_status.type); return_status.size = dbretlen(dbproc, i); fprintf(options.verbose, "\n\tret len %d is %d\n", i, return_status.size); dbconvert(dbproc, return_status.type, dbretdata(dbproc, i), return_status.size, SYBVARCHAR, (BYTE *) parameter_string, -1); fprintf(stderr, "ret data %d is %s\n", i, parameter_string); } } else { fprintf(options.verbose, "none\n"); } } /* wend dbresults */ fprintf(options.verbose, "%s:%d: dbresults() returned NO_MORE_RESULTS (%d):\n", options.appname, __LINE__, erc); }
int main(int argc, char *argv[]) { LOGINREC *login; DBPROCESS *dbproc; int i; RETCODE ret; int big_endian = 1; char *out_file = "t0017.out"; static const char in_file_le[] = FREETDS_SRCDIR "/t0017.in"; static const char in_file_be[] = FREETDS_SRCDIR "/t0017.in.be"; const char *in_file = in_file_le; const char *err_file = "t0017.err"; DBINT rows_copied; int num_cols = 0; int col_type[256]; DBBOOL col_varylen[256]; int prefix_len; if (((char *) &big_endian)[0] == 1) big_endian = 0; if (big_endian) in_file = in_file_be; setbuf(stdout, NULL); setbuf(stderr, NULL); set_malloc_options(); read_login_info(argc, argv); printf("Starting %s\n", software_version); dbinit(); dberrhandle(syb_err_handler); dbmsghandle(syb_msg_handler); printf("About to logon ... "); login = dblogin(); assert(login); BCP_SETL(login, TRUE); DBSETLPWD(login, PASSWORD); DBSETLUSER(login, USER); DBSETLAPP(login, "t0017"); printf("done\n"); printf("Opening \"%s\" for \"%s\" ... ", SERVER, USER); dbproc = dbopen(login, SERVER); assert(dbproc); if (strlen(DATABASE)) { dbuse(dbproc, DATABASE); } dbloginfree(login); printf("done\n"); printf("Creating table ... "); dbcmd(dbproc, "create table #dblib0017 (c1 int null, c2 text)"); dbsqlexec(dbproc); while (dbresults(dbproc) != NO_MORE_RESULTS) { /* nop */ } printf("done\n"); dbcmd(dbproc, "insert into #dblib0017(c1,c2) values(1144201745,'prova di testo questo testo dovrebbe andare a finire in un campo text')"); dbsqlexec(dbproc); while (dbresults(dbproc) != NO_MORE_RESULTS) { /* nop */ } /* BCP out */ printf("bcp_init... "); ret = bcp_init(dbproc, "#dblib0017", out_file, err_file, DB_OUT); if (ret != SUCCEED) failed = 1; printf("done\n"); printf("Issuing SELECT ... "); dbcmd(dbproc, "select * from #dblib0017 where 0=1"); dbsqlexec(dbproc); printf("done\nFetching metadata ... "); if (dbresults(dbproc) != FAIL) { num_cols = dbnumcols(dbproc); for (i = 0; i < num_cols; ++i) { col_type[i] = dbcoltype(dbproc, i + 1); col_varylen[i] = dbvarylen(dbproc, i + 1); } while (dbnextrow(dbproc) != NO_MORE_ROWS) { } } printf("done\n"); printf("bcp_columns ... "); ret = bcp_columns(dbproc, num_cols); if (ret != SUCCEED) failed = 1; for (i = 0; i < num_cols; i++) { prefix_len = 0; if (col_type[i] == SYBIMAGE || col_type[i] == SYBTEXT) { prefix_len = 4; } else if (col_varylen[i]) { prefix_len = 1; } printf("bind %d prefix %d col_type %s\n", i, prefix_len, col_type[i] == SYBIMAGE ? "image" : "other"); ret = bcp_colfmt(dbproc, i + 1, col_type[i], prefix_len, -1, NULL, 0, i + 1); if (ret == FAIL) { fprintf(stderr, "return from bcp_colfmt = %d\n", ret); failed = 1; } } printf("done\n"); rows_copied = -1; printf("bcp_exec ... "); ret = bcp_exec(dbproc, &rows_copied); if (ret != SUCCEED || rows_copied != 1) failed = 1; printf("%d rows copied\n", rows_copied); /* delete rows */ dbcmd(dbproc, "delete from #dblib0017"); dbsqlexec(dbproc); while (dbresults(dbproc) != NO_MORE_RESULTS) { /* nop */ } /* * BCP in */ printf("bcp_init... "); ret = bcp_init(dbproc, "#dblib0017", in_file, err_file, DB_IN); if (ret != SUCCEED) failed = 1; printf("done\n"); printf("Issuing SELECT ... "); dbcmd(dbproc, "select * from #dblib0017 where 0=1"); dbsqlexec(dbproc); printf("done\nFetching metadata ... "); if (dbresults(dbproc) != FAIL) { num_cols = dbnumcols(dbproc); for (i = 0; i < num_cols; i++) { col_type[i] = dbcoltype(dbproc, i + 1); col_varylen[i] = dbvarylen(dbproc, i + 1); } while (dbnextrow(dbproc) != NO_MORE_ROWS) { } } while (dbresults(dbproc) != NO_MORE_RESULTS) { /* nop */ } printf("done\n"); printf("bcp_columns ... "); ret = bcp_columns(dbproc, num_cols); if (ret != SUCCEED) failed = 1; for (i = 0; i < num_cols; i++) { prefix_len = 0; if (col_type[i] == SYBIMAGE || col_type[i] == SYBTEXT) { prefix_len = 4; } else if (col_varylen[i]) { prefix_len = 1; } ret = bcp_colfmt(dbproc, i + 1, col_type[i], prefix_len, -1, NULL, 0, i + 1); if (ret == FAIL) { fprintf(stderr, "return from bcp_colfmt = %d\n", ret); failed = 1; } } printf("done\n"); printf("bcp_exec ... "); rows_copied = -1; ret = bcp_exec(dbproc, &rows_copied); if (ret != SUCCEED || rows_copied != 1) failed = 1; printf("done\n"); /* test we inserted correctly row */ if (!failed) { dbcmd(dbproc, "SET NOCOUNT ON DECLARE @n INT SELECT @n = COUNT(*) FROM #dblib0017 WHERE c1=1144201745 AND c2 LIKE 'prova di testo questo testo dovrebbe andare a finire in un campo text' IF @n <> 1 SELECT 0"); dbsqlexec(dbproc); while (dbresults(dbproc) != NO_MORE_RESULTS) { while ((ret=dbnextrow(dbproc)) != NO_MORE_ROWS) { fprintf(stderr, "Invalid dbnextrow result %d executing query\n", ret); failed = 1; } } } printf("%d rows copied\n", rows_copied); dbclose(dbproc); dbexit(); printf("dblib %s on %s\n", (failed ? "failed!" : "okay"), __FILE__); return failed ? 1 : 0; }