Reg: Extracting the data to CSV format

[sivadhanekula]

Hello Everyone

A quick and direct question: I need a C/C++ program to extract the data from the database and the output should be in CSV "Comma Separated Value" format.
Briefly: I will be given a database and my work is to extract a particular data from that whole database and should get a output in CSV format. The database given to me has 24 tables and I need to extract data from any one of the table and if I press the run button I should get the output directly in CSV format.
Program what I have: The program what I have is just to get the details of each table---[CODE=c]
#include <windows.h>
#include <stdio.h>
#include <string.h>

#include <mysql.h>

#define DEFALT_SQL_STMT "SELECT * FROM db"
#ifndef offsetof
#define offsetof(TYPE, MEMBER) ((size_t) &((TYPE *)0)->MEMBER)
#endif

int
main( int argc, char * argv[] )
{

char szSQL[ 200 ], aszFlds[ 25 ][ 25 ], szDB[ 50 ] ;
const char *pszT;
int i, j, k, l, x ;
MYSQL * myData ;
MYSQL_RES * res ;
MYSQL_FIELD * fd ;
MYSQL_ROW row ;

//....just curious....
printf( "sizeof( MYSQL ) == %d\n", sizeof( MYSQL) ) ;
if ( argc == 2 )
{
strcpy( szDB, argv[ 1 ] ) ;
strcpy( szSQL, DEFALT_SQL_STMT ) ;
if (!strcmp(szDB,"--debug"))
{
strcpy( szDB, "mysql" ) ;
printf("Some mysql struct information (size and offset):\n");
printf("net:\t% 3d %3d\n",sizeof(m yData->net),offsetof( MYSQL,net));
printf("host:\t %3d %3d\n",sizeof(m yData->host),offsetof (MYSQL,host));
printf("port:\t %3d %3d\n",sizeof(m yData->port),offsetof (MYSQL,port));
printf("protoco l_version:\t%3d %3d\n",sizeof(m yData->protocol_versi on),
offsetof(MYSQL, protocol_versio n));
printf("thread_ id:\t%3d %3d\n",sizeof(m yData->thread_id),
offsetof(MYSQL, thread_id));
printf("affecte d_rows:\t%3d %3d\n",sizeof(m yData->affected_rows) ,
offsetof(MYSQL, affected_rows)) ;
printf("packet_ length:\t%3d %3d\n",sizeof(m yData->packet_length) ,
offsetof(MYSQL, packet_length)) ;
printf("status: \t%3d %3d\n",sizeof(m yData->status),
offsetof(MYSQL, status));
printf("fields: \t%3d %3d\n",sizeof(m yData->fields),
offsetof(MYSQL, fields));
printf("field_a lloc:\t%3d %3d\n",sizeof(m yData->field_alloc) ,
offsetof(MYSQL, field_alloc));
printf("free_me :\t%3d %3d\n",sizeof(m yData->free_me),
offsetof(MYSQL, free_me));
printf("options :\t%3d %3d\n",sizeof(m yData->options),
offsetof(MYSQL, options));
puts("");
}
}
else if ( argc > 2 ) {
strcpy( szDB, argv[ 1 ] ) ;
strcpy( szSQL, argv[ 2 ] ) ;
}
else {
strcpy( szDB, "winutms_rt _db" ) ;
strcpy( szSQL, DEFALT_SQL_STMT ) ;
}
//....

if ( (myData = mysql_init((MYS QL*) 0)) &&
mysql_real_conn ect( myData, NULL, NULL, NULL, NULL, MYSQL_PORT,
NULL, 0 ) )
{
if ( mysql_select_db ( myData, szDB ) < 0 ) {
printf( "Can't select the %s database !\n", szDB ) ;
mysql_close( myData ) ;
return 2 ;
}
}
else {
printf( "Can't connect to the mysql server on port %d !\n",
MYSQL_PORT ) ;
mysql_close( myData ) ;
return 1 ;
}
//....
if ( ! mysql_query( myData, szSQL ) ) {
res = mysql_store_res ult( myData ) ;
i = (int) mysql_num_rows( res ) ; l = 1 ;
printf( "Query: %s\nNumber of records found: %ld\n", szSQL, i ) ;
//....we can get the field-specific characteristics here....
for ( x = 0 ; fd = mysql_fetch_fie ld( res ) ; x++ )
strcpy( aszFlds[ x ], fd->name ) ;
//....
while ( row = mysql_fetch_row ( res ) ) {
j = mysql_num_field s( res ) ;
printf( "Record #%ld:-\n", l++ ) ;
for ( k = 0 ; k < j ; k++ )
printf( " Fld #%d (%s): %s\n", k + 1, aszFlds[ k ],
(((row[k]==NULL)||(!strl en(row[k])))?"NULL":row[k])) ;
puts( "============== =============== =\n" ) ;
}
mysql_free_resu lt( res ) ;
}
else printf( "Couldn't execute %s on the server !\n", szSQL ) ;
//....
puts( "==== Diagnostic info ====" ) ;
pszT = mysql_get_clien t_info() ;
printf( "Client info: %s\n", pszT ) ;
//....
pszT = mysql_get_host_ info( myData ) ;
printf( "Host info: %s\n", pszT ) ;
//....
pszT = mysql_get_serve r_info( myData ) ;
printf( "Server info: %s\n", pszT ) ;
//....
res = mysql_list_proc esses( myData ) ; l = 1 ;
if (res)
{
for ( x = 0 ; fd = mysql_fetch_fie ld( res ) ; x++ )
strcpy( aszFlds[ x ], fd->name ) ;
while ( row = mysql_fetch_row ( res ) ) {
j = mysql_num_field s( res ) ;
printf( "Process #%ld:-\n", l++ ) ;
for ( k = 0 ; k < j ; k++ )
printf( " Fld #%d (%s): %s\n", k + 1, aszFlds[ k ],
(((row[k]==NULL)||(!strl en(row[k])))?"NULL":row[k])) ;
puts( "============== =============== =\n" ) ;
}
}
else
{
printf("Got error %s when retreiving processlist\n", mysql_error(myD ata));
}
//....
res = mysql_list_tabl es( myData, "%" ) ; l = 1 ;
for ( x = 0 ; fd = mysql_fetch_fie ld( res ) ; x++ )
strcpy( aszFlds[ x ], fd->name ) ;
while ( row = mysql_fetch_row ( res ) ) { j = mysql_num_field s( res ) ;
printf( "Table #%ld:-\n", l++ ) ;
for ( k = 0 ; k < j ; k++ )
printf( " Fld #%d (%s): %s\n", k + 1, aszFlds[ k ],
(((row[k]==NULL)||(!strl en(row[k])))?"NULL":row[k])) ;
puts( "============== =============== =\n" ) ;
// int sol;
// sol= mysql> SELECT absTestID, Messwert, TestID FROM abstest;
// printf("Resulta nt value is %d\n",sol);
}
//....
pszT = mysql_stat( myData ) ;
puts( pszT ) ;
//....
mysql_close( myData ) ;
return 0 ;

}[/CODE]

