ODBC 各种数据库连接串

2023-10-26

Overview

Generally, one of the first steps when you are trying to work with databases is open it. You can find several types of those, and each have a different mode of connection. When you try to connect with your database sometimes, you don't know the correct connection string that you must use. It is for that I wrote this article. I wanted to compile the connection strings to the majority of known databases...

ODBC DSN Less Connection

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 theFormat=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={Microsoft Visual Foxpro Driver};UID=;"
    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 itspassword. 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 Serverparameter. 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.login 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 useOption= 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={Sybase System 10};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;"
        "User Id=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;User Id=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;"
        "User ID=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 followingconnection stringserver=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;User Id=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;User ID=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;"
        "User ID=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;"
            "User Id=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;"
        "Data Source=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. Thisconnection 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 OLAPConnecting 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;"
        "User Id=myUsername;Password=myPassword;");

For a Trusted connection:

  • OS Authenticated connect setting user ID to "/":
    strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;"
       "User Id=/;Password=;");
  • OS Authenticated connect using OSAuthent:
    strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=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.

OLE DB Data Link Connections

Data Link File - UDL

strConnection = _T("File Name=c:\\DataBasePath\\DatabaseName.udl;");

If you want to create a Data Link File, you can make a new empty text file, change its extension by .udl, then double click over the file, and the operating system calls for you the Data Link app.

[ Data Link Application ]

本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)

ODBC 各种数据库连接串 的相关文章

