工事中...
1)SQLを使ったVC VBA
のプログラミングメモ
2)マイコンＴｉｐｓ動画工事中...
MFCgrid_ctrl
MFCMFC覚え書き
2008-02/03
-----Ａｃｃｅｓｓ SQL編(競馬データー.mdbの操作 )------
１ RA（レース詳細データーからメインレースの抽出） 2008-02/03
２ RA（開催日にtotal何レースあるか抽出）
３ RA（開催日のみ抽出）
同じ列は表示しない
4 RA（開催日の開催場所のみ抽出）
5 RA（開催日の開催場所は何箇所か？）
6 RA（登録されたデーターからのメインレースの抽出と日にち昇順表示 (1改) ）
2008-02/11
&
02/17
7 RA（VCを使いExecuteをパラメーターで、開催日のみ抽出 ）
2008-03/02
8 RA（VCで実際開催場所を抽出して表示させる ）
2008-03/09
１〜８までで、開催日時とレースの選択画面を作成する。
9
UMA_RACE 馬毎レース情報を使い出馬表の表示 2008-03/19
データーベースファイルの登録で、INSERT UPDATEがうまくできていなかった。 2008-03/23
10 UMA_RＡCE（VCで、データーベースの最適化を行う）
2008-03/30
11 SQLでINSERT,UPDATE,DELETE文の基本形
2008-06/1
-------------------------------------------------------------------------------------------
1
RA（レース詳細データーからメインレースの抽出）
問題点 中央競馬は、必ず11Rがメインレースとは限らず有馬記念など9Rがメインとなるときがあり考慮しなければならない。
とりあえず11RとメインレースがG1のときだけデーターをすべて抽出してみる。
SELECT
*
FROM
RACE
WHERE
RaceNum = "11" OR GradeCD = "A"
-------------------------------------------------------------------------------------------
2
RA（開催日にtotal何レースあるか抽出
12月22日のレース数）
SELECT
COUNT
(*)
FROM
RACE
WHERE MonthDay = "1222";
結果 36
WHERE MonthDay =
"1223";（有馬記念当日）
結果 34
try{
if(!pRecordSet->GetadoEOF()){
while((!pRecordSet->GetadoEOF()){){
m_count++;
pRecordSet->MoveNext();
}
}
}
m_count=34;
一括で、よみこめなかった。
-------------------------------------------------------------------------------------------
3
RA（開催日のみ抽出）
SELECT
Distinct(MonthDay)
FROM
RACE
-------------------------------------------------------------------------------------------
4
RA（開催日の開催場所のみ抽出）
SELECT
Distinct(JyoCD)
FROM
RACE
WHERE
MonthDay="1028"
結果 10/28の開催場所のコード 03 05 08
(福島東京京都)
-------------------------------------------------------------------------------------------
5 RA（開催日の開催場所は何箇所か？）
4 のRA（開催日の開催場所のみ抽出）してからプログラム側で何箇所か？数えるしかないみたい
-------------------------------------------------------------------------------------------
6 RA（登録されたデーターからメインレースの抽出と日にちの昇順表示）
SELECT
*
FROM
RACE
WHERE
RaceNum = "11" OR GradeCD = "A"
ORDER BY MonthDay
問題点 登録されたデーターをすべて表示してしまう為に地方競馬のデーターも表示してしまう。
プログラム側で地方競馬のデーターをはじくか、サブクエリーを使って抽出？
中央競馬のみ表示させるには、IN
('01','02','03','04','05','06','07','08','09','10')の中央競馬のコードのみから抽出すればいい
その他に、JyoCD >='0' AND JyoCD <='10'
SELECT
*
FROM
RACE
WHERE
JyoCD IN
('01','02','03','04','05','06','07','08','09','10')AND (RaceNum = "11" OR
GradeCD = "A")
ORDER BY MonthDay
問題点
開催場所がばらばらに表示されるので、追加
（2007/2/24）
SELECT
*
FROM
RACE
WHERE
JyoCD IN ('01','02','03','04','05','06','07','08','09','10')AND (RaceNum = "11"
OR GradeCD = "A")
ORDER BY MonthDay,JyoCD
問題点
RaceNum = "11" OR GradeCD =
"A"としている為に、2007年のデーターでは、
11/25日の東京競馬で、10Rのメインレース ジャパンカップG1のみ取得したいが、最終11Rの
アプローズ賞まで抽出してしまう。5/27のダービー当日もG1以外抽出
これだけは、条件が思い浮かばないので、プログラム側ではじく。
------------------------------------------------------------------------------------------
7 RA（VCを使いExecuteをパラメーターで、開催日のみ抽出 ）
CString JV_SELECT_DAY="1020";
CString
str;
str.Format("SELECT * FROM RACE WHERE MonthDay='%s' ODER BY
RaceNum,JyoCD",JV_SELECT_DAY);
_bstr_t bsq(str);
pRecordSet =
pCn->Execute(bsq,****************);
結果
場所コード,レースNo
03 01
05
01
08 01
03 02
05 02
08 02
------------------------------------------------------------------------------------------
8 RA（VCで開催場所を表示させる ）
CString JV_SELECT_DAY="1020";
CString
str;
str.Format("SELECT Distinct(JyoCD) FROM RACE WHERE MonthDay='%s' ODER BY
JyoCD",JV_SELECT_DAY);
_bstr_t
bsq(str);
_variant_t vt(0L);
pRecordSet =
pCn->Execute(bsq,&vt,adOptionUnspecified);
*
*
CString
str1;
char db[1024];
_variant_t
vBuff;
str1="";
while(!pRecordSet->GetadoEOF()){
vBuff=pRecordSet->GetCollect(L"JyoCD")
strcpy(db,(_bsr_t)vBuff);
str1+=db;
pRecordSet->MoveNext();
}
AfxMessageBox(str1);
*
データーベース終了処理
*
結果
AfxMessageBoxで、030508の開催場所のコードが表示される。
------------------------------------------------------------------------------------------
10 UMA_RACE（VCでデーターベースの最適化を行う）
データーベースの最適化を行ってみる。
単純にコピペで結果がでた。
#include
<afxdao.h>
{
char bufpath[MAX_PATH];
char
buffn[MAX_PATH];
GetCurrentDirectory(MAX_PATH, bufpath);
GetTempFileName(
bufpath,
"jvdb",
0,
buffn);
strcpy(&buffn[lstrlen(buffn)-3],"mdb");
CDaoWorkspace
ws;
ws.Open(NULL);
ws.CompactDatabase(_T(strFilePath), _T(buffn));
DeleteFile(strFilePath);
MoveFile( buffn,
strFilePath);
}
------------------------------------------------------------------------------------------
10 SQL構文 ＩＮＳＥＲＴ ＵＰＤＡＴＥ ＤＥＬＥＴＥ
文の基本形しか,わからない。
INSERT INTO テーブル名 (field1,field2) values
('8','9')
INSERT INTO テーブル名 values
('8','9')
(フィールドすべてINSERT)
UPDATEテーブル名SETfield1='10',field2='20' where
条件
DELETE FORM テーブル名 where
field1='10'
その他プライマリーキーとか、Jetの場合更新するフィールド数の制約がある。
’の着くデーターが有る場合は注意がいるみたい(
It's 等 )
------------------------------------------------------------------------------------------
7/30未確認 未整理
(1)
MDBの使用例ACCESS 2000以上
(2) DAO読込
(3) DAO 追加、更新、削除
(4) レコード件数を求める
(5) 最終番号 + 1 を求める
(6)
プログラムの終了時に、MDBを最適化し、肥大化を防ぎます。
(7) ODBC
(8)ADO接続
(1) MDBの使用例ACCESS
2000以上
#include <afxdao.h> // MFC DAO database
classes をインクルードする
// メンバー変数
CDaoDatabase
m_dbSYAMST;
CSyainSet*
m_pSetSYA; // 社員マスタ レコードセット
void
CTheView::OnInitialUpdate()
{
CString
sDBname;
try
{
AfxGetModuleState()->m_dwVersion = 0x0601; // for ACCESS
2000
AfxDaoInit();
sDBname =
"C:¥¥TEST¥¥SYAMST.MDB";
m_dbSYAMST.Open( sDBname0
); // DB Open
}
catch (CDaoException* e)
{
::DaoErrorMsg(e);
}
m_pSetSYA = new CSyainSet(&m_dbSYAMST); //
社員マスタ
}
(2) DAO読込
CDaoDatabase dbHINMST;
CDaoRecordset* rs;
COleVariant varVal;
CString sSQL,sHinban,sHinmei,sSuryo,sTanka,sYmd;
dbHINMST.Open( "C:¥¥MYDB¥¥HINMST.MDB" );
rs = new CDaoRecordset(&dbHINMST);
sSQL = "SELECT 品番,品名,数量,単価,登録日 FROM 品名マスタ ORDER BY 品番";
rs->Open(dbOpenDynaset, sSQL, dbReadOnly);
while (!rs->IsEOF()) {
varVal = rs->GetFieldValue("品番");
sHinban = (LPCSTR)varVal.bstrVal;
varVal = rs->GetFieldValue("品名");
sHinmei = (LPCSTR)varVal.bstrVal;
varVal = rs->GetFieldValue("数量");
sSuryo.Format("%d", (short)varVal.iVal);
varVal = rs->GetFieldValue("単価");
sTanka.Format("%d", (short)varVal.iVal);
varVal = rs->GetFieldValue("登録日");
sYmd = COleDateTime(varVal).Format();
rs->MoveNext();
}
rs->Close();
delete rs;
■カラム内容の取得(インデックス指定)
COleVariant varVal;
varVal = rs->GetFieldValue(3); // 0から始まる3番目のカラム
■カラム内容の取得(インデックス指定)
COleVariant varVal;
short cnt = (short)rs->GetFieldCount(); // 全フィールド数
for (short i=0; i<cnt; i++) {
varVal = rs->GetFieldValue(i);
}
■カラム内容の取得(カラム名指定)
COleVariant varVal;
varVal = rs->GetFieldValue("金額");
■カラム内容の取得後、タイプで判断
整数型 VT_I2 signed int (2 bytes)
長整数型 VT_I4 signed int (4 bytes)
単精度浮動小数点型 VT_R4 float (4 bytes)
長精度浮動小数点型 VT_R8 float (8 bytes)
日付/時刻型 VT_DATE OLE Date
VB文字列型 VT_BSTR OLE String
論理値 VT_BOOL OLE Boolean
COleVariant varVal;
CString str;
switch (varVal.vt ) {
case VT_BOOL: if (varVal.boolVal == 0) str = "False";
else str = "True"; break;
case VT_I2 : str.Format("%d", (short)var.iVal); break;
case VT_I4 : str.Format("%d", varVal.lVal); break;
case VT_R4 : str.Format("%f", (double)varVal.fltVal); break;
case VT_R8 : str.Format("%f", varVal.dblVal); break;
case VT_BSTR: str = (LPCSTR)varVal.bstrVal; break;
case VT_DATE: str = COleDateTime(varVal).Format(); break;
}
AfxMessageBox(str);
(3) DAO 追加、更新、削除
//********************************************//*001CDaoRecordsetを使ったレコードの追加*//********************************************CDaoDatabasem_dbNAME;CRecordSet*m_pRS;m_pRS=newCRecordSet(&m_dbNAME);m_pRS->Open();m_pRS->AddNew();m_pRS->SetFieldValue(0,s1);m_pRS->SetFieldValue(1,s2);m_pRS->SetFieldValue(2,s3);//又はm_pRS->m_sField1=s1;m_pRS->m_sField2=s2;m_pRS->m_sField3=s3;m_pRS->Update();m_pRS->Close();//***************************************************//*002CDaoDatabaseを使ったレコードの追加*//***************************************************CDaoDatabase*m_pDB=newCDaoDatabase;CStringstrConnect;strConnect.Format(_T("ODBC;DSN=abc;UID=sa;PWD=;"));
m_pDB->Open(_T(""), FALSE, FALSE, strConnect);
// レコードの挿入
CString s1 = "001";
CString s2 = "002";
CString s3 = "003";
sSQL.Format("INSERT INTO テーブル VALUES('%s', '%s', '%s')", s1, s2, s3);
m_pDB->Execute(sSQL);
m_pDB->CLose();
----------------------------------------------------------------------
// 又は
m_dbNAME.Open("TEST.MDB");
// レコードの挿入
CString s1 = "001";
CString s2 = "002";
CString s3 = "003";
sSQL.Format("INSERT INTO テーブル VALUES('%s', '%s', '%s')", s1, s2, s3);
m_dbNAME.Execute(sSQL);
m_dbNAME.CLose();
//********************************************
//* 003 CDaoRecordsetを使ったレコードの更新 *
//********************************************
m_pRS->Open();
sSQL = "社員番号 = 15";
if ( m_pRS->FindFirst(sSQL) ) {
m_pRS->Edit();
m_pRS->m_sName = "○○○";
m_pRS->Update();
}
m_pRS->Close();
//********************************************
//* 004 CDaoRecordsetを使ったレコードの削除 *
//********************************************
m_pRS->Open();
sSQL = "社員番号 = 15";
if ( m_pRS->FindFirst(sSQL) ) {
m_pRS->Edit();
m_pRS->Delete();
}
m_pRS->Close();
//***************************************************
//* 005 CDaoDatabaseを使った全レコードの削除 *
//***************************************************
m_dbNAME.Open("TEST.MDB");
// レコードを全て削除する場合
sSQL = "DELETE * FROM テーブル";
m_pDB->Execute(sSQL);
m_dbNAME.CLose();
(4) レコード件数を求める
m_pRS->Open();
int nCount = m_pRS->GetRecordCount();
if ( nCount > 0 ) {
m_pRS->MoveLast();
nCount = m_pRS->GetRecordCount();
}
m_pRS->Close();
(5) 最終番号 + 1 を求める
int nCount,nNewBangou;
m_pSetDBX->Open();
nCount = m_pSetDBX->GetRecordCount();
if ( nCount > 0 ) {
m_pSetDBX->MoveLast();
nNewBangou = m_pSetDBX->m_nBangou + 1;
}
else {
nNewBangou = 1;
}
m_pSetDBX->Close();
(6) プログラムの終了時に、MDBを最適化し、肥大化を防ぎます。
CDaoDatabase m_database;
char szDbName[128];
strcpy( szDbName, "C:¥¥MYDB¥¥HINMEI.MDB" );
try {
m_database.Close(); // DBをクローズしておく
CDaoWorkspace WorkSpace;
WorkSpace.Open();
WorkSpace.CompactDatabase(szDbName, "TEMP.MDB"); // 最適化
WorkSpace.Close();
CFile::Remove(szDbName);
CFile::Rename("TEMP.MDB", szDbName);
}
catch (CDaoException* e) {
AfxMessageBox(e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
e->Delete();
(7) ODBC
■ DB(ODBC)関連Header を追加します。
#include <afxdb.h>
■ ＤＢ接続
CDatabase db;
// DB接続文字列 ... "DSN=データソース; UID=ユーザー名; PWD=パスワード;"
if (!db.OpenEx( "DSN=DBSRC; UID=user1; PWD=pswd" )) {
AfxMessageBox("ODBC Connect NG!",MB_OK,0);
}
・・・・ＤＢ操作処理
db.Close();
//***********************************************************************
//* テーブルの読み込み
//***********************************************************************
CDBVariant varVal;
int nCode;
CString sName;
CRecordset rs(&db);
rs.Open( CRecordset::forwardOnly,
_T("SELECT * FROM 商品マスタ ORDER BY 商品CD") );
while( !rs.IsEOF() ) {
rs.GetFieldValue((short)0, varVal ); // 長整数
nCode = varVal.m_lVal;
rs.GetFieldValue((short)1, sName ); // 文字列
rs.MoveNext();
}
rs.Close();
★フィールド名を指定して、値を得る方法
CString sVal;
rs.GetFieldValue( "品名", sVal);
AfxMessageBox(sVal,MB_OK,0);
-------------------------------------------------------------------------------
<参考>
CDaoRecordset::GetFieldValue
virtual void GetFieldValue( LPCTSTR lpszName, COleVariant& varValue );
throw( CDaoException, CMemoryException );
virtual void GetFieldValue( int nIndex, COleVariant& varValue );
throw( CDaoException, CMemoryException );
virtual COleVariant GetFieldValue（ LPCTSTR lpszName ）;
throw（ CDaoException, CMemoryException ）;
virtual COleVariant GetFieldValue（ int nIndex ）;
throw（ CDaoException, CMemoryException ）;
//***********************************************************************
//* フィールド名の読み込み
//***********************************************************************
CODBCFileInfo fi;
short i,nCount;
CRecordset rs(&db);
rs.Open( CRecordset::forwardOnly,
_T("SELECT * FROM 商品マスタ") );
nCount = rs.GetODBCFieldCount(); // フィールド数を求める
if ( !rs.IsEOF() ) {
for (i=0; i<nCount; i++) {
rs.GetODBCFieldInfo(i,fi);
AfxMessageBox( fi.m_strName, MB_OK,0);
}
}
rs.Close();
//***********************************************************************
//* レコード件数を求める
//***********************************************************************
CString str;
CRecordset rs(&db);
rs.Open( CRecordset::snapshot,
_T("SELECT COUNT(*) AS 数量 FROM 商品マスタ") );
rs.GetFieldValue("数量", str );
AfxMessageBox(str, MB_OK, 0);
rs.Close();
（ フィールド:数量 を利用して値を得ている ）
注）レコードを最後に移動(MoveLast）してする方法もあるが
ゼロ件等を考慮すると、この方が良い。
//***********************************************************************
//* ＤＢ操作（ＳＱＬ発行）
//***********************************************************************
db.BeginTrans(); //トランザクション開始
db.ExecuteSQL( "insert into 商品マスタ(商品CD,品名) values(80,'品名８０')" ); // 登録
// db.ExecuteSQL( "update 商品マスタ set 品名 = 'ああああ' where 商品CD = 70" ); // 修正
// db.ExecuteSQL( "delete from 商品マスタ where 商品CD=12" ); // 削除
db.CommitTrans(); //トランザクションの完了
注）ＭＤＢ（アクセス）をＯＤＢＣ接続した場合の LIKE のワイルドカードは %
SELECT * FROM 伝票ファイル WHERE 摘要 LIKE '*運賃*' ・・・ ＮＧ
SELECT * FROM 伝票ファイル WHERE 摘要 LIKE '%運賃%' ・・・ ＯＫ
■ CODBCFieldInfo 構造体の内容
struct CODBCFieldInfo
{
CString m_strName;
SWORD m_nSQLType;
UDWORD m_nPrecision;
SWORD m_nScale;
SWORD m_nNullability;
};
■ CDBvariantについて
m_boolVal BOOL 型。
m_chVal unsigned char 型。
m_dblVal double 型。
m_dwType 格納値のデータ型。DWORD 型。
m_fltVal float 型。
m_iVal short 型。
m_lVal long 型。
m_pbinary CLongBinary 型のオブジェクトへのポインタ。
m_pdate TIMESTAMP_STRUCT 型のオブジェクトへのポインタ。
m_pstring CString 型のオブジェクトへのポインタ。
m_dwType 共用体データ メンバ
DBVT_NULL 有効な共用体メンバはなく、アクセスできません。
DBVT_BOOL m_boolVal
DBVT_UCHAR m_chVal
DBVT_SHORT m_iVal
DBVT_LONG m_lVal
DBVT_SINGLE m_fltVal
DBVT_DOUBLE m_dblVal
DBVT_DATE m_pdate
DBVT_STRING m_pstring
DBVT_BINARY m_pbinary
■データの取出し方
CDBVariant varVal;
short index;
indx=2; // 3番目のフィールドを取り出す
rs.GetFieldValue( index,varVal);
switch (varVal.m_dwType){
case DBVT_NULL : TRACE("NULL 無効の値¥n");
break;
case DBVT_BOOL : TRACE("m_boolVal BOOL 型¥n");
TRACE("%d¥n",val.m_boolVal);
break;
case DBVT_UCHAR : TRACE("m_chVal unsigned char 型¥n");
TRACE("%s¥n",val.m_chVal);
break;
case DBVT_SHORT : TRACE("m_iVal short 型¥n");
TRACE("%d¥n",val.m_iVal);
break;
case DBVT_LONG : TRACE("m_lVal long 型¥n");
TRACE("%d¥n",val.m_lVal);
break;
case DBVT_SINGLE : TRACE("m_fltVal float 型¥n");
TRACE("%f¥n",val.m_fltVal);
break;
case DBVT_DOUBLE : TRACE("m_dblVal double 型¥n");
TRACE("%f¥n",val.m_dblVal);
break;
case DBVT_DATE : TRACE("m_pdate TIMESTAMP_STRUCT 型のオブジェクトへのポインタ¥n");
TIMESTAMP_STRUCT*pT;
pT=val.m_pdate;
TRACE("%d/%d/%d %d:%d:%d¥n",pT->year,pT->month,pT->day,pT->hour,pT->minute,pT->second );
break;
case DBVT_STRING : TRACE("m_pstring CString 型のオブジェクトへのポインタ¥n");
TRACE("%s¥n",*val.m_pstring);
break;
case DBVT_BINARY : TRACE("m_pbinary¥n CLongBinary 型のオブジェクトへのポインタ");
break;
default :
break;
}
■ 結合したテーブルの読み込み(JOIN)
レコードセットクラスを作成し、このレコードセットをOpenし読み込めば良い。
(8)ADO接続
ODBC Driver for dBASE
strConnection = _T("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;"
"Dbq=c:¥¥DatabasePath;");
Note: You must specify the filename in the SQL statement... For example:
CString strQuery = _T("Select Name, Address From Clients.dbf");
ODBC Driver for Excel
strConnection = _T("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"
bq=C:¥¥DatabasePath¥¥DBSpreadSheet.xls;DefaultDir=c:¥¥databasepath;");
ODBC Driver for Text
strConnection = _T("Driver={Microsoft Text Driver (*.txt; *.csv)};"
"Dbq=C:¥¥DatabasePath¥¥;Extensions=asc,csv,tab,txt;");
If you are using tab delimited files, you must create the schema.ini file, and you must inform the Format=TabDelimited option in your connection string.
Note: You must specify the filename in the SQL statement... For example:
CString strQuery = _T("Select Name, Address From Clients.csv");
Visual FoxPro
If you are using a database container, the connection string is the following:
strConnection = _T("Driver={MicrosoftVisualFoxproDriver};UID=;"</span>ourceType=DBC;SourceDB=C:¥¥DatabasePath¥¥MyDatabase.dbc;Exclusive=No");
If you are working without a database container, you must change the SourceType parameter by DBF as in the following connection string:
strConnection = _T("Driver={Microsoft Visual Foxpro Driver};UID=;"
"SourceType=DBF;SourceDB=C:¥¥DatabasePath¥¥MyDatabase.dbc;Exclusive=No");
ODBC Driver for Access
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
"Dbq=c:¥¥DatabasePath¥¥dbaccess.mdb;Uid=;Pwd=;");
If you are using a Workgroup (System database): you need to inform the SystemDB Path, the User Name and its password. For that, you have two solutions: inform the user and password in the connection string or in the moment of the open operation. For example:
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
"Dbq=C:¥¥VC Projects¥¥ADO¥¥Samples¥¥AdoTest¥¥dbTestSecurity.mdb;"
"SystemDB=C:¥¥Program Files¥¥Microsoft Office¥¥Office¥¥SYSTEM.mdw;"
"Uid=Carlos Antollini;Pwd=carlos");
or may be:
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
"Dbq=C:¥¥VC Projects¥¥ADO¥¥Samples¥¥AdoTest¥¥dbTestSecurity.mdb;"
"SystemDB=C:¥¥Program Files¥¥Microsoft Office¥¥Office¥¥SYSTEM.mdw;");
if(pDB.Open(strConnection, "DatabaseUser", "DatabasePass"))
{
DoSomething();
pDB.Close();
}
If you want to open in Exclusive mode:
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
"Dbq=c:¥¥DatabasePath¥dbaccess.mdb;Exclusive=1;");
ODBC Driver for SQL Server
For Standard security:
strConnection = _T("Driver={SQL Server};Server=MyServerName;"
"Trusted_Connection=no;"
"Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");
For Trusted Connection security (Microsoft Windows NT integrated security):
strConnection = _T("Driver={SQL Server};Server=MyServerName;"
"Database=myDatabaseName;Uid=;Pwd=;");
Also, you can use the parameter Trusted_Connection that indicates that you are using the Microsoft Windows NT Authentication Mode to authorize user access to the SQL Server database. For example:
strConnection = _T("Driver={SQL Server};Server=MyServerName;"
"Database=MyDatabaseName;Trusted_Connection=yes;");
If the SQL Server is running in the same computer, you can replace the name of the server by the word (local) like in the following sample:
strConnection = _T("Driver={SQL Server};Server=(local);"
"Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");
If you want to connect with a remote SQL Server, you must inform the address, the port, and the Network Library to use:
The Address parameter must be an IP address and must include the port. The Network parameter can be one of the following:
dbnmpntw Win32 Named Pipes dbmssocn Win32 Winsock TCP/IP dbmsspxn Win32 SPX/IPX dbmsvinn Win32 Banyan Vines dbmsrpcn Win32 Multi-Protocol (Windows RPC)
For more information, see Q238949.
strConnection = _T("Driver={SQL Server};Server=130.120.110.001;"
"Address=130.120.110.001,1052;Network=dbmssocn;Database=MyDatabaseName;"
"Uid=myUsername;Pwd=myPassword;");
ODBC Driver for Oracle
For the current Oracle ODBC driver from Microsoft:
strConnect = _T("Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;"
"Uid=MyUsername;Pwd=MyPassword;");
For the older Oracle ODBC driver from Microsoft:
strConnect = _T("Driver={Microsoft ODBC Driver for Oracle};"
"ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;");
ODBC Driver for MySQL
If you want to connect to a local database, you can use a connection string like the following:
strConnect = _T("Driver={MySQL ODBC 3.51 Driver};Server=localhost;"
"Database=MyDatabase;User=MyUserName;Password=MyPassword;Option=4;");
If you want to connect with a remote database, you need to specify the name of the server or its IP in the Server parameter. If the Port is distinct to 3306 (default port), you must specify it.
strConnect = _T("Driver={mySQL ODBC 3.51 Driver};Server=MyRemoteHost;"
"Port=3306;Option=4;Database=MyDatabase;Uid=MyUsername;Pwd=MyPassword;");
The parameter Option can be one or more of the following values:
1 - The client can't handle that MyODBC returns the real width of a column. 2 - The client can't handle that MySQL returns the true value of affected rows. If this flag is set then MySQL returns 'found rows' instead. One must have MySQL 3.21.14 or newer to get this to work. 4 - Make a debug log in c:¥myodbc.log. This is the same as putting MYSQL_DEBUG=d:t:O,c::¥myodbc.log in AUTOEXEC.BAT. 8 - Don't set any packet limit for results and parameters. 16 - Don't prompt for questions even if driver would like to prompt. 32 - Enable or disable the dynamic cursor support. This is not allowed in MyODBC 2.50. 64 - Ignore use of database name in 'database.table.column'. 128 - Force use of ODBC manager cursors (experimental). 256 - Disable the use of extended fetch (experimental). 512 - Pad CHAR fields to full column length. 1024 - SQLDescribeCol() will return fully qualified column names. 2048 - Use the compressed server/client protocol. 4096 - Tell server to ignore space after function name and before '(' (needed by PowerBuilder). This will make all function names keywords! 8192 - Connect with named pipes to a MySQLd server running on NT. 16384 - Change LONGLONG columns to INT columns (some applications can't handle LONGLONG). 32768 - Return 'user' as Table_qualifier and Table_owner from SQLTables (experimental). 65536 - Read parameters from the client and ODBC groups from my.cnf. 131072 - Add some extra safety checks (should not be needed but...).
If you want to have multiple options, you should add the above flags! For example: 16 + 1024 = 1030 and use Option= 1030;.
For more information, go to MyODBC Reference Manual.
ODBC Driver for AS400
strConnect = _T("Driver={Client Access ODBC Driver (32-bit)};System=myAS400;"
"Uid=myUsername;Pwd=myPassword;");
ODBC Driver for SyBase
strConnect = _T("Driver= {SybaseSystem10};Srvr=MyServerName;Uid=MyUsername;"
"Pwd=myPassword;");
ODBC Driver for Sybase SQL AnyWhere
strConnect = _T("ODBC;Driver=Sybase SQL Anywhere 5.0;"
"DefaultDir=C:¥¥DatabasePath¥;Dbf=C:¥¥SqlAnyWhere50¥¥MyDatabase.db;"
"Uid=MyUsername;Pwd=MyPassword;Dsn=¥"¥";");
DSN Connection
ODBC DSN
strConnect = _T("DSN=MyDSN;Uid=MyUsername;Pwd=MyPassword;");
OLE DB Provider
OLE DB Provider for SQL Server
For Standard security:
strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
"Initial Catalog=MyDatabaseName;"
" UserId=MyUsername;Password=MyPassword;");
For Trusted Connection security (Microsoft Windows NT integrated security):
strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
"Initial Catalog=MyDatabaseName;"
"Integrated Security=SSPI;");
If you want to connect to a "Named Instance" (SQL Server 2000), you must to specify Data Source=Servere Name¥Instance Name like in the following example:
strConnect = _T("Provider=sqloledb;Data Source=MyServerName¥MyInstanceName;"
"Initial Catalog= MyDatabaseName;UserId=MyUsername;Password=MyPassword;");
If you want to connect with a SQL Server running on the same computer, you must specify the keyword (local) in the Data Source like in the following example:
strConnect = _T("Provider=sqloledb;Data Source=(local);"
"Initial Catalog=myDatabaseName;"
" UserID=myUsername;Password=myPassword;");
To connect to SQL Server running on a remote computer (via an IP address):
strConnect = _T("Provider=sqloledb;Network Library=DBMSSOCN;"
"Data Source=130.120.110.001,1433;"
"Initial Catalog=MyDatabaseName;User ID=MyUsername;"
"Password=MyPassword;");
OLE DB Provider for MySQL (By Todd Smith)
strConnection = _T("Provider=MySQLProv;Data Source=test");
Where test is the name of MySQL database. Also, you can replace the name of the database by the following connection string: server=localhost;DB=test.
OLE DB Provider for AS400
strConnect = _T("Provider=IBMDA400;Data source=myAS400;User Id=myUsername;"
"Password=myPassword;");
For more information, see: Using the OLE DB Provider for AS/400 and VSAM.
OLE DB Provider for Active Directory
strConnect = _T("Provider= ADSDSOObject;UserId=myUsername;Password=myPassword;");
For more information, see: Microsoft OLE DB Provider for Microsoft Active Directory Service.
OLE DB Provider for DB2
If you are using a TCP/IP connection:
strConnect = _T("Provider=DB2OLEDB;Network Transport Library=TCPIP;"
"Network Address=130.120.110.001;"
"Initial Catalog=MyCatalog;Package Collection=MyPackageCollection;"
"Default Schema= MySchema;UserID=MyUsername;Password=MyPassword;");
If you are using APPC connection:
strConnect = _T("Provider=DB2OLEDB;APPC Local LU Alias=MyLocalLUAlias;"
"APPC Remote LU Alias=MyRemoteLUAlias;Initial Catalog=MyCatalog;"
"Package Collection=MyPackageCollection;Default Schema=MySchema;"
" UserID=MyUsername;Password=MyPassword;");
For more information, see: Using the OLE DB Provider for DB2.
OLE DB Provider for Microsoft Jet
- Connecting to an Access file using the JET OLE DB Provider:
Using Standard security:
strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
"Data Source=C:¥¥DatabasePath¥¥MmDatabase.mdb;"
" UserId=admin;Password=;");
If you are using a Workgroup (System database):
strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
"Data Source=C:¥¥DataBasePath¥¥mydb.mdb;"
"Jet OLEDB:System Database=MySystem.mdw;");
pRs.Open(strConnect, "MyUsername", "MyPassword");
- Connecting to an Excel Spreadsheet using the JET OLE DB Provider:
strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
"Data Source=C:¥¥DatabasePath¥¥DBSpreadSheet.xls;"
"Extended Properties=¥"¥"Excel 8.0;HDR=Yes;¥"¥";");
Note: If "HDR=Yes", the provider will not include the first row of the selection into the recordset. If "HDR=No", the provider will include the first row of the cell range (or named ranged) into the recordset.
For more information, see: Q278973.
- Connecting to a Text file using the JET OLE DB Provider:
strConnect =
_T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:¥¥DatabasePath¥¥;"
"Extended Properties=¥"¥"text;"
"HDR=Yes;FMT=Delimited;¥"¥";");Note: You must specify the filename in the SQL statement... For example:
CString strQuery = _T("Select Name, Address From Clients.txt");
For more information, see: Q262537.
- Connecting to an Outlook 2000 personal mail box using the JET OLE DB Provider: (By J. Cardinal)
strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;Outlook 9.0;"
"MAPILEVEL=;DATABASE=C:¥¥Temp¥¥;")
Replace c:¥temp with any temporary folder. It will create a schema file in that folder when you open it which shows all the fields available. Blank MAPILEVEL indicates top level of folders).
- Connecting to an Exchange mail box through JET: (By J. Cardinal)
strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;Exchange 4.0;"
"MAPILEVEL=Mailbox - Pat Smith|;DATABASE=C:¥¥Temp¥¥;")
You must replace c:¥temp with any temporary folder.
Replace Pat Smith with the name of the mail box and you must keep vertical pipe character | to indicate top level of folders. Place sub folder after vertical pipe if accessing specific folder.
Note: you can enter queries against the mail store just like a database... For example:
CString strQuery = _T("SQL "SELECT Contacts.* FROM Contacts;");
For more information, see: The Jet 4.0 Exchange/Outlook IISAM.
OLE DB Provider for ODBC Databases
If you want to connect with a Microsoft Access database:
strConnect = _T("Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};"
"Dbq=c:¥¥DatabasePath¥¥MyDatabase.mdb;Uid=MyUsername;Pwd=MyPassword;");
If you want to connect with a SQL Server database:
strConnect = _T("Provider=MSDASQL;Driver={SQL Server};Server=MyServerName;"
"Database=MyDatabaseName;Uid=MyUsername;Pwd=MyPassword;");
If you want to use DSN:
strConnect = _T("Provider=MSDASQL;PersistSecurityInfo=False;"
"Trusted_Connection=Yes;"
" DataSource=MyDSN;catalog=MyDatabase;");
For more information, see: Microsoft OLE DB Provider for ODBC.
OLE DB Provider for OLAP
Microsoft® OLE DB for Online Analytical Processing (OLAP) is a set of objects and interfaces that extends the ability of OLE DB to provide access to multidimensional data stores.
strConnect = _T("Provider=MSOLAP;Data Source=MyOLAPServerName;"
"Initial Catalog=MyOLAPDatabaseName;");
Connection using HTTP:
This feature enables a client application to connect to an Analysis server through Microsoft® Internet Information Services (IIS) by specifying a URL in the Data Source property in the client application's connection string. This connection method allows PivotTable® Service to tunnel through firewalls or proxy servers to the Analysis server. A special Active Server Pages (ASP) page, Msolap.asp, enables the connection through IIS. The directory in which this file resides must be included as part of the URL when connecting to the server (for example, http://www.myserver.com/myolap/).
Using a URL
strConnect = _T("Provider=MSOLAP;Data Source=http://MyOLAPServerName/;"
"Initial Catalog=MyOLAPDatabaseName;");
Using SSL
strConnect = _T("Provider=MSOLAP;Data Source=https://MyOLAPServerName/;"
"Initial Catalog=MyOLAPDatabaseName;");
For more information, see: OLE DB for OLAP, Connecting Using HTTP.
OLE DB Provider for Oracle
OLE DB Provider for Oracle (from Microsoft)
The Microsoft OLE DB Provider for Oracle allows ADO to access Oracle databases.
strConnect = _T("Provider=MSDAORA;Data Source=MyOracleDB;User Id=myUsername;"
"Password=myPassword;");
For more information, see: Microsoft OLE DB Provider for Oracle.
OLE DB Provider for Oracle (from Oracle).
For Standard security:
strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;"
" UserId=myUsername;Password=myPassword;");
For a Trusted connection:
- OS Authenticated connect setting user ID to "/":
strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;"
" UserId=/;Password=;");
- OS Authenticated connect using OSAuthent:
strConnect = _T("Provider= OraOLEDB.Oracle;DataSource=MyOracleDB;OSAuthent=1;")
Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name.
For more information, see: Oracle Provider for OLE DB Developer's Guide.
OLE DB Provider for Visual FoxPro
strConnect = _T("Provider=vfpoledb;"
"Data Source=C:¥¥DatabasePath¥¥MyDatabase.dbc;");
For more information, see: Microsoft OLE DB Provider for Visual FoxPro.
OLE DB Provider for Index Server (By Chris Maunder)
strConnect = _T("provider=msidxs;Data Source=MyCatalog;");
For more information, see: Microsoft OLE DB Provider for Microsoft Indexing Service.
O
-------------------------------------------------------------------------------------------