Thread: Adding a *.csv to MYSQL server

Originally Posted by Salem

> "USE wireless;\
> INSERT INTO master (epoch,macTS,macAddr,seqNum,sigStr,noise,attna,pck tLen)\
> VALUES(csvData.epoch, csvData.macTS, csvData.macAddr,\
> csvData.seqNum,csvData.sigStr,csvData.noise,csvDat a.attna,csvData.pcktLen)");
Well it's not going to know anything about your variable names is it?
You need to store the substituted values in a string, like

Code:

sprintf( buff, "VALUES(%s,%s,%s,%s,%s,%s,%s,%s)",
    csvData.epoch, csvData.macTS, csvData.macAddr,
    csvData.seqNum, csvData.sigStr, csvData.noise,
    csvData.attna, csvData.pcktLen );

In fact, if these are in the same order that they were read from the file, then you've wasted your time (as already noted). All you needed to do was strip the newline off the end of the line when you read the file with fgets, and do this:

Code:

/* use return of fgets to control the loop, not feof() */
while ( fgets( line, sizeof line, ifp ) != NULL ) {
    char buff[BUFSIZ];
    char *p = strchr( line, '\n' );
    if ( p ) {
        *p = '\0'; /* remove newline */
    }
    sprintf( buff, "USE wireless;"
                   "INSERT INTO master (epoch,macTS,macAddr,seqNum,sigStr,noise,attna,pcktLen)"
                   "VALUES(%s)",
                   line );
    /* Insert structure into table */
    mysql_query(conn, buff );
}

The line was already full of commas, all you did was remove them just to put them back in again.

Also note the way long "strings" are folded, ie, not by using \

The structure is created is located in the header file.

Code:

sprintf( buff, "USE wireless;"
                   "INSERT INTO master (epoch,macTS,macAddr,seqNum,sigStr,noise,attna,pcktLen)"
                   "VALUES(%s)",
                   line );

Unfortunately, the above will generate a SQL error indicating that the column count does not match the value count.

Code:

sprintf( buff, "USE wireless;"
                   "INSERT INTO master (epoch)"
                   "VALUES('%s')",
                   line );

The above will work as long as epoch is declared as a string field in your database and the string field is large enough to hold "line" variable. But you're stuffing all your data into one field. Note the required single quotes enclosing the string field.

Code:

//#define DELETEIT 
#include <stdio.h>
#include <string.h>
#include <winsock2.h>
#include <mysql.h>

MYSQL mysql;
MYSQL *con;
MYSQL *init;
MYSQL_ROW row;
MYSQL_RES *res;

#define CONNECT_HOST   "localhost"
#define CONNECT_USER   "root"
#define CONNECT_PSW    "new-password"
#define CONNECT_DB     "wireless"
#define CONNECT_TABLE  "master"

#define E_OPENDB   -1
#define E_OK        0
#define E_NOMEM     1
#define E_NOCONN    2

int open_db_connection (char * pHost)
{
    init = mysql_init (NULL);
    if (init == NULL)
    {
        printf("Init failed, insufficient memory \n");
        return E_NOMEM;
    }
    con = mysql_real_connect (init, CONNECT_HOST, CONNECT_USER,
        CONNECT_PSW, NULL, 0, NULL, 0);
    if (con == NULL)
    {
        printf("Connnection failed\n");
        return E_NOCONN;   
    }

    return E_OK;
}

void PrintLine (void)
//----------------------------------------------------------  
{
    MYSQL_FIELD *field;
    unsigned int i, j;

    mysql_field_seek(res,0);
    for ( i = 0; i < mysql_num_fields(res); i++ )
    {
        field = mysql_fetch_field(res);
        for ( j = 0; j < field->max_length + 2; j++ )
            fputc('=',stdout);
    }
}

