Data Sources and Access Technologies

 

  

http://www.dataodyssey.com

http://express.dataodyssey.com

    Dataodyssey Express allows to access data sources of the following types:

маркированный список Microsoft SQL Server database and ODBC access
маркированный список Microsoft SQL Server database and IIS access with HTTP + XML
маркированный список ORACLE and ODBC access
маркированный список MySQL and ODBC access
маркированный список Visual FoxPro DBF tables and file-server access
маркированный список Visual FoxPro DBC database and file-server access
маркированный список Microsoft Access database and file-server access
маркированный список Any type of data source above and DataOdyssey Express server access using RPC and XML
маркированный список Any type of data source above and DataOdyssey Express server access using IIS, XML and HTTP

    You should configure data source properties according to the type of data source and access. DATAMAP data source properties can be opened when the program is launched, while the properties of other data sources – from the list of data sources. It is usually required to construct a special connection string that depends on the source and access type.

Microsoft SQL Server Database + ODBC Access

    Data is stored in a Microsoft SQL Server database, accessed using ODBC. The access technology is client-server. Dataodyssey Express station must view the Microsoft SQL Server computer, located on the Internet or in a local area network. The Microsoft SQL Server computer may have a WINS or DNS name (as a Windows server or station usually does) or an IP-address. Any name or IP-address can be used.

    But! There is usually a problem encountered when security systems lock all ports except those required for HTTP, FTP, SMTP and POP3 access. By default Microsoft SQL Server is accessed using ODBC and TCP/IP via port 1433. Make sure that all firewall and antivirus software, other intermediate security systems on your computer, the Microsoft SQL Server computer and proxy servers do not lock TCP/IP access via port 1433.

    Anyway, ODBC access to Microsoft SQL Server is the fastest and most secure, it is recommended that you use it.

    Connection string has the following format:

Driver={SQL Server Native Client 10.0}; Server=server name or IP; Database=database name; uid=user name; pwd=password

    !!! Make sure SQL Server Native Client 2008 is installed onto your computer! This driver isn't included into MDAC and operation systems up to Windows 7. You can download driver installation from here (win32) or Microsoft site http://www.microsoft.com/downloads/details.aspx?familyid=228de03f-3b5a-428a-923f-58a033d316e1&displaylang=en !

    If version of SQL Server is 2000 then you can use "SQL Server" driver (instead of "SQL Server Native Client 10.0") which included into MDAC or installed with operation system.

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

    The ODBC driver for Microsoft SQL Server is delivered with the MDAC package, which you can always download at the Microsoft site. Dataodyssey Express will also try to install the package during the installation of its own.

