by John Ragan
|
Select A Section home public service database mgr. data access data modeler site notes |
Currently In This Section CoreReader Data Sources ( Please Scroll Down ) |
Pages In Section summary data sources user manual advanced ops data gateway downloads |
|
This is a legally copyrighted work, ( Has not been substantially updated in years after work on CoreReader stopped. Most data sources have changed greatly. ) __________________________________________________
============================================ Copyright ============================================ CoreReader Technology
__________________________________________________ Chapter Introduction __________________________________________________ This is certainly not intended to be an exhaustive list of all the data sources to which CoreReader has been connected around the world. Remember that the following are brand names, and that a listing does not constitute an endorsement of any brand name. They are addressed individually only as a service to CoreReader users because most of them have hidden eccentricities. Your favorite data source is recommended reading for yourself because people are frequently not aware of the shortcomings and eccentricities of the one that they know best. When a data source is missing from this section, it means only that nobody has taken the time to send a report of it. Reports of successes, failures, and problems are appreciated. This section may also make good reading for those who want to become generally knowledgable of the profession. Perhaps it may also be of interest to those who appreciate CoreReader's theoretical foundation as a manifestation of E.F. Codd's foundation work.
__________________________________________________ Chapter Data Sources __________________________________________________
_________________________ IBM has refused to cooperate with the CoreReader project, demanding payment for information, so CoreReader's example connection comes from Microsoft. Perhaps it is common knowledge now, but when I worked with an AS400 ten years ago, I discovered that DB2 was hidden inside it. A simple connection string using the IBM data socket might look something like:
_________________________ AxleBase needs no data socket, but must be installed on the computer. Only two things are needed for a connection; the name of the database and the path to the domain. Other parameters are optional. They can be in a connection string or entered as parameters. The examples include AxleBase connections. An AxleBase database is always associated with a domain which controls databases. In the server name box, enter the complete path to the domain database. If the path is too long for that box, then use a connection string. Enter the name of the database in its box. The domain will direct queries to that database. ( Enter nothing in the path text box. ) The connection examples include AxleBase connections. Select an example to get its parameters. Then change the name. CAUTION: Connecting to CoreReader's own database can endanger the database, so be sure to make backups first. The "numeric data typing" should be turned off for all enterprise level servers including AxleBase. This setting usually provides best query results, but may need to be turned on for some queries. Set the max keyword to "limit". Use the asterisk wildcard character.
_________________________ CoreReader was tested against a DB2 server running on an s390 mainframe. A Neon data socket was used. Some of the comments in the mainframe section may also be generally applicable to a DB2 connection, depending upon the host infrastructure. The DB2 example in CoreReader may be misleading because your server may require much more information. If your PC has a functioning DSN, use it first. Additional information may be found in the Mainframe section.
_________________________ Before attempting to connect and query a Ms. Exchange Server, some study and research is recommended. Although it is, technically, a database server, it is a database server only in the sense that any lump of data with a means of accessing it can be called a database server. It might more appropriately be referred to as a data source because it is so deviant. The industry standard ODBC connection cannot be used to access it. Only an OLEDB connection can be used to connect to it. The data socket that must be on the client computer is the ExOLEDB provider. The SQL syntax used by Ms. Exchange Server is too deviant to be referred to as a SQL dialect. The user must be familiar with it. It would be nonsensical to even include it as a dialect in CoreReader.
_________________________ Special thanks to Reinier Olislagers of The Netherlands for input. Also, refer to the InterBase notes. Firebird is an open source product that was spun off from InterBase. The InterBase owners later closed the code. By now, there may be lots of differences between the two, but the basics have not changed. Unfortunately, extraneous non-standard characters are allowed in object names. That problem can be circumvented through the use of CoreReader's configuration parameters. It appears that perhaps quotation characters should be used as delimiters instead of the standard apostrophe. I have not been able to get any of the data sockets to work in the test environment. Maybe it's my fault or maybe it's because they're so new. After all, the MySql people did not have a reliable data socket for a couple of years after they announced themselves. The data socket that was reportedly used to connect CoreReader to Firebird version 1 was the XTG ODBC driver (beta 16). Many data sockets may be used to connect to InterBase and to Firebird. Three of the free ones are the XTG driver, the ODBC/JDBC (Starkey) driver, and the IBPhoenix driver. These data sockets use non-standard connection parameters such as the DBNAME instead of DATABASE. Therefore, a connection string must be used instead of entering parameters into the boxes. A connection string using the IBPhoenix data socket might look something like this:
A connection string using the XTG data socket might look something like this:
_________________________ ( Special thanks to Peter Gore of the U.K. for input. ) FoxPro can use either of two types of databases, both of which are supported by CoreReader (of course). They are also sometimes referred to as source types. They are the DBC, which is an encapsulated database file, and the DBF, which defines a directory as a database. For the DBF type, the path is the database. When it is set, all of the tables in the directory are included in the database. It is a no-nonsense method of increasing speed and manageability that is used by several database managers. Important! Place the source type in the database name field and place the source db in the database path field when using parameters to establish a Foxpro connection. ( Ok, I know that this paragraph looks dumb, but I keep forgetting to do that and I forget to read the documentation, and it sometimes takes me days to make a FoxPro connection! And then I REALLY feel dumb. ) CoreReader can use either a drive or an UNC in parameter connections. Limited testing indicates that FoxPro or the Microsoft drivers may have a problem with case sensitivity when using wild-card characters.
_________________________ ( Special thanks to Jeffery Ross of Little Rock, AR. for input. ) The HP300 is a fine system with interesting structural characteristics. Its architecture is as interesting as that of Oracle. A benefit of it is that it allows CoreReader to work in multiple databases simultaneously. The architecture of the HP3k allows a connection to the server without opening a database. Therefore, it is possible for CoreReader to make a good HP3K connection and then be unable to load a database. This may happen, for example, if a database password is incorrect. This behavior is not a shortcoming in database managers which do it, but can be confusing if one is not accustomed to it. It is the same interesting response that Paradox exhibits. Although there are more complicated connections, the HP3K architecture requires a fairly complex connection string. However, that complex connection provides benefits. One of the tremendous advantages that this developer encountered on a job site was the ability to make a single connection to multiple databases and use them as a single data source. Care should be exercised when selecting data sockets for the HP3k. This developer encountered a data socket on a job site that could not handle standard SQL adequately and required proprietary commands.
_________________________ Please refer to the HP3000 notes.
_________________________ Ingres began life as an academician's study of the RDBMS theoretical construct which initially produced a delightfully predictable system. Based on a review of SQL code that I wrote ten years ago for an Ingres project, CoreReader can be expected to function with Ingres if Computer Associates did not introduce problems after buying it.
_________________________ A review of my very old Delphi code indicates that CoreReader will operate InterBase without problems, but I no longer have InterBase running to test it. Interbase was intentionally written to ANSI standards by the Borland team, so you ought to be able to make it jump through hoops if it has not recently deviated. Recent versions allow extraneous non-standard characters in object names. Use quotation characters (not apostrophes) as delimiters. Many data sockets may be used to connect to InterBase and to Firebird. Three of the free ones are the XTG driver, the ODBC/JDBC (Starkey) driver, and the IBPhoenix driver. These data sockets use non-standard connection parameters such as the DBNAME instead of DATABASE. Therefore, a connection string must be used instead of entering parameters into the boxes. A connection string using the IBPhoenix data socket might look something like this:
A connection string using the XTG data socket might look something like this:
_________________________ Although IBM refused to cooperate with the CoreReader project, CoreReader has been successfully connected to IBM mainframes. The mainframe example that you find in CoreReader's connection screen is a minimal generic connection. The example provides required information, but much more can be used when connecting and much more may be required for a particular connection. The easiest way would be to use a DSN if one is handy. The data socket determines how well a mainframe can be made to behave like a database server. In some cases, such as when accessing VSAM data sets, contextual mapping makes SQL problematic, so be prepared for surprises. Behind the scenes, mainframe connections are extremely complex. For example, the connection must use APPC and LU6.2 in addition to the standard TCP/IP. If a native data socket is not on your machine, then try the Ms. SNA provider. Don't bother with MSDASQL because it does not speak all of the required protocols. Since CoreReader is so forgiving, feel free to experiment. Mainframe connections are not easy. A word of caution: Take care that you do not ask CoreReader to bring a mainframe-sized data table to your PC because he will do it and you may spend the following week cleaning up the mess. CoreReader's ability to abstract may be particularly useful in mainframe work. Refer to the Database Abstraction section.
_________________________ The CoreReader test environment includes Access 97 and Access 2000. CAUTION! A fact that is known only among experienced engineers is that Ms. Access is atrociously ill behaved. Among other astounding feats of which it is capable, I have witnessed it repeatedly crash a major corporation's enterprise level database server running on a remote machine.
Therefore, do not run Ms. Access when CoreReader is running. Ms. Access may lock up CoreReader. If that happens, unload Ms. Access, and CoreReader will sometimes resume where it was interrupted. Sometime prior to release number 20921, Microsoft made a hidden change in their data sources. Whether it was part of their frenzied security repairs or more attempts to control the end user cannot be known. What is known is that any CoreReader releases before that date will not function with current Microsoft data sources. Object names with embedded spaces are frowned upon in professional circles. However, many Ms. Access databases seem to have them. If your database has that problem, configure CoreReader to accept illegal characters in names. ( See the configuration section of the advanced documentation. )
Also, your tables should be keyed to allow joins. CoreReader can load Ms. Access queries along with the tables. If you understand their nature and purpose, you can use this feature of CoreReader to emulate server views. A database load will include any working tables such as temporary or deleted tables that access has not cleaned out of its work areas. Those will have strange looking names such as those with an umlaut prefix. CoreReader can be coerced to query them, but they are trash.
_________________________ The CoreReader test environment includes a Microsoft Enterprise Server version 7 on a Ms. Win2k Server, a Ms. Enterprise Server version 7 running on NT 4.0 SP6, and a Ms. Server version 7 running on Win2k Workstation. Sometime prior to release number 20921, Microsoft made a hidden change in their data sources. Whether it was part of their recent frenzied security repairs or more of their infamous attempts to control us cannot be known. What is known is that any CoreReader releases before that date will not function with current Ms. data sources. It pains me to say anything good about that company, but data connections to their databases are the best, simply because they are simple and straight forward ( relatively speaking ). Database Abstraction If the appender is used in Sql Server, the inclusion of the dbo is required. ( Which I tend to forget, and which almost caused me to drop the feature from CoreReader because I thought that it had stopped working. ) Refer to the Database Abstraction section of the documentation for details. Connection Strings Remember that, if it is necessary, CoreReader will accept parameters along with a connection string. This allows the simplicity of entering parameters with enhancement by the connection string. When using both, do not duplicate values. Exclude the carriage returns in the following examples which are used here for readability. Also, except in a driver name, spaces are also usually taboo. Security A Ms. SQL Server connection can use either the server's security or windows security. CoreReader always tries to use the server's security. That may not work if your organization supports only windows security. Microsoft recently began pushing windows security for logins instead of using the server's security. If your organization requires that you use windows security, then use a connection string so you can use non-standard parameters in the connection. In the connection string, include the Trusted_Connection parameter. An ODBC connection string using windows security might be something like: The very same connection string using server security might be something like: If using a DSN with windows security, the string might be something like: Using OLEDB OLEDB connections are more confusing, slower, and used less often. An OLEDB connection to a Ms. SQL Server 2000 can use either the SQLOLEDB or the MSDASQL data socket. Although newer, SQLOLEDB does not allow multiple connections and does not maintain a connection as well as the older MSDASQL.
_________________________ The CoreReader test environment includes a MySql server version 3.23.49 running on a Ms. NT4 SP6 Server and a server version 4.018 on win2k. The ODBC drivers used for testing were {MyOdbc} version 2.50.19.00 dated 5 Oct 98 and {MySQL ODBC 3.51 Driver}. First of all, and this is important, the early version of the data socket just plain did not work. This is the Microsoft method of marketing that withholds the limitations to lead one to invest in a product before knowing the facts. It wasted a lot of my time, which is why I was angry about it. Let us hope that this is not indicative of the future. ( See the policy letter published on this site. ) The MySql server has SQL limitations which are taking a very long time to correct. For example, it still cannot do a full join. Refer to the MySql documentation about its SQL limitations before running against it. Many of the standard CoreReader queries will fail with misleading error messages. When using a DSN to connect, the MySql data socket seemed to require that a client cursor be specified. The MySql examples in the connection screen have been tested, and all CoreReader MySql connections are copied from them. Part of the power of MySql arises from its ability to run on so many operating systems, but this can create confusion about requirements for object names. Do not let this concern you. CoreReader will handle all naming constraints and conventions on all platforms. To insure compliance with the various naming requirements, regardless of the platform on which MySql is running, simply allow CoreReader to generate and control the names of all objects in the queries.
_________________________ This is recommended reading if you are new to Oracle ! The CoreReader test environment includes an Oracle Enterprise Server version 8.1.7 running on a Ms. Win2k Server, and using the native Oracle data sockets. Oracle is a great product for any large company that has a dedicated DBA staff, but it has some peculiarities. Oracle has a unique and interesting implementation of the relational database concept. It is not incorrect and is not problematic, but if one does not know it and assumes that it is like other implementations, it will not make sense. CoreReader presents Oracle data in a form similar to other servers by handling each Oracle schema as a separate database.
Formatting SQL CoreReader allows SQL statements to be formatted with carriage returns and tabs so humans can read them. Some versions of Oracle cannot handle control characters so they cause problems when they receive formatted SQL. Remove all formatting from the statements before sending them to Oracle unless you know that your version can handle them. DSN Driver Name When using a DSN connection, do not enter the Oracle driver name. When the Oracle driver name is entered, the Oracle data socket ignores the DSN and requires a complete connection string even if a DSN is requested. Limiting The Data Return Caution! Oracle provides no standard safeguard to limit the size of a data set. If you inadvertently ask for a very large dataset, Oracle will stomp on your little workstation. When that happens, the problem is sometimes compounded by the Oracle sub-systems which can continue the crash into the rest of an infrastructure. If that frightens you, that is the intent. However, reasonable precautions will prevail.
Load Speed Loading an Oracle database can be very slow. Be prepared for a five or ten minute load of a company's database. This is caused by the Oracle implementation of the database concept, which is not incorrect, but is different from most others. Its relational construct implementation conforms as well as any other to the EF Codd model, but the basic database concept is mapped into the computing environment slightly differently. It is not a bad implementation, but it will cause a slow load of a twenty-thousand-table database. This problem can be handled through the CoreReader configuration. See the Database Abstraction section of the documentation for help.
Data Socket Hangs If CoreReader happens to be connecting to an Oracle server during a server crash, the data socket locks onto CoreReader and sometimes takes CoreReader down with it. I've not yet found a way to protect CoreReader from Oracle because of the socket lock. Perhaps newer versions of the socket will behave better. Persistent Connection Hangs When an Oracle server locks up because it has used up the RAM on its server, the data socket sometimes locks into CoreReader. When this happens, problems with Oracle connections will persist until the server is cleared. DBA Login Warning Be careful of which login is used. Connecting and loading with a DBA login can inundate a PC with twenty or thirty thousand tables although you ask for only one database. For assistance with this problem, refer to the Database Abstraction section. Data Type Peculiarities Be aware of the peculiarities of Oracle's data types. Because Oracle has problems with some data types, Oracle data sockets should be used if they are available. Also, Oracle's query ability is limited by some of its data types. If you are not familiar with Oracle, you will learn that you need to be aware of the types of data in each table. Certain Oracle data types combined with certain queries will cause the queries to fail. A Major Join Shortcoming Oracle cannot do industry standard joins. It is limited to equal joins only. Also, Oracle uses non-standard plus operators in its joins. If you are new to Oracle, you must be aware of this deviant behavior. However, CoreReader will support it. Enter the non-standard operators into the fields which contain the names of the join columns. For example: columnname (+) equals columnname This is so dumb that perhaps it will be rectified in future versions of Oracle.
The "AS" keyword Do not enable the SQL 'as' keyword because Oracle is not capable of understanding it.
More Data Type Problems ! Do not use an ODBC connection to query the Oracle LONG, CLOB, and BLOB datatypes. Those Oracle datatypes cause catastrophic failure of the Oracle ODBC data socket. When that occurs, the data socket locks into CoreReader and cannot be cleared. It sometimes requires a machine reset to clear it! The Oracle OLEDB data socket does not seem to have this problem. ( I am still looking for a way to insulate CoreReader from ill behaved data sockets. ) Inaccessible Tables I personally do not consider this a shortcoming, although it can be problematic. The return of a table name in an Oracle load does not necessarily mean that it is available to the connection. You may build a query with a table that is listed on the screen, only to find that Oracle cannot see it! The problem seems to be that the Oracle schema construct canonical boundary is intended to exhibit transparency without permeability. For some purposes, this could be a fantastically useful feature. This may merit study as a new tool within the larger theoretical construct. Very interesting behavior, indeed. In any case, schema boundaries may be rendered permeable through the use of the CoreReader prepended segmented qualifiers when not obviated by the security matrix. See the Database Abstraction section of the documentation for details.
_________________________ The CoreReader test environment includes Paradox 7.0 for Windows 3.1 and Paradox 7.0 for NT. The sophistication and craftsmanship of the old Paradox earned special recognition for those who created it. Security If the login is not correct, Paradox will report that a good connection has been made and the table names will be loaded. But since the database is locked the column names will return an error message. This is standard Paradox behavior since the table names can be seen on disk anyway. Paradox permitted a developer to stack multiple passwords for sophisticated system engineering, but CoreReader has only one password entry box. If multiple passwords are needed, a connection string or DSN should be used. Versions As of release number 20724, CoreReader will connect to any Paradox version. The defaults are for version 4 and are shown in the parameters below. Net lock file If a parameter connection is made, notice that there is no provision for a net lock file. However, CoreReader sets the READONLY property to TRUE. Database name The old Paradox database was constructed like some modern servers, so enter its path in the database name field and leave the path field blank. Default Overrides CoreReader allows overriding the following defaults by a connection string.
_________________________ I have not run PostgreSQL and have received no reports on it, but I expect no problems. I will personally test it when I have time to bring another Linux box on line.
_________________________ CoreReader has queried the S/390 mainframe. Please see the notes on the mainframe and DB2 pages.
_________________________ There have been many brands of spreadsheets, so insure that you use the correct data socket for your brand and version. If you cannot find a data socket to plug into, try treating it as text. CoreReader attempts to handle spreadsheets like a database. If the first row contains column names, CoreReader will allow you to use column names as though it is a database table. CoreReader will do many of the database functions such as selections, sorts, etc. Functionality depends upon the spreadsheet and the data socket.
_________________________ Use the Ms. Sql Server as an example, but use the Sybase data socket. SyBase and Ms. stopped sharing data sockets a few years ago.
_________________________ When using text files as a database, do not expect all the functionality that can be obtained from a database manager. Like other data sources, text files require a data socket. The most popular is Microsoft's which is in the ODBC driver list on the connection setup screen. However, others can be used. Loading the database when the Microsoft data socket is used causes the text files in the target to be returned as table names. A CDO text driver is being constructed because the big companies do not want to give us real access to data. It has been moved ahead of some other projects simply because IBM, Microsoft, and others are becoming competitive with CoreReader.
_________________________ See the mainframe section.
_________________________ Please see the S/390 and mainframe sections.
_________________________ Feel free to experiment. Any data socket should work. Use the "other" type of database manager and CoreReader will try whatever you tell him. Some brands of database managers require unique connection parameters. If CoreReader's available parameters do not meet your database needs, then use a DSN or a connection string. Reports of unique connection requirements and reports of anomalous behaviour will be appreciated.
|
|
Technology and web site
|
|
|
Web site is maintained with Notepad.
|
|