void PrintTable(void)
//----------------------------------------------------------  
{
    MYSQL_FIELD *field;
    unsigned int i, j, column_length;

    mysql_field_seek(res,0);

    for ( i = 0; i < mysql_num_fields(res); i++ )
    {
        field = mysql_fetch_field(res);
        column_length = strlen(field->name);
        if ( column_length < field->max_length )
            column_length = field->max_length;
        if ( column_length < 4 && !IS_NOT_NULL(field->flags) )
            column_length = 4;
        field->max_length = column_length;
    }
    PrintLine();
    fputc('\n',stdout);
    mysql_field_seek(res,0);
    for ( i = 0; i < mysql_num_fields(res); i++ )
    {
        field = mysql_fetch_field(res);
        fprintf(stdout," %-*s ",field->max_length,field->name);
    }
    fputc('\n',stdout);
    PrintLine();
    fputc('\n',stdout);
    while ((row = mysql_fetch_row(res)) != NULL)
    {
        mysql_field_seek(res,0);

        for ( i = 0; i < mysql_num_fields(res); i++ )
        {
            field = mysql_fetch_field(res);
            if ( row[i] == NULL )
                fprintf(stdout," %-*s ",field->max_length,"NULL");
            else
                if ( IS_NUM(field->type) )
                    fprintf(stdout," %*s ",field->max_length,row[i]);
                else
                    fprintf(stdout," %-*s ",field->max_length,row[i]);
        }
        fputc('\n',stdout);
    }
    fputc('\n',stdout);
}

int main(void)
{
    int err;
    char szTemp[4096] = {0};

    if ((err = open_db_connection (NULL)) != E_OK)
    {
        printf ("open_db_connection () fails with %d!\n", err);
        return E_OPENDB;
    }
 #ifdef DELETEIT
    sprintf(szTemp, "DROP DATABASE %s", CONNECT_DB);
    if ( mysql_real_query(init,szTemp,strlen(szTemp)) != 0 ) 
        printf("Database delete failed\n");
    else printf("database delete successful\n");
 #endif
    sprintf( szTemp, "CREATE DATABASE %s", CONNECT_DB);
    if ( mysql_real_query(init,szTemp,strlen(szTemp)) != 0 ) 
    {
        printf("Failed to create database.\n"
            "Error code: %u\n"
            "Description: %s\n", mysql_errno(init), mysql_error(init));
    } 
    else printf("database create successful\n");
    if(mysql_select_db (con, CONNECT_DB) != 0)
    {
        printf("Failed to select database.\n"
            "Error code: %u\n"
            "Description: %s\n", mysql_errno(init), mysql_error(init));
    }
    else printf("database selected\n");

    sprintf(szTemp,"CREATE TABLE %s (epoch INT, macTS REAL, macAddr VARCHAR(24), seqNum INT,  sigStr INT, noise INT, attna INT, pcktLen INT)",CONNECT_TABLE);
    if ( mysql_real_query(init,szTemp,strlen(szTemp)) != 0 ) 
    {
        printf("Failed to create table.\n"
            "Error code: %u\n"
            "Description: %s\n", mysql_errno(init), mysql_error(init));
    }
    else printf("database table create successful\n");

    struct Data
    {
        unsigned long epoch;
        char macTS[24];
        char macAddr[24];
        int seqNum;
        int sigStr;
        int noise;
        int attna;
        int pcktLen;
    };
    struct Data csvData  = {1196741136,"1.01466E+19" ,"00:0F:F7:BE:FF:80" ,2307,-74,-96,1,177};
    sprintf(szTemp, "INSERT INTO %s.%s(epoch,macTS,macAddr,seqNum,sigStr,noise,attna,pcktLen) VALUES (%d, '%s','%s', %d, %d, %d, %d, %d )"
        ,CONNECT_DB, CONNECT_TABLE ,csvData.epoch, csvData.macTS, csvData.macAddr, csvData.seqNum,csvData.sigStr,csvData.noise,csvData.attna,csvData.pcktLen );
    if ( mysql_real_query(init,szTemp,strlen(szTemp)) != 0 ) 
    {
        printf("Failed to insert data\n"
            "Error code: %u\n"
            "Description: %s\n", mysql_errno(init), mysql_error(init));
    }
    else printf("Insert successful\n");
    //Display record(s) in database
    sprintf(szTemp,"select * from %s.%s",CONNECT_DB,CONNECT_TABLE);
    mysql_query(con,szTemp);
    res = mysql_store_result(con);
    PrintTable();
    mysql_close (con);
    return 0;
}