使用CVI軟件平臺進(jìn)行Mysql數(shù)據(jù)庫開發(fā)簡介

1、使用Labwindow CVI軟件平臺進(jìn)行Mysql數(shù)據(jù)庫開發(fā)簡介針對初學(xué)者進(jìn)行數(shù)據(jù)庫開發(fā)，不是很了解相應(yīng)過程，現(xiàn)將我剛剛成功的驗證過程，與大家分享。文中主要描述不能使用映射方式修改數(shù)據(jù)庫中數(shù)據(jù)的問題。1. CVI版本為9.0（348），NI SQL Toolkit 2.2請前往 www.NI.com 下載試用版軟件系統(tǒng)！2. MySQL版本為5.6.221、 安裝MySQL過程可參考網(wǎng)上的說明，如網(wǎng)絡(luò)上，MySQL下載與安裝安裝圖解請前往處下載開源數(shù)據(jù)庫系統(tǒng)MySQL3. 使用王建新老師的例程進(jìn)行學(xué)習(xí)4. 更改了修改數(shù)據(jù)按鈕中的代碼備注：1、 通過更換SQL Toolkit2

2、.1改為2.2后，還是不能修改MySQL已存在數(shù)據(jù)庫中數(shù)據(jù)的問題。2、 不使用映射的方式進(jìn)行更新數(shù)據(jù)庫數(shù)據(jù)，這種方式能滿足Access創(chuàng)建的數(shù)據(jù)庫，但是不能修改MySQL創(chuàng)建的數(shù)據(jù)庫，原因不知，希望有高手指點 weiyizhinengQQ.com3、 更改為使用SQL直接進(jìn)行更改數(shù)據(jù)，實現(xiàn)更改表中一條數(shù)據(jù)的功能。謝謝LabWindows/CVI交流群 20328398里各位老師的奉獻(xiàn)！修改后的代碼：#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;/建立數(shù)據(jù)庫聯(lián)接，并獲得聯(lián)接句柄hdbc = DBConnect ("DSN=client");DisplayPanel (panelHandle);RunUserInterface ();DiscardPanel (panelHandle);/當(dāng)退出應(yīng)用程序時，關(guān)閉數(shù)據(jù)庫連接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);/獲得活動單元格所在行，且列數(shù)為第1列單元格內(nèi)的數(shù)據(jù)GetTableCellVal (panelHandle, PANEL_TABLE, MakePoint(1, colrow.y), &value);/當(dāng)聯(lián)接數(shù)據(jù)庫成功時進(jìn)行下面操作if (hdbc > 0) /對于帶參數(shù)的數(shù)據(jù)庫查詢，首先準(zhǔn)備執(zhí)行SQL查詢聲明hstat = DBPrepareSQL (hdbc, "DELETE FROM table2 WHERE ID = ?

7、");/創(chuàng)建一個整型量參數(shù)預(yù)備查詢方式resultcode = DBCreateParamInt (hstat, "", DB_PARAM_INPUT, value);/執(zhí)行帶參數(shù)的SQL查詢resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat);/刷新數(shù)據(jù)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");/獲得記錄總數(shù)numberofrecords = DBNumberOfRecords (hstat);DBDeactivateSQL (hs

9、tat);/帶參數(shù)查詢hstat = DBPrepareSQL (hdbc, "INSERT INTO table2 VALUES (?, '', '', 0)");resultcode = DBCreateParamInt (hstat, "ID", DB_PARAM_INPUT, numberofrecords + 1);resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat); /刷新數(shù)據(jù)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;/設(shè)置i為靜態(tài)局部變量，在本函數(shù)內(nè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:/設(shè)置表格為可修改狀態(tài)SetCtrlAttribute (panelHandle, PANEL_TABLE, ATTR_CTRL_MODE, VAL_HOT);/以下程序表示：當(dāng)點擊“修改”按鈕時，按鈕標(biāo)簽將變?yōu)椤氨４妗眎f (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;/得到按鈕的標(biāo)簽文本GetCtrlAttribute (panelHandle, PANEL_CMD_M

13、ODIFY, ATTR_LABEL_TEXT, labeltext);/將得到的文本與“修改”二字相對照comparisonresult = strcmp (labeltext, "修改");/如果確定標(biāo)簽文本上的字符就是“修改”二字，則執(zhí)行以下操作if (comparisonresult = 0)/當(dāng)聯(lián)接數(shù)據(jù)庫成功時進(jìn)行下面操作if (hdbc > 0) memset(SqlCommand,0,200);/一次修改只能修改最后觸發(fā)的行數(shù)據(jù),否則將以下代碼放到表格控件的事件函數(shù)中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);/帶參數(shù)更新hstat = DBPrepareSQL (hdbc, SqlCommand);resultcode = DBExecutePreparedSQL (hstat);DBClosePreparedSQL (hstat); break;return 0;/刪除數(shù)據(jù)表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");/如果表存在數(shù)據(jù)，執(zhí)行如下操作if (hstat > 0)/釋放被激活句柄DBDeactivateSQL (hstat);/刪除數(shù)據(jù)表DBImmediateSQL (hdbc, "DROP TABLE table2"); DeleteTableRows (panelHandle, PANE

17、L_TABLE, 1, -1);SetCtrlAttribute (panelHandle, PANEL_CMD_READDATA, ATTR_DIMMED, 1);break;return 0;/創(chuàng)建數(shù)據(jù)表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:/創(chuàng)建一個數(shù)據(jù)庫映射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);/由此映射創(chuàng)建一個數(shù)據(jù)表resultcode = DBCreateTableFromMap (hmap, "table2");if (resultcode >= 0)/向數(shù)據(jù)表中添加數(shù)

20、據(jù)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;/讀取數(shù)據(jù)，即刷新數(shù)據(jù)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");/以下操作為將每列字段綁定到相關(guān)變量中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);/獲得記錄的總數(shù)total = DBNumberOfRecords (hstat);/刪除面板中的表格控件所有行Del

24、eteTableRows (panelHandle, PANEL_TABLE, 1, -1);/插入與數(shù)據(jù)表中記錄數(shù)相同的行數(shù)InsertTableRows (panelHandle, PANEL_TABLE, 1, total, VAL_USE_MASTER_CELL_TYPE);if (total > 0)/利用數(shù)據(jù)指針逐行寫入面板表格控件中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)/當(dāng)在面板中的表格控件中雙擊時，產(chǎn)生以下操作case EVENT_LEFT_DOUBLE_CLICK:/獲得活動表格當(dāng)前的行與列GetActiveTableCell (panelHandle, PANEL_TABLE, &colrow);/獲得本行一列中數(shù)據(jù)的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;/設(shè)置i為靜態(tài)局部變量，在本函數(shù)內(nè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:/設(shè)置表格為可以修改狀態(tài)SetCtrlAttribute (panelHandle,PANEL_TABLE, ATTR_CTRL_MODE, VAL_HOT);/以下部分表示，當(dāng)點擊修改按鈕時，按鈕標(biāo)簽將變?yōu)椤氨４妗眎f(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;/獲得按鈕標(biāo)簽文本數(shù)據(jù)GetCtrlAttribute (panelHandle,PANEL_COMMANDBUTTON_8, ATTR_LABEL_TEXT,labeltext);/將得到的文本與“修改”二字進(jìn)行比照comparisonresult = strcmp(labeltext,"修改");/如果確定標(biāo)簽文本