Database User Connection Strings

This section provides you with the connection strings you can use to connect your database user in Ozeki NG - SMS Gateway to the database server.

When configuring the database user, you have to enter the appropriate connection string in the Connection string template of the Database connection tab in the configuration form (Figure 1).
In some situations, you need to modify the SQL templates in order to have database connectivity. This can be done in the SQL for sending tab of the configuration form.

The connection strings are organized into groups according to the following table of contents:

• Overview
• DSN-Less Connection
  • dBASE
  • Excel
  • Text File
  • Visual FoxPro
  • Access database
  • SQL Server
  • Oracle
  • MySQL
  • AS400
  • Sybase
  • Sybase SQL AnyWhere
• DSN Connection
  • ODBC DSN
• OLE DB Provider
  • SQL Server
  • MySQL
  • AS400
  • Active Directory
  • DB2
  • Microsoft Jet
  • ODBC Databases
  • OLAP
  • Oracle
  • Visual FoxPro
  • Index Server
• OLE DB Data Link Connections
  • Data Link File - UDL

Generally, one of the first steps when you work with databases is to open it. There are several types of database, and each has a different mode of connection. To connect to your database, you need to know the appropriate connection string. This appendix contains the connection strings to most of the known databases. You can find the connection string in the grey box below the name of each database.

ODBC DSN Less Connection ODBC Driver for dBASE

Note: You need to specify the file name in the SQL template statement. For example:

If you use tab-delimited files, you have to create the schema.ini file, and you need to inform the Format=TabDelimited option in your connection string.

Note: You need to specify the file name in the SQL template statement. For example:

If you use a database container, the connection string is the following:

If you work without a database container, you have to change the SourceType parameter by DBF as in the following connection string:

If you use 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 at the moment of the open operation. For example:

or:

If you want to open in Exclusive mode:

For Standard security:

For Trusted Connection security (Microsoft Windows NT integrated security):

• dbnmpntw Win32 Named Pipes
• dbmssocn Win32 Winsock TCP/IP
• dbmsspxn Win32 SPX/IPX
• dbmsvinn Win32 Banyan Vines
• dbmsrpcn Win32 Multi-Protocol (Windows RPC)

For the older Oracle ODBC driver from Microsoft:

If you want to connect to a local database, you can use a connection string like the following:

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 different from 3306 (default port), you have to specify it.

The parameter Option can be one or more of the following values:

• 1 - The client can't handle the fact that MyODBC returns the real width of a column.
• 2 - The client can't handle the fact that MySQL returns the true value of affected rows. If this flag is set, then MySQL returns 'found rows' instead. You need MySQL 3.21.14 or more recent 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 the driver would like to prompt.
• 32 - Enable or disable the dynamic cursor support. This is not allowed in MyODBC 2.50.
• 64 - Ignore the use of database name in 'database.table.column'.
• 128 - Force the 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 the 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.

For Standard security:

For Trusted Connection security (Microsoft Windows NT integrated security):

If you want to connect to a "Named Instance" (SQL Server 2000), you need to specify Data Source=Servere Name\Instance Name as in the following example:

If you want to connect with a SQL Server running on the same computer, you have to specify the keyword (local) in the Data Source as in the following example:

To connect to SQL Server running on a remote computer (via an IP address):

Where test is the name of MySQL database, you can also replace the name of the database by the following connection string: server=localhost;DB=test.

For more information, see: Using the OLE DB Provider for AS/400 and VSAM.

For more information, see: Microsoft OLE DB Provider for Microsoft Active Directory Service.

If you use a TCP/IP connection:

If you use APPC connection:

For more information, see: Using the OLE DB Provider for DB2.

Using Standard security:

If you use a Workgroup (System database):

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) in the recordset.

Note: You need to specify the file name in the SQL template statement. For example:

Replace c:\temp with any temporary folder. It will create a schema file in that folder when you open it, showing all the fields available. Blank MAPILEVEL indicates top level of folders).

You have to replace c:\temp with any temporary folder.

Replace Pat Smith with the name of the mailbox, and you have to keep the vertical pipe character | to indicate the top level of folders. Place the sub-folder after the vertical pipe when accessing the specific folder.

Note: you can enter queries against the mail store just like a database. For example:

For more information, see: The Jet 4.0 Exchange/Outlook IISAM.

If you want to connect to a Microsoft Access database:

If you want to connect to an SQL Server database:

If you want to use DSN:

For more information, see: Microsoft OLE DB Provider for ODBC.

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.

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 has to be included as part of the URL when connecting to the server (for example, http://www.myserver.com/myolap/).

For more information, see: OLE DB for OLAP, Connecting Using HTTP.

The Microsoft OLE DB Provider for Oracle allows ADO to access Oracle databases.

For more information, see: Microsoft OLE DB Provider for Oracle.

For Standard security:

For a Trusted connection:

OS Authenticated connect setting user ID to "/":

OS Authenticated connect using OSAuthent:

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.

For more information, see: Microsoft OLE DB Provider for Visual FoxPro.

For more information, see: Microsoft OLE DB Provider for Microsoft Indexing Service.

Data Link File - 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 the file, and the operating system calls the Data Link app. for you