用Lab Windows CVI 从MySQL\Oracle中读写数据

最近做的BMS上位机软件项目有一个新需求：把测试报告中的部分数据写入公司的质量管理系统，包括项目名、pack号、充放电压差、安时、合格判定、测试时间等等。这样可以替代测试员手工录入测试数据，效率高，不易出错，也方便以后在质量管理系统中查询数据。这就需要CVI连接数据库读写数据，我先自己建了个MySql的数据库做实验，但公司的质量管理系统是oracle的，最终还是要用到oracle。

第一步：安装CVI SQL Toolkit 2.1

           需要有CVI SQL Toolkit 2.1安装包，安装完成后才可以连接数据库。只要按软件提示安装即可，过程不再             赘述，在编程时要加上#include "cvi_db.h" ，否则连不上数据库哦 ！ 

第二步：安装MySQL数据库

           我是在MySQL官网上下载的mysql-5.6.34-winx64，是Zip格式的，具体安装步骤如下：

1. 解压，解压在 C:\Program Files\MySQL\MySQL Server 5.6

2. 配置环境变量：我的电脑->属性->高级->环境变量

选择PATH,在其后面添加:C:\Program Files\MySQL\MySQL Server 5.6\bin (注意是追加,不是覆盖)

3. 修改配置文件：C:\Program Files\MySQL\MySQL Server 5.6\my-default.ini

在其中修改或添加配置： 

basedir=C:\Program Files\MySQL\MySQL Server 5.6（mysql所在目录） 

datadir=C:\Program Files\MySQL\MySQL Server 5.6\data （mysql所在目录\data）

4. 以管理员的身份运行cmd， 输入cd C:\Program Files\MySQL\MySQL Server 5.6\bin ， 进入mysql的bin 文件夹输入：mysqld -install (如果不用管理员身份运行，将会因为权限不够而出现错误：Install/Removeof the Service Denied!) 

5. 启动服务：在cmd中输入:net start mysql, 服务启动成功！

6. 服务启动成功之后登录，输入mysql -u root -p（第一次登录没有密码，直接按回车过）,登录成功！

7. 修改密码：mysqladmin -u root password "newpass"

第三步：安装MySQL ODBC驱动

我的电脑的操作系统是Win7 64位的，一开始下载安装了mysql-connector-odbc-5.1.13-winx64，配置好数据源。

        CVI中连接数据库的代码 ：

 hdbc = DBConnect( "DSN=mysql5.1_32" ); //建立连接

        总是提示error number -10  Native error code -2147467259 0x80004005 Microsoft OLE DB Provider for ODBC Drivers:Microsoft ODBC 驱动程序管理器 未发现数据源名称并且未指定默认驱动程序

      后经查找资料，由于我的CVI 9.0可能是32位的，所以又下载安装了32位的MySql ODBC驱动：mysql-connector-odbc-5.1.13-win32，配置好数据源，使系统DSN指向这个32位的数据源，再运行程序，OK啦！

第四步：安装oracle ODBC驱动，配置数据源

点击打开链接

第四步： 接下来就可以写代码啦

如需建立oracle连接，只需修改为 hdbc = DBConnect( "DSN=oracle;UID=test;PWD=test" ); //建立连接  

