maria-discuss team mailing list archive

[juerg oehler <juerg.oehler@xxxxxxxxxxx>]

hi

I'm working with|mariadb|and|c-connect|prepared statements and run into 
crashes executing them once a while. i so addressed my problem to 
stakeoverflow 
<https://stackoverflow.com/questions/76170354/mariadb-howto-c-connect-bindings> 
hoping to get more information for binding programs data structure to db 
interface.

i did get an answer. but this answer does not go inline with your 
documentation 
<https://mariadb.com/kb/en/mariadb-connectorc-data-structures/#mysql_bind>. 
i so do have the option to follow your documentation or use the answer 
and run into crashes in either way. you might ask why i'm not just going 
with your documentation? your documentation is far to thin to get a 
reliable idea how bind structure is used and i do get proofed this by 
crashes. second i played with <my_bool *    is_nulll    Pointer which 
indicates if column is NULL (not used for parameters)> for parameters 
and behavior was not as documented.

this is my motivation to bring my problem to this mailing list hopping 
someone can point me to a documentation i can rely on.

i found a short and useful documentation in mysql with permissible in 
and out types 
<https://dev.mysql.com/doc/c-api/8.0/en/c-api-prepared-statement-type-codes.html> 
and statement data structures 
<https://dev.mysql.com/doc/c-api/8.0/en/c-api-prepared-statement-data-structures.html>. 
can i use these informations to build 2 test applications one focusing 
on parameter (attachment) one focusing on result?

currently i implemented MariaPrepUpd.c using mariadb documentation. i 
felt like flying blind and ended up in crashes - once a while. do you 
think i could use mysql documentation for improving bindings? or do you 
have a better option?

(i studied your bulk example 
<https://mariadb.com/kb/en/bulk-insert-row-wise-binding/>. i even was 
using this example as base for first try an hit the wall by using 
STMT_INDICATOR_NTS. i then learned my problem is not just the example 
using STMT_ATTR_ARRAY_SIZE = 1.)

my goal is to have a version of MariaPrepUpd.c i can use for any 
application for iteration 1 to n.

thank you for short answer.

have a great day.

juerg(=george) oehler

/*------v---v--------v----------------------------------------------------------
 * @(#) Maria DB test for prepared statements focus on parameters
 * @(#) cPgmMain     2022-02 by juerg.oehler@xxxxxxxxxxx
 *------------------------------------------------------------------------------
 * 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 <stdio.h>
#include <stdlib.h>
#include <stdint.h>
#include <string.h>
#include <errno.h>
#include <time.h>
#include <mysql/mysql.h>
 
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");
}