Changeset 3188 for trunk/archive/psdb/psDB.c

Timestamp:

Feb 10, 2005, 10:56:41 AM (21 years ago)

Author:

jhoblitt

Message:

add psDBUpdateRows() (incomplete)
add psDBGenerateSetSQL()
add psDBGenerateUpdateRowSQL()

File:

• trunk/archive/psdb/psDB.c (modified) (8 diffs)

trunk/archive/psdb/psDB.c

@@ -8 +8 @@
  *  @author Joshua Hoblitt
  *
- *  @version $Revision: 1.9 $ $Name: not supported by cvs2svn $
- *  @date $Date: 2005-02-10 00:02:16 $
+ *  @version $Revision: 1.10 $ $Name: not supported by cvs2svn $
+ *  @date $Date: 2005-02-10 20:56:41 $
  *
  *  Copyright 2005 Joshua Hoblitt, University of Hawaii
@@ -61 +61 @@
 static char    *psDBGenerateSelectColumnSQL(const char *tableName, const char *col, const psU64 limit);
 static char    *psDBGenerateSelectRowsSQL(const char *tableName, psMetadata *where);
+static char    *psDBGenerateUpdateRowSQL(const char *tableName, psMetadata *where, psMetadata *values);
+static char    *psDBGenerateSetSQL(psMetadata *set);
 
 psDB *psDBInit(const char *host, const char *user, const char *passwd, const char *dbname)
@@ -628 +630 @@
     // loop over rows
     for (j = 0; j < rowSet->n; j++) {
-        cursor = psListIteratorAlloc(row->list, 0);
+        // extract the psList, from the psMetadata, in the current psArray element
+        cursor = psListIteratorAlloc(((psMetadata *)rowSet->data[j])->list, 0);
 
         // reset bind for each row
@@ -660 +663 @@
                 } else {
                     // error, not a pointer to a string
+                    mysql_rollback(dbh->mysql);
+
+                    psFree(cursor);
+                    psFree(bind);
+                    mysql_stmt_close(stmt);
+
+                    return false;
                 }
             }
@@ -672 +682 @@
             fprintf(stderr, "Failed to bind params.  Error: %s\n", mysql_stmt_error(stmt));
 
+            mysql_rollback(dbh->mysql);
+
             psFree(bind);
             mysql_stmt_close(stmt);
@@ -681 +693 @@
             fprintf(stderr, "Failed to execute prepared statement.  Error: %s\n", mysql_stmt_error(stmt));
 
+            mysql_rollback(dbh->mysql);
+
             psFree(bind);
             mysql_stmt_close(stmt);
@@ -702 +716 @@
 {
     return psDBSelectRows(dbh, tableName, NULL);
+}
+
+psS64 psDBUpdateRows(psDB *dbh, const char *tableName, psMetadata *where, psMetadata *values)
+{
+    char            *query;
+    MYSQL_STMT      *stmt;              // prepared db statement
+    psU32           paramCount;         // number of placeholders in query
+    MYSQL_BIND      *bind;              // field values to insert
+    psU64           rowsAffected;
+
+    query = psDBGenerateUpdateRowSQL(tableName, where, values);
+    if (!query) {
+        return -1;
+    }
+
+    printf("complete query is %s\n", query);
+
+    stmt = mysql_stmt_init(dbh->mysql);
+    if (!stmt) {
+        // call abort?
+        fprintf(stderr, " mysql_stmt_init(), out of memory\n");
+
+        return -1;
+    }
+
+    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 -1;
+    }
+
+    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);
+
+    if (mysql_stmt_bind_param(stmt, bind)) {
+        fprintf(stderr, "Failed to bind params.  Error: %s\n", mysql_stmt_error(stmt));
+
+        mysql_rollback(dbh->mysql);
+
+        psFree(bind);
+        mysql_stmt_close(stmt);
+
+        return -1;
+    }
+
+    if (mysql_stmt_execute(stmt)) {
+        fprintf(stderr, "Failed to execute prepared statement.  Error: %s\n", mysql_stmt_error(stmt));
+
+        mysql_rollback(dbh->mysql);
+
+        psFree(bind);
+        mysql_stmt_close(stmt);
+
+        return -1;
+    }
+
+    psFree(bind);
+
+    // point of no return
+    mysql_commit(dbh->mysql);
+
+    rowsAffected = (psU64)mysql_stmt_affected_rows(stmt);
+
+    mysql_stmt_close(stmt);
+
+    return rowsAffected;
+}
+
+static char *psDBGenerateUpdateRowSQL(const char *tableName, psMetadata *where, psMetadata *values)
+{
+    char            *query;
+    char            *string;
+
+    if ((!values) || (!where)) {
+        return NULL;
+    }
+
+    // init query with a psMemBlock
+    query = psStringCopy("UPDATE ");
+    psStringAppend(&query, tableName);
+
+    string = psDBGenerateSetSQL(values);
+    if (!string) {
+        return NULL;
+    }
+    psStringAppend(&query, " ");
+    psStringAppend(&query, string);
+    psFree(string);
+
+    string = psDBGenerateWhereSQL(where);
+    if (!string) {
+        return NULL;
+    }
+    psStringAppend(&query, " ");
+    psStringAppend(&query, string);
+    psFree(string);
+
+    return query;
 }
 
@@ -785 +905 @@
         if (!cursor->offEnd) {
             psStringAppend(&query, " AND ");
+        }
+    }
+
+    psFree(cursor);
+
+    return query;
+}
+
+static char *psDBGenerateSetSQL(psMetadata *set)
+{
+    char            *query;
+    psListIterator  *cursor;
+    psMetadataItem  *item;
+
+    // init query with a psMemBlock
+    query = psStringCopy("SET ");
+
+    cursor = psListIteratorAlloc(set->list, 0);
+
+    // find column name 
+    while ((item = psListGetNext(cursor))) {
+        // + column name + _ + = + _ + ?
+        psStringAppend(&query, item->name);
+        psStringAppend(&query, " = ?");
+
+        // + ", " after every column declaration except the last one
+        if (!cursor->offEnd) {
+            psStringAppend(&query, ",  ");
         }
     }