From f91b9aee396de79684f3c2f98d5486a9816ac0e7 Mon Sep 17 00:00:00 2001 From: onefang Date: Sat, 25 Apr 2020 05:16:24 +1000 Subject: Some db clean up, and support INSERT. --- src/sledjchisl/sledjchisl.c | 206 +++++++++++++++++++++++++++++++++----------- 1 file changed, 156 insertions(+), 50 deletions(-) (limited to 'src') diff --git a/src/sledjchisl/sledjchisl.c b/src/sledjchisl/sledjchisl.c index d6c7730..35f6202 100644 --- a/src/sledjchisl/sledjchisl.c +++ b/src/sledjchisl/sledjchisl.c @@ -1016,6 +1016,14 @@ d("Freeing field %s", fld->name); flds->free(flds); } +enum dbCommandType +{ + CT_SELECT, + CT_CREATE, + CT_UPDATE, + CT_NONE +}; + typedef struct _dbRequest dbRequest; struct _dbRequest { @@ -1028,11 +1036,13 @@ struct _dbRequest MYSQL_BIND *inBind, *outBind; rowData *rows; my_ulonglong count; + enum dbCommandType type; boolean freeOutParams; }; -void dbDoSomething(dbRequest *req, boolean count, ...) +int dbDoSomething(dbRequest *req, boolean count, ...) { + int ret = 0; va_list ap; struct timespec now, then; int i, j; @@ -1046,45 +1056,94 @@ void dbDoSomething(dbRequest *req, boolean count, ...) if (NULL == req->prep) { D("Creating prepared statement for %s - %s", req->table, req->where); + + if (0 == req->type) + req->type = CT_SELECT; + req->flds = dbGetFields(req->db, req->table); if (NULL == req->flds) { E("Unknown fields for table %s.", req->table); + ret++; goto end; } - char *select = xmprintf(""); - i = 0; - while (req->outParams[i] != NULL) + switch (req->type) { - char *t = xmprintf("%s,%s", select, req->outParams[i]); - free(select); - select = t; - i++; - } - if (0 == i) - { - free(select); - if (count) - select = xmprintf(",Count(*)"); - else - select = xmprintf(",*"); - } + case CT_SELECT : + { + char *select = xmprintf(""); - if (NULL == req->join) - req->join = ""; + i = 0; + while (req->outParams[i] != NULL) + { + char *t = xmprintf("%s,%s", select, req->outParams[i]); + free(select); + select = t; + i++; + } + if (0 == i) + { + free(select); + if (count) + select = xmprintf(",Count(*)"); + else + select = xmprintf(",*"); + } - if (req->where) - req->sql = xmprintf("SELECT %s FROM %s %s WHERE %s", &select[1], req->table, req->join, req->where); - else - req->sql = xmprintf("SELECT %s FROM %s", &select[1], req->table, req->join); - free(select); - if (req->order) - { - char *t = xmprintf("%s ORDER BY %s", req->sql, req->order); + if (NULL == req->join) + req->join = ""; + + if (req->where) + req->sql = xmprintf("SELECT %s FROM %s %s WHERE %s", &select[1], req->table, req->join, req->where); + else + req->sql = xmprintf("SELECT %s FROM %s", &select[1], req->table, req->join); + free(select); + if (req->order) + { + char *t = xmprintf("%s ORDER BY %s", req->sql, req->order); + + free(req->sql); + req->sql = t; + } + break; + } + + case CT_CREATE : + { + char *values = xmprintf(""); + + i = 0; + while (req->inParams[i] != NULL) + { + char *t = xmprintf("%s, %s=?", values, req->inParams[i]); + free(values); + values = t; + i++; + } + if (0 == i) + { + E("Statement prepare for INSERT must have in paramaters."); + ret++; + free(values); + values = xmprintf(""); + } + req->sql = xmprintf("INSERT INTO %s SET %s", req->table, &values[1]); + free(values); + + break; + } - free(req->sql); - req->sql = t; + case CT_UPDATE : + { + break; + } + + case CT_NONE : + { + W("No SQL type!"); + break; + } } d("New SQL statement - %s", req->sql); @@ -1095,11 +1154,13 @@ d("New SQL statement - %s", req->sql); if (NULL == req->prep) { E("Statement prepare init failed: %s\n", mysql_stmt_error(req->prep)); + ret++; goto end; } if (mysql_stmt_prepare(req->prep, req->sql, strlen(req->sql))) { E("Statement prepare failed: %s\n", mysql_stmt_error(req->prep)); + ret++; goto end; } @@ -1111,6 +1172,7 @@ d("New SQL statement - %s", req->sql); if (i != req->inCount) { E("In parameters count don't match %d != %d for - %s", i, req->inCount, req->sql); + ret++; goto freeIt; } req->inBind = xzalloc(i * sizeof(MYSQL_BIND)); @@ -1122,6 +1184,7 @@ W("Allocated %d %d inBinds for %s", i, req->inCount, req->sql); if (NULL == fld) { E("Unknown input field %d %s.%s for - %s", i, req->table, req->inParams[i], req->sql); + ret++; goto freeIt; } else @@ -1135,45 +1198,53 @@ W("Allocated %d %d inBinds for %s", i, req->inCount, req->sql); { case MYSQL_TYPE_TINY: { +d("TINY %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_SHORT: { req->inBind[i].is_unsigned = FALSE; +d("SHORT %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_INT24: { req->inBind[i].is_unsigned = FALSE; +d("INT24 %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_LONG: { req->inBind[i].is_unsigned = FALSE; +d("LONG %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_LONGLONG: { req->inBind[i].is_unsigned = FALSE; +d("LONGLONG %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_FLOAT: { +d("FLOAT %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_DOUBLE: { +d("DOUBLE %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_NEWDECIMAL: { +d("NEWDECIMAL %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } @@ -1182,12 +1253,14 @@ W("Allocated %d %d inBinds for %s", i, req->inCount, req->sql); case MYSQL_TYPE_DATETIME: case MYSQL_TYPE_TIMESTAMP: { +d("DATE / TIME ish %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_STRING: case MYSQL_TYPE_VAR_STRING: { +d("STRING / VARSTRING %d %s %d", i, fld->name, req->inBind[i].buffer_length); req->inBind[i].is_null = xzalloc(sizeof(my_bool)); req->inBind[i].length = xzalloc(sizeof(unsigned long)); break; @@ -1198,6 +1271,7 @@ W("Allocated %d %d inBinds for %s", i, req->inCount, req->sql); case MYSQL_TYPE_MEDIUM_BLOB: case MYSQL_TYPE_LONG_BLOB: { +d("BLOBs %d %s %d", i, fld->name, req->inBind[i].buffer_length); req->inBind[i].is_null = xzalloc(sizeof(my_bool)); break; } @@ -1205,11 +1279,13 @@ W("Allocated %d %d inBinds for %s", i, req->inCount, req->sql); case MYSQL_TYPE_BIT: { req->inBind[i].is_null = xzalloc(sizeof(my_bool)); +d("BIT %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_NULL: { +d("NULL %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } } @@ -1217,18 +1293,22 @@ W("Allocated %d %d inBinds for %s", i, req->inCount, req->sql); } // TODO - if this is not a count, setup result bind paramateres, may be needed for counts as well. - prepare_meta_result = mysql_stmt_result_metadata(req->prep); - if (!prepare_meta_result) + if (CT_SELECT == req->type) { - D(" mysql_stmt_result_metadata() error %d, returned no meta information - %s\n", mysql_stmt_errno(req->prep), mysql_stmt_error(req->prep)); - goto freeIt; + prepare_meta_result = mysql_stmt_result_metadata(req->prep); + if (!prepare_meta_result) + { + E(" mysql_stmt_result_metadata() error %d, returned no meta information - %s\n", mysql_stmt_errno(req->prep), mysql_stmt_error(req->prep)); + ret++; + goto freeIt; + } } if (count) { I("count!!!!!!!!!!!!!!!!"); } - else + else if (CT_SELECT == req->type) { req->outCount = mysql_num_fields(prepare_meta_result); i = 0; @@ -1253,6 +1333,7 @@ I("count!!!!!!!!!!!!!!!!"); if (i != req->outCount) { E("Out parameters count doesn't match %d != %d foqr - %s", i, req->outCount, req->sql); + ret++; goto freeIt; } req->outBind = xzalloc(i * sizeof(MYSQL_BIND)); @@ -1264,6 +1345,7 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); if (NULL == fld) { E("Unknown output field %d %s.%s foqr - %s", i, req->table, req->outParams[i], req->sql); + ret++; goto freeIt; } else @@ -1306,7 +1388,7 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); case MYSQL_TYPE_LONGLONG: { -//d("LONG LONG %s %d", fld->name, req->outBind[i].buffer_length); +//d("LONGLONG %s %d", fld->name, req->outBind[i].buffer_length); req->outBind[i].is_unsigned = FALSE; break; } @@ -1334,14 +1416,14 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); case MYSQL_TYPE_DATETIME: case MYSQL_TYPE_TIMESTAMP: { -//d("DATETIME %s %d", fld->name, req->outBind[i].buffer_length); +//d("DATE / TIME ish %s %d", fld->name, req->outBind[i].buffer_length); break; } case MYSQL_TYPE_STRING: case MYSQL_TYPE_VAR_STRING: { -//d("STRING %s %d", fld->name, req->outBind[i].buffer_length); +//d("STRING / VARSTRING %s %d", fld->name, req->outBind[i].buffer_length); req->outBind[i].length = xzalloc(sizeof(unsigned long)); break; } @@ -1351,7 +1433,7 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); case MYSQL_TYPE_MEDIUM_BLOB: case MYSQL_TYPE_LONG_BLOB: { -//d("BLOB %s %d", fld->name, req->outBind[i].buffer_length); +//d("BLOBs %s %d", fld->name, req->outBind[i].buffer_length); break; } @@ -1371,14 +1453,15 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); } if (mysql_stmt_bind_result(req->prep, req->outBind)) { - E("Bind failed error %d.", mysql_stmt_errno(req->prep)); - goto freeIt; + E("Bind failed error %d.", mysql_stmt_errno(req->prep)); + ret++; + goto freeIt; } } } -//d("input bind for %s", req->sql); +d("input bind for %s", req->sql); for (i = 0; i < req->inCount; i++) { dbField *fld = req->flds->get(req->flds, req->inParams[i], NULL, false); @@ -1386,6 +1469,7 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); if (NULL == fld) { E("Unknown input field %s.%s for - %s", req->table, req->inParams[i], req->sql); + ret++; goto freeIt; } else @@ -1397,7 +1481,8 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); int c = va_arg(ap, int); signed char d = (signed char) c; - memcpy(&d, req->inBind[i].buffer, (size_t) fld->length); + memcpy(req->inBind[i].buffer, &d, (size_t) fld->length); +T("TINY %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } @@ -1406,7 +1491,8 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); int c = va_arg(ap, int); short int d = (short int) c; - memcpy(&d, req->inBind[i].buffer, (size_t) fld->length); + memcpy(req->inBind[i].buffer, &d, (size_t) fld->length); +T("SHORT %d %s %d = %d", i, fld->name, req->inBind[i].buffer_length, c); break; } @@ -1414,7 +1500,8 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); { int d = va_arg(ap, int); - memcpy(&d, req->inBind[i].buffer, (size_t) fld->length); + memcpy(req->inBind[i].buffer, &d, (size_t) fld->length); +T("INT24 %d %s %d - %d", i, fld->name, req->inBind[i].buffer_length, d); break; } @@ -1422,7 +1509,8 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); { long d = va_arg(ap, long); - memcpy(&d, req->inBind[i].buffer, (size_t) fld->length); + memcpy(req->inBind[i].buffer, &d, (size_t) fld->length); +T("LONG %d %s %d = %ld", i, fld->name, req->inBind[i].buffer_length, d); break; } @@ -1430,7 +1518,8 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); { long long int d = va_arg(ap, long long int); - memcpy(&d, req->inBind[i].buffer, (size_t) fld->length); + memcpy(req->inBind[i].buffer, &d, (size_t) fld->length); +T("LONGLONG %d %s %d = %lld", i, fld->name, req->inBind[i].buffer_length, d); break; } @@ -1439,7 +1528,8 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); double c = va_arg(ap, double); float d = (float) c; - memcpy(&d, req->inBind[i].buffer, (size_t) fld->length); + memcpy(req->inBind[i].buffer, &d, (size_t) fld->length); +T("FLOAT %d %s %d = %f", i, fld->name, req->inBind[i].buffer_length, d); break; } @@ -1447,12 +1537,14 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); { double d = va_arg(ap, double); - memcpy(&d, req->inBind[i].buffer, (size_t) fld->length); + memcpy(req->inBind[i].buffer, &d, (size_t) fld->length); +T("DOUBLE %d %s %d = %f", i, fld->name, req->inBind[i].buffer_length, d); break; } case MYSQL_TYPE_NEWDECIMAL: { +T("NEWDECIMAL %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } @@ -1463,7 +1555,8 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); { MYSQL_TIME d = va_arg(ap, MYSQL_TIME); - memcpy(&d, req->inBind[i].buffer, (size_t) fld->length); + memcpy(req->inBind[i].buffer, &d, (size_t) fld->length); +T("DATE / TIME ish %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } @@ -1478,6 +1571,7 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); *(req->inBind[i].length) = l; strncpy(req->inBind[i].buffer, d, (size_t) l); ((char *) req->inBind[i].buffer)[l] = '\0'; +T("STRING / VARSTRING %d %s %d = %s", i, fld->name, req->inBind[i].buffer_length, d); break; } @@ -1486,16 +1580,20 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); case MYSQL_TYPE_MEDIUM_BLOB: case MYSQL_TYPE_LONG_BLOB: { +// TODO - should write this, we will likely need it. Main problem is - how long is this blob? Probably should add alongth param before the blob. +T("BLOBs %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_BIT: { +T("BIT %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } case MYSQL_TYPE_NULL: { +T("NULL %d %s %d", i, fld->name, req->inBind[i].buffer_length); break; } } @@ -1504,6 +1602,7 @@ W("Allocated %d %d outBinds for %s", i, req->outCount, req->sql); if (mysql_stmt_bind_param(req->prep, req->inBind)) { E("Bind failed error %d.", mysql_stmt_errno(req->prep)); + ret++; goto freeIt; } @@ -1514,6 +1613,7 @@ d("Execute %s", req->sql); if (mysql_stmt_execute(req->prep)) { E("Statement execute failed %d: %s\n", mysql_stmt_errno(req->prep), mysql_stmt_error(req->prep)); + ret++; goto freeIt; } @@ -1526,6 +1626,7 @@ d("Execute %s", req->sql); if (mysql_stmt_store_result(req->prep)) { E(" mysql_stmt_store_result() failed %d: %s", mysql_stmt_errno(req->prep), mysql_stmt_error(req->prep)); + ret++; goto freeIt; } req->rowCount = mysql_stmt_num_rows(req->prep); @@ -1577,6 +1678,7 @@ d("Execute %s", req->sql); if (NULL == req->outBind[i].buffer) { E("Field %d %s is NULL", i, fld->name); + ret++; goto freeIt; } char *t = xmprintf("%d", (int) *((int *) (req->outBind[i].buffer))); @@ -1654,7 +1756,10 @@ freeIt: if (prepare_meta_result) mysql_free_result(prepare_meta_result); if (mysql_stmt_free_result(req->prep)) + { E("Statement result freeing failed %d: %s\n", mysql_stmt_errno(req->prep), mysql_stmt_error(req->prep)); + ret++; + } end: va_end(ap); @@ -1673,7 +1778,8 @@ end: I("The prepared0 statement itself is NOT NULL."); else W("The prepared0 statement itself is NULL!"); - return; + + return ret; } // Copy the SQL results into the request structure. -- cgit v1.1