Data sources and access types

    Dataodyssey Express allows accessing the data sources of the following types:

маркированный список MS SQL Server Database and ODBC access
маркированный список MS SQL Server Database and IIS using HTTP access + XML
маркированный список ORACLE and ODBC access
маркированный список MySQL and ODBC access
маркированный список VFP DBF tables and File-Server access
маркированный список VFP DBC database and File-Server access
маркированный список Any type of data source above and DataOdyssey server access using RPC and XML
маркированный список Any type of data source above and DataOdyssey server access using IIS, XML and HTTP

    According data source and access type you must set source properties. The DATAMAP source properties can be called when the program is launched, the properties of other sources – from the list of data sources. Usually it is required to compose a special connection string, the format of which depends on source and access type.

MS SQL Server Database + ODBC access

    Data is located in Microsoft SQL server database, it’s accessed via ODBC. The access technology is "client-server". Dataodyssey station is to observe the SQL server, located on the Internet or in a local access network. SQL server may have a WINS or DNS (as Windows server or station usually does) name or IP. Any variant of the name and IP can be used.

    But there is usually a problem when security systems lock all ports except those, required for HTTP, FTP, SMTP and POP3 access. By default a SQL server is accessed using ODBC and TCP/IP via port 1433. In any case, MS SQL access via ODBC is the fastest and most secure, we recommend using it.

    Connection string has the following format: Driver={SQL Server}; Server=server name or IP; Database=database name; uid=user name; pwd=password

    There are special rules for using the user name and password in connection strings.

