1、使用 Labwindow CVI 软件平台进行 Mysql 数据库开发简介针对初学者进行数据库开发，不是很了解相应过程，现将我刚刚成功的验证过程，与大家分享。文中主要描述不能使用映射方式修改数据库中数据的问题。1. CVI 版本为 9.0（348），NI SQL Toolkit 2.2请前往 www.NI.com 下载试用版软件系统！2. MySQL 版本为 5.6.221、安装 MySQL 过程可参考网上的说明，如网络上，MySQL 下载与安装 5.6.10.1 安装图解http:/ http:/ MySQL3. 使用王建新老师的例程进行学习DBTable_王 建 新源 文 件 .zip4.
2、 更改了修改数据按钮中的代码DBTable_更 改 修改 数 据 .zip备注：1、通过更换 SQL Toolkit2.1 改为 2.2 后，还是不能修改 MySQL 已存在数据库中数据的问题。2、不使用映射的方式进行更新数据库数据，这种方式能满足 Access 创建的数据库，但是不能修改 MySQL 创建的数据库，原因不知，希望有高手指点 weiyizhinengQQ.com3、更改为使用 SQL 直接进行更改数据，实现更改表中一条数据的功能。谢谢LabWindows/CVI 交流群 20328398 里各位老师的奉献！修改后的代码：#include “cvi_db.h“#include #
3、include #include #include #include #include “databasesample.h“static int hdbc;static int globalvalue;static int panelHandle;int main (int argc, char *argv)if (InitCVIRTE (0, argv, 0) = 0)return -1; /* out of memory */if (panelHandle = LoadPanel (0, “databasesample.uir“, PANEL) 0)/对于带参数的数据库查询，首先准备执行
4、SQL 查询声明hstat = DBPrepareSQL (hdbc, “DELETE FROM table2 WHERE ID = ?“);/创建一个整型量参数预备查询方式resultcode = DBCreateParamInt (hstat, “, DB_PARAM_INPUT, value);/执行带参数的 SQL 查询resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat);/刷新数据readdata (panel, PANEL_CMD_READDATA, EVENT_COMMIT, NULL, 0, 0
5、); break;return 0;/插入记录int CVICALLBACK insertdata (int panel, int control, int event,void *callbackData, int eventData1, int eventData2)int resultcode;int hstat;int numberofrecords;switch (event)case EVENT_COMMIT:/激活 SQL 查询hstat = DBActivateSQL (hdbc, “SELECT * FROM table2“);/获得记录总数numberofrecords =
6、 DBNumberOfRecords (hstat);DBDeactivateSQL (hstat);/带参数查询hstat = DBPrepareSQL (hdbc, “INSERT INTO table2 VALUES (?, , , 0)“);resultcode = DBCreateParamInt (hstat, “ID“, DB_PARAM_INPUT, numberofrecords + 1);resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat); /刷新数据readdata (panel, PA
7、NEL_CMD_READDATA, EVENT_COMMIT, NULL, 0, 0); break;return 0;/修改记录int CVICALLBACK modifydata (int panel, int control, int event,void *callbackData, int eventData1, int eventData2)char labeltext10;int comparisonresult;int hstat;int value;Point colrow;/设置 i 为静态局部变量，在本函数内部值不会丢失static int i = 1;long ages
8、tatus;int agevalue;long sexstatus;char sexvalue10;int resultcode;long idstatus;int idvalue;long namestatus;char namevalue10;char SqlCommand200;int hmap;switch (event)case EVENT_COMMIT:/设置表格为可修改状态SetCtrlAttribute (panelHandle, PANEL_TABLE, ATTR_CTRL_MODE, VAL_HOT);/以下程序表示：当点击“修改”按钮时，按钮标签将变为 “保存”if (i
9、)SetCtrlAttribute (panelHandle, PANEL_CMD_MODIFY, ATTR_LABEL_TEXT, “保存 “);i = 0;elseSetCtrlAttribute (panelHandle, PANEL_CMD_MODIFY, ATTR_LABEL_TEXT, “修改 “); SetCtrlAttribute (panelHandle, PANEL_TABLE, ATTR_CTRL_MODE, VAL_INDICATOR);i = 1;/得到按钮的标签文本GetCtrlAttribute (panelHandle, PANEL_CMD_MODIFY, AT
10、TR_LABEL_TEXT, labeltext);/将得到的文本与“修改”二字相对照comparisonresult = strcmp (labeltext, “修改“);/如果确定标签文本上的字符就是“修改” 二字，则执行以下操作if (comparisonresult = 0)/当联接数据库成功时进行下面操作if (hdbc 0)memset(SqlCommand,0,200);/一次修改只能修改最后触发的行数据 ,否则将以下代码放到表格控件的事件函数中GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(2, globalvalue)
11、, namevalue);GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(3, globalvalue), sexvalue); GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(4, globalvalue), sprintf(SqlCommand,“UPDATE table2 SET NAME=%s,SEX=%s,AGE=%d WHERE ID=%d;“,namevalue,sexvalue,agevalue,globalvalue);/带参数更新hstat = DBPrepa
12、reSQL (hdbc, SqlCommand);resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat); break;return 0;/删除数据表int CVICALLBACK deletetable (int panel, int control, int event,void *callbackData, int eventData1, int eventData2)int hstat;switch (event)case EVENT_COMMIT:DisableBreakOnLibraryErrors
13、();/激活 SQL 查询hstat = DBActivateSQL (hdbc, “SELECT * FROM table2“);/如果表存在数据，执行如下操作if (hstat 0)/释放被激活句柄DBDeactivateSQL (hstat);/删除数据表DBImmediateSQL (hdbc, “DROP TABLE table2“);DeleteTableRows (panelHandle, PANEL_TABLE, 1, -1);SetCtrlAttribute (panelHandle, PANEL_CMD_READDATA, ATTR_DIMMED, 1);break;ret
14、urn 0;/创建数据表int CVICALLBACK createtable (int panel, int control, int event,void *callbackData, int eventData1, int eventData2)long agestatus;int agevalue;long sexstatus;char sexvalue10;long namestatus;char namevalue10;int resultcode;long idstatus;int idvalue;int hmap;switch (event)case EVENT_COMMIT:
15、/创建一个数据库映射hmap = DBBeginMap (hdbc);resultcode = DBMapColumnToInt (hmap, “ID“, resultcode = DBMapColumnToChar (hmap, “NAME“, 10, namevalue, resultcode = DBMapColumnToChar (hmap, “SEX“, 10, sexvalue, resultcode = DBMapColumnToInt (hmap, “AGE“, /由此映射创建一个数据表resultcode = DBCreateTableFromMap (hmap, “tabl
16、e2“);if (resultcode = 0)/向数据表中添加数据DBImmediateSQL (hdbc, “INSERT INTO table2 VALUES (1, 杨过, 男, 23)“);DBImmediateSQL (hdbc, “INSERT INTO table2 VALUES (2, 小龙女, 女, 29)“);DBImmediateSQL (hdbc, “INSERT INTO table2 VALUES (3, 周伯通, 男, 78)“); DBDeactivateMap (hmap); SetCtrlAttribute (panelHandle, PANEL_CMD_
17、READDATA, ATTR_DIMMED, 0); break;return 0;/读取数据，即刷新数据int CVICALLBACK readdata (int panel, int control, int event,void *callbackData, int eventData1, int eventData2)long agestatus;int agevalue;long sexstatus;char sexvalue10;long namestatus;char namevalue10;int resultcode;long idstatus;int idvalue;int
18、 hstat;int total = 0;int i = 1;switch (event)case EVENT_COMMIT:DisableBreakOnLibraryErrors ();/激活 SQL 查询hstat = DBActivateSQL (hdbc, “SELECT * FROM table2“);/以下操作为将每列字段绑定到相关变量中resultcode = DBBindColInt (hstat, 1, resultcode = DBBindColChar (hstat, 2, 10, namevalue, resultcode = DBBindColChar (hstat,
19、 3, 10, sexvalue, resultcode = DBBindColInt (hstat, 4, /获得记录的总数total = DBNumberOfRecords (hstat);/删除面板中的表格控件所有行DeleteTableRows (panelHandle, PANEL_TABLE, 1, -1);/插入与数据表中记录数相同的行数InsertTableRows (panelHandle, PANEL_TABLE, 1, total, VAL_USE_MASTER_CELL_TYPE);if (total 0)/利用数据指针逐行写入面板表格控件中while (DBFetch
20、Next(hstat) != DB_EOF)SetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(1,i), idvalue);SetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(2,i), namevalue);SetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(3,i), sexvalue);SetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(4,i), agevalue);i+
21、;DBDeactivateSQL (hstat);break;return 0;int CVICALLBACK tableCB (int panel, int control, int event,void *callbackData, int eventData1, int eventData2)int value;Point colrow;switch (event)/当在面板中的表格控件中双击时，产生以下操作case EVENT_LEFT_DOUBLE_CLICK:/获得活动表格当前的行与列GetActiveTableCell (panelHandle, PANEL_TABLE, /获得
22、本行一列中数据的 ID 号GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(1, colrow.y), /将 ID 号作为全局变量处理globalvalue = value;break;return 0;修改前的部分代码：int CVICALLBACK Modifydata (int panel, int control, int event,void *callbackData, int eventData1, int eventData2)char labeltext10;int comparisonresult;int result
23、code;int hstat;int value;Point colrow;/设置 i 为静态局部变量，在本函数内部值不会丢失static int i = 1;long agestatus;int agevalue;long sexstatus;char sexvalue10;long namestatus;char namevalue10;long idstatus;int idvalue;int hmap;switch (event)case EVENT_COMMIT:/设置表格为可以修改状态SetCtrlAttribute (panelHandle,PANEL_TABLE, ATTR_C
24、TRL_MODE, VAL_HOT);/以下部分表示，当点击修改按钮时，按钮标签将变为 “保存”if(i)SetCtrlAttribute (panelHandle,PANEL_COMMANDBUTTON_8, ATTR_LABEL_TEXT, “保存 “);i = 0;elseSetCtrlAttribute (panelHandle,PANEL_COMMANDBUTTON_8, ATTR_LABEL_TEXT, “修改 “);SetCtrlAttribute (panelHandle,PANEL_TABLE, ATTR_CTRL_MODE, VAL_INDICATOR);i = 1;/获得
25、按钮标签文本数据GetCtrlAttribute (panelHandle,PANEL_COMMANDBUTTON_8, ATTR_LABEL_TEXT,labeltext);/将得到的文本与“修改”二字进行比照comparisonresult = strcmp(labeltext,“修改“);/如果确定标签文本上的字符为“修改”二字，则执行以下操作if(comparisonresult = 0)/创建一个数据库映射hmap = DBBeginMap(hdbc);resultcode = DBMapColumnToInt(hmap,“ID“,resultcode = DBMapColumnTo
26、Char(hmap,“NAME“,10,namevalue,resultcode = DBMapColumnToChar(hmap,“SEX“,10,sexvalue,resultcode = DBMapColumnToInt(hmap,“AGE“,/激活映射hstat = DBActivateMap(hmap,“table2“);if(hstat 0)/定位数据指针while(resultcode = DBFetchNext(hstat) != DB_EOF)if(idvalue = globalvalue)break;GetTableCellVal(panelHandle,PANEL_TA
27、BLE,MakePoint(2,globalvalue),namevalue);GetTableCellVal(panelHandle,PANEL_TABLE,MakePoint(3,globalvalue),sexvalue);GetTableCellVal(panelHandle,PANEL_TABLE,MakePoint(4,globalvalue),/添加记录/DBCreateRecord (hstat);/更新数据库数据DBUpdateBatch (hmap, DB_AFFECT_CURRENT);resultcode = DBPutRecord(hmap);/释放被激活的句柄resultcode = DBDeactivateMap(hmap);break;return 0;
