ado connect 收集.docx

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