MS SQL Server database + IIS using HTTP access and XML

    The data is located in an MS SQL server database. It’s accessed by Microsoft Internet Information Server Virtual Directory Management for SQL server using HTTP and XML data format. Access configuration steps are described on MSDN site ( http://msdn2.microsoft.com/en-us/library/aa226553(SQL.80).aspx ) or in MS SQL Book Online article “Accessing SQL server using HTTP”.

    Here is an extract from MS SQL Books Online:

***

Creating the nwind Virtual Directory

This example creates the nwind virtual directory. The nwind virtual directory is used in most of the examples that are used to illustrate URL access to Microsoft® SQL Server™ 2000.

Before you create the nwind virtual directory, you need a physical directory associated with the virtual directory that you are creating (for example, C:\Inetpub\Wwwroot\nwind where nwind is the physical directory associated with the nwind virtual directory that is created in the following procedure).

You also need to create two subdirectories in the physical directory associated with the virtual directory (for example, C:\Inetpub\Wwwroot\nwind\template, and C:\Inetpub\Wwwroot\nwind\schema). These are the directories associated with the virtual names of template and schema types that are created as part of creating nwind virtual directory.

To create the nwind virtual directory

  1. In the Microsoft SQL Server program group, click Configure SQL XML Support in IIS.
     
  2. Expand a server, and then click the Web site you want.
     
  3. On the Action menu, point to New, and then click Virtual Directory. The property page for the new virtual directory is displayed on the screen.
     
  4. On the General tab of the New Virtual Directory Properties dialog box, enter the name of the virtual directory. For this example, type nwind and the physical directory path (for example, C:\Inetpub\Wwwroot\nwind, assuming you have a subdirectory nwind created in the C:\Inetpub\Wwwroot directory). You can optionally use the Browse button to select the directory.
     
  5. On the Security tab, select SQL Server and enter the valid SQL Server login information. When you go to the next tab, you will be asked to confirm the password you just entered.
     
  6. On the Data Source tab, in the SQL Server box, enter the name of a server, for example (local), and optionally, the name of an instance of SQL Server 2000 if more than one instance is installed on the specified computer. In the Database box, enter Northwind as the name of the default database.
     
  7. On the Settings tab, select the Allow URL queries, Allow template queries, Allow XPath, and Allow POST options.
     
  8. On the Virtual Names tab, click New to create the virtual name for the template type.

    In the Virtual Name Configuration dialog box:

    • Enter template in the Virtual name box (it can be any user specified name). In the Type list, select template. Enter the path (for example, C:\Inetpub\Wwwroot\nwind\template, assuming there is a subdirectory template in the physical directory associated with the virtual directory, however the existence of the path is not checked). Click Save to save the virtual name.
  9. On the Virtual Names tab, click New to create the virtual names for the schema type.
    • Enter schema in the Virtual name box (it can be any user specified name). In the Type list, select schema. Enter the path (for example, C:\Inetpub\Wwwroot\nwind\schema, assuming there is a subdirectory schema in the physical directory associated with the virtual directory). Click the Save button to save the virtual name.
  10. On the Virtual Names tab, click New to create the virtual names for the template and schema types.
    • Enter dbobject in the Virtual name box (it can be any user specified name). In the Type list, select dbobject.  Click the Save button to save the virtual name.
  11. Click OK to save the settings.

This creates a virtual directory nwind. The queries specified using this virtual directory are, by default, executed against the Northwind database.

To test the virtual directory, in the browser type: http://<IISServer>/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root and press ENTER.

***

    Connection string format: Host; Virtual directory

    For example, if a virtual directory named “nvind” as created at www.dataodyssey.com, the connection string will be:

www.dataodyssey.com; nwind

    IIS HTTP access is slower and less secure than ODBC access, but it requires no additional network settings or settings for each station from which you want to access data. Just configure internet connection.
 

ORACLE + ODBC Access

    Here is an extract from Oracle ODBC help:

***

   The following list of keywords can be included in the connection string argument of the SQLDriverConnect function call. Missing keywords will be read from the 32-bit Administrator entry for the data source. Values specified in the connection string will override those contained in the 32-bit Administrator entry. See the Microsoft ODBC 3.0 Software Development Kit and Programmer's Reference for more information about the SQLDriverConnect function.

 
Keyword Meaning Values (bold text denotes the default value)
DSN= ODBC Data Source Name User-supplied name.
DBQ= TNS Service Name User-supplied name.
UID= User ID or User Name User-supplied name.
PWD= Password User-supplied password. Specify PWD=; for an empty password.
DBA= Database Attribute W=write access. R=read-only access.
APA= Applications Attributes T=Thread Safety Enabled. F=Thread Safety Disabled.
RST= Result Sets T=Result Sets Enabled. F=Result Sets Disabled.
QTO= Query Timeout Option T=Query Timeout Enabled. F=Query Timeout Disabled.
CSR= Close Cursor Enabled T=Close Cursor Enabled. F=Close Cursor Disabled.
BAM Batch Autocommit Mode IfAllSuccessful=Commit only if all statements are successful (old behavior) UpToFirstFailure=Commit up to first failing statement (V7 ODBC behavior) AllSuccessful=Commit all successful statements (only when connected to an Oracle8 or higher database; against other databases, same behavior as V7.)
PFC= Prefetch Count User-supplied numeric value (specify a value of 0 or greater). The default is 10.
FEN= Failover Enabled T=Failover Enabled. F=Failover Disabled.
FRC= Failover Retry Count User-supplied numeric value. The default is 10.
FDL= Failover Delay User-supplied numeric value. The default is 10.
LOB= LOB Writes Enabled  T=LOBs Enabled. F=LOBs Disabled.
FRL= Force Retrieval of Oracle Long Column T=Forced Long Reads Enabled. F=Forced Long Reads Disabled.
MTS= Microsoft Transaction Server Support T=Disabled. F=Enabled.
FWC= Force SQL_WCHAR Support T=Force SQL_WCHAR Enabled. F=Force SQL_WCHAR Disabled.
EXC= EXEC Syntax Enabled T=EXEC Syntax Enabled F=EXEC Syntax Disabled
XSM= Schema Field Default=Default Database=Database Name Owner=Owner Name
GDE= SQLGetData Extensions T=SQLGetData Extensions enabled F=SQLGetData Extensions disabled
MDI= Set Metadata Id Default to SQL_TRUE T=SQL_ATTR_METADATA_ID defaults to SQL_TRUE F=SQL_ATTR_METADATA_ID defaults to SQL_FALSE
TLO= Translation Option User-supplied numeric value. The default is 0.
TLL= Translation Library Name User-supplied name.
 

If the following keyword is specified in the connection string, the Oracle ODBC Driver will not read any values defined from the 32-bit Administrator:

DRIVER={Oracle ODBC Driver}

Examples of valid connection strings are:

1) DSN=Personnel;UID=Kotzwinkle;PWD=;

