Index: /trunk/archive/psdb/psDB.c =================================================================== --- /trunk/archive/psdb/psDB.c (revision 3174) +++ /trunk/archive/psdb/psDB.c (revision 3175) @@ -8,6 +8,6 @@ * @author Joshua Hoblitt * - * @version $Revision: 1.8 $ $Name: not supported by cvs2svn $ - * @date $Date: 2005-02-09 01:15:39 $ + * @version $Revision: 1.9 $ $Name: not supported by cvs2svn $ + * @date $Date: 2005-02-10 00:02:16 $ * * Copyright 2005 Joshua Hoblitt, University of Hawaii @@ -25,6 +25,6 @@ typedef struct { - psU32 type; - bool isUnsigned; + enum enum_field_types type; + bool isUnsigned; } mysqlType; @@ -564,4 +564,129 @@ return false; } + + // point of no return + mysql_commit(dbh->mysql); + + psFree(bind); + mysql_stmt_close(stmt); + + return true; +} + +bool psDBInsertRows(psDB *dbh, const char *tableName, psArray *rowSet) +{ + psMetadata *row; // row of data + char *query; // SQL query + MYSQL_STMT *stmt; // prepared db statement + MYSQL_BIND *bind; // field values to insert + psU32 paramCount; // number of placeholders in query + psHash *mysqlFieldType; // type lookup table + mysqlType *mType; // type tmp variable + char *key; // hash tmp variable + psMetadataItem *item; // field in row + psListIterator *cursor; // row iterator + psU32 i; // field index + psU64 j; // row index + bool isNull = true; // used in a MYSQL_BIND to indicate NULL + + // we are assuming that all rows in the set have an identical with reguard + // to field count and type + row = rowSet->data[0]; + + query = psDBGenerateInsertRowSQL(tableName, row); + if (!query) { + return NULL; + } + + stmt = mysql_stmt_init(dbh->mysql); + if (!stmt) { + // call abort? + fprintf(stderr, " mysql_stmt_init(), out of memory\n"); + + return false; + } + + if (mysql_stmt_prepare(stmt, query, (unsigned long)strlen(query))) { + fprintf(stderr, "Failed to prepare query. Error: %s\n", mysql_stmt_error(stmt)); + + mysql_stmt_close(stmt); + psFree(query); + + return false; + } + + psFree(query); + + // how many place holders are in our query + paramCount = (psU32)mysql_stmt_param_count(stmt); + + // structure larger enough to hold one field of data per place holder + bind = psAlloc(sizeof(MYSQL_BIND) * paramCount); + + mysqlFieldType = psDBGetPSToMySQLTable(); + + // loop over rows + for (j = 0; j < rowSet->n; j++) { + cursor = psListIteratorAlloc(row->list, 0); + + // reset bind for each row + memset(bind, 0, sizeof(MYSQL_BIND) * paramCount); + + // loop over fields + i = 0; + while ((item = psListGetNext(cursor))) { + key = psDBIntToString((psU64)item->pType); + mType = psHashLookup(mysqlFieldType, key); + psFree(key); + + // input data length is determined by the MYSQL_TYPE_* unless it's a string + bind[i].buffer = (char *)item->data.V; + bind[i].buffer_type = mType->type; + bind[i].is_unsigned = mType->isUnsigned; + + // convert NaNs to NULL + bind[i].is_null = psDBIsPTypeNaN(item->pType, item->data.V) + ? (my_bool *)&isNull + : NULL; + + if (item->pType == PS_TYPE_PTR) { + if (item->type == PS_META_STR) { + if (*(char *)item->data.V == '\0') { + bind[i].is_null = (my_bool *)&isNull; + } else { + bind[i].buffer_length = (unsigned long)strlen((char *)item->data.V); + bind[i].is_null = NULL; + } + } else { + // error, not a pointer to a string + } + } + + // increment field index + i++; + } + + psFree(cursor); + + if (mysql_stmt_bind_param(stmt, bind)) { + fprintf(stderr, "Failed to bind params. Error: %s\n", mysql_stmt_error(stmt)); + + psFree(bind); + mysql_stmt_close(stmt); + + return false; + } + + if (mysql_stmt_execute(stmt)) { + fprintf(stderr, "Failed to execute prepared statement. Error: %s\n", mysql_stmt_error(stmt)); + + psFree(bind); + mysql_stmt_close(stmt); + + return false; + } + } // end loop over rows + + psFree(mysqlFieldType); // point of no return Index: /trunk/archive/psdb/psDB.h =================================================================== --- /trunk/archive/psdb/psDB.h (revision 3174) +++ /trunk/archive/psdb/psDB.h (revision 3175) @@ -10,6 +10,6 @@ * @author Joshua Hoblitt * - * @version $Revision: 1.3 $ $Name: not supported by cvs2svn $ - * @date $Date: 2005-02-09 01:15:39 $ + * @version $Revision: 1.4 $ $Name: not supported by cvs2svn $ + * @date $Date: 2005-02-10 00:02:16 $ * * Copyright 2005 Joshua Hoblitt, University of Hawaii @@ -51,4 +51,6 @@ bool psDBInsertOneRow(psDB *dbh, const char *tableName, psMetadata *row); +bool psDBInsertRows(psDB *dbh, const char *tableName, psArray *rowSet); + psArray *psDBDumpRows(psDB *dbh, const char *tableName); Index: /trunk/archive/psdb/test.c =================================================================== --- /trunk/archive/psdb/test.c (revision 3174) +++ /trunk/archive/psdb/test.c (revision 3175) @@ -48,4 +48,17 @@ psFree(md); + + // setup horses table for later tests + { + psMetadata *md; + psMetadataItem *item; + + md = psMetadataAlloc(); + item = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, "100"); + psMetadataAddItem(md, item, 3); + psFree(item); + psDBCreateTable(dbh, "horses", md); + psFree(md); + } // psDBSelectColumn() @@ -103,7 +116,48 @@ psFree(md); + //psDBInsertRows(); + + { + psArray *rowSet; + psMetadata *row; + psMetadataItem *field; + + rowSet = psArrayAlloc(3); + rowSet->n = 0; + + row = psMetadataAlloc(); + field = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, "brown"); + psMetadataAddItem(row, field, 0); + psFree(field); + psArrayAdd(rowSet, 0, row); + psFree(row); + + row = psMetadataAlloc(); + field = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, ""); + psMetadataAddItem(row, field, 0); + psFree(field); + psArrayAdd(rowSet, 0, row); + psFree(row); + + row = psMetadataAlloc(); + field = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, "pink"); + psMetadataAddItem(row, field, 0); + psFree(field); + psArrayAdd(rowSet, 0, row); + psFree(row); + + if (psDBInsertRows(dbh, "horses", rowSet)) { + printf("psDBInsertRows() succeeded\n"); + } + + psFree(rowSet); + } + //psDBDumpRows(); resultSet = psDBDumpRows(dbh, "horses"); psFree(resultSet); + + // cleanup other tests + psDBDropTable(dbh, "horses"); // close db