使用CVI软件平台进行Mysql数据库开发简介

1、使用Labwindow CVI软件平台进行Mysql数据库开发简介针对初学者进行数据库开发，不是很了解相应过程，现将我刚刚成功的验证过程，与大家分享。文中主要描述不能使用映射方式修改数据库中数据的问题。1. CVI版本为9.0（348），NI SQL Toolkit 2.2请前往 www.NI.com 下载试用版软件系统！2. MySQL版本为5.6.221、 安装MySQL过程可参考网上的说明，如网络上，MySQL下载与安装安装图解请前往处下载开源数据库系统MySQL3. 使用王建新老师的例程进行学习4. 更改了修改数据按钮中的代码备注：1、 通过更换SQL Toolkit2

2、.1改为2.2后，还是不能修改MySQL已存在数据库中数据的问题。2、 不使用映射的方式进行更新数据库数据，这种方式能满足Access创建的数据库，但是不能修改MySQL创建的数据库，原因不知，希望有高手指点 weiyizhinengQQ.com3、 更改为使用SQL直接进行更改数据，实现更改表中一条数据的功能。谢谢LabWindows/CVI交流群 20328398里各位老师的奉献！修改后的代码：#include "cvi_db.h"#include <ansi_c.h>#include <utility.h>#include <format

3、io.h>#include <cvirte.h>#include <userint.h>#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, "databasesamp

4、le.uir", PANEL) < 0)return -1;/建立数据库联接，并获得联接句柄hdbc = DBConnect ("DSN=client");DisplayPanel (panelHandle);RunUserInterface ();DiscardPanel (panelHandle);/当退出应用程序时，关闭数据库连接DBDisconnect (hdbc); return 0;int CVICALLBACK QuitCallback (int panel, int control, int event,void *callbackData,

5、 int eventData1, int eventData2)switch (event)case EVENT_COMMIT:QuitUserInterface (0);break;return 0;/删除记录int CVICALLBACK deletedata (int panel, int control, int event,void *callbackData, int eventData1, int eventData2)Point colrow;int resultcode;int hstat;int value = 0;switch (event)case EVENT_COMM

6、IT:/获得活动表格单元GetActiveTableCell (panelHandle, PANEL_TABLE, &colrow);/获得活动单元格所在行，且列数为第1列单元格内的数据GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(1, colrow.y), &value);/当联接数据库成功时进行下面操作if (hdbc > 0) /对于带参数的数据库查询，首先准备执行SQL查询声明hstat = DBPrepareSQL (hdbc, "DELETE FROM table2 WHERE ID = ?

7、");/创建一个整型量参数预备查询方式resultcode = DBCreateParamInt (hstat, "", DB_PARAM_INPUT, value);/执行带参数的SQL查询resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat);/刷新数据readdata (panel, PANEL_CMD_READDATA, EVENT_COMMIT, NULL, 0, 0); break;return 0;/插入记录int CVICALLBACK insertdata (int

8、 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 = DBNumberOfRecords (hstat);DBDeactivateSQL (hs

9、tat);/带参数查询hstat = DBPrepareSQL (hdbc, "INSERT INTO table2 VALUES (?, '', '', 0)");resultcode = DBCreateParamInt (hstat, "ID", DB_PARAM_INPUT, numberofrecords + 1);resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat); /刷新数据readdata (panel, PANEL_CM

10、D_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 agestatus;in

11、t 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)SetCtrlA

12、ttribute (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_M

13、ODIFY, ATTR_LABEL_TEXT, labeltext);/将得到的文本与“修改”二字相对照comparisonresult = strcmp (labeltext, "修改");/如果确定标签文本上的字符就是“修改”二字，则执行以下操作if (comparisonresult = 0)/当联接数据库成功时进行下面操作if (hdbc > 0) memset(SqlCommand,0,200);/一次修改只能修改最后触发的行数据,否则将以下代码放到表格控件的事件函数中GetTableCellVal (panelHandle, PANEL_TABLE, Ma

14、kePoint(2, globalvalue), namevalue);GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(3, globalvalue), sexvalue); GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(4, globalvalue), &agevalue);sprintf(SqlCommand,"UPDATE table2 SET NAME='%s',SEX='%s',AGE=%d WHERE ID=%d;&

15、quot;,namevalue,sexvalue,agevalue,globalvalue);/带参数更新hstat = DBPrepareSQL (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)in

