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

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

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

3、tatic 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) /对于带参数的数据库查询，首先准备执行SQL查询声明hstat = DBPrepareSQL (hdbc, DELETE FROM table2 WHERE ID = ?);/创建一个整型量参数预备查询方式resultcode = DBCreate

4、ParamInt (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 panel, int control, int event,void *callbackData, int ev

5、entData1, int eventData2)int resultcode;int hstat;int numberofrecords;switch (event)case EVENT_COMMIT:/激活SQL查询hstat = DBActivateSQL (hdbc, SELECT * FROM table2);/获得记录总数numberofrecords = DBNumberOfRecords (hstat);DBDeactivateSQL (hstat);/带参数查询hstat = DBPrepareSQL (hdbc, INSERT INTO table2 VALUES (?,

6、, , 0);resultcode = DBCreateParamInt (hstat, ID, DB_PARAM_INPUT, numberofrecords + 1);resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat); /刷新数据readdata (panel, PANEL_CMD_READDATA, EVENT_COMMIT, NULL, 0, 0); break;return 0;/修改记录int CVICALLBACK modifydata (int panel, int control, int

7、 event,void *callbackData, int eventData1, int eventData2)char labeltext10;int comparisonresult;int hstat;int value;Point colrow;/设置i为静态局部变量，在本函数内部值不会丢失static int i = 1;long agestatus;int agevalue;long sexstatus;char sexvalue10;int resultcode;long idstatus;int idvalue;long namestatus;char namevalue1

8、0;char SqlCommand200;int hmap;switch (event)case EVENT_COMMIT:/设置表格为可修改状态SetCtrlAttribute (panelHandle, PANEL_TABLE, ATTR_CTRL_MODE, VAL_HOT);/以下程序表示：当点击“修改”按钮时，按钮标签将变为“保存”if (i)SetCtrlAttribute (panelHandle, PANEL_CMD_MODIFY, ATTR_LABEL_TEXT, 保存);i = 0;elseSetCtrlAttribute (panelHandle, PANEL_CMD_M

9、ODIFY, ATTR_LABEL_TEXT, 修改); SetCtrlAttribute (panelHandle, PANEL_TABLE, ATTR_CTRL_MODE, VAL_INDICATOR);i = 1;/得到按钮的标签文本GetCtrlAttribute (panelHandle, PANEL_CMD_MODIFY, ATTR_LABEL_TEXT, labeltext);/将得到的文本与“修改”二字相对照comparisonresult = strcmp (labeltext, 修改);/如果确定标签文本上的字符就是“修改”二字，则执行以下操作if (comparisonr

10、esult = 0)/当联接数据库成功时进行下面操作if (hdbc 0) memset(SqlCommand,0,200);/一次修改只能修改最后触发的行数据,否则将以下代码放到表格控件的事件函数中GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(2, globalvalue), namevalue);GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(3, globalvalue), sexvalue); GetTableCellVal (panelHandle, PANEL_TA

11、BLE, MakePoint(4, globalvalue), &agevalue);sprintf(SqlCommand,UPDATE table2 SET NAME=%s,SEX=%s,AGE=%d WHERE ID=%d;,namevalue,sexvalue,agevalue,globalvalue);/带参数更新hstat = DBPrepareSQL (hdbc, SqlCommand);resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat); break;return 0;/删除数据表int CVI

12、CALLBACK deletetable (int panel, int control, int event,void *callbackData, int eventData1, int eventData2)int hstat;switch (event)case EVENT_COMMIT:DisableBreakOnLibraryErrors ();/激活SQL查询hstat = DBActivateSQL (hdbc, SELECT * FROM table2);/如果表存在数据，执行如下操作if (hstat 0)/释放被激活句柄DBDeactivateSQL (hstat);/删

13、除数据表DBImmediateSQL (hdbc, DROP TABLE table2); DeleteTableRows (panelHandle, PANEL_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)lo

14、ng agestatus;int agevalue;long sexstatus;char sexvalue10;long namestatus;char namevalue10;int resultcode;long idstatus;int idvalue;int hmap;switch (event)case EVENT_COMMIT:/创建一个数据库映射hmap = DBBeginMap (hdbc);resultcode = DBMapColumnToInt (hmap, ID, &idvalue, &idstatus);resultcode = DBMapColumnToChar

15、(hmap, NAME, 10, namevalue, &namestatus, );resultcode = DBMapColumnToChar (hmap, SEX, 10, sexvalue, &sexstatus, );resultcode = DBMapColumnToInt (hmap, AGE, &agevalue, &agestatus);/由此映射创建一个数据表resultcode = DBCreateTableFromMap (hmap, table2);if (resultcode = 0)/向数据表中添加数据DBImmediateSQL (hdbc, INSERT IN

16、TO 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_READDATA, ATTR_DIMMED, 0); break;return 0;/读取数据，即刷新数据int CVICALLBACK readd

17、ata (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 hstat;int total = 0;int i = 1;switch (event)case EVENT_COMMIT:DisableBrea

18、kOnLibraryErrors ();/激活SQL查询hstat = DBActivateSQL (hdbc, SELECT * FROM table2);/以下操作为将每列字段绑定到相关变量中resultcode = DBBindColInt (hstat, 1, &idvalue, &idstatus);resultcode = DBBindColChar (hstat, 2, 10, namevalue, &namestatus, );resultcode = DBBindColChar (hstat, 3, 10, sexvalue, &sexstatus, );resultcode

19、 = DBBindColInt (hstat, 4, &agevalue, &agestatus);/获得记录的总数total = DBNumberOfRecords (hstat);/删除面板中的表格控件所有行DeleteTableRows (panelHandle, PANEL_TABLE, 1, -1);/插入与数据表中记录数相同的行数InsertTableRows (panelHandle, PANEL_TABLE, 1, total, VAL_USE_MASTER_CELL_TYPE);if (total 0)/利用数据指针逐行写入面板表格控件中while (DBFetchNext(

20、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+;DBDe

21、activateSQL (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, &colrow)

22、;/获得本行一列中数据的ID号GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(1, colrow.y), &value);/将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;in

23、t resultcode;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,

24、ATTR_CTRL_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;/获得按钮标签文本数据GetCtrlAttribute (panelHandle,PANEL_COMMANDBUTTON_8, ATTR_LABEL_TEXT,labeltext);/将得到的文本与“修改”二字进行比照comparisonresult = strcmp(labeltext,修改);/如果确定标签文本上的字符为“修改”二字，则执行以下操作if(comparisonresult =