/*------v---v--------v---------------------------------------------------------- * @(#) Maria DB test for prepared statements focus on parameters * @(#) cPgmMain 2022-02 by juerg.oehler@ois-net.org *------------------------------------------------------------------------------ * compile: first set your HOST, DB, USER, PWD * CFLAGS =-Wall -g -O2 -D _XOPEN_SOURCE=700 -D _GNU_SOURCE * LDLIBS =-lmysqlclient * docs: no * refs: https://mariadb.com/kb/en/mariadb-connector-c/ * https://mariadb.com/kb/en/connectorc-types-and-definitions/ * comment: most simple application as a howto use prepared statement interface * focused on parameter only. * statments: * create table t_test (number integer primary key, name varchar(30), stamp datetime, birthtm datetime(6), price float, precise double, smally smallint, tiny tinyint unsigned, epoch bigint unsigned, x509cert varchar(3072), picture blob(65535)) comment 'test table mariadb test programs'; * select number, name, stamp, birthtm, price, precise, smally, tiny, epoch, length (x509cert), length (picture) from t_test; * history: *------------------------------------------------------------------------------ * this program is distributed under GPL license in the hope that it will * be useful, but WITHOUT ANY WARRANTY; without even the implied warranty * of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. * See the GNU Library General Public License for more details at * free software foundation. * * (c) Copyright 2022- by OIS-net.org ---------------------------------------------------------------------------- */ #include #include #include #include #include #include #include typedef struct s_dbinfo { // MARIADB_CONNECTION char *host; // HOST int port; // PORT char *user; // USER } dbinfo; // see https://mariadb.com/kb/en/mariadb_get_infov typedef struct test { // input / output buffer structure int number; // -2147483648 to 2147483647 char numberI; // null indicator char name[31]; // db buffer + EOS char nameI; char stamp[31]; // db buffer + EOS char stampI; MYSQL_TIME birthtm; // datetime as datastructure char birthtmI; float price; // 6 digit guaranteed precision IEEE 754 char priceI; double precise; // 15 digit guaranteed precision IEEE 754 char preciseI; short smally; // -32768 to 32767 char smallyI; unsigned char tiny; // 0 to 255 char tinyI; unsigned long long epoch; // -2^63 to 2^63-1 char epochI; char *x509cert; // allocated on demand EOS terminated char x509certI; char *picture; // allocated on demand not EOS terminated char pictureI; unsigned long pictureL; } test; /*------------------------------------------------------------------------------ * begin coding - this is the shortest way to show problems using goto i never use elsewhere - a more complete test is MysqlDynamic please ask for code if interested - compile it with CFLAGS =-Wall -g -O2 -D _XOPEN_SOURCE=700 -D _GNU_SOURCE - link it with LDLIBS =-lmysqlclient *--------------------------------------------------------------------------- */ int main (int argc, char *argv[]) { dbinfo dbi; int sver, cver; unsigned int errsav; MYSQL *con = 0; MYSQL_STMT *stmt = 0; MYSQL_BIND *bnd = 0; my_ulonglong done; test data; int bndvars, i; if (! (con = mysql_init (NULL))) { printf ("cant't initialize mariadb\n"); exit (1); } mysql_real_connect (con, "localhost", "testA", "Atest", "test", 0, NULL, 0); if ((errsav = mysql_errno (con))) { printf ("can't connect to test err:%d <%s>\n", errsav, mysql_error (con)); exit (2); } mariadb_get_infov (con, MARIADB_CONNECTION_HOST, (void *)&dbi.host); mariadb_get_infov (con, MARIADB_CONNECTION_USER, (void *)&dbi.user); printf ("connected on %s to db test by %s\n", dbi.host, dbi.user); sver = mysql_get_server_version (con); cver = mysql_get_client_version (); printf ("mariadb server ver:<%d>, client ver:<%d>\n", sver, cver); /*-------------------------------------------------------------------------- * set data - yea - it's stupid - but short * data record with num will be updated *----------------------------------------------------------------------- */ memset (&data, 0, sizeof (data)); data.number = 87; strcpy (data.name, "just a test"); strcpy (data.stamp, "2002-03-26"); data.birthtm.year = 2001; data.birthtm.month = 2; data.birthtm.day = 20; data.birthtm.hour = 16; data.birthtm.minute = 45; data.birthtm.second =3; data.birthtm.second_part = 455; data.birthtmI = 1; data.price = -234.345; data.precise = 198.77930890098; data.preciseI = 1; data.smally = 19458; data.tiny = 'A'; data.epoch = time( NULL ); data.x509cert = malloc (10); memset(data.x509cert, 0, 10); // short cut data.x509certI = 1; data.picture = malloc (10); memset(data.picture, 0, 10); // short cut data.pictureI = 1; /*-------------------------------------------------------------------------- * prepare .. currently an update *----------------------------------------------------------------------- */ stmt = mysql_stmt_init (con); if (!stmt) { puts ("mysql_stmt_init() out of memory"); goto end; } char query[] = "update t_test set name = ?, stamp = ?, birthtm = ?, price = ?, precise = ?, smally = ?, tiny = ?, epoch = ?, x509cert = ? where number = ?"; bndvars = 10; if (mysql_stmt_prepare (stmt, query, -1)) { errsav = mysql_stmt_errno (stmt); printf ("prepare <%s> failed err:%d <%s>\n", query, errsav, mysql_stmt_error (stmt)); goto end; } i = sizeof (MYSQL_BIND) * bndvars; bnd = malloc (i); if (!bnd) { errsav = errno; printf ("can^t malloc bnd err:%d <%s>\n", errsav, strerror (errsav)); errsav = errno; goto end; } memset (bnd, 0, i); bnd[0].buffer_type = MYSQL_TYPE_STRING; bnd[0].buffer = &data.name; bnd[0].is_null_value = data.nameI; bnd[0].length_value = (unsigned long) strlen (data.name); bnd[1].buffer_type = MYSQL_TYPE_STRING; bnd[1].buffer = &data.stamp; bnd[1].is_null_value = data.stampI; bnd[1].length_value = (unsigned long) strlen (data.stamp); bnd[2].buffer_type = MYSQL_TYPE_DATETIME; bnd[2].buffer = &data.birthtm; bnd[2].is_null_value = data.birthtmI; bnd[3].buffer_type = MYSQL_TYPE_FLOAT; bnd[3].buffer = &data.price; bnd[3].is_null_value = data.priceI; bnd[3].is_unsigned = 0; bnd[4].buffer_type = MYSQL_TYPE_DOUBLE; bnd[4].buffer = &data.precise; bnd[4].is_null_value = data.preciseI; bnd[4].is_unsigned = 0; bnd[5].buffer_type = MYSQL_TYPE_SHORT; bnd[5].buffer = &data.smally; bnd[5].is_null_value = data.smallyI; bnd[5].is_unsigned = 0; bnd[6].buffer_type = MYSQL_TYPE_TINY; bnd[6].buffer = &data.tiny; bnd[6].is_null_value = data.tinyI; bnd[6].is_unsigned = 1; bnd[7].buffer_type = MYSQL_TYPE_LONGLONG; bnd[7].buffer = &data.epoch; bnd[7].is_null_value = data.epochI; bnd[7].is_unsigned = 1; bnd[8].buffer_type = MYSQL_TYPE_STRING; bnd[8].buffer = &data.x509cert; bnd[8].is_null_value = data.x509certI; bnd[8].length_value = (unsigned long) strlen (data.x509cert); bnd[9].buffer_type = MYSQL_TYPE_LONG; bnd[9].buffer = &data.number; bnd[9].is_null_value = data.numberI; bnd[9].is_unsigned = 0; if (mysql_stmt_bind_param (stmt, bnd)) { errsav = mysql_stmt_errno (stmt); fprintf (stderr, "bind param failed err:%d <%s>\n", errsav, mysql_stmt_error (stmt)); goto end; } /*-------------------------------------------------------------------------- * update database record *----------------------------------------------------------------------- */ if (mysql_stmt_execute (stmt)) { errsav = mysql_stmt_errno (stmt); fprintf (stderr, "execute failed err:%d <%s>\n", errsav, mysql_stmt_error (stmt)); goto end; } done = mysql_stmt_affected_rows (stmt); if (done) { printf ("%llu records updated\n", done); if (mysql_commit (con)) { errsav = mysql_errno (con); fprintf (stderr, "commit failed err:%d <%s>\n", errsav, mysql_error (con)); } } else { puts ("no records updated"); } end: /*-------------------------------------------------------------------------- * shoot down *----------------------------------------------------------------------- */ if (bnd) { free (bnd); } if (stmt) { mysql_stmt_close (stmt); } if (con) { mysql_close (con); } puts ("i'm done"); }