MS SQL Server database + IIS using HTTP access and XML

    Data is stored in a Microsoft SQL Server database, accessed using Microsoft Internet Information Server Virtual Directory Management for SQL Server and HTTP and XML data format. The steps of access configuration are described on the MSDN site ( http://msdn2.microsoft.com/en-us/library/aa226553(SQL.80).aspx ) or in the Microsoft SQL Books Online article “Accessing SQL server using HTTP”.

    Here is an extract from Microsoft SQL 2000 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.

***

    The simplest connection string format in Dataodyssey Express is:

Host; Virtual directory

    For example, if the virtual directory nwind has been created at www.dataodyssey.com, the connection string will be:

www.dataodyssey.com; nwind

    HTTP access using IIS is slower and less secure than ODBC access, but it does not require configuring additional network settings or settings for each station you want to access data from. Just configure the Internet connection.

But!

    Actually when you allow anonymous user access configuring the virtual directory like this, you allow anyone to do anything with your server via Internet. Any data changing commands will be executed. To prevent unauthorized operations it is recommended you enforce additional security rules. Do the following:

    Dataodyssey Express initially suggests the following connection string format:

<app_uid>:<app_pwd>@Host; Virtual directory

    to transfer user name and password to virtual directory (to SQL server).

    If you are intended to allow anonymous access anyway or the specifics of your Internet connection prevents you from using the syntax to transfer the user name and password in URL, you will have to remove <app_uid>:<app_pwd>@ from the connection string.

    Also you can include into connection string:     Host; Virtual directory [;Proxy] [;SSL] [;Port]

Proxy name Add proxy name or IP as third parameter www.dataodyssey.com; nwind; myproxyname or <app_uid>:<app_pwd>@Host; Virtual directory; myproxyname
Secure Socket Layer directive (SSL) to use HTTPS:\\ Add 'SSL' as fourth parameter

www.dataodyssey.com; nwind; myproxyname; SSL  or www.dataodyssey.com; nwind;; SSL or ...
Port number Include number of port as fifth parameter www.dataodyssey.com; nwind; myproxyname; SSL; PornNumber  or <app_uid>:<app_pwd>@www.dataodyssey.com; nwind;; SSL; PortNumber or www.dataodyssey.com; nwind;;; PortNumber or ...

 

    Due to low operation speed it is not recommended to use this type of access to SQL Server databases for creating and updating cubes. Use it only to create missions and reports that a user can open via Internet. It is optimal. But use ODBC to create and update cubes.

***

Technical remarks:

ORACLE + ODBC Access

    Here is an extract from the 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;

 

***

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

    ODBC access to an ORACLE database is the fastest and most secure, we recommend using it.

    The typical connection string from Dataodyssey Express:

DSN=ORACLE;UID=<app_uid>;PWD=<app_pwd>;DBQ=ORACLE ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;

    Default format uses a predefined user-supplied ODBC data source. Reason - name of driver depends on version of ORACLE. Open ODBC data sources by navigating to Control Panel > Administrative tools and create an ODBC data source to connect to the required ORACLE database. After that you’ll be able to connect to the ORACLE database from Dataodyssey Express, using DSN (data source name) of this data source.

    But! You can use driver reference instead of DSN reference if you know driver name. For example:

Driver={Oracle in OraHome90};Server=server name or IP;UID=<app_uid>;PWD=<app_pwd>;DBQ=ORACLE ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;

    And there are special rules for applying user name and password in connection strings (! User name, database name(DBQ), ... all names are CASE-SENSITIVE !)

    ! User name, database name(DBQ), ... all names are CASE-SENSITIVE !

Notes!

    The 'interoperability' of Microsoft and Oracle applications usually causes more trouble, than it is described in manuals. Some real issues that were encountered are described below:

    The ODBC driver for ORACLE can only be installed by the Oracle Universal Installer. It is included into the installation package of Oracle and usually installs the entire client part of the package.

MySQL + ODBC Access

    Here is an extract from the 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 client 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 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, 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 client/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 [client] 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 predefined ODBC data source, it appeared impossible to do. So navigate to Windows Control Panel > Administrative tools and open the list of ODBC data sources. Create a custom ODBC data source to connect to the MySQL database you need. After that you will be able to access it from Dataodyssey Express using the DSN (data source name) of this data source.

    Problems usually occur when security systems lock all ports except those required for HTTP, FTP, SMTP and POP3 access. By default the MySQL server 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, MySQL server and proxy servers allow access via port 3306.

    Anyway, ODBC access to MySQL is the fastest and most secure and it is recommended that you use 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;

    And there are special rules for applying user name and password in connection strings.

    The MySQL ODBC driver is a separate package that can be downloaded from the MySQL site at http://mysql.com.

Visual FoxPro DBF Tables and File-Server Access

    You will have to specify a directory with DBF tables as a connection string. When creating models, you will be able to add tables from various Visual FoxPro DBF or DBC data sources to one model. This is possible only when the file-server technology is used.

Visual FoxPro DBC Database and File-Server access

    You will have to specify a Visual FoxPro database (a DBC file) as a connection string. When creating models, you will be able to add tables from various Visual FoxPro DBF or DBC data sources to one model. This is possible only when the file-server technology is used.

Microsoft Access Database and File-Server Access

    Dataodyssey Express uses three technologies to access Microsoft Access databases: ODBC, ADO and ADOX:

    But, even using of set of technologies don't allows getting full access ! You can't deal with Microsoft Access data like with that from other data sources, because there is actually no full-fledged DDL and SQL syntax support and programmers meet other enormous odds. At that Microsoft strategic plans fail to position Microsoft Access as a full-fledged database for serious tasks. That is why the following restrictions are imposed by Dataodyssey Express when operating data in Microsoft Access:

   The typical connection string looks like:

Driver={Microsoft Access Driver (*.mdb)}; dbq=Full_Path\File.mdb; uid=<app_uid>; pwd=<app_pwd>

   Though ODBC is used the technology is not client-server, because the dbq= option should refer to a real file accessible from the computer or local area network.

    And there are special rules for applying user name and password in connection strings.

Any Data Source Type above and Dataodyssey Express Server Access Using RPC and XML

    You can use Dataodyssey Express not only on the client side. It is also possible to establish client-server access using Dataodyssey Express as a remote automation server. This server can be accessed using the RPC (Remote Procedure Call) Windows service or IIS (Internet Information Server).

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

    1. Install Dataodyssey Express on a remote computer.
    2. Launch Dataodyssey Express on the remote computer and create all necessary data sources.
    3. Configure the RPC service to remotely access the Dataodyssey.dcom application ( http://technet2.microsoft.com/windowsserver/en/library/4dbc4c95-935b-4617-b4f8-20fc947c72881033.mspx?mfr=true ).

    4. Make sure that no necessary network ports are locked by a firewall or antivirus software on any machine involved.
    5. The client-side connection string will look like this:

Host ; Remote data source name

    Host is the IP-address, WINS or domain name of the remote computer. Remote data source name is the name of the data source you created in Dataodyssey Express on the 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.

    Thus, accessing a Dataodyssey Express server by using IIS is more practical, but harder to configure.

    Here is an extract from the 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 clients. The RPC clients 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>

!!!

    You may encounter an access problem. If you see the 'Access denied' message or some other errors occurred when connecting to a remote Dataodyssey server, it is required to take the following steps :

1.  Check that DCOM is enabled on both the machine running client and the machine that you are trying to connect (server). In the registry HKLM\Software\Microsoft\OLE the value EnableDCOM should be set to 'Y'. Note that you may have to restart the computer (or at least the DCOM service) after changing this setting.

2. On a Windows XP computers, make sure that remote logons are not being forced into the security context of the GUEST account. To do this (on the server):

  • Select 'Start | Run' and type 'secpol.msc' in the Run dialog
  • Expand the 'Local Policies' node and select 'Security Options'.
  • If the setting 'Network access: Sharing and security model for local accounts' is set to 'Guest only', change it to 'Classic' and restart the computer.

!!!

One more important point about working with Dataodyssey Express remote servers!

    If you place the data map on a Dataodyssey Express remote server and it is the same for the remote server, the problem of data source visibility is very soon to appear. As is mentioned above, you should first define data sources on the server to be seen from this server only. But on the client side you can in turn define data sources, which are not seen from remote servers, being visible to clients only. To avoid getting confused and having lots of error messages, use proper naming notation. For example, begin remote server data source names with REM_ or REMOTE_.

!!! This access type is slow, especially when writing data to tables (a cube update, a join with queries from incompatible sources, etc.)

Any Data Source Type above and Dataodyssey Express Server Access Using IIS, XML and HTTP

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

Host; Virtual directory; Remote data source name

    Also you can include into connection string:     Host; Virtual directory; Remote data source name [;Proxy] [;SSL] [;Port]

Proxy name Add proxy name or IP as fourth parameter www.dataodyssey.com;Scripts;Remote data source name;myproxyname or Host;Virtual directory;Remote data source name;myproxyname
Secure Socket Layer directive (SSL) to use HTTPS:\\ Add 'SSL' as fifth parameter

www.dataodyssey.com;Scripts;Remote data source name;myproxyname;SSL  or www.dataodyssey.com;Scripts;Remote data source name;;SSL or ...
Port number Include number of port as sixth parameter www.dataodyssey.com;Scripts;Remote data source name; Remote data source name;myproxyname;SSL;PornNumber  or www.dataodyssey.com;Scripts; Remote data source name;;SSL;PortNumber or www.dataodyssey.com;Scripts; Remote data source name;;;PortNumber or ...

    http://technet.microsoft.com/en-us/library/bb124892(EXCHG.65).aspx

!!!

One more important point about working with Dataodyssey Express remote servers!

    If you place the data map on a Dataodyssey Express remote server and it is the same for the remote server, the problem of data source visibility is very soon to appear. As is mentioned above, you should first define data sources on the server to be seen from this server only. But on the client side you can in turn define data sources, which are not seen from remote servers, being visible to clients only. To avoid getting confused and having lots of error messages, use proper naming notation. For example, begin remote server data source names with REM_ or REMOTE_.

Technical remarks:

!!! This access type is the slowest, especially when writing data to tables (a cube update, a join with queries from incompatible sources, etc.)

www.dataodyssey.com         Table of contents