16、t hstat;switch (event)case EVENT_COMMIT:DisableBreakOnLibraryErrors ();/激活SQL查询hstat = DBActivateSQL (hdbc, "SELECT * FROM table2");/如果表存在数据，执行如下操作if (hstat > 0)/释放被激活句柄DBDeactivateSQL (hstat);/删除数据表DBImmediateSQL (hdbc, "DROP TABLE table2"); DeleteTableRows (panelHandle, PANE

17、L_TABLE, 1, -1);SetCtrlAttribute (panelHandle, PANEL_CMD_READDATA, ATTR_DIMMED, 1);break;return 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 na

18、mevalue10;int resultcode;long idstatus;int idvalue;int hmap;switch (event)case EVENT_COMMIT:/创建一个数据库映射hmap = DBBeginMap (hdbc);resultcode = DBMapColumnToInt (hmap, "ID", &idvalue, &idstatus);resultcode = DBMapColumnToChar (hmap, "NAME", 10, namevalue, &namestatus, &qu

19、ot;");resultcode = DBMapColumnToChar (hmap, "SEX", 10, sexvalue, &sexstatus, "");resultcode = DBMapColumnToInt (hmap, "AGE", &agevalue, &agestatus);/由此映射创建一个数据表resultcode = DBCreateTableFromMap (hmap, "table2");if (resultcode >= 0)/向数据表中添加数

20、据DBImmediateSQL (hdbc, "INSERT INTO table2 VALUES (1, '杨过', '男', 23)");DBImmediateSQL (hdbc, "INSERT INTO table2 VALUES (2, '小龙女', '女', 29)");DBImmediateSQL (hdbc, "INSERT INTO table2 VALUES (3, '周伯通', '男', 78)"); DBDeacti

21、vateMap (hmap); SetCtrlAttribute (panelHandle, PANEL_CMD_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

22、 namevalue10;int resultcode;long idstatus;int idvalue;int hstat;int total = 0;int i = 1;switch (event)case EVENT_COMMIT:DisableBreakOnLibraryErrors ();/激活SQL查询hstat = DBActivateSQL (hdbc, "SELECT * FROM table2");/以下操作为将每列字段绑定到相关变量中resultcode = DBBindColInt (hstat, 1, &idvalue, &ids

23、tatus);resultcode = DBBindColChar (hstat, 2, 10, namevalue, &namestatus, "");resultcode = DBBindColChar (hstat, 3, 10, sexvalue, &sexstatus, "");resultcode = DBBindColInt (hstat, 4, &agevalue, &agestatus);/获得记录的总数total = DBNumberOfRecords (hstat);/删除面板中的表格控件所有行Del

24、eteTableRows (panelHandle, PANEL_TABLE, 1, -1);/插入与数据表中记录数相同的行数InsertTableRows (panelHandle, PANEL_TABLE, 1, total, VAL_USE_MASTER_CELL_TYPE);if (total > 0)/利用数据指针逐行写入面板表格控件中while (DBFetchNext(hstat) != DB_EOF)SetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(1,i), idvalue);SetTableCellVal (pa

25、nelHandle, PANEL_TABLE, MakePoint(2,i), namevalue);SetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(3,i), sexvalue);SetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(4,i), agevalue);i+;DBDeactivateSQL (hstat);break;return 0;int CVICALLBACK tableCB (int panel, int control, int event,void *call

26、backData, int eventData1, int eventData2)int value;Point colrow;switch (event)/当在面板中的表格控件中双击时，产生以下操作case EVENT_LEFT_DOUBLE_CLICK:/获得活动表格当前的行与列GetActiveTableCell (panelHandle, PANEL_TABLE, &colrow);/获得本行一列中数据的ID号GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(1, colrow.y), &value);/将ID号作

27、为全局变量处理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 resultcode;int hstat;int value;Point colrow;/设置i为静态局部变量，在本函数内部值不会丢失static int i = 1;long agest

28、atus;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_CTRL_MODE, VAL_HOT);/以下部分表示，当点击修改按钮时，按钮标签将变为“保存”if(i)SetCtrlAttribute (panelHandle,PANEL_CO

29、MMANDBUTTON_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;/获得按钮标签文本数据GetCtrlAttribute (panelHandle,PANEL_COMMANDBUTTON_8, ATTR_LABEL_TEXT,labeltext);/将得到的文本与“修改”二字进行比照comparisonresult = strcmp(labeltext,"修改");/如果确定标签文本