对 tbl_qual_par_def 表的操作函数;MYSQL_BIND 按照sql ? 顺序绑定;
#include<stdio.h>
#include<string.h>
#include<mysql.h>
#include"DbMysqlStruct.h"
#include <stdbool.h>
/*******************************************************************
*
*
* V1 20200727 hyxu 返回值:-1 失败,0 成功,1 成功
*
*
********************************************************************/
#define PRINTFERROR(errmessage) printf("%s error\n",errmessage)
MYSQL_STMT *mysql_stmt=NULL;
MYSQL *mysql=NULL;
MYSQL_RES *mysql_res=NULL;
MYSQL_BIND bind[12];
my_ulonglong affected_rows;
my_bool is_null[12];
unsigned long length[12];
int row_count=0,ret=0;
tbl_qual_par_def tmpStruct;
char sqlbuf[512]= {0};
int getMysqlStmt()
{
mysql=mysql_init(NULL);
mysql=mysql_connect_qual();
if(mysql==NULL)
{
PRINTFERROR("mysql_connect_qual error");
return -1;
}
mysql_stmt=mysql_stmt_init(mysql);
if(mysql_stmt==NULL)
{
PRINTFERROR("mysql_stmt_init error");
return -1;
}
return 0;
}
int tbl_qual_par_def_bind_all()
{
memset(bind,0,sizeof(bind));
bind[0].buffer_type=MYSQL_TYPE_STRING;
bind[0].buffer=tmpStruct.par_id;
bind[0].buffer_length=sizeof(tmpStruct.par_id);
length[0]=strlen(tmpStruct.par_id);
bind[0].length=&length[0];
bind[0].is_null=&is_null[0];
bind[1].buffer_type=MYSQL_TYPE_STRING;
bind[1].buffer=tmpStruct.par_key;
bind[1].buffer_length=sizeof(tmpStruct.par_key);
length[1]=strlen(tmpStruct.par_key);
bind[1].length=&length[1];
bind[1].is_null=&is_null[0];
bind[2].buffer_type=MYSQL_TYPE_STRING;
bind[2].buffer=tmpStruct.par_val;
bind[2].buffer_length=sizeof(tmpStruct.par_val);
length[2]=strlen(tmpStruct.par_val);
bind[2].length=&length[2];
bind[2].is_null=&is_null[0];
bind[3].buffer_type=MYSQL_TYPE_STRING;
bind[3].buffer=tmpStruct.val_name;
bind[3].buffer_length=sizeof(tmpStruct.val_name);
length[3]=strlen(tmpStruct.val_name);
bind[3].length=&length[3];
bind[3].is_null=&is_null[0];
bind[4].buffer_type=MYSQL_TYPE_STRING;
bind[4].buffer=tmpStruct.effective;
bind[4].buffer_length=sizeof(tmpStruct.effective);
length[4]=strlen(tmpStruct.effective);
bind[4].length=&length[4];
bind[4].is_null=&is_null[0];
bind[5].buffer_type=MYSQL_TYPE_STRING;
bind[5].buffer=tmpStruct.resv_fld1;
bind[5].buffer_length=sizeof(tmpStruct.resv_fld1);
length[5]=strlen(tmpStruct.resv_fld1);
bind[5].length=&length[5];
bind[5].is_null=&is_null[0];
bind[6].buffer_type=MYSQL_TYPE_STRING;
bind[6].buffer=tmpStruct.resv_fld2;
bind[6].buffer_length=sizeof(tmpStruct.resv_fld2);
length[6]=strlen(tmpStruct.resv_fld2);
bind[6].length=&length[6];
bind[6].is_null=&is_null[0];
bind[7].buffer_type=MYSQL_TYPE_STRING;
bind[7].buffer=tmpStruct.resv_fld3;
bind[7].buffer_length=sizeof(tmpStruct.resv_fld3);
length[7]=strlen(tmpStruct.resv_fld3);
bind[7].length=&length[7];
bind[7].is_null=&is_null[0];
bind[8].buffer_type=MYSQL_TYPE_STRING;
bind[8].buffer=tmpStruct.first_upd_tm;
bind[8].buffer_length=sizeof(tmpStruct.first_upd_tm);
length[8]=strlen(tmpStruct.first_upd_tm);
bind[8].length=&length[8];
bind[8].is_null=&is_null[0];
bind[9].buffer_type=MYSQL_TYPE_STRING;
bind[9].buffer=tmpStruct.last_upd_usr;
bind[9].buffer_length=sizeof(tmpStruct.last_upd_usr);
length[9]=strlen(tmpStruct.last_upd_usr);
bind[9].length=&length[9];
bind[9].is_null=&is_null[0];
bind[10].buffer_type=MYSQL_TYPE_STRING;
bind[10].buffer=tmpStruct.last_upd_tm;
bind[10].buffer_length=sizeof(tmpStruct.last_upd_tm);
length[10]=strlen(tmpStruct.last_upd_tm);
bind[10].length=&length[10];
bind[10].is_null=&is_null[0];
bind[11].buffer_type=MYSQL_TYPE_STRING;
bind[11].buffer=tmpStruct.last_opr_usr;
bind[11].buffer_length=sizeof(tmpStruct.last_opr_usr);
length[11]=strlen(tmpStruct.last_opr_usr);
bind[11].length=&length[11];
bind[11].is_null=&is_null[0];
return 0;
}
int tbl_qual_par_def_bind_by_pk()
{
memset(bind,0,sizeof(bind));
bind[0].buffer_type=MYSQL_TYPE_STRING;
bind[0].buffer=tmpStruct.par_key;
bind[0].buffer_length=sizeof(tmpStruct.par_key);
length[0]=strlen(tmpStruct.par_key);
bind[0].length=&length[0];
bind[0].is_null=&is_null[0];
bind[1].buffer_type=MYSQL_TYPE_STRING;
bind[1].buffer=tmpStruct.par_val;
bind[1].buffer_length=sizeof(tmpStruct.par_val);
length[1]=strlen(tmpStruct.par_val);
bind[1].length=&length[1];
bind[1].is_null=&is_null[0];
bind[2].buffer_type=MYSQL_TYPE_STRING;
bind[2].buffer=tmpStruct.val_name;
bind[2].buffer_length=sizeof(tmpStruct.val_name);
length[2]=strlen(tmpStruct.val_name);
bind[2].length=&length[2];
bind[2].is_null=&is_null[0];
bind[3].buffer_type=MYSQL_TYPE_STRING;
bind[3].buffer=tmpStruct.effective;
bind[3].buffer_length=sizeof(tmpStruct.effective);
length[3]=strlen(tmpStruct.effective);
bind[3].length=&length[3];
bind[3].is_null=&is_null[0];
bind[4].buffer_type=MYSQL_TYPE_STRING;
bind[4].buffer=tmpStruct.resv_fld1;
bind[4].buffer_length=sizeof(tmpStruct.resv_fld1);
length[4]=strlen(tmpStruct.resv_fld1);
bind[4].length=&length[4];
bind[4].is_null=&is_null[0];
bind[5].buffer_type=MYSQL_TYPE_STRING;
bind[5].buffer=tmpStruct.resv_fld2;
bind[5].buffer_length=sizeof(tmpStruct.resv_fld2);
length[5]=strlen(tmpStruct.resv_fld2);
bind[5].length=&length[5];
bind[5].is_null=&is_null[0];
bind[6].buffer_type=MYSQL_TYPE_STRING;
bind[6].buffer=tmpStruct.resv_fld3;
bind[6].buffer_length=sizeof(tmpStruct.resv_fld3);
length[6]=strlen(tmpStruct.resv_fld3);
bind[6].length=&length[6];
bind[6].is_null=&is_null[0];
bind[7].buffer_type=MYSQL_TYPE_STRING;
bind[7].buffer=tmpStruct.first_upd_tm;
bind[7].buffer_length=sizeof(tmpStruct.first_upd_tm);
length[7]=strlen(tmpStruct.first_upd_tm);
bind[7].length=&length[7];
bind[7].is_null=&is_null[0];
bind[8].buffer_type=MYSQL_TYPE_STRING;
bind[8].buffer=tmpStruct.last_upd_usr;
bind[8].buffer_length=sizeof(tmpStruct.last_upd_usr);
length[8]=strlen(tmpStruct.last_upd_usr);
bind[8].length=&length[8];
bind[8].is_null=&is_null[0];
bind[9].buffer_type=MYSQL_TYPE_STRING;
bind[9].buffer=tmpStruct.last_upd_tm;
bind[9].buffer_length=sizeof(tmpStruct.last_upd_tm);
length[9]=strlen(tmpStruct.last_upd_tm);
bind[9].length=&length[9];
bind[9].is_null=&is_null[0];
bind[10].buffer_type=MYSQL_TYPE_STRING;
bind[10].buffer=tmpStruct.last_opr_usr;
bind[10].buffer_length=sizeof(tmpStruct.last_opr_usr);
length[10]=strlen(tmpStruct.last_opr_usr);//last_opr_usr
bind[10].length=&length[10];
bind[10].is_null=&is_null[0];
bind[11].buffer_type=MYSQL_TYPE_STRING;
bind[11].buffer=tmpStruct.par_id;
bind[11].buffer_length=sizeof(tmpStruct.par_id);
length[11]=strlen(tmpStruct.par_id);
bind[11].length=&length[11];
bind[11].is_null=&is_null[0];
return 0;
}
int tbl_qual_par_def_printf(tbl_qual_par_def *tmp)
{
memset(&tmpStruct,0,sizeof(tbl_qual_par_def));
memcpy(&tmpStruct,tmp,sizeof(tbl_qual_par_def));
printf("tmp->par_id is [%s]\n", tmp->par_id );
printf("tmp->par_key is [%s]\n", tmp->par_key );
printf("tmp->par_val is [%s]\n", tmp->par_val );
printf("tmp->val_name is [%s]\n", tmp->val_name );
printf("tmp->effective is [%s]\n", tmp->effective );
printf("tmp->resv_fld is [%s]\n", tmp->resv_fld1 );
printf("tmp->resv_fld is [%s]\n", tmp->resv_fld2 );
printf("tmp->resv_fld3 is [%s]\n", tmp->resv_fld3 );
printf("tmp->first_upd_tm is [%s]\n", tmp->first_upd_tm );
printf("tmp->last_upd_usr is [%s]\n", tmp->last_upd_usr );
printf("tmp->last_upd_tm is [%s]\n", tmp->last_upd_tm );
printf("tmp->last_opr_usr is [%s]\n", tmp->last_opr_usr);
printf("tmpStruct.par_id is [%s]\n", tmpStruct.par_id );
printf("tmpStruct.par_key is [%s]\n", tmpStruct.par_key );
printf("tmpStruct.par_val is [%s]\n", tmpStruct.par_val );
printf("tmpStruct.val_name is [%s]\n", tmpStruct.val_name );
printf("tmpStruct.effective is [%s]\n", tmpStruct.effective );
printf("tmpStruct.resv_fld is [%s]\n", tmpStruct.resv_fld1 );
printf("tmpStruct.resv_fld is [%s]\n", tmpStruct.resv_fld2 );
printf("tmpStruct.resv_fld3 is [%s]\n", tmpStruct.resv_fld3 );
printf("tmpStruct.first_upd_tm is [%s]\n", tmpStruct.first_upd_tm );
printf("tmpStruct.last_upd_usr is [%s]\n", tmpStruct.last_upd_usr );
printf("tmpStruct.last_upd_tm is [%s]\n", tmpStruct.last_upd_tm );
printf("tmpStruct.last_opr_usr is [%s]\n", tmpStruct.last_opr_usr);
}
int mysql_stmt_prepare_execute()
{
if(mysql_stmt_bind_param(mysql_stmt,bind))
{
PRINTFERROR("mysql_stmt_bind_param error,EXIT\n");
return -1;
}
ret=mysql_stmt_execute(mysql_stmt);
if(ret)
{
printf("mysql_stmt_excute error[%d],EXIT\n",ret);
ret = mysql_errno(mysql);
printf("the ret is [%d]\n",ret);
printf("the error message is [%s]\n",mysql_stmt_error(mysql_stmt));
return ret;
}
affected_rows=mysql_stmt_affected_rows(mysql_stmt);
printf("mysql_stmt_affected_rows is [%d]\n",affected_rows);
mysql_stmt_close(mysql_stmt);
mysql_close(mysql);
return 0;
}
int tbl_qual_par_def_sel_by_pk(tbl_qual_par_def *tmp)
{
MYSQL_BIND bind_in[1];
my_bool is_null_in[1];
unsigned long length_in[1];
memset(bind_in,0,sizeof(bind_in));
memset(sqlbuf,0,sizeof(sqlbuf));
strcpy(sqlbuf,"select * from tbl_qual_par_def where par_id=? ");
memset(&tmpStruct,0,sizeof(tbl_qual_par_def));
memcpy(&tmpStruct,tmp,sizeof(tbl_qual_par_def));
ret=getMysqlStmt();
if(ret)
{
PRINTFERROR("getMysqlStmt error");
return -1;
}
ret=mysql_stmt_prepare(mysql_stmt,sqlbuf,strlen(sqlbuf));
if(ret)
{
PRINTFERROR("mysql_stmt_prepare error,EXIT\n");
return -1;
}
mysql_res=mysql_stmt_result_metadata(mysql_stmt);
if(mysql_res==NULL)
{
PRINTFERROR("mysql_stmt_result_metadata error\n");
printf("error message[%s]\n",mysql_stmt_error(mysql_stmt));
return -1;
}
bind_in[0].buffer_type=MYSQL_TYPE_STRING;
bind_in[0].buffer=tmpStruct.par_id;
bind_in[0].buffer_length=sizeof(tmpStruct.par_id);
length_in[0]=strlen(tmpStruct.par_id);
bind_in[0].length=&length_in[0];
bind_in[0].is_null=&is_null_in[0];
/* 绑定参数 */
if (mysql_stmt_bind_param(mysql_stmt, bind_in))
{
PRINTFERROR("mysql_stmt_bind_param error,EXIT\n");
return -1;
}
if(mysql_stmt_execute(mysql_stmt))
{
PRINTFERROR("mysql_stmt_execute error\n");
printf("error Message [%s]\n",mysql_stmt_error(mysql_stmt));
return -1;
}
tbl_qual_par_def_bind_all();
if(mysql_stmt_bind_result(mysql_stmt,bind))
{
PRINTFERROR("mysql_stmt_bind_result error\n");
printf("mysql error message[%s]\n",mysql_stmt_error(mysql_stmt));
return -1;
}
if(mysql_stmt_store_result(mysql_stmt))
{
PRINTFERROR("mysql_stmt_store_result error\n");
printf("mysql error message[%s]\n",mysql_stmt_error(mysql_stmt));
return -1;
}
memset(&tmpStruct,0,sizeof(tbl_qual_par_def));
if(mysql_stmt_fetch(mysql_stmt)==0)
{
row_count++;
printf("fetching the [%d] data\n",row_count);
memcpy(tmp,&tmpStruct,sizeof(tbl_qual_par_def));
}
else
{
PRINTFERROR("mysql_stmt_fetch error\n");
mysql_free_result(mysql_res);
mysql_stmt_close(mysql_stmt);
return -1;
}
mysql_free_result(mysql_res);
mysql_stmt_close(mysql_stmt);
return 0;
}
int tbl_qual_par_def_insert(tbl_qual_par_def *tmp)
{
memset(sqlbuf,0,sizeof(sqlbuf));
strcpy(sqlbuf,"insert into tbl_qual_par_def values (?,?,?,?,?,?,?,?,?,?,?,?)");
memset(&tmpStruct,0,sizeof(tbl_qual_par_def));
ret=getMysqlStmt();
if(ret)
{
PRINTFERROR("getMysqlStmt error");
return -1;
}
memcpy(&tmpStruct,tmp,sizeof(tbl_qual_par_def));
ret=mysql_stmt_prepare(mysql_stmt,sqlbuf,strlen(sqlbuf));
if(ret)
{
PRINTFERROR("mysql_stmt_prepare error,EXIT\n");
return -1;
}
tbl_qual_par_def_bind_all();
ret=mysql_stmt_prepare_execute();
if(ret)
{
PRINTFERROR("mysql_stmt_prepare_execute error,EXIT\n");
return -1;
}
return 0;
}
int tbl_qual_par_def_update_by_pk(tbl_qual_par_def *tmp)
{
memset(sqlbuf,0,sizeof(sqlbuf));
strcpy(sqlbuf," update tbl_qual_par_def set par_key=?,par_val=?,val_name=?,effective=?,resv_fld1=?,resv_fld2=?,resv_fld3=?,first_upd_tm=?,\
last_upd_usr=?,last_upd_tm=?,last_opr_usr=? where par_id=? ");
memset(&tmpStruct,0,sizeof(tbl_qual_par_def));
ret=getMysqlStmt();
if(ret)
{
PRINTFERROR("getMysqlStmt error");
return -1;
}
memcpy(&tmpStruct,tmp,sizeof(tbl_qual_par_def));
ret=mysql_stmt_prepare(mysql_stmt,sqlbuf,strlen(sqlbuf));
if(ret)
{
PRINTFERROR("mysql_stmt_prepare error,EXIT\n");
return -1;
}
tbl_qual_par_def_bind_by_pk();
ret=mysql_stmt_prepare_execute();
if(ret)
{
PRINTFERROR("mysql_stmt_prepare_execute error,EXIT\n");
return -1;
}
return 0;
}
int tbl_qual_par_def_del_by_pk(tbl_qual_par_def *tmp)
{
memset(sqlbuf,0,sizeof(sqlbuf));
strcpy(sqlbuf,"delete from tbl_qual_par_def where par_id=? ");
memset(&tmpStruct,0,sizeof(tbl_qual_par_def));
ret=getMysqlStmt();
if(ret)
{
PRINTFERROR("getMysqlStmt error");
return -1;
}
memcpy(&tmpStruct,tmp,sizeof(tbl_qual_par_def));
ret=mysql_stmt_prepare(mysql_stmt,sqlbuf,strlen(sqlbuf));
if(ret)
{
PRINTFERROR("mysql_stmt_prepare error,EXIT\n");
return -1;
}
tbl_qual_par_def_bind_all();
ret=mysql_stmt_prepare_execute();
if(ret)
{
PRINTFERROR("mysql_stmt_prepare_execute error,EXIT\n");
return -1;
}
return 0;
}
int tbl_qual_par_def_openCur(char *sqlbuf)
{
ret=getMysqlStmt();
if(ret)
{
PRINTFERROR("getMysqlStmt error");
return -1;
}
if(mysql_stmt_prepare(mysql_stmt,sqlbuf,strlen(sqlbuf)))
{
PRINTFERROR("mysql_stmt_prepare error");
return -1;
}
mysql_res=mysql_stmt_result_metadata(mysql_stmt);
if(mysql_res==NULL)
{
PRINTFERROR("mysql_stmt_result_metadata error\n");
printf("error message[%s]\n",mysql_stmt_error(mysql_stmt));
return -1;
}
if(mysql_stmt_execute(mysql_stmt))
{
PRINTFERROR("mysql_stmt_execute error\n");
printf("error Message [%s]\n",mysql_stmt_error(mysql_stmt));
return -1;
}
tbl_qual_par_def_bind_all();
if(mysql_stmt_bind_result(mysql_stmt,bind))
{
PRINTFERROR("mysql_stmt_bind_result error\n");
printf("mysql error message[%s]\n",mysql_stmt_error(mysql_stmt));
return -1;
}
if(mysql_stmt_store_result(mysql_stmt))
{
PRINTFERROR("mysql_stmt_store_result error\n");
printf("mysql error message[%s]\n",mysql_stmt_error(mysql_stmt));
return -1;
}
}
int tbl_qual_par_def_fetch(tbl_qual_par_def *tmp)
{
memset(&tmpStruct,0,sizeof(tbl_qual_par_def));
if(mysql_stmt_fetch(mysql_stmt)==0)
{
row_count++;
printf("fetching the [%d] data\n",row_count);
memcpy(tmp,&tmpStruct,sizeof(tbl_qual_par_def));
return 1;
}
return 0;
}
int tbl_qual_par_def_closeCur()
{
mysql_free_result(mysql_res);
if(mysql_stmt_close(mysql_stmt))
{
PRINTFERROR("mysql_stmt_close error\n");
printf("mysql error message[%s]\n",mysql_stmt_error(mysql_stmt));
return -1;
}
mysql_close(mysql);
}
###########################################################################################