ado connect 收集.docx
《ado connect 收集.docx》由会员分享,可在线阅读,更多相关《ado connect 收集.docx(14页珍藏版)》请在冰豆网上搜索。
adoconnect收集
ADO连接字符串归纳(oraclesqlservermysqlaccess)
2008-03-1417:
22
Overview
Generally,oneofthefirststepswhenyouaretryingtoworkwithdatabasesisopenit.Youcanfindseveraltypesofthose,andeachhaveadifferentmodeofconnection.Whenyoutrytoconnectwithyourdatabasesometimes,youdon'tknowthecorrectconnectionstringthatyoumustuse.ItisforthatIwrotethisarticle.Iwantedtocompiletheconnectionstringstothemajorityofknowndatabases...
ODBCDSNLessConnection
ODBCDriverfordBASE
strConnection=_T("Driver={MicrosoftdBASEDriver(*.dbf)};DriverID=277;"
"Dbq=c:
\\DatabasePath;");
Note:
YoumustspecifythefilenameintheSQLstatement...Forexample:
CStringstrQuery=_T("SelectName,AddressFromClients.dbf");
ODBCDriverforExcel
strConnection=_T("Driver={MicrosoftExcelDriver(*.xls)};DriverId=790;"
bq=C:
\\DatabasePath\\DBSpreadSheet.xls;DefaultDir=c:
\\databasepath;");
ODBCDriverforText
strConnection=_T("Driver={MicrosoftTextDriver(*.txt;*.csv)};"
"Dbq=C:
\\DatabasePath\\;Extensions=asc,csv,tab,txt;");
Ifyouareusingtabdelimitedfiles,youmustcreatetheschema.inifile,andyoumustinformtheFormat=TabDelimitedoptioninyourconnectionstring.
Note:
YoumustspecifythefilenameintheSQLstatement...Forexample:
CStringstrQuery=_T("SelectName,AddressFromClients.csv");
VisualFoxPro
Ifyouareusingadatabasecontainer,theconnectionstringisthefollowing:
strConnection=_T("Driver={MicrosoftVisualFoxproDriver};UID=;"
ourceType=DBC;SourceDB=C:
\\DatabasePath\\MyDatabase.dbc;Exclusive=No");
Ifyouareworkingwithoutadatabasecontainer,youmustchangetheSourceTypeparameterbyDBFasinthefollowingconnectionstring:
strConnection=_T("Driver={MicrosoftVisualFoxproDriver};UID=;"
"SourceType=DBF;SourceDB=C:
\\DatabasePath\\MyDatabase.dbc;Exclusive=No");
ODBCDriverforAccess
strConnection=_T("Driver={MicrosoftAccessDriver(*.mdb)};"
"Dbq=c:
\\DatabasePath\\dbaccess.mdb;Uid=;Pwd=;");
IfyouareusingaWorkgroup(Systemdatabase):
youneedtoinformtheSystemDBPath,theUserNameanditspassword.Forthat,youhavetwosolutions:
informtheuserandpasswordintheconnectionstringorinthemomentoftheopenoperation.Forexample:
strConnection=_T("Driver={MicrosoftAccessDriver(*.mdb)};"
"Dbq=C:
\\VCProjects\\ADO\\Samples\\AdoTest\\dbTestSecurity.mdb;"
"SystemDB=C:
\\ProgramFiles\\MicrosoftOffice\\Office\\SYSTEM.mdw;"
"Uid=CarlosAntollini;Pwd=carlos");
ormaybe:
strConnection=_T("Driver={MicrosoftAccessDriver(*.mdb)};"
"Dbq=C:
\\VCProjects\\ADO\\Samples\\AdoTest\\dbTestSecurity.mdb;"
"SystemDB=C:
\\ProgramFiles\\MicrosoftOffice\\Office\\SYSTEM.mdw;");
if(pDB.Open(strConnection,"DatabaseUser","DatabasePass"))
{
DoSomething();
pDB.Close();
}
IfyouwanttoopeninExclusivemode:
strConnection=_T("Driver={MicrosoftAccessDriver(*.mdb)};"
"Dbq=c:
\\DatabasePath\dbaccess.mdb;Exclusive=1;");
ODBCDriverforSQLServer
ForStandardsecurity:
strConnection=_T("Driver={SQLServer};Server=MyServerName;"
"Trusted_Connection=no;"
"Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");
ForTrustedConnectionsecurity(MicrosoftWindowsNTintegratedsecurity):
strConnection=_T("Driver={SQLServer};Server=MyServerName;"
"Database=myDatabaseName;Uid=;Pwd=;");
Also,youcanusetheparameterTrusted_ConnectionthatindicatesthatyouareusingtheMicrosoftWindowsNTAuthenticationModetoauthorizeuseraccesstotheSQLServerdatabase.Forexample:
strConnection=_T("Driver={SQLServer};Server=MyServerName;"
"Database=MyDatabaseName;Trusted_Connection=yes;");
IftheSQLServerisrunninginthesamecomputer,youcanreplacethenameoftheserverbytheword(local)likeinthefollowingsample:
strConnection=_T("Driver={SQLServer};Server=(local);"
"Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");
IfyouwanttoconnectwitharemoteSQLServer,youmustinformtheaddress,theport,andtheNetworkLibrarytouse:
TheAddressparametermustbeanIPaddressandmustincludetheport.TheNetworkparametercanbeoneofthefollowing:
∙dbnmpntwWin32NamedPipes
∙dbmssocnWin32WinsockTCP/IP
∙dbmsspxnWin32SPX/IPX
∙dbmsvinnWin32BanyanVines
∙dbmsrpcnWin32Multi-Protocol(WindowsRPC)
Formoreinformation,seeQ238949.
strConnection=_T("Driver={SQLServer};Server=130.120.110.001;"
"Address=130.120.110.001,1052;Network=dbmssocn;Database=MyDatabaseName;"
"Uid=myUsername;Pwd=myPassword;");
ODBCDriverforOracle
ForthecurrentOracleODBCdriverfromMicrosoft:
strConnect=_T("Driver={MicrosoftODBCforOracle};Server=OracleServer.world;"
"Uid=MyUsername;Pwd=MyPassword;");
FortheolderOracleODBCdriverfromMicrosoft:
strConnect=_T("Driver={MicrosoftODBCDriverforOracle};"
"ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;");
ODBCDriverforMySQL
Ifyouwanttoconnecttoalocaldatabase,youcanuseaconnectionstringlikethefollowing:
strConnect=_T("Driver={MySQLODBC3.51Driver};Server=localhost;"
"Database=MyDatabase;User=MyUserName;Password=MyPassword;Option=4;");
Ifyouwanttoconnectwitharemotedatabase,youneedtospecifythenameoftheserveroritsIPintheServerparameter.IfthePortisdistinctto3306(defaultport),youmustspecifyit.
strConnect=_T("Driver={mySQLODBC3.51Driver};Server=MyRemoteHost;"
"Port=3306;Option=4;Database=MyDatabase;Uid=MyUsername;Pwd=MyPassword;");
TheparameterOptioncanbeoneormoreofthefollowingvalues:
∙1-Theclientcan'thandlethatMyODBCreturnstherealwidthofacolumn.
∙2-Theclientcan'thandlethatMySQLreturnsthetruevalueofaffectedrows.IfthisflagissetthenMySQLreturns'foundrows'instead.OnemusthaveMySQL3.21.14ornewertogetthistowork.
∙4-Makeadebugloginc:
\myodbc.log.ThisisthesameasputtingMYSQL_DEBUG=d:
t:
O,c:
:
\myodbc.loginAUTOEXEC.BAT.
∙8-Don'tsetanypacketlimitforresultsandparameters.
∙16-Don'tpromptforquestionsevenifdriverwouldliketoprompt.
∙32-Enableordisablethedynamiccursorsupport.ThisisnotallowedinMyODBC2.50.
∙64-Ignoreuseofdatabasenamein'database.table.column'.
∙128-ForceuseofODBCmanagercursors(experimental).
∙256-Disabletheuseofextendedfetch(experimental).
∙512-PadCHARfieldstofullcolumnlength.
∙1024-SQLDescribeCol()willreturnfullyqualifiedcolumnnames.
∙2048-Usethecompressedserver/clientprotocol.
∙4096-Tellservertoignorespaceafterfunctionnameandbefore'('(neededbyPowerBuilder).Thiswillmakeallfunctionnameskeywords!
∙8192-ConnectwithnamedpipestoaMySQLdserverrunningonNT.
∙16384-ChangeLONGLONGcolumnstoINTcolumns(someapplicationscan'thandleLONGLONG).
∙32768-Return'user'asTable_qualifierandTable_ownerfromSQLTables(experimental).
∙65536-ReadparametersfromtheclientandODBCgroupsfromf.
∙131072-Addsomeextrasafetychecks(shouldnotbeneededbut...).
Ifyouwanttohavemultipleoptions,youshouldaddtheaboveflags!
Forexample:
16+1024=1030anduseOption=1030;.
Formoreinformation,gotoMyODBCReferenceManual.
ODBCDriverforAS400
strConnect=_T("Driver={ClientAccessODBCDriver(32-bit)};System=myAS400;"
"Uid=myUsername;Pwd=myPassword;");
ODBCDriverforSyBase
strConnect=_T("Driver={SybaseSystem10};Srvr=MyServerName;Uid=MyUsername;"
"Pwd=myPassword;");
ODBCDriverforSybaseSQLAnyWhere
strConnect=_T("ODBC;Driver=SybaseSQLAnywhere5.0;"
"DefaultDir=C:
\\DatabasePath\;Dbf=C:
\\SqlAnyWhere50\\MyDatabase.db;"
"Uid=MyUsername;Pwd=MyPassword;Dsn=\"\";");
DSNConnection
ODBCDSN
strConnect=_T("DSN=MyDSN;Uid=MyUsername;Pwd=MyPassword;");
OLEDBProvider
OLEDBProviderforSQLServer
ForStandardsecurity:
strConnect=_T("Provider=sqloledb;DataSource=MyServerName;"
"InitialCatalog=MyDatabaseName;"
"UserId=MyUsername;Password=MyPassword;");
ForTrustedConnectionsecurity(MicrosoftWindowsNTintegratedsecurity):
strConnect=_T("Provider=sqloledb;DataSource=MyServerName;"
"InitialCatalog=MyDatabaseName;"
"IntegratedSecurity=SSPI;");
Ifyouwanttoconnecttoa"NamedInstance"(SQLServer2000),youmusttospecifyDataSource=ServereName\InstanceNamelikeinthefollowingexample:
strConnect=_T("Provider=sqloledb;DataSource=MyServerName\MyInstanceName;"
"InitialCatalog=MyDatabaseName;UserId=MyUsername;Password=MyPassword;");
IfyouwanttoconnectwithaSQLServerrunningonthesamecomputer,youmustspecifythekeyword(local)intheDataSourcelikeinthefollowingexample:
strConnect=_T("Provider=sqloledb;DataSource=(local);"
"InitialCatalog=myDatabaseName;"
"UserID=myUsername;Password=myPassword;");
ToconnecttoSQLServerrunningonaremotecomputer(viaanIPaddress):
strConnect=_T("Provider=sqloledb;NetworkLibrary=DBMSSOCN;"
"DataSource=130.120.110.001,1433;"
"InitialCatalog=MyDatabaseName;UserID=MyUsername;"
"Password=MyPassword;");
OLEDBProviderforMySQL(ByToddSmith)
strConnection=_T("Provider=MySQLProv;DataSource=test");
WheretestisthenameofMySQLdatabase.Also,youcanreplacethenameofthedatabasebythefollowingconnectionstring:
server=localhost;DB=test.
OLEDBProviderforAS400
strConnect=_T("Provider=IBMDA400;Datasource=myAS400;UserId=myUsername;"
"Password=myPassword;");
Formoreinformation,see:
UsingtheOLEDBProviderforAS/400andVSAM.
OLEDBProviderforActiveDirectory
strConnect=_T("Provider=ADSDSOObject;UserId=myUsername;Password=myPassword;");
Formoreinformation,see:
MicrosoftOLEDBProviderforMicrosoftActiveDirectoryService.
OLEDBProviderforDB2
IfyouareusingaTCP/IPconnection:
strConnect=_T("Provider