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