Description:
Here you have my patch for csv (comma separeted value) format, it's very usefull in perl, bash, awk scripts, i use it from about two years in dozen of scripts.
Default separator is ';', I think it will be good if this will be configurable via comand line arguments. There can be problem if field have ; in his body. I use ',' (comma) for separation of data in one field. In some case other separator will be better.
Regreads, Witold Baryluk
--- ./mysql.cc.org 2004-09-07 00:29:37.000000000 +0200
+++ ./mysql.cc 2004-09-22 21:57:39.000000000 +0200
@@ -127,7 +127,7 @@
rehash=1,skip_updates=0,safe_updates=0,one_database=0,
opt_compress=0, using_opt_local_infile=0,
vertical=0, line_numbers=1, column_names=1,opt_html=0,
- opt_xml=0,opt_nopager=1, opt_outfile=0, named_cmds= 0,
+ opt_xml=0,opt_csv=0,opt_nopager=1, opt_outfile=0, named_cmds= 0,
tty_password= 0, opt_nobeep=0;
static ulong opt_max_allowed_packet, opt_net_buffer_length;
static uint verbose=0,opt_silent=0,opt_mysql_port=0, opt_local_infile=0;
@@ -148,6 +148,8 @@
"<", "<",
0, 0
};
+static char *csv_separator=";";
+static my_bool csv_show_lines=0;
static const char *day_names[]={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"};
static const char *month_names[]={"Jan","Feb","Mar","Apr","May","Jun","Jul",
"Aug","Sep","Oct","Nov","Dec"};
@@ -285,6 +287,7 @@
static void print_table_data(MYSQL_RES *result);
static void print_table_data_html(MYSQL_RES *result);
static void print_table_data_xml(MYSQL_RES *result);
+static void print_table_data_csv(MYSQL_RES *result);
static void print_tab_data(MYSQL_RES *result);
static void print_table_data_vertically(MYSQL_RES *result);
static ulong start_timer(void);
@@ -492,6 +495,8 @@
0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
{"xml", 'X', "Produce XML output", (gptr*) &opt_xml, (gptr*) &opt_xml, 0,
GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
+ {"csv", 'c', "Produce CSV output", (gptr*) &opt_csv, (gptr*) &opt_csv, 0,
+ GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
{"line-numbers", OPT_LINE_NUMBERS, "Write line numbers for errors.",
(gptr*) &line_numbers, (gptr*) &line_numbers, 0, GET_BOOL,
NO_ARG, 1, 0, 0, 0, 0, 0},
@@ -1504,6 +1509,8 @@
print_table_data_html(result);
else if (opt_xml)
print_table_data_xml(result);
+ else if (opt_csv)
+ print_table_data_csv(result);
else if (vertical)
print_table_data_vertically(result);
else if (opt_silent && verbose <= 2 && !output_tables)
@@ -1693,9 +1700,10 @@
MYSQL_FIELD *field;
mysql_field_seek(result,0);
- (void) tee_fputs("<TABLE BORDER=1><TR>", PAGER);
+ (void) tee_fputs("<TABLE BORDER=1>", PAGER);
if (column_names)
{
+ (void) tee_fputs("<TR>", PAGER);
while((field = mysql_fetch_field(result)))
{
tee_fprintf(PAGER, "<TH>%s</TH>", (field->name ?
@@ -1752,6 +1760,55 @@
(void) tee_fputs("</resultset>\n", PAGER);
}
+static void
+print_table_data_csv(MYSQL_RES *result)
+{
+ MYSQL_ROW cur;
+ MYSQL_FIELD *field;
+ ulong *lengths;
+ int first=0;
+
+ mysql_field_seek(result,0);
+
+ /* Columns */
+ if (column_names)
+ {
+ first = 0;
+ if (csv_show_lines) {
+ (void) tee_fputs("line_number", PAGER);
+ (void) tee_fputs(csv_separator, PAGER);
+ first++;
+ }
+ while((field = mysql_fetch_field(result)))
+ {
+ if(first++) {
+ (void) tee_fputs(csv_separator, PAGER);
+ }
+ tee_fprintf(PAGER, "%s", (field->name ? (field->name[0] ? field->name : " ") : "NULL"));
+ }
+ (void) tee_fputs("\n",PAGER);
+ }
+
+ /* Fields */
+ while ((cur = mysql_fetch_row(result)))
+ {
+ first = 0;
+ lengths = mysql_fetch_lengths(result);
+ if (csv_show_lines) {
+ (void) tee_fputs("line_number", PAGER);
+ (void) tee_fputs(csv_separator, PAGER);
+ first++;
+ }
+ for (uint i=0; i < mysql_num_fields(result); i++)
+ {
+ if(first++) {
+ (void) tee_fputs(csv_separator, PAGER);
+ }
+ safe_put_field(cur[i],lengths[i]);
+ }
+ (void) tee_fputs("\n", PAGER);
+ }
+}
static void
print_table_data_vertically(MYSQL_RES *result)
How to repeat:
Apply my patch
Suggested fix:
Main branch
Description: Here you have my patch for csv (comma separeted value) format, it's very usefull in perl, bash, awk scripts, i use it from about two years in dozen of scripts. Default separator is ';', I think it will be good if this will be configurable via comand line arguments. There can be problem if field have ; in his body. I use ',' (comma) for separation of data in one field. In some case other separator will be better. Regreads, Witold Baryluk --- ./mysql.cc.org 2004-09-07 00:29:37.000000000 +0200 +++ ./mysql.cc 2004-09-22 21:57:39.000000000 +0200 @@ -127,7 +127,7 @@ rehash=1,skip_updates=0,safe_updates=0,one_database=0, opt_compress=0, using_opt_local_infile=0, vertical=0, line_numbers=1, column_names=1,opt_html=0, - opt_xml=0,opt_nopager=1, opt_outfile=0, named_cmds= 0, + opt_xml=0,opt_csv=0,opt_nopager=1, opt_outfile=0, named_cmds= 0, tty_password= 0, opt_nobeep=0; static ulong opt_max_allowed_packet, opt_net_buffer_length; static uint verbose=0,opt_silent=0,opt_mysql_port=0, opt_local_infile=0; @@ -148,6 +148,8 @@ "<", "<", 0, 0 }; +static char *csv_separator=";"; +static my_bool csv_show_lines=0; static const char *day_names[]={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}; static const char *month_names[]={"Jan","Feb","Mar","Apr","May","Jun","Jul", "Aug","Sep","Oct","Nov","Dec"}; @@ -285,6 +287,7 @@ static void print_table_data(MYSQL_RES *result); static void print_table_data_html(MYSQL_RES *result); static void print_table_data_xml(MYSQL_RES *result); +static void print_table_data_csv(MYSQL_RES *result); static void print_tab_data(MYSQL_RES *result); static void print_table_data_vertically(MYSQL_RES *result); static ulong start_timer(void); @@ -492,6 +495,8 @@ 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, {"xml", 'X', "Produce XML output", (gptr*) &opt_xml, (gptr*) &opt_xml, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, + {"csv", 'c', "Produce CSV output", (gptr*) &opt_csv, (gptr*) &opt_csv, 0, + GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, {"line-numbers", OPT_LINE_NUMBERS, "Write line numbers for errors.", (gptr*) &line_numbers, (gptr*) &line_numbers, 0, GET_BOOL, NO_ARG, 1, 0, 0, 0, 0, 0}, @@ -1504,6 +1509,8 @@ print_table_data_html(result); else if (opt_xml) print_table_data_xml(result); + else if (opt_csv) + print_table_data_csv(result); else if (vertical) print_table_data_vertically(result); else if (opt_silent && verbose <= 2 && !output_tables) @@ -1693,9 +1700,10 @@ MYSQL_FIELD *field; mysql_field_seek(result,0); - (void) tee_fputs("<TABLE BORDER=1><TR>", PAGER); + (void) tee_fputs("<TABLE BORDER=1>", PAGER); if (column_names) { + (void) tee_fputs("<TR>", PAGER); while((field = mysql_fetch_field(result))) { tee_fprintf(PAGER, "<TH>%s</TH>", (field->name ? @@ -1752,6 +1760,55 @@ (void) tee_fputs("</resultset>\n", PAGER); } +static void +print_table_data_csv(MYSQL_RES *result) +{ + MYSQL_ROW cur; + MYSQL_FIELD *field; + ulong *lengths; + int first=0; + + mysql_field_seek(result,0); + + /* Columns */ + if (column_names) + { + first = 0; + if (csv_show_lines) { + (void) tee_fputs("line_number", PAGER); + (void) tee_fputs(csv_separator, PAGER); + first++; + } + while((field = mysql_fetch_field(result))) + { + if(first++) { + (void) tee_fputs(csv_separator, PAGER); + } + tee_fprintf(PAGER, "%s", (field->name ? (field->name[0] ? field->name : " ") : "NULL")); + } + (void) tee_fputs("\n",PAGER); + } + + /* Fields */ + while ((cur = mysql_fetch_row(result))) + { + first = 0; + lengths = mysql_fetch_lengths(result); + if (csv_show_lines) { + (void) tee_fputs("line_number", PAGER); + (void) tee_fputs(csv_separator, PAGER); + first++; + } + for (uint i=0; i < mysql_num_fields(result); i++) + { + if(first++) { + (void) tee_fputs(csv_separator, PAGER); + } + safe_put_field(cur[i],lengths[i]); + } + (void) tee_fputs("\n", PAGER); + } +} static void print_table_data_vertically(MYSQL_RES *result) How to repeat: Apply my patch Suggested fix: Main branch