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---

#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(myData->net),offsetof(MYSQL,net));
    printf("host:\t%3d %3d\n",sizeof(myData->host),offsetof(MYSQL,host));
    printf("port:\t%3d %3d\n",sizeof(myData->port),offsetof(MYSQL,port));
    printf("protocol_version:\t%3d %3d\n",sizeof(myData->protocol_version),
           offsetof(MYSQL,protocol_version));
    printf("thread_id:\t%3d %3d\n",sizeof(myData->thread_id),
           offsetof(MYSQL,thread_id));
    printf("affected_rows:\t%3d %3d\n",sizeof(myData->affected_rows),
           offsetof(MYSQL,affected_rows));
    printf("packet_length:\t%3d %3d\n",sizeof(myData->packet_length),
           offsetof(MYSQL,packet_length));
    printf("status:\t%3d %3d\n",sizeof(myData->status),
           offsetof(MYSQL,status));
    printf("fields:\t%3d %3d\n",sizeof(myData->fields),
           offsetof(MYSQL,fields));
    printf("field_alloc:\t%3d %3d\n",sizeof(myData->field_alloc),
           offsetof(MYSQL,field_alloc));
    printf("free_me:\t%3d %3d\n",sizeof(myData->free_me),
           offsetof(MYSQL,free_me));
    printf("options:\t%3d %3d\n",sizeof(myData->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((MYSQL*) 0)) && 
       mysql_real_connect( 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_result( 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_field( res ) ; x++ )
      strcpy( aszFlds[ x ], fd->name ) ;
    //....
    while ( row = mysql_fetch_row( res ) ) {
      j = mysql_num_fields( 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)||(!strlen(row[k])))?"NULL":row[k])) ;
      puts( "==============================\n" ) ;
    }
    mysql_free_result( res ) ;
  }
  else printf( "Couldn't execute %s on the server !\n", szSQL ) ;
  //....
  puts( "====  Diagnostic info  ====" ) ;
  pszT = mysql_get_client_info() ;
  printf( "Client info: %s\n", pszT ) ;
  //....
  pszT = mysql_get_host_info( myData ) ;
  printf( "Host info: %s\n", pszT ) ;
  //....
  pszT = mysql_get_server_info( myData ) ;
  printf( "Server info: %s\n", pszT ) ;
  //....
  res = mysql_list_processes( myData ) ; l = 1 ;
  if (res)
    {
      for ( x = 0 ; fd = mysql_fetch_field( res ) ; x++ )
    strcpy( aszFlds[ x ], fd->name ) ;
      while ( row = mysql_fetch_row( res ) ) {
    j = mysql_num_fields( 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)||(!strlen(row[k])))?"NULL":row[k])) ;
    puts( "==============================\n" ) ;
      }
    }
  else
    {
      printf("Got error %s when retreiving processlist\n",mysql_error(myData));
    }
  //....
  res = mysql_list_tables( myData, "%" ) ; l = 1 ;
  for ( x = 0 ; fd = mysql_fetch_field( res ) ; x++ )
    strcpy( aszFlds[ x ], fd->name ) ;
  while ( row = mysql_fetch_row( res ) ) {    j = mysql_num_fields( 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)||(!strlen(row[k])))?"NULL":row[k])) ;
    puts( "==============================\n" ) ;
//    int sol;
//  sol= mysql> SELECT absTestID, Messwert, TestID FROM abstest;
  // printf("Resultant value is %d\n",sol);
  }   
  //....
  pszT = mysql_stat( myData ) ;
  puts( pszT ) ;
  //....
  mysql_close( myData ) ;
  return 0 ;
 
}

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

  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);

EDIT: On second thought, why are you doing all this when MySQL has a handy

SELECT INTO OUTFILE

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_query) for the select statement from the tables themselves then use mysql_use_results 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"))==NULL) {

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=freopen("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.....

[r035198x]

Read my post number 4 again.
Do you understand the meaning of the word "redirect"?

[sivadhanekula]

Sorry....I didn't get you...Can you tell me reg that briefly.....

ThanQ

[r035198x]

Your code already had lots of printfs which were printing to the console. You wanted that output to go to the file instead. That code that I posted redirects the output from going to the console to going to the file. So you just use your printfs after that code but they will be writing to the file rather than writing to the console.
That is the concept of redirecting.

[sivadhanekula]

Ok....got it..and even I am getting the output for my program.

Thank you for one and all who help me

C you all in other thread...bye

[sivadhanekula]

Hmmmmm again I have a problem with the program.....Now I am able to get a separate .CSV file in my project but the problem is the data what I wana insert into that is not there....Can you tell me how to get the .CSV file with my required data...

Thank you

[r035198x]

You didn't forget to fclose(fp); did you?

[sivadhanekula]

ya....I haven't forget that....but I have solved the prob and it seems to be very simple...slapped of myself becoz of the prev thread,,looks like funny..I just forgot one '\' and for that I am struggling from half a day. Now it is OK....

Thanks for the reply

[r035198x]

Better make that two slaps.