2) DRIVER={Oracle ODBC Driver};UID=Kotzwinkle;PWD=whatever;DBQ=instl_alias;DBA=W;

 

***  

    Unlike SQL Server ODBC Driver, Oracle requires a predetermined ODBC user source. Open ODBC data source by navigating to Control Panel > Administrative tools and create an ODBC user data source to connect to the required Oracle database. After that you’ll be able to connect to Oracle from Dataodyssey, using DSN (data source name) of this source.

    The problems usually occur when security systems block all ports except those required for HTTP, FTP, SMTP and POP3 access. By default ORACLE is accessed using ODBC and TCP/IP (or native) via port 1521. Make sure that all firewall and antivirus systems, other intermediate security systems on your computer, computer with ORACLE and Proxy servers don’t block TCP/IP access via port 1521.

    ODBC access is much faster and secure, we recommend using it.

MySQL + ODBC Access

Here is an extract from MySQL ODBC manual:

1.9.4. Connection Parameters

You can specify the following parameters for MyODBC in the [Data Source Name] section of an ODBC.INI file or through the InConnectionString argument in the SQLDriverConnect() call.

Parameter Default Value Comment
user ODBC (on Windows) The username used to connect to MySQL.
server localhost The hostname of the MySQL server.
database   The default database.
option 0 Options that specify how MyODBC should work. See below.
port 3306 The TCP/IP port to use if server is not localhost.
stmt   A statement to execute when connecting to MySQL.
password   The password for the user account on server.
socket   The Unix socket file or Windows named pipe to connect to if server is localhost.

The option argument is used to tell MyODBC that the customer isn't 100% ODBC compliant. On Windows, you normally select options by toggling the checkboxes in the connection screen, but you can also select them in the option argument. The following options are listed in the order in which they appear in the MyODBC connect screen:

