C-MySQL API准备的语句 [英] C - MySQL API prepared statements

问题描述

我遵循了此 MySQL手册的字母，并稍作更改以尝试制作一个向表中插入4个值的程序.该表的结构为:

  MariaDB [(none)]>desc analytics.live;+ -------------- + ------------- + ------ + ----- + --------+ ------- +|领域|类型|空|关键默认值|额外|+ -------------- + ------------- + ------ + ----- + --------+ ------- +|dat_sent |int(11)|否||NULL |||machine_id |varchar(33)|是的|NULL |||foreign_addr |varchar(50)|是的|NULL |||con_state |varchar(50)|是的|NULL |||数|int(11)|否||NULL ||+ -------------- + ------------- + ------ + ----- + --------+ ------- + 

我已经编译了它，并且可以成功地写入数据库..但是，我正在努力了解如何将参数传递到脚本中.

我的目标是拥有它，以便我可以通过cli的方式将4个参数传递给脚本:-

  ./a.out 0"989b3gf047196h2243bd395a97cde4c"；"192.168.0.1"已建立" 

目前，代码库位于此处: https://pastebin.com/npP1C8uz

我的问题是，如何使它接受每个argv []作为INSERT的一部分?

解决方案

我只对字符串使用了一个数组，每个字段都需要使用一个数组(及其伴随的计数)

使其正常工作:)

完整的代码-

  https://pastebin.com/WS1Qx050 

我必须将每个字段绑定到它们的 own 数据字符串.为此，我必须首先相应地定义每个字段:

  MYSQL * mysql = mysql_init(NULL);MYSQL_STMT * stmt;MYSQL_BIND绑定[4];int param_count;//简短的small_data;int f1_data;字符f2_data [STRING_SIZE];字符f3_data [STRING_SIZE];字符f4_data [STRING_SIZE];无符号长f2_length;无符号长f3_length;无符号长f4_length; 

然后，在进入INSERT阶段之前，我声明了每个绑定的.buffer属性作为指向其各自数组的指针.

 /*为所有4个参数绑定数据*/memset(bind，0，sizeof(bind));/*整数参数*/bind [0] .buffer_type = MYSQL_TYPE_LONG;bind [0] .buffer =(char *)& f1_data;bind [0] .is_null = 0;bind [0] .length = 0;/*字符串参数*/bind [1] .buffer_type = MYSQL_TYPE_STRING;bind [1] .buffer =(char *)f2_data;bind [1] .buffer_length = STRING_SIZE;bind [1] .is_null = 0;bind [1] .length =& f2_length;/*字符串参数*/bind [2] .buffer_type = MYSQL_TYPE_STRING;bind [2] .buffer =(char *)f3_data;bind [2] .buffer_length = STRING_SIZE;bind [2] .is_null = 0;bind [2] .length =& f3_length;/*字符串参数*/bind [3] .buffer_type = MYSQL_TYPE_STRING;bind [3] .buffer =(char *)f4_data;bind [3] .buffer_length = STRING_SIZE;bind [3] .is_null = 0;bind [3] .length =& f4_length; 

这样做可以让我正确地向MySQL提交插入查询，在第8行定义的准备好的语句中填写所有?.

I've followed this MySQL Manual to the letter, and changed it slightly to try and make a program that inserts 4 values into table. The structure of the table is:

MariaDB [(none)]> desc analytics.live;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| dat_sent     | int(11)     | NO   |     | NULL    |       |
| machine_id   | varchar(33) | YES  |     | NULL    |       |
| foreign_addr | varchar(50) | YES  |     | NULL    |       |
| con_state    | varchar(50) | YES  |     | NULL    |       |
| count        | int(11)     | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

I have compiled it, and can successfully write to database.. However I'm struggling to understand how I'm going to pass arguments into the script.

My goal is to have it so that I can pass 4 arguments into the script, from the cli in the fashion:-

./a.out 0 "989b3gf047196h2243bd395a97cde4c" "192.168.0.1" "ESTABLISHED"

At the minute, the code base is here: https://pastebin.com/npP1C8uz

My question is, how would I make it so that it accepts each argv[] as part of the INSERT?

解决方案

I was using only one array for strings, needed to use one for each field (and the accompanying counts)

Got it working :)

Completed code-

https://pastebin.com/WS1Qx050

I had to bind each of the fields to their own data strings. To do this, I had to firstly define each field accordingly:

    MYSQL *mysql = mysql_init(NULL);
    MYSQL_STMT    *stmt;
    MYSQL_BIND    bind[4];
    int           param_count;
    // short         small_data;

    int           f1_data;
    char          f2_data[STRING_SIZE];
    char          f3_data[STRING_SIZE];
    char          f4_data[STRING_SIZE];

    unsigned long f2_length;
    unsigned long f3_length;
    unsigned long f4_length; 

Then, before I got to the stage of INSERT, I declared the .buffer property for each bind as pointer to it's respective array.

 
    /* Bind data for all 4 parameters */
    memset(bind, 0, sizeof(bind));
 
    /* INTEGER PARAM */
    bind[0].buffer_type= MYSQL_TYPE_LONG;
    bind[0].buffer= (char *)&f1_data;
    bind[0].is_null= 0;
    bind[0].length= 0;
 
    /* STRING PARAM */
    bind[1].buffer_type= MYSQL_TYPE_STRING;
    bind[1].buffer= (char *)f2_data;
    bind[1].buffer_length= STRING_SIZE;
    bind[1].is_null= 0;
    bind[1].length= &f2_length;
 
    /* STRING PARAM */
    bind[2].buffer_type= MYSQL_TYPE_STRING;
    bind[2].buffer= (char *)f3_data;
    bind[2].buffer_length= STRING_SIZE;
    bind[2].is_null= 0;
    bind[2].length= &f3_length;
 
    /* STRING PARAM */
    bind[3].buffer_type= MYSQL_TYPE_STRING;
    bind[3].buffer= (char *)f4_data;
    bind[3].buffer_length= STRING_SIZE;
    bind[3].is_null= 0;
    bind[3].length= &f4_length;
 

Doing this allowed me to properly commit the insert query to MySQL, filling in all the ?'s in the prepared statement defined at line 8.

这篇关于C-MySQL API准备的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！