// Include files#include <windows.h>#include <ansi_c.h>#include "App_Db.h"#include "cvi_db.h"#include <utility.h>#include "toolbox.h"#include <cvirte.h>#include <userint.h>#include "App_JAC.h"#include "App_Data.h"#include "Junco_Pack_Test_FCT.h" //==============================================================================// Constants//==============================================================================// Types//==============================================================================// Static global variablesstatic int hstmt;static int hmap;static int hdbc;//==============================================================================// Static functions//==============================================================================// Global variablestypedef struct _DBTABLE_DATA{char idvalue[30];char pjvalue[10];char psnvalue[6];char typevalue[6];char datevalue[10];char timevalue[8];}DBTABLE_DATA;DBTABLE_DATADbTableData[PACK_NUMBERS];//==============================================================================// Global functionsint App_DBinitConncet( void ){int rtvalue;int errorCode;char strMsg[512];rtvalue = 0;errorCode = DBInit (DB_INIT_MULTITHREADED);hdbc = DBConnect( "DSN=mysql5.1_32" ); //建立连接if (hdbc <= 0) {errorCode = DBError();sprintf ( strMsg, "Error number %d\n%s", errorCode, DBErrorMessage () );rtvalue = 1;}return rtvalue; }int App_DBdisConnect( void ){int resCode;resCode = DBDisconnect(hdbc); //断开连接释放系统资源    return  resCode;}//*****************************************************************************int App_DBinsert( int iPackNum ){PACK_CTRL_STRUCT*pStrPackCtrl;STR_BUSS_DATA_STRUCT*pStrBussData;char idvalue[30];char pjvalue[10];char psnvalue[6];char typevalue[6];char datevalue[10];char timevalue[8];char compName[30];char cpNum;int hstmt1;int resCode;long idStatus;long pjStatus;long psnStatus;long typeStatus;long dateStatus;long timeStatus;pStrPackCtrl = &StrPackCtrl[iPackNum];pStrBussData = &StrBussData[iPackNum];/*//get idvalueGetCompName ( compName );strcpy ( idvalue, pStrBussData->DateTime );strcat ( idvalue, compName + ( strlen(compName) - 4 ) );idlen = strlen (idvalue);//get pjvaluestrncpy ( pjvalue , pStrPackCtrl->packname, 4 );//get psnvaluestrncpy ( psnvalue , pStrPackCtrl->packname+5, 5 );//get typevalueswitch ( pStrPackCtrl->iTestMode ){case 3:cpNum = 3; break;case 4:cpNum = 4; break;case 5:cpNum = 2; break;default:break;}strncpy ( typevalue, pStrPackCtrl->secondFPart, cpNum );//get datevalue, timevaluestrncpy ( datevalue, pStrBussData->DateTime, 8 );strncpy ( timevalue, pStrBussData->DateTime+9, 6 );*/sprintf ( idvalue, "%s","20161209101240absx" );sprintf ( pjvalue, "%s","IEV6" );sprintf ( psnvalue, "%s","09685" );sprintf ( typevalue, "%s","DCHG" );sprintf ( datevalue, "%s","20160910" );sprintf ( timevalue, "%s","133045" );hstmt1 = DBActivateSQL ( hdbc, "SELECT * FROM finaltest" );resCode = DBBindColChar ( hstmt1, 1, 30, idvalue, &idStatus,"" );resCode = DBBindColChar ( hstmt1, 2, 10, pjvalue, &pjStatus,"" );resCode = DBBindColChar ( hstmt1, 3, 6, psnvalue, &psnStatus,"" );resCode = DBBindColChar ( hstmt1, 4, 6, typevalue, &typeStatus,"" );resCode = DBBindColChar ( hstmt1, 5, 10, datevalue, &dateStatus,"" );resCode = DBBindColChar ( hstmt1, 6, 8, timevalue, &timeStatus,"" );resCode = DBCreateRecord (hstmt1);resCode = DBPutRecord (hstmt1);    //Insert data to databaseresCode = DBDeactivateSQL (hstmt1);return resCode;}int App_TableInsert ( int iPackNum ){DBTABLE_CTRL*pDbtableCtrl;DBTABLE_DATA*pDbtableData;char rowNum;int resCode;pDbtableCtrl = &DbTableCtrl;pDbtableData = &DbTableData[iPackNum];rowNum = pDbtableCtrl->rownum;resCode = InsertTableRows ( iDBresPanelHandle, DBRS_TABLE_RS, -1, 1, VAL_CELL_STRING );resCode = SetTableCellVal ( iDBresPanelHandle, DBRS_TABLE_RS, MakePoint(1,rowNum), pDbtableData->pjvalue );  resCode = SetTableCellVal ( iDBresPanelHandle, DBRS_TABLE_RS, MakePoint(2,rowNum), pDbtableData->psnvalue );      resCode = SetTableCellVal ( iDBresPanelHandle, DBRS_TABLE_RS, MakePoint(3,rowNum), pDbtableData->typevalue );    resCode = SetTableCellVal ( iDBresPanelHandle, DBRS_TABLE_RS, MakePoint(4,rowNum), pDbtableData->datevalue );    resCode = SetTableCellVal ( iDBresPanelHandle, DBRS_TABLE_RS, MakePoint(5,rowNum), pDbtableData->timevalue );  return resCode;}int App_DBfind( int iPackNum ){PACK_CTRL_STRUCT*pStrPackCtrl;DBTABLE_CTRL*pDbtableCtrl;DBTABLE_DATA*pDbtableData;char sPrj[10];char sPsn[6];long idStatus;long pjStatus;long psnStatus;long typeStatus;long dateStatus;long timeStatus;pStrPackCtrl = &StrPackCtrl[iPackNum];pDbtableData = &DbTableData[iPackNum];pDbtableCtrl = &DbTableCtrl;memset ( sPrj, 0, 10 );memset ( sPsn, 0, 6 );sprintf ( pStrPackCtrl->packname, "%s",  "IEV6_09685" );strncpy ( sPrj, pStrPackCtrl->packname, 4 );//if ( !strcmp ( sPrj, " IEV6") ){//strcpy ( sPrj, "iev6" );}//else if ()//{}strncpy ( sPsn, pStrPackCtrl->packname+5, 5 );DBAllowFetchAnyDirection ( hdbc, 1 );hmap=DBBeginMap ( hdbc );DBMapColumnToChar ( hmap, "ID", 30, pDbtableData->idvalue, &idStatus,"" );DBMapColumnToChar ( hmap, "PROJECT", 10, pDbtableData->pjvalue, &pjStatus,"" );DBMapColumnToChar ( hmap, "PACKSN", 6, pDbtableData->psnvalue, &psnStatus,"" );DBMapColumnToChar ( hmap, "TYPE", 6, pDbtableData->typevalue, &typeStatus,"" );DBMapColumnToChar ( hmap, "DATE", 10, pDbtableData->datevalue, &dateStatus,"" );DBMapColumnToChar ( hmap, "TIME", 8, pDbtableData->timevalue, &timeStatus,"" );hstmt=DBActivateMap ( hmap,"finaltest");while ( DBFetchNext(hstmt) != DB_EOF )   {if( !strcmp( sPrj, pDbtableData->pjvalue ) && !strcmp( sPsn, pDbtableData->psnvalue )){if ( !pDbtableCtrl->ucshowtable ){App_InitDBRSPanel();pDbtableCtrl->ucshowtable = 1;}pDbtableCtrl->rownum++;App_TableInsert (1);}}DBDeactivateMap(hmap);if( pDbtableCtrl->ucshowtable ){MessagePopup("查找","此电池包PACK号已存在！");DisplayPanel ( iDBresPanelHandle );}else{MessagePopup("查找","不存在！");}return 0;}int App_InitDBRSPanel ( void ){PANEL_SIZE*pPanelSize;pPanelSize = &Panel_Size;if ( ( iDBresPanelHandle = LoadPanel ( iMainPanelHandle, "Junco_Pack_Test_FCT.uir", DBRS ) ) < 0 ){return NOT_OK;}SetPanelAttribute ( iDBresPanelHandle, ATTR_TOP, 20 );SetPanelAttribute ( iDBresPanelHandle, ATTR_LEFT, pPanelSize->iSummaryWidth * 2 );//删除面板中的表格控件所有行    DeleteTableRows ( iDBresPanelHandle, DBRS_TABLE_RS, 1, -1 );HidePanel ( iDBresPanelHandle );return OK;}int CVICALLBACK QuitDBRSPanel (int panel, int control, int event,void *callbackData, int eventData1, int eventData2){switch (event){case EVENT_COMMIT:{DBTABLE_CTRL*pDbtableCtrl;pDbtableCtrl = &DbTableCtrl;pDbtableCtrl->ucshowtable = 0;HidePanel ( iDBresPanelHandle );}break;}return 0;}