Kindly help me in this case as soon as possible

Thanks and Regards in advance
Dhanekula. Siva

[donbock]

Do you have a specific question?
... Are you getting a build error?
... Is the program malfunctioning?

[sivadhanekula]

Thanks for the reply

The given program is running and even I am getting output for that. but my question is how to extract the data from the above program and the output should be in CSV format.
i.e. If I run the above program the output is the list of processes and tables from mydatabase and I need to extract required data from any of the tables so that if I press the Run button I should directly get CSV output....This is what I need

[r035198x]

Looks like you are using tabs to separate your data? You'd need to change those tabs into commas. Outputting to a file is easy. You can then easily redirect your printf's to print to a file instead using something like
[CODE=c]
FILE *fp;
if((fp=freopen( "file.csv", "w" ,stdout))==NULL ) {
printf("Cannot open file.\n");
exit(1);
}
printf("All printfs should now be printing to the file.");

//don't forget somewhere at the end to close the fp
fclose(fp);[/CODE]

EDIT: On second thought, why are you doing all this when MySQL has a handy [CODE=mysql]SELECT INTO OUTFILE[/CODE] command

[sivadhanekula]

Thank you for the reply

I think you didn't understand my ques may be bcoz of my poor english.

My problem is:: I was given a database with lot of tables in it and my work is to extract some data from any one of the table and I have to analyse the data through MINITAB 15(out of question). I can do it directly in MYSQL front end but, my TL asked me to try it in othér way. i.e. he wants a C/C++ code that extracts the data and get the out put as CSV format. For that I have taken the example program that was given in mysql and changed the database and when I am running it I am just getting the list of the tables. Now my question is how to extract a certain table from that and how to extract the data from that table and how can I get it in .CSV format

Thanks in advance
Siva

[r035198x]

You need to use mysql_query (or mysql_real_quer y) for the select statement from the tables themselves then use mysql_use_resul ts to process the results. You can about all that from the refmanual itself.
P.S Your English is fine.

[sivadhanekula]

Thank you

By using the mentioned commands I am able to extract the data from the table but I am not getting the output in CSV format. Can you provide me any code for doing so in C-lan(I think by using file"fprintf")

Regards
Dhanekula.Siva

[r035198x]

Now read my reply #4 above again.

[sivadhanekula]

Hi

ya I have tried with that but I got the following error message

C: \ Program Files \ Microsoft Visual Studio \ MyProject \ winutms \ winutms.cpp (180): error C2664: 'freopen': conversion of the parameter 3 of 'char *' in 'struct _iobuf *' not possible

Can you tell me What is "w" in the given program?
and What is the difference between fopen and freopen?

Thankyou

[Banfa]

Did you put this

if((fp=freopen( "file.csv", "w" ,"stdout"))==NU LL) {

instead of this

if((fp=freopen( "file.csv", "w" ,stdout))==NULL ) {

as post 4 specified?

The "w" instructs the function to create a handle to a writeable file, i.e. an output file not an input file.

[sivadhanekula]

Hmmmm again the same error !!!!!

[r035198x]

Let's see the code you used.
P.S Just post the part around which the error is being reported.

[sivadhanekula]

FILE *fp;
"""if((fp=freop en("file.csv"," w+","row[k]"))== NULL){;"""
printf("cannot open file \n");
exit(1);
}
else printf("All values now printing to the file. \n");
fclose(fp);
}

Near the """quoted """ part

[Banfa]

if((fp=freopen( "file.csv","w+" ,"row[k]"))== NULL){

That is not

if((fp=freopen( "file.csv","w+" , stdout))== NULL){

as was suggested to you. You can not replace the file pointer stdout with an array of characters "row[k]".

You are clearly calling the function without having taken the trouble to find out what it does, I suggest that you read the function reference.

[sivadhanekula]

Ok...But where is my input data.....where can I give that and without giving my input data how can I get the output in CSV format.....