Appendix "G" - Database plugin connection strings
This section gives you the connection strings you can use to connect to your database server in the database plugin of Ozeki Message Server 6. To configure the database plugin, you have to enter one of these connection strings on the database plugin configuration form (Figure 1). In some situtations the SQL templates need to be modified in order to have database connectivity. This can be done on the SQL Templates tab of this form.
The connection strings are organized into groups according to the following table of contents:
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 this chapter is written. It contains the connection strings to the majority of known databases...
ODBC DSN Less Connection ODBC Driver for dBASE
Connection string: "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277; Dbq=c:\DatabasePath;" |
Note: You must specify the filename in the SQL template statement... For example:
"Select Name, Address From Clients.dbf" |
ODBC Driver for Excel
Connection string: "Driver={Microsoft Excel Driver (*.xls)};DriverId=790; bq=C:\DatabasePath\DBSpreadSheet.xls;DefaultDir=c:\databasepath;" |
ODBC Driver for Text
Connection string: "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.inifile, and you must inform the Format=TabDelimited
option in your connection string.
Note: You must specify the filename in the SQL template statement... For example:
"Select Name, Address From Clients.csv" |
Visual FoxPro
If you are using a database container, the connection string is the following:
Connection string: "Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=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:
Connection string: "Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=DBF; SourceDB=C:\DatabasePath\MyDatabase.dbc;Exclusive=No" |
ODBC Driver for Access
Connection string: "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:
Connection string: "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:
Connection string: "Driver={Microsoft Access Driver (*.mdb)}; Dbq=C:\VC Projects\ADO\Samples\AdoTest\dbTestSecurity.mdb; SystemDB=C:\Program Files\Microsoft Office\Office\SYSTEM.mdw;" |
If you want to open in Exclusive mode:
Connection string: "Driver={Microsoft Access Driver (*.mdb)}; Dbq=c:\DatabasePath\dbaccess.mdb;Exclusive=1;" |
ODBC Driver for SQL Server
For Standard security:
Connection string: "Driver={SQL Server};Server=MyServerName; Trusted_Connection=no;Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;" |
For Trusted Connection security (Microsoft Windows NT integrated security):
Connection string: "Driver={SQL Server};Server=MyServerName; Database=myDatabaseName;Uid=;Pwd=;" |
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:
Connection string: "Driver={SQL Server};Server=MyServerName; Database=MyDatabaseName;Trusted_Connection=yes;" |
(local)
like in the following sample:
Connection string: "Driver={SQL Server};Server=(local); Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;" |
Address
parameter must be an IP address and must include the port. The Network
parameter can be one of the following:
dbnmpntw
Win32 Named Pipesdbmssocn
Win32 Winsock TCP/IPdbmsspxn
Win32 SPX/IPXdbmsvinn
Win32 Banyan Vinesdbmsrpcn
Win32 Multi-Protocol (Windows RPC)
Connection string: "Driver={SQL Server};Server=130.120.110.001; Address=130.120.110.001,1052; Network=dbmssocn;Database=MyDatabaseName;Uid=myUsername;Pwd=myPassword;" |
Connection string: "Driver={Microsoft ODBC for Oracle}; Server=OracleServer.world;Uid=MyUsername;Pwd=MyPassword;" |
For the older Oracle ODBC driver from Microsoft:
Connection string: "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:
Connection string: "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.
Connection string: "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 puttingMYSQL_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
- PadCHAR
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
- ChangeLONGLONG
columns toINT
columns (some applications can't handleLONGLONG
).32768
- Return 'user' asTable_qualifier
andTable_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
Connection string: "Driver={Client Access ODBC Driver (32-bit)}; System=myAS400;Uid=myUsername;Pwd=myPassword;" |
Connection string: "Driver={Sybase System 10};Srvr=MyServerName; Uid=MyUsername;Pwd=myPassword;" |
ODBC Driver for Sybase SQL AnyWhere
Connection string: "ODBC;Driver=Sybase SQL Anywhere 5.0;DefaultDir=C:\DatabasePath\; Dbf=C:\SqlAnyWhere50\MyDatabase.db;Uid=MyUsername;Pwd=MyPassword;Dsn="\";" |
DSN Connection ODBC DSN
Connection string: "DSN=MyDSN;Uid=MyUsername;Pwd=MyPassword;" |
OLE DB Provider OLE DB Provider for SQL Server
For Standard security:
Connection string: "Provider=sqloledb;Data Source=MyServerName; Initial Catalog=MyDatabaseName;User Id=MyUsername;Password=MyPassword;" |
For Trusted Connection security (Microsoft Windows NT integrated security):
Connection string: "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:
Connection string: "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:
Connection string: "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):
Connection string: "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)
Connection string: "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
.
Connection string: "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 DirectoryConnection string: "Provider=ADSDSOObject;User Id=myUsername;Password=myPassword;" |
For more information, see: Microsoft OLE DB Provider for Microsoft Active Directory Service.
OLE DB Provider for DB2If you are using a TCP/IP connection:
Connection string: "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:
Connection string: "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:
Connection string: "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\DatabasePath\MmDatabase.mdb;User Id=admin;Password=;" |
If you are using a Workgroup (System database):
Connection string: "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\DataBasePath\mydb.mdb;Jet OLEDB:System Database=MySystem.mdw;" |
Connection string: "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.
Connection string: "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 template statement... For example:
"Select Name, Address From Clients.txt" |
Connecting to an Outlook 2000 personal mail box using the JET OLE DB Provider: (By J. Cardinal)
Connection string: "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).
Connection string: "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:
Sql template: SELECT Contacts.* FROM Contacts;" |
For more information, see: The Jet 4.0 Exchange/Outlook IISAM.
If you want to connect with a Microsoft Access database:
Connection string: "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:
Connection string: "Provider=MSDASQL;Driver={SQL Server};Server=MyServerName; Database=MyDatabaseName;Uid=MyUsername;Pwd=MyPassword;" |
If you want to use DSN:
Connection string: "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 OLAPMicrosoft 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.
Connection string: "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/).
Connection string: "Provider=MSOLAP;DataSource=http://MyOLAPServerName/; Initial Catalog=MyOLAPDatabaseName;" |
Using SSL
Connection string:"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.
Connection string: "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:
Connection string:
"Provider=OraOLEDB.Oracle; Data Source=MyOracleDB; User Id=myUsername;Password=myPassword;" |
For a Trusted connection:
OS Authenticated connect setting user ID to "/":
Connection string: "Provider=OraOLEDB.Oracle; Data Source=MyOracleDB;User Id=/;Password=;" |
OS Authenticated connect using OSAuthent:
Connection string: "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
Connection string: "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)Connection string: "provider=msidxs;Data Source=MyCatalog;" |
For more information, see: Microsoft OLE DB Provider for Microsoft Indexing Service.
OLE DB Data Link ConnectionsConnection string: "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.
Source: http://www.codeproject.com/database/connectionstrings.asp
More information
- A1 - OTA Bitmap
- A2 - Operator logo I
- A3 - Operator logo II
- A4 - Ringtone
- A5 - Wappush
- A6 - VCalendar
- Appendix "A7" - Message types - VCard
- A8 - Barcode
- B - Service providers
- C - SMSC numbers
- D - GSM Operators
- E - SMS alphabet
- Appendix "F" - GSM error codes
- G - Conn strings
- H - References
- I - MMSC information
- Frequently asked questions
- K - SMS Format