随机推荐

  • 字节、字、位、比特的概念和关系

    1 位 bit 来自英文bit 音译为 比特 表示二进制位 位是计算机内部数据储存的最小单位 11010100是一个8位二进制数 一个二进制位只可以表示0和1两种状态 21 两个二进制位可以表示00 01 10 11四种 22 状态 三位二
  • 如何避免服务器被恶意网络攻击

    服务器的IP是可公开透明的 很容易遭到黑客入侵或网络攻击 对服务器的恶意网络行为包括两个方面 首先是恶意的攻击行为 如DDoS攻击 网络病毒等等 这些行为旨在消耗服务器资源 影响服务器的正常运作 甚至服务器所在网络的瘫痪 另外一个就是恶意的
  • STM8L151的IAR工程编译报错Fatal Error[Pe035]: #error directive: “Please select first the target STM8L...

    项目场景 STM8L151G4XX单片机IAR工程编译报错 问题描述 编译报错 Fatal Error Pe035 error directive Please select first the target STM8L device us
  • token的使用流程以及 JWT构成和构建

    1 什么是token token是一个令牌 是前后端开发时的一个验证工具 就是一个字符串 我们先解释一下他的含义 1 Token的引入 Token是在客户端频繁向服务端请求数据 服务端频繁的去数据库查询用户名和密码并进行对比 判断用户名和密
  • Linux的诞生和发展&开源模式的流行

    Linux的诞生和发展 Linux的诞生 1991年最初由Linus Torvalds针对386 个人计算机 开发 Linus Torvalds那时是芬兰的一名研究生 20世纪80年代 计算机硬件的性能不断提高 PC的市场不断扩大 当时可供
  • axivion和astree_Axivion架构分析及验证工具

    德国Axivion公司 德国Axivion 公司是一家完全自主的技术公司 公司保持持续增长 在软件开发解决方案领域拥有超过10年的经验 作为源于斯图加特大学的公司 Axivion公司提供了一个良好的学术背景 开发专业完整的解决方案 以确保软
  • 2011年终总结-DIY 苹果手机铃声

    一首 月亮之上 红遍中国南北 只要这铃声响起 100个人得有10个人掏出手机看看 当之无愧的山寨歌王 当IPhone变成街机 出厂铃声数量不多 铃声总是撞车 DIY个性铃声很有必要的 只要不选择 月亮之上 就没问题 IPhone设置铃声的方
  • IDEA 打war包部署时子模块jar包没依赖上

    使用IDEA在tomcat中部署maven多模块时出现的一个莫名的bug 首先在server中打印下 可以看见 context startup failed due to previous error see server log for
  • <Python>PyQt5,多窗口之间参数传递和函数调用

    PyQt5的多窗口之间参数如何传递 函数如何调用 之前在学习PyQt5的过程中 试着自己编写一个音乐播放器 基本功能实现后 希望添加一个独立的播放列表 然后实现播放列表和播放主界面之间的函数调用 双击播放列表的歌曲 主窗口播放歌曲 这虽然是
  • 数据库应用:MySQL高级语句(三)存储过程

    目录 一 理论 1 存储过程 2 存储过程操作 3 存储过程的参数 4 存储过程的控制语句 二 实验 1 创建和调用存储过程 编辑 编辑 2 存储过程的参数 3 存储过程的控制语句 三 总结 一 理论 1 存储过程 1 概念 存储过程是一组
  • Android 删除文件

    删除文件 代码逻辑实现 一 首先是将数据给 查 出来 二 删除操作 一 删除系统数据库表对应的索引 二 删除成功后需要更新MediaStore 不然下次查出来的数据有发现怎么删除了的还在 三 根据个人需求 重新查一遍数据 更新数据列表 注意
  • matlab向量生成和运算

    向量的生成 1 直接输入 a 1 2 3 4 2 冒号运算符 3 从矩阵中抽取行或者列 4 线性等分法 利用元素间的等差值来实现 利用函数的形式来实现的 语法格式如下 向量名 linespace 第一个元素数值 最后一个元素数值 向量位数
  • Java架构师之旅(二十九 附录《MyBatis3 用户指南》中文版)

    夜光序言 岁月波光粼粼 赋予爱与生命 唯有生活不能被他人代替 只会有寂寞相随 正文 MyBatis 3 2010 08 01 翻译的一个版本 虽难比较老了 但是有一些基础还是值得学习 毕竟是中文版 Contents MyBatis是什么 6
  • SpringBoot项目启动后执行指定方法的四种实现

    今日的好天气 文章目录 方式一 方法上添加注解 PostConstruct 1 定义 2 PostConstruct和 Autowired 构造函数的执行顺序 3 PostConstruct使用时注意事项 4 几点建议 方式二 实现Comm
  • 深度学习03-卷积神经网络(CNN)

    简介 CNN 即卷积神经网络 Convolutional Neural Network 是一种常用于图像和视频处理的深度学习模型 与传统神经网络相比 CNN 有着更好的处理图像和序列数据的能力 因为它能够自动学习图像中的特征 并提取出最有用
  • Java RSA加密解密及签名验证

    一 简介 RSA公开密钥密码体制是一种使用不同的加密密钥与解密密钥 在公开密钥密码体制中 加密密钥 即公开密钥 PK是公开信息 而解密密钥 即秘密密钥 SK是需要保密的 加密算法E和解密算法D也都是公开的 虽然解密密钥SK是由公开密钥PK决
  • opengl的纹理过滤

    和纹理映射相关的另一个重要概念是 过滤 我们已经讨论了怎样将纹理坐标 这是个0到1之间的分数 映射到纹素上 纹理贴图中纹素的坐标总是以整数定义的 但是如果纹理坐标映射到纹素上的坐标为 152 34 745 14 怎么办 不明智的方案是将这个
  • css的三种引入方式

    目录 三种引入方式 1 行内样式 2 内部样式 内嵌式 3 外部样式 3 1外链式 3 2导入式 引入方式的优先级 三种引入方式 1 行内样式 行内样式就是直接把css样式添加在HTML标签中 作为style样式的属性值
  • DDR4 设计概述以及分析仿真案例(硬件学习)

    转载 DDR4 设计概述以及分析仿真案例 硬件设计讨论 EDA365电子论坛网 引言 随着计算机 服务器的性能需求越来越高 DDR4开始应用在一些高端设计中 然而目前关于DDR4的资料非常少 尤其是针对SI 信号完整性 部分以及相关中文资料
  • ODBC 各种数据库连接串

    Overview Generally one of the first steps when you are trying to work with databases is open it You can find several typ