Value Description
1 The customer can't handle that MyODBC returns the real width of a column.
2 The customer can't handle that MySQL returns the true value of affected rows. If this flag is set, MySQL returns “found rows” instead. You 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.log in AUTOEXEC.BAT. (On Unix, the file is /tmp/myodbc.log.)
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. (Not allowed in MyODBC 2.50.)
64 Ignore use of database name in db_name.tbl_name.col_name.
128 Force use of ODBC manager cursors (experimental).
256 Disable the use of extended fetch (experimental).
512 Pad CHAR columns to full column length.
1024 SQLDescribeCol() returns fully qualified column names.
2048 Use the compressed customer/server protocol.
4096 Tell server to ignore space after function name and before ‘(’ (needed by PowerBuilder). This makes 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 [customer] and [odbc] groups from my.cnf.
131072 Add some extra safety checks (should not be needed but...).
262144 Disable transactions.
524288 Enable query logging to c:\myodbc.sql(/tmp/myodbc.sql) file. (Enabled only in debug mode.)
1048576 Do not cache the results locally in the driver, instead read from server (mysql_use_result()). This works only for forward-only cursors. This option is very important in dealing with large tables when you don't want the driver to cache the entire result set.
2097152 Force the use of Forward-only cursor type. In case of applications setting the default static/dynamic cursor type, and one wants the driver to use non-cache result sets, then this option ensures the forward-only cursor behavior.

To select multiple options, add together their values. For example, setting option to 12 (4+8) gives you debugging without packet limits.

The default myodbc3.dll is compiled for optimal performance. If you want to debug MyODBC 3.51 (for example, to enable tracing), you should instead use myodbc3d.dll. To install this file, copy myodbc3d.dll over the installed myodbc3.dll file. Make sure to revert back to the release version of the driver DLL once you are done with the debugging because the debug version may cause performance issues. Note that the myodbc3d.dll isn't included in MyODBC 3.51.07 through 3.51.11. If you are using one of these versions, you should copy that DLL from a previous version (for example, 3.51.06).

For MyODBC 2.50, myodbc.dll and myodbcd.dll are used instead.

The following table shows some recommended option values for various configurations:

Configuration Option Value
Microsoft Access 3
Microsoft Visual Basic 3
Large tables with too many rows 2049
Driver trace generation (Debug mode) 4
Query log generation (Debug mode) 524288
Generate driver trace as well as query log (Debug mode) 524292
Large tables with no-cache results 3145731

 

***  

    Though MySQL developers claim that MySQL can be accessed without any predetermined ODBC user source, we couldn't do it. So, navigate to Windows Control Panel > Administrative tools and open ODBC data sources. Create an ODBC user source to connect the necessary MySQL database. After that you will be able to connect to MySQL using Dataodyssey and DSN (data source name) of this source.

    The problems usually occur when security systems lock all ports except those, required for HTTP, FTP, SMTP and POP3 access. By default MySQL is accessed using ODBC and TCP/IP via port 3306. Make sure that all firewall and antivirus systems, other intermediate security systems on your computer, computer with ORACLE and Proxy servers don’t block TCP/IP access via port 3306.

    Anyway, ODBC access is much faster and secure, we recommend using it.

    Connection string has the following format:

DRIVER={MySQL ODBC 3.51 Driver}; SERVER=server name; DATABASE=database name; USER=<app_uid>; PASSWORD=<app_pwd>; OPTION=3;

VFP DBF tables and File-Server access

    You will have to specify a directory with DBF tables as a connection string. When creating tables, you'll be able to add VFP DBF or VFP DBC tables from different sources to one model. This is possible only when the "file-server" technology is used.

VFP DBC database and File-Server access

    You will have to specify a VFP database (*.DBC file) as a connection string. When creating tables, you'll be able to add VFP DBF or VFP DBC tables from different sources to one model. This is possible only when the "file-server" technology is used.

Any data type mentioned above accessing a Dataodyssey Express server using RPC and XML.

    You can use Dataodyssey Express not only as a client. It's also possible to establish client-server communication using Dataodyssey Express as a remote automation server. Such server can be accessed using the RPC (Remote Procedure Call) Windows service or IIS (Internet Inforamtion Server).

    If you are going to gain access using RPC, you should:

1. Install Dataodyssey on a remote computer.
2. Launch Dataodyssey on a remote computer and create all necessary data sources..
3. Configure the RPC service to remotely access Dataodyssey.dcom application ( http://technet2.microsoft.com/windowsserver/en/library/4dbc4c95-935b-4617-b4f8-20fc947c72881033.mspx?mfr=true , http://www.access-remote-pc.com/rpcservice.shtm , ... ).
4. Make sure that all the necessary ports are not blocked by firewall or other antivirus means on both the customer and server sides.
5. Dataodyssey customer-side connection string will look like:

Host ; Remote data source name

    Host is the IP or WINS/domain name of a remote computer. Remote data source name is the name of the data source you created in Dataodyssey on a remote computer. Actually this access type is effective when you work in a private network and can unlock all ports. It is unsafe to unlock ports in a global network.

    Actually, accessing Dataodyssey server using IIS is more practical, but harder to configure.

    Here is an extract from RPC documentation:

Network Ports Used by RPC

RPC server programs typically use dynamic port mappings to avoid conflicts with programs and protocols registered in the range of well-known TCP ports. RPC server programs associate their universally unique identifier (UUID) with a dynamic port and register the combination with the RPC EPM. The EPM provides a single point of contact for RPC customers. The RPC customers contact the EPM and use the server program’s UUID to determine the port being used by the server program. The following table indicates the network ports normally used by RPC.

Port Assignments for RPC

Service Name UDP TCP
HTTP 80, 443, 593 80, 443, 593
Named Pipes 445 445
RPC Endpoint Mapper 135 135
RPC Server Programs <Dynamically assigned> <Dynamically assigned>

Any data source type mentioned above accessing a Dataodyssey Express server using IIS, XML and HHTP.

Dataodyssey can function as a remote server, accessed using IIS, HTTP and XML.

Dataodyssey connection string format:

Host; Virtual directory; Remote data source name

www.dataodyssey.com         Table of contents