C API prepared insert truncates strings to 5 characters
Posted by: 7 reeds
Date: March 18, 2013 02:31PM

Hi,

I have written a couple of small C API programs in the past but this is my first time to use prepared statements. What I am doing compiles and runs but the resulting strings in the target table are all chopped to 5 characters.

I can not see what would limit the string length in the code

The target table:

mysql> describe log;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| machine      | varchar(64)      | NO   | MUL | NULL    |                |
| date         | datetime         | NO   | MUL | NULL    |                |
| event        | varchar(8)       | NO   | MUL | NULL    |                |
| netid        | varchar(16)      | NO   | MUL | NULL    |                |
| domain       | varchar(32)      | NO   | MUL | NULL    |                |
| line         | varchar(64)      | NO   | MUL | NULL    |                |
| source       | varchar(8)       | NO   | MUL | NULL    |                |
| dateInserted | datetime         | NO   | MUL | NULL    |                |
| notes        | varchar(128)     | NO   |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

The insert code:

int insertIntoDB(MYSQL *mysql, struct env PEnv, char *date, char *source) {

    char *insertSQL = "\
INSERT INTO personalDB.log \
            (id, event, machine, netid, line, source, date) \
VALUES(NULL, UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?), NOW())";

    char *tmp;
    char *datePtr;

    int numBindCols = 5;
    int col = 0;

    int result = 0;

    unsigned long bufferLength = 0;

    MYSQL_STMT *sth;

    if (DEBUG) {
        syslog(LOG_AUTHPRIV | LOG_INFO, "insertIntoDB: start");
    }

    /*
     * "date" will usually be NULL. If not NULL it will be in ISO std
     * YYYY-MM-DD HH:mm:SS format.  If we have a date string, modify the 
     * insert statement to accept a bind value for the date -- i.e. replace
     * the "NOW()" in the insert string with a "?"
     */

    if (date != NULL ) {
        tmp = strstr(insertSQL, "NOW())");
        if (tmp != NULL ) {
            tmp = "?)";
            numBindCols++;
        }
    }

    /*
     * setup the statement
     */

    if ((sth = mysql_stmt_init(mysql)) != NULL ) {
        /*
         * setup the prepare
         */
        if (mysql_stmt_prepare(sth, insertSQL, strlen(insertSQL)) == 0) {
            MYSQL_BIND bind[numBindCols];
            memset(bind, 0, sizeof(bind));

            bind[col].buffer_type = MYSQL_TYPE_STRING;
            bind[col].buffer = (void *) PEnv.event;
            bind[col].is_unsigned = 0;
            bind[col].is_null = 0;
            bind[col].buffer_length = 8;
            bufferLength = strlen(PEnv.event);
            bind[col++].length = &bufferLength;
            if (DEBUG) {
                syslog(LOG_AUTHPRIV | LOG_INFO,
                        "insertIntoDB: event=%s/%lu", PEnv.event,
                        bufferLength);
            }

            bind[col].buffer_type = MYSQL_TYPE_STRING;
            bind[col].buffer = (void *) PEnv.rhost;
            bind[col].is_unsigned = 0;
            bind[col].is_null = 0;
            bind[col].buffer_length = 64;
            bufferLength = strlen(PEnv.rhost);
            bind[col++].length = &bufferLength;
            if (DEBUG) {
                syslog(LOG_AUTHPRIV | LOG_INFO,
                        "insertIntoDB: rhost=%s/%lu", PEnv.rhost,
                        bufferLength);
            }

            bind[col].buffer_type = MYSQL_TYPE_STRING;
            bind[col].buffer = (void *) PEnv.user;
            bind[col].is_unsigned = 0;
            bind[col].is_null = 0;
            bind[col].buffer_length = 16;
            bufferLength = strlen(PEnv.user);
            bind[col++].length = &bufferLength;
            if (DEBUG) {
                syslog(LOG_AUTHPRIV | LOG_INFO,
                        "insertIntoDB: user=%s/%lu", PEnv.user,
                        bufferLength);
            }

            bind[col].buffer_type = MYSQL_TYPE_STRING;
            bind[col].buffer = (void *) PEnv.service;
            bind[col].is_unsigned = 0;
            bind[col].is_null = 0;
            bind[col].buffer_length = 64;
            bufferLength = strlen(PEnv.service);
            bind[col++].length = &bufferLength;
            if (DEBUG) {
                syslog(LOG_AUTHPRIV | LOG_INFO,
                        "insertIntoDB: service=%s/%lu",
                        PEnv.service, bufferLength);
            }

            bind[col].buffer_type = MYSQL_TYPE_STRING;
            bind[col].buffer = (void *) source;
            bind[col].is_unsigned = 0;
            bind[col].is_null = 0;
            bind[col].buffer_length = 8;
            bufferLength = strlen(source);
            bind[col++].length = &bufferLength;
            if (DEBUG) {
                syslog(LOG_AUTHPRIV | LOG_INFO,
                        "insertIntoDB: source=%s/%lu", source,
                        bufferLength);
            }

            /*
             * do we have a date string?
             * ... I've not tested this section yet, not sure if the 
             * strtol's will do what I want ...
             */
            if (numBindCols > col) {
                if (DEBUG) {
                    syslog(LOG_AUTHPRIV | LOG_INFO,
                            "insertIntoDB: numBindCols=%d,col=%d",
                            numBindCols, col);
                }

                MYSQL_TIME localDate;
                localDate.year = strtol(date, &datePtr, 10);
                datePtr++;
                localDate.month = strtol(datePtr, &datePtr, 10);
                datePtr++;
                localDate.day = strtol(datePtr, &datePtr, 10);
                datePtr++;
                localDate.hour = strtol(datePtr, &datePtr, 10);
                datePtr++;
                localDate.minute = strtol(datePtr, &datePtr, 10);
                datePtr++;
                localDate.second = strtol(datePtr, &datePtr, 10);

                bind[col].buffer_type = MYSQL_TYPE_DATETIME;
                bind[col].buffer = &localDate;
                bind[col].is_unsigned = 0;
                bind[col].is_null = 0;
                bufferLength = sizeof(localDate);
                bind[col++].length = &bufferLength;
                if (DEBUG) {
                    syslog(LOG_AUTHPRIV | LOG_INFO,
                            "insertIntoDB: date=%s/%lu", date,
                            bufferLength);
                }
            }

            if (mysql_stmt_bind_param(sth, bind) == 0) {
                if (mysql_stmt_execute(sth) != 0) {
                    syslog(LOG_AUTHPRIV | LOG_INFO,
                            "insertIntoDB: MySQL could not execute: %s",
                            mysql_stmt_error(sth));
                    result = __LINE__;
                }
            } else {
                syslog(LOG_AUTHPRIV | LOG_INFO,
                        "insertIntoDB: MySQL could not bind values: %s",
                        mysql_stmt_error(sth));
                result = __LINE__;
            }
        } else {
            syslog(LOG_AUTHPRIV | LOG_INFO,
                    "insertIntoDB: MySQL could not prepare query: %s",
                    mysql_stmt_error(sth));
            result = __LINE__;
        }

        mysql_stmt_free_result(sth);
        mysql_stmt_close(sth);
    } else {
        syslog(LOG_AUTHPRIV | LOG_INFO,
                "insertIntoDB: MySQL could not init statement: %s",
                mysql_stmt_error(sth));
        result = __LINE__;
    }

    if (DEBUG) {
        syslog(LOG_AUTHPRIV | LOG_INFO, "insertIntoDB: end");
    }

    return result;
}

Options: ReplyQuote


Subject
Views
Written By
Posted
C API prepared insert truncates strings to 5 characters
3539
March 18, 2013 02:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.