by John Ragan
|
Select A Section home public service database mgr. data access data modeler site notes |
Currently In This Section CoreReader Operation Manual ( 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. A report of erroneous or misleading information will be appreciated. ) __________________________________________________
============================================ Copyright ============================================ CoreReader Technology
__________________________________________________ Chapter Introduction __________________________________________________
_________________________ The CoreReader documentation is in three books. Book 1:
The point-and-click character of CoreReader allows a beginner to benefit from the system without mastering any documentation. The section on connecting to data sources is probably necessary, but the other sections can wait until the beginner wants them. Book 2:
Book 3:
_________________________
Introduction This is a query tool which connects to all data sources and allows the creation of queries just by pointing and clicking. CoreReader connects to data sources. A data source may be a database server, a desktop database, a spreadsheet, a mainframe data set, or numerous other types of data storage configurations. If you can think of it, CoreReader has probably been used to query it. The size of this documentation misrepresents CoreReader to a newcomer because very little of the information is needed for CoreReader's basic purpose. Those who are familiar with database servers and queries may not even need to read it before beginning. However, to fully exploit the many features, this documentation may be required reading. Administrators should study their section in detail for the rich feature set that CoreReader offers. The bad news: This is a tool for techies. Even its simple point-and-click operation requires some understanding. And data connections can frustrate even techies.
Introduction If a multi-user networked installation is needed, please read the Multi-User section later in this documentation after reading this section. Organizations that deploy multiple copies of CoreReader can dramatically shorten startup by initializing connections and configurations for the end user. The most difficult part of its operation is the connection, so CoreReader is designed to allow the distribution of connections to the end user. Also, if there are any known issues with the data sources, they can be handled in the distributed configuration tables. As explained in the advanced documentation, configurations and connections are maintained in tables in a data directory. Therefore, if single-user installations are used, the system can be configured as the manager wants and the data directory can then be copied. After CoreReader is installed on each workstation, the copied data directory can be copied to each installation. When it is started up, instead of creating new tables, it will use the distributed versions. A few thoughts for managers:
Introduction The CoreReader objective is the ANSI SQL-92 standard. A failed query may be the fault of the data source, the data socket, or CoreReader. Of all failed queries, none have yet been verifiably reported to be the fault of CoreReader. If a valid query fails, it should be tried using a different data socket or a different connection type. CoreReader is so quick and easy to use that it will quickly run into a server's shortcomings, so the operator should be aware of the shortcomings of each of his data sources. Refer to the section on data source specifics for a few notes on particular servers. For example, some servers fail on joins of tables containing BLOB's.
Introduction Please. This is a one-man project. Therefore, the testing gets about as many man-hours as I spend on marketing, which is what is left after I spend a full day in development. Also, I have personally worked with only a dozen or so database managers and have only a half dozen running at home. The field is too big for a lone developer to know it well. Your input will be appreciated.
Introduction Note that there is a Getting Started section which steps through a connection and queries. After following the Getting Started steps, immediately connect to another data source and enjoy the power and ease of CoreReader queries. Do not be concerned with configuration initially. CoreReader defaults to the proficient level on startup to grab the interest of the professional. If you find that there are too many bells and whistles for your comfort, change the system to the novice level in the configuration screen, and move to higher skill levels as proficiency is acquired. Data sources that adhere to industry standards require little or no configuration. However if errors are encountered in queries, then CoreReader may be reconfigured to compensate for non-standard shortcomings in the data source. (Some very well known servers are deviants.)
_________________________
Introduction Create a temporary work directory. Choose a download package and download it into the work directory. The download package is a self-extracting compressed file, so it needs no program to unpack it. Just double click on it to uncompress it in the temporary directory. The file will extract into several files, one of which is setup.exe. Run setup.exe to install CoreReader. CoreReader will install under program files in its own directory. When CoreReader is loaded the first time, he builds a database under his startup directory. It is a good idea to save the downloaded install package. If you later upgrade and run into a bug in the new release, uninstall it, and re-install your old one until a bug fix release is posted.
Introduction The change log page on the web site shows the current release number and a list of the changes. To see which release you have, load CoreReader and press the "about" button. CoreReader's install procedure will not over-write an existing installation. Before installing a new CoreReader, the old one must be properly uninstalled. To uninstall the existing CoreReader, see the following uninstall section. Copy the database to a backup location. Delete the old database. ( CoreReader's database can be found in the \db\corereader directory and can be red with an editor or word processor. ) Reboot the computer. Follow the previous installation instructions to install the new CoreReader. Sometimes a problem is encountered in an upgrade simply because I don't have time to test everything in every release in such a large project. Therefore, it might be a good idea to keep your old download in case you ever need to roll back to it from an upgrade.
Introduction Shut down all applications. Reboot the computer to insure that no components are running in the background. Open the Windows settings panel, find the option to uninstall software and select it. Then select CoreReader. Press the uninstall button. Answer "yes" to also uninstall all of the shared components. That's all. It would be a good idea to reboot the computer again to insure tht no components were left running in the background. Inure that Windows did not fail to uninstall these files:
If they are still in the CoreReader directory, then unregister them. For example, to unregister CoreSys.dll, on the windows main menu, select run. In the run box, enter
After they are safely unregistered, they can be deleted.
Introduction ( Beginners should ignore this section. ) The license to which you will or have agreed permits redistribution of CoreReader within commercial distributions to support commercial software. Any commercial system which requires CoreReader's supporting power is expected to be too complex for the mass market. Therefore, if you wish, you may distribute CoreReader's original download to your clients. Please note that, although a commercial re-distribution is not required to acknowledge CoreReader's use, if you are asked in any way about the supporting software, you are then required to immediately and freely acknowledge CoreReader along with copies of the license and copyright.
_________________________
Introduction Download, unpack, and install the system as described in the installation section. No other preparation is needed; CoreReader is immediately ready for service. You will later find that CoreReader is configurable and you may want to change it's appearance or behaviour. Configuration is covered in the advanced documentation book, but is not needed to begin real work. Two sections are recommended reading for everybody as soon as time permits: Connecting and querying. For the beginner, they may not make a lot of sense, so skim over them so you know where to find answers. There are four steps in using CoreReader:
Connecting to a data source is simplified by the CoreReader interface, but it is, by far, the most difficult operation. Those who are not familiar with data connections should first scan the section on connections. Then keep the documentation handy for reference while starting. The initial startup of the system displays a data connection wizard. Follow its directions slowly while referring to the documentation. Completing its instructions will cause it to unload, load the parameters into the connector screen, and display that screen. Then press the connect button to make the connection. Do not be discouraged if CoreReader tells you that the connection failed. After years of working with databases, I still sometimes have trouble with connections. Correct the parameter on the connection screen and press the connect button to retry. After connecting to the data source, it must be loaded into CoreReader. This is done by pressing the load button on the main screen. A load gets handles on all of the objects in the database and displays their names. This allows you to select them simply by clicking on their names. (You will later find that they are also loaded into other areas for use.) After the load, queries may be run. Only two things are needed for a query: Double click on a table name to select it and then select its columns by highlighting them.
Introduction 1. Please read the previous instructions before beginning this exercise. ( This exercise uses CoreReader's built-in database simply because it is the most universally reliable. However, the construction of the embedded database manager is not yet complete, so it cannot respond to all all of CoreReader's query abilities. ) ( If you continue querying CoreReader's database beyond this exercise, you should read it's coverage in the Data Source Specifics section. ) 2. Download, unpack, and install CoreReader. 3. The executable is now in your c:\program files\coreread\ directory. Place a shortcut to it on the computer's desktop. 4. Load CoreReader by clicking on its executable or on its shortcut. 5. You will see the main screen and the connection wizard screen. ( You can use the connection wizard to help make connections, but we will skip it in this exercise.) 6. Press the close button on the connection wizard.
7. On the connector screen, locate the text box containing the words "stored data connections". 8. Press the button on that box to drop down the list of connection examples.
9. Move the cursor down to the AxleBase example and click it.
10. Notice how clicking on the connection name fills the screen with its parameters. 11. Type over the connection name to change it. ( Required. ) 12. Locate the connect button at the bottom of the screen. 13. Press the connect button. 14. A message will pop up telling you that you are connected.
15. Press the ok button. 16. The connector screen will close and you will see only the main screen. The data source to which it is connected will be shown at the top. 17. On the main screen, locate the load button. 18. Press the load button to load the database. 19. Note the table names that appear in the table list. 20. Double click on the setting table to select it.
21. Locate the frame selector on the left of the screen. 22. Click on the "choose columns" to display the column frame. 23. Locate the column names in the window.
24. Place the mouse on the first column name and drag it down to select all of the columns. 25. Locate the query button. 26. Press the query button. 27. You will see all of the system's current settings displayed on screen in the data browser. 28. On the main screen, change nothing except to do the following steps. 29. Display the "choose records" frame. 30. Locate any one of the empty boxes in the second column. 31. Drop down its list to see the names of the table's columns. 32. Select the setting_name column. 33. Locate the next column which contains the logic, but do not change it from "contains". 34. Locate the rightmost column which is blank. 35. In the rightmost column, type the word "load" without the quotes. 36. Press the query button. 37. The query will return only the records that have the "load" string in the setting_name column. 38. Close the data browser by pressing its close button. 39. On the main screen, locate the output selector which now holds the words "data browser". 40. Drop down the output selector list and select the "text display" type of output. 41. Press the query button. 42. You will see the output displayed as text. ( If you care to look, you will find the text file stored in c:\program files\coreread\output\query.text . ) 43. Note the other selections in the output selector. 44. Display the sql frame by selecting "sql code" on the main screen. 45. Note that you can edit the SQL code. 46. Locate the tools button. 47. Press the tools button to open the advanced tools screen. 48. Note the warnings for beginners. 49. Close the tools screen by pressing its close button. 50. Note that the connection that you created is now listed in the main screen's "stored data connections" box. 51. Locate the setup button. 52. Press the setup button to again display the connector screen. 53. Note that the connector screen has the current data connection displayed, and that CoreReader has added your connection to its files.
54. Locate the exit button on the main screen. 55. Press the exit button.
__________________________________________________ Chapter Connecting To A Data Source __________________________________________________
_________________________ There are two requirements for connecting to a data source: 1. CoreReader must be able to see it through network connections. The data source may be anywhere in the world, and if there is a path to it from your computer, CoreReader can connect to it. The path can be of any degree of complexity. 2. The data source's socket must be available for CoreReader to plug into it. Data sockets are covered later in this section under drivers and providers.
_________________________ Connecting to a database is the only difficult operation in CoreReader. Data connections are an arcane subject which even developers seldom master. Confusion arises because so much information is required to make a data connection. But this is understandable when one considers that a valid connection can tell CoreReader how to set up a communication channel to any data source on any kind of computer located anywhere in the world. Also, connections are confusing because they appear to be simple, but then one finds that each server, database, and connection requires different information and even different kinds of information. Also, confusion happens because some of the parameters have confusion built into them. For example, a server is a piece of software such as a database server which runs on a computer, but system administrators and technicians refer to computers as servers, so a server name may be the name of the computer or the database server. Additionally, Oracle installations sometimes compound the problem by confusing a database with a server. Data connections are an excellent example of the fact that we system engineers, system administrators, and managers are not as smart as we think we are.
_________________________ The connection assistant was introduced in release number 20721. When CoreReader is first loaded, he will load the connection assistant. The assistant provides help with basic connections. He is not as smart as he thinks that he is, but he does a fairly good job of prompting for information. If the automatic display of the connection assistant is easily turned off. It turned off, it can be re-enabled in the configuration screen. Also, it is always available from the connection screen via the assistant button.
_________________________ Making a data connection is like plugging a computer's power cord into a wall socket. There is a complex mass of software in the computer that we will refer to as data sockets, and each socket is shaped differently. To make a connection, we give information to CoreReader that tells him how to plug into one of those sockets. Connections are created on the connection setup screen. However, since creating a connection is complex, when CoreReader first loads, he displays the connection assistant. The assistant helps by presenting each connection parameter along with suggestions and observations. (Like most specialists, the assistant is overly arrogant, so please overlook that.) When it closes, it loads the connection setup screen. After the needed values are filled in on the setup screen, press the connect button to connect to the data source. If the connection succeeds, CoreReader will tell you, automatically save the connection, and close the connection screen. If the connection fails, he will tell you. When a connection is saved, its name is added to the list in the dropdown box. Connections in the list may be used simply by selecting one and pressing the connect button. CoreReader may be connected and re-connected to different data sources as often as needed by pressing the connect button. CoreReader handles housekeeping chores in the background and there is no penalty for changing data connections. There is also a save button on the connection screen. It allows an invalid connection to be saved into the connection table. This practice is not recommended. All connections should be validated before saving. CoreReader is distributed with example connections. Select one in the dropdown box to see its parameters. To base a connection on an example, select it to display the parameters, change the name, and then change the parameters as needed. ( Examples can be disabled in the configuration screen. CoreReader will then stop displaying examples in the drop-down list. ) Because of the many different brands of servers and ways of configuring databases, the data connection apparatus must be flexible, so the parameters on the connection screen are not all necessary for any particular data connection. For example, if the database is a server, you don't need a path name, and if it is a desktop database, you don't need the server name. Feel free to experiment with the parameters because CoreReader will not allow you to harm anything and will advise you of errors. After a successful connection, you will load the database by pressing the load button. It should be noted that the objects that are loaded depend upon the permissions for the login that was used in the connection. If insufficient superfluous objects are returned, check the login permissions. Some organizations set up special logins for such operations. The connection table disk file can be edited, but hand editing is not recommended except by very technical personnel. If the repository is rendered inoperable through editing, delete it, and CoreReader will create a blank one ( and any existing connection information will be lost). ( Complaint was received that CoreReader has too many connection parameters. If you find that to be true, your particular server brand name probably has a less flexible tool built just for it that may be a better fit for you. )
_________________________ The following describes the parameters which appear on the data connection setup screen. Not all are needed for a single connection. Entire libraries have been written about these parameters. These very brief descriptions are given for identification and to help the neophyte get started.
Connecting To A Data Source Required. This can be any name that makes sense to the operator. It is required so that CoreReader can save and retrieve your connections. CoreReader will complain if a name contains a colon or a semi-colon because you may later need to use those characters for a special purpose with a name.
Connecting To A Data Source Required. This is usually the commercial brand name of the data source. Some brands of database server require that CoreReader use special mechanisms to make a connection, so he needs to know which to use. If the name is in the selection list, it must be spelled exactly as shown.
Connecting To A Data Source Required. ODBC is recommended. This is required because CoreReader uses a very different mechanism for each type. The connection type specifies the mechanism that will be used between CoreReader and the database manager. ODBC is small and quick because it uses industry standard mechanisms. OLEDB requires additional software, but some companies require it and the monopolists are trying to force you to give up ODBC, so CoreReader will also do OLEDB.
Be aware that the characteristics of a database as manifested by one type of connection will not necessarily be the same as those manifested by the other type. Those manifested characteristics also vary between data socket brand names. ( The .net connection is .not; the billionaires have gotten enough of our money, and CoreReader does not do it. )
Connecting To A Data Source Rarely used. This is for use only by those who are experienced enough to know when it is needed. Improper use with some data source and data socket combinations can cause unpredictable behaviour.
Connecting To A Data Source If you have a connection string, it is the recommended connection method because of its simplicity. Everything can be entered into it, and the remaining parameters ignored. If the data socket requires unusual parameters, then a connection string is necessary. ( If there are security issues, be aware that the connection string will be saved when the connection is made. ) If a complete connection string is entered, no more information is needed and you can press the connect button at this point.
Connecting To A Data Source This is the name of the database to which you want to connect. The Ms. Access type MUST have the mdb file extension (which I usually forget). Data sources, such as Paradox, that use the path as the database name must have the path entered here as the database name, and nothing in the path parameter.
Connecting To A Data Source The name of a database server or computer which CoreReader can resolve to an IP address. This is not the database name and is not the software brand name.
Connecting To A Data Source If the server is on the internet or if the name is not known, CoreReader can use a routable IP address instead of a name. CoreReader operates well across the internet. ( If you address multiple NIC's, I view that as evidence of a high level skill set and therefore offer no guidance in that area.)
Connecting To A Data Source If you have a DSN set up on your computer, it can be entered here. CoreReader can modify the DSN of some data sockets by sending qualifying parameters along with the DSN. For example, if the DSN cannot contain the login, enter the login and password along with the DSN name.
Connecting To A Data Source The driver is a data socket into which CoreReader will plug to get data from the source. It is a piece of software which is known as an ODBC driver. Drivers come from various sources. There may already be drivers installed on your system. When you buy a database manager, you should receive a driver with it. There are also many third party drivers for sale and for free on the internet. A few examples are listed in the drop-down list on the connection screen, but they are not required and not endorsed and may not even be on your computer.
Connecting To A Data Source An OLEDB connection requires a provider, which is a data socket like a driver. Technically, it is referred to as an OLEDB provider. If the computer has no provider software for the database, an OLEDB connection cannot be made. I usually use MSDASQL for Ms. Sql Server because I'm accustomed to its set of bugs. However, MSDASQL is a generic provider which talks to an ODBC driver. I should be using the SQLOLEDB provider for versions 6.5 and later which would pass the ODBC layer and get a tighter connection. Or just use ODBC connections to simplify your life.
Connecting To A Data Source Needed for desktop databases such as Ms. Access. UNC's may be used. Since some data sockets return confusing error messages, CoreReader usually validates paths.
Connecting To A Data Source If your data source is secured, you may need to enter an ID. The permissions that are assigned to the login determine what can be seen in the database and in query returns. CoreReader understands domain security. If your organization uses domain security, leave the login Id blank, and CoreReader will talk to your domain guardian about approving the connection.
Connecting To A Data Source The login password to the database. If your organization uses domain security, leave the password blank, and CoreReader will talk to your domain guardian about approving the connection. Ulcer prevention: When a connection is prevented by security issues, some servers hide the fact by stating that the connection failed instead of advising that the login was not approved. If you are not alert to this, you can waste many hours debugging when you just needed to enter your password correctly. The default behaviour is for CoreReader to save passwords with the connection. That setting can be turned off in the configuration screen.
Connecting To A Data Source The number of seconds that CoreReader will wait for the infrastructure to complete a connection. On a slow network, this may need to be increased. After that time passes, CoreReader will assume that the connection failed, give up, and tell you.
Connecting To A Data Source The number of seconds that the system will wait for the database to return a response to each query.
_________________________ 1. Decide upon a connection method. The three deliver identical connections, perform identically, and require identical information.
2. Enter a name for the new connection. 3. Select or enter the database manager brand name. 4. Select ODBC or OLEDB connection type. 5. Based upon step one, either:
6. Press the connect button.
_________________________ The parameters in this section are not needed to connect to a source. CoreReader provides them as non-standard extensions. In some cases, they increase the power of a connection, and in others, they compensate for the weakness of a data source or data socket. There are two salient characteristics of CoreReader's SQL. First, it attempts to be compliant with the ANSI 92 SQL standard. Also, it attempts to construct queries that are theoretically appropriate to a universal data source construct. The SQL panel allows CoreReader to be reconfigured to generate non-standard SQL to meet the needs of data sources that have deviated from standards. Press the SQL button on the connection screen to display the SQL panel. On the SQL panel, individual settings may be changed, or a brand name can be selected to reset all SQL to the dialect for that brand of data source. See the advanced documentation's abstracted databases section for use and description of the virtual database parameters. They should be used only by the technically advanced. They are a powerful feature that can be confusing.
Connecting To A Data Source CoreReader is not a light-weight tool. If he is told to retrieve a terabyte, he will go to work while the operator watches his computer being overwhelmed. Unfortunately, a safe-guard is not straght-forward because of the SQL deviancy of some major companies. That deviancy causes so many design problems that I would have skipped this feature if it were not so important. A partial remedy for the problem is the max records setting. This may limit the number of records for some queries, but is dependent upon the ability of the data source, the data socket, and the connection type to understand. Entering a Max Records value changes some of CoreReader's internal operations, but does not change the SQL query which also requires protective measures. Protection within the SQL query is affected as explained in the max keyword section. If the max keyword is set (discussed in the next section), then this value will be used by it. Some thoughts for beginners: For system testing, I usually run with a setting of a hundred or less to increase speed. In production, I usually run with max records set to five thousand, but ten or twenty thousand might be more reasonable unless they are very large records. I have run with settings as high as a hundred thousand for small records, but remember that a system like CoreReader must manipulate multiple copies of your data in RAM. The default is 200.
Connecting To A Data Source If the data source recognizes a keyword that limits the size of the data return, that keyword can be entered here. The selections are "limit" and "top". Each keyword is a flag to CoreReader telling him to construct the SQL clause that is specific to that keyword. MySql and AxleBase can use the "limit" clause. For Microsoft products, a "top" statement is used. Each keyword requires a specialized SQL construct. Therefore, if a keyword is selected, queries can be run against only those data sources that understand the selected keyword. When a max keyword is used, it requires the entry of a quantity in the record set size. The default is blank.
Connecting To A Data Source Setting this parameter to "yes" adds an industry standard asterisk to the column name lists in the query screen. Otherwise, only the column names are displayed. Selection of the asterisk in the column name list will return all of the columns. The default is yes.
Connecting To A Data Source The simplification of query construction forces CoreReader to guess what kind of data is entered into selection parameters. When numeric data typing is turned on, he will guess that a number is a numeric data type. When it is turned off, he will treat all values as alpha. Interestingly, most high end database servers usually do not mind if a numeric data type is delimited as an alpha type. Therefore, it is usually best to set this parameter to no when working with servers and with AxleBase. Some low end data sources, such as Ms. Access, need to be told when one of their own columns is numeric. Therefore, when using Ms. Access, this setting may need to be changed to yes. The default is no.
Connecting To A Data Source Inexperienced data modelers sometimes embed spaces or other problematic characters in object names. CoreReader observes professional standards and will normally disallow the use of dangerous characters in names, but he can be told to accept characters such as spaces in object names by setting this option to yes. ( If this option is enabled, the name delimiters must also be set because of delimiter variations in the industry. ) CoreReader checks for only those characters that can certainly cause problems when embedded in object names. More may be added in the future. The following characters are now checked.
Note: When a non-standard name is encountered in a database, it may be indicative of the of other problems and the operator should be alert for unpredictable errors. The default is no.
Connecting To A Data Source When CoreReader is told to accept object names which contain non-standard characters, he must be told which name delimiters to use. Supported delimiters vary among data sources, and it is up to the operator to use the ones that are recognized by each of his data sources. Incorrect delimiters can produce anomalous behaviour. I hate to encourage a lack of professionalism, but because so many beginners use Ms. Access, the left and right delimiters for it are [ and ]. The defaults are blank.
Connecting To A Data Source The default variable delimiter is the industry standard apostrophe, but it may be changed to any printable character. For example, MySql and some versions of Ms. Access allow the apostrophe to be replaced by quotation marks and AxleBase allows any string of characters. Changing to a delimiter other than the apostrophe can cause problems if the database manager does not use it. The default is the apostrophe.
Connecting To A Data Source CoreReader automatically masks the delimiter character in query variables. This service can be turned off to allow the operator to assume the burden. The default is yes.
Connecting To A Data Source To address the universal data source construct, CoreReader creates SQL statements which alias tables, but this service can be toggled off. The default is yes.
Connecting To A Data Source The "as" SQL keyword is considered noise by some servers and some data sources cannot understand it at all although it is a standard. Therefore, since I know of none that require it, the default is toggled off. It can be turned on for any that might require it. The default is no.
Connecting To A Data Source CoreReader uses the industry standard SQL wild card character which is the percent sign, %. It may be changed in this setting if the data source requires it. For example, AxleBase and Access use the asterisk. An incorrect setting of this will cause a failure to query most data sources. The default is %.
Connecting To A Data Source When a connection is made, the operator must then press the load button to load the database object names. If this parameter is set to yes, when the connection is made, CoreReader will immediately begin loading the names. Turning this feature on can be useful in some instances, but is discouraged. When it is turned on, data source errors can sometimes be masked or confusing. The default is no.
Connecting To A Data Source This setting allows sharing a connection in a multi-user system. If it is turned on, the connection can be loaded and used by everyone in the system. However, a shared connection can be altered and deleted only by whomever created it. The default is yes.
Connecting To A Data Source When this toggle is set to yes, it allows CoreReader to attempt to display BLOBs. The handler and locator must be specified if it is enabled. The default is no.
Connecting To A Data Source Since BLOB's are not data, they must each be handled by a program that is designed for that particular kind of BLOB. For example, if the BLOB is a bitmap, perhaps it will be handled by Ms. Paint or by a browser. Enter the name of the program in the blob handler field. Your computer may require the full path with the name. An example might be c:\program files\internet explorer\iexplore.exe. The default is blank.
Connecting To A Data Source If the BLOBs are located in a file, and the database contains the path to each BLOB, select "pointer". When CoreReader is told to display a BLOB, he will pass the pointer to the BLOB handler program which will read it from disk. If the BLOB is embedded in a database table, then select "embedded". The default is blank.
Connecting To A Data Source The name prefixes are a space delimited list of character strings. The list limits the table type objects that will be loaded to those whose leading characters match one of the prefixes. The list is a case-sensitive exact match. Any number of characters may be used in each mask, and any number of masks may be used, but the list must not exceed three thousand characters. If the list is blank, it has no impact on the loading. If it contains a value, every table type object must match at least one of the listed mask values or it will be excluded from the database load. The masks may be used to decrease operator confusion. Also, they can increase the load speed of very large databases tremendously by excluding objects that are not needed. For example, a very large Oracle database might be limited to tables that begin with T_ and to views with their prefix. The default is blank.
Connecting To A Data Source The name list is a space delimited list of the names of table objects. The list limits the objects that will be loaded to those that are specified in the list. This is in addition to the limits imposed by the prefix list. If both masks are used, an object must satisfy both in order to be included. If the list is blank, it has no impact on the loading. But if it contains a value, only those table type objects that exactly match one of the listed values will be loaded. Any number of masks may be entered with any number of characters, but the list must not exceed three thousand characters The masks may be used to decrease operator confusion. Also, they can increase the load speed of very large databases tremendously by excluding objects that are not needed. The default is blank.
Connecting To A Data Source See the advanced documentation's abstracted databases section for use and description. Transcends canonical boundaries to append external objects to the database. CAUTION! This is a powerful feature that data sources and data sockets will generally not expect an external system to employ, so the results are unpredictable. The default is blank.
Connecting To A Data Source See the advanced documentation's abstracted databases section for use and description. Either redesignates or qualifies a database. The default is blank.
__________________________________________________ Chapter Creating and Running Queries __________________________________________________ CoreReader can be configured to operate at various skill levels in the configuration screen. CoreReader's startup default is the proficient level. That level requires knowledge of SQL, the data source, and CoreReader. Most "bug" reports are received from beginners who do not understand operations at that level, so beginners are encouraged to use a lower skill level until their skill increases.
_________________________
Creating and Running Queries 1. CoreReader must be connected to the data source before it can be queried. If a connection has not been made at this time, return to the data connection section for guidance. 2. After the connection has been made, the database can be loaded as outlined in the next sub-section. The database load makes the objects of the database available for queries. 3. After the objects are made available, select the items desired and press the query button. That's all. Just connect, select a table, select its columns, and press the query button. These operations are detailed in the following sub-sections.
Creating and Running Queries A connection has been made, so now load the database by pressing the "load" button. ( CoreReader loads the entire database for point_and_click operation. A database with ten thousand tables can require several minutes to load. ) The database load places the table names in the scrolling window list and makes all of the database objects available for queries. The table names that are displayed will remain unchanged until a different connection is made. The connection may be changed as often as needed, and each time, the load will get the tables from the new data source. Depending upon the ability of the data source, the load may actually make available more than just the database's tables. The available objects depend upon the ability of the data source to return information to CoreReader, so the object types vary. CoreReader handles the differences, and presents all of them as table type objects. This information is presented for database professionals, and if you are new to database work, you can think of all of them as tables.
Creating and Running Queries Query logic is simplified by CoreReader's visual analogue; each part of the query is represented visually by a frame on the screen. The frames are listed in the query option box on the left side of the screen, and each is displayed by clicking on its name. The fames toward the bottom of the list create more complex queries. To simplify the work, each frame, or query clause, is displayed alone so the operator can concentrate on just that part of the query. The downside to this approach is that the operator must remember to make each frame match the others. This is especially evident when a new query is begun. The clear button assists by clearing the entire preceeding query from all frames. In addition to the clear button, there is a button on each frame which will clear that frame, so that most of the preceeding query can be used in a new query. The remainder of the section discusses the use of the various frames to develop queries. The operator must be alert to the fact that as he changes table selections, CoreReader changes the lists in other frames. However, CoreReader is designed to not be overly aggresive because he must also serve the advanced operator, so he preserves some selections that have been made in the various frames. As the operator changes queries, he must be aware of that behavior. That means that although CoreReader attempts to assist by clearing for a new query, the operator must be aware of which frames that he needs to cleared. The clear is dependent upon the previous query logic and the next query logic. Pressing the clear button to clear all selections before building any new query may be the best procedure for those of us with short memories.
Creating and Running Queries The table frame is the first frame seen on the screen. On it is seen a window with the list of tables that were loaded when the load button was pressed. Select a table by double clicking on it. The selection will be copied into the table selections window. De-select it by double clicking the selection. When the query is run, CoreReader will use what it finds in the selections window. Any number of tables may be selected, and they may be selected in any order. The order in which they are placed in the selections window is the order in which CoreReader will place them in the query. Be reminded that, when a table is changed, or when the table sequence is changed, CoreReader may change data in other frames.
Creating and Running Queries After a table is selected, the columns that are wanted from it must be selected. Click on "choose columns" in the query option window to display the columns frame. All of the table's columns will be seen displayed in the list. Any number of columns may be selected for a query. Click on one to highlight it. Or drag the cursor down any of them to select the group. Or click on one, then hold down the control key while clicking on others to be more selective. Those two operations are all that are required. Just select a table and select its columns. After making selections, press the query button to run the query. There are six column windows, each of which corresponds to a selected table. Each is displayed by clicking on its numbered tab. Columns are selected from each table in its columns window. The column lists are updated if the corresponding tables change or if the table sequence changes. Don't forget to re-select columns when the table selection is changed.
Creating and Running Queries If specific records are wanted from the table, click the "choose records" in the query options window to reveal the record selection frame. In the drop-down lists on that frame will be found all of the column names of the tables that were selected. If multiple tables were selected, all of the columns will be in the list with a number prefix showing the table to which each belongs. Select a column by clicking on it. That will be the column used to identify the records that are wanted. In the field next to it, select a logical operator, such as "contains". Then enter the selection data in the text box. Up to six columns may be used to choose records. Multiple "where" selections may be used with any combination of "and" and "or". ( For advanced operators: It was decided that parenthetical delineation would make the CoreReader interface overly complex, so it was excluded. You can use the SQL screen to handle such needs. ) CoreReader watches for table changes and clears and refills the where lists accordingly, but he will not clear selections or entries. Therefore, if a table is changed, the where selections should be checked.
Creating and Running Queries CoreReader simplifies the specification of records by listing standard logical expressions in the drop-down boxes. Instead of technical jargon, they are in plain English which CoreReader translates into the SQL language to talk to systems for you. The following are the logical operators which can be selected:
For example, to find a record with the first name of johnathan or the last name of smithsonian, you might use the 'contains' keyword. When you are finished, your where clause on the CoreReader screen might look something like:
Creating and Running Queries If a sort is desired, click on "sorter" in the query options window to display the sort frame. In the sort frame are drop-down lists of all of the columns in the selected table. Select the column on which the results will be sorted by clicking on it. The query results will be sorted on the selected column in ascending order. The order may be changed to descending by clicking on the descending box. Sub-sorts may be done by selecting up to six columns using any combination of ascending and descending. When tables are changed, CoreReader will change the lists in the sort drop-downs, but he will not clear any previously made sort selections. He behaves in that manner to avoid clearing a complex query in which only a single table may have changed.
Creating and Running Queries Re-usable queries and procedures are stored in a table. The saved query frame displays the names of the items in the table. The saving and management of queries is covered later in the Query Table section. ( A stored procedure is another name for a saved query. ) When the system starts up, it loads the names of any queries that were previously stored in the query table, and the list is displayed in the stored query frame. A query is selected by clicking on one of the names in the list, and then run by pressing the query button. After a connection table and a query table are built, the system can be started and queries quickly run. When the saved query frame is not used, queries are run as discussed previously. However, if a saved query is selected in the saved query frame, and the query button is pressed, that tells the system to run the saved query regardless of other entries. If a different frame is opened, the system forgets about the saved query, expecting the operator to enter a new query.
Creating and Running Queries The SQL display frame is provided for the construction of advanced SQL queries, to teach beginners, and to assist system engineers with constructing embedded SQL statements. If any part of any query that is seen in the SQL frame is not immediately recognized and understood, then your expertise is probably at the novice level. CoreReader uses the SQL language to talk to all data sources. As a query is created by pointing and clicking on the main screens, CoreReader builds a SQL statement in the background. If the SQL frame is displayed, CoreReader will write the statement on it. If a query is run while the SQL frame is displayed, CoreReader uses only the SQL statement in the SQL frame for the query. This feature allows the construction of a query with point-and-click, and enhancement on the SQL screen to create more complex queries. If you manually alter a displayed statement, display a different frame, and then return to the SQL frame, your manual work is, of course, erased. For advanced users, a query can be run from the SQL frame without loading the database. Knowing that a connection has been made, CoreReader will assume that the operator knows what he is doing.
_________________________ When the system is configured to operate at the intermediate level, the following operations are available in addition to all of the novice level operations.
Creating and Running Queries In addition to the novice level logical where operators, the following are available in the intermediate level:
Creating and Running Queries Multiple tables that are selected on the object frame may be linked in the query. After selecting the tables, display the equal-link frame. The drop downs will list all of the columns for the selected tables with the number of each table in front of its columns. Select a column in the left box, and a column from another table in the right box to which it will be linked. Then press the query button. This will return all of the records from both tables where the value in the left column is equal to the value in the right column. Multiple tables may be joined in the same query by continuing to make selections in the remaining lists. For example, table A may be linked to table B which is linked to table C.
Creating and Running Queries The function frame allows standard SQL functions to be performed on tables.
Up to six functions may be performed simulaneously. Mixing functions is made simple by CoreReader, but such operations can sometimes overwhelm the infrastructure.
Creating and Running Queries The frames can be used together to build complex queries. For example: A table might be selected on the main frame and a couple of its columns might be used in a where clause to select records. Then, instead of selecting its columns, the function frame might be displayed to summarize quantities. At this point, I discovered that the power of CoreReader combined with the power of my dumb queries can profoundly confuse the entire system of computers that are involved. If you too achieve such notable heights of confusion, you may need to clear the connection.
_________________________ When the system is configured to operate at this level, the following operations are available in addition to those available at the other levels.
Creating and Running Queries ( Cartesian Joins is a misnomer, but it will do until I find a better term to convey the essence of the operation. I'm open to suggestions. ) To join tables, click on "cartesian join" in the query options window. On the join frame, select a base table from the drop-down list. This is usually table one, but may be any in the list. In a left column drop-down, select a field from the base table. In the corresponding right column, select a field from the table that will be joined to it. Continue the selections down the frame until all joins are complete. The linked queries default to left joins, but a different type of join may be selected from the list box for every join in the operation. !!Remember that not all data sources speak SQL as well as CoreReader, and some are not capable of performing all join types!! That is emphasized in hopes that the shortcomings of a favorite server are not ascribed to CoreReader. The construction of a join operation within the query may be indicative of the operator's intent to alter the overall logic of the query. Therefore, when a join is performed, CoreReader reads table names from the join frame, and the table frame is used only to provide data for the other frames. Up to six tables may be joined. The field names in the drop-down lists are prefixed with numbers to distinguish the various tables. The inclusion of a parenthetical delineation control mechanism would have caused the interface to become overly complex and defeat the ease-of-use for which CoreReader was designed. Therefore, CoreReader always affects parenthetical delineation from the left. At this skill level, the operator is proficient with joins, so constraints have been removed from the join frame to allow the flexibility that is required for this operation. This permits complex SQL statements, but proficiency is required.
Creating and Running Queries In addition to the novice and intermediate level logical where operators, the following is available in the proficient level:
The select logic box can be cleared of CoreReader's selections and other logic may be typed into it. CoreReader will attempt whatever is entered.
Creating and Running Queries Data sets may be grouped by selecting from the lists on the group frame. Grouping is a prime example of how a simple idea can require complex thought. Extremely complex queries with as many as six groupings may be created. The count box must be checked to show counts.
Creating and Running Queries The data browser was designed previously for the CoreModel project, where the having constraint is not used. Therefore, when a having constraint is used, output must be to something other than the data browser. The having logic box can be cleared of CoreReader's selections and other logic may be typed into it. CoreReader will attempt whatever is entered.
Creating and Running Queries Those who operate at this level are accustomed to writing extensive SQL statements using embedded logic. Therefore, fields will accept free form entry without validation. Logic can be embedded in them and the SQL frame can then be opened to edit and run the statement.
Creating and Running Queries The system can be easily configured to display of multiple result sets. Press the tools button to open the advanced tools screen, press the configuration button to open the configuration screen, change the multiple browser setting, and press the save button. Before configuring CoreReader to deliver multiple data sets, the operator should be aware that the system must manipulate multiple copies of each data set entirely in RAM. CoreReader is capable of almost any load, but the host computer is not. When using these features, the operator must take care to insure that the workstation is not overwhelmed by applications and data. To deliver multiple data sets on screen, change the configuration option that limits CoreReader to a single data browser. When this is changed, each query will load a new data browser, so that many data sets will be simultaneously displayed. It then becomes the responsibility of the operator to close each one. Additionally, the enabling of multiple data browsers also enables multiple text displays on screen. Each time that output is sent to the text file, it will be displayed on screen as a fresh display instead of replacing the previous display. The operator must manually close each text display.
_________________________ The non-technical level is provided for line personnel who's work involves the use of data sources, but who are not technically qualified to operate all of CoreReader's bells and whistles. The assumption is made when operating at the non-technical level that the system has been configured by a technician so that the operator is not concerned with data connections and other technical requirements. Usually, at this level, the auto-connect option has been enabled so that CoreReader automatically connects to a data source when it is loaded. For advanced users, the technician may have also provided some additional connections with instructions on how to use them. Also, at this level, the auto-query option may have been enabled. If so, it will cause CoreReader to immediately connect and run a specified query after it is loaded. After the auto-query finishes, then other queries may be run.
Creating and Running Queries A query, sometimes referred to as a stored procedure, is a small program which is written in a specialized language for database servers. Since CoreReader speaks that language, he can communicate queries to a database server and then bring back the results. To reduce confusion for non-technical personnel, the only frame that CoreReader displays is the query table. When the system is started up, it loads the names of the queries that are stored in the query table, and the list is displayed in the stored query frame. A query is selected by clicking on one of the names in the list. When a query is selected, the system looks it up in the table and loads it for execution. If a description is found with it in the table, the description will appear in the description box. After a query is loaded, the query button will execute it against the data source.
_________________________ This section may be entirely ignored by beginners. As soon as CoreReader loads, it is ready for operation; just start it up and connect to a database. CoreReader can produce five kinds of output that can be selected in a dropdown list box at the bottom left corner of the query screen.
When CoreReader is loaded, he creates the standard output directory under his location. All output that is written to disk will go there. To change the output targets, see the configuration section of the advanced documentation.
Creating and Running Queries The data browser is the default output type. When CoreReader is installed, a connection is made, the database is loaded, and a query is made, the results will pop up on screen in the data browser. Those who have experimented with the CoreModel system will recognize the same data browser in CoreReader. It presents a data grid which CoreReader fills with data returned in the query. The data browser's tools may be used for additional manipulation of the displayed data. It operates just as it does in the CoreModel system. Press its info button for information on advanced manipulation.
Creating and Running Queries When text output is sent to the screen, CoreReader also creates a text file in the standard output location. If the output is too large for CoreReader's display, he will tell you so you can display the file with your favorite word processor. Header and footer text may be created and stored for text reports. See the configuration section of the advanced documentation for operation. The stored headers and footers will automatically be added to each report. Header and footer lines may be up to a thousand characters or the width of the page, whichever is less.
Creating and Running Queries The disk-only option sends the output to a text file on disk without a visual display. The objective of this type of output is to allow reports to be generated by the CoreReader job controller without a human operator at the controls. Another objective is to generate data files that are suitable for consumption by other systems. Therefore, headers and footers are not added. Only the column headers are included. CoreReader analyzes the data set that is returned and formats the table into fixed length records. Disk only output has two options; fixed width and separated. If fixed width is selected, the data on every line in a column has the same width. This is accomplished by adding spaces where needed. If separated columns is selected, the columns on a line are printed contiguously and the columns are separated by a special character. ( Separated output is usually referred to as delimited but the delimited nomenclature is misleading because the columns are not delimited. They are only separated by the special character. The creator of CoreReader does not expect others to follow his insistence on correct nomenclature. ) As with the text output, the standard output location is used. That can be changed to any location. See the configuration section of the advanced documentation for instructions.
Creating and Running Queries When output is sent to a web page, CoreReader generates standard HTML in a complete web page which can immediately be loaded into a web browser by clicking on it. However, the operator needs no web experience or HTML knowledge. CoreReader can be used to quickly create the web page which can be handed off to a web site administrator. The entire query output will be formatted into a web page with the column headings. The web page will be named by the entry in the report name parameter. If headers and footers are entered into the CoreReader configuration, they will appear centered on the page. Provision is made for integrating the web page output into an operational web site. Linking into the site is done through the "web return page" configuration parameter. Usually, this parameter is the name of the web page which will be loading the CoreReader web page. The CoreReader page will carry a link back to that page. If a header file is specified in the settings, it will be included in the web page. It must assume responsibility for the html, header, and body tags in the page. If a footer file is specified, it must assume responsibility for closing the body and html.
Creating and Running Queries The operator does not need to know anything about XML to use this feature. He simply creates a query as usual, and selects the XML output type. CoreReader generates a machine-readable schema-based XML wrapped data output. He automatically constructs the appropriate schema before beginning the XML generation. All constructs are dynamically generated by CoreReader without operator intervention to permit immediate remote system consumption. CoreReader dynamically creates the XML structure for each query by first automatically analyzing the data structure that he built for the SQL return. The returned data structure can be of any complexity since CoreReader will handle all of the complexity. The operator needs to know nothing of the operation except how to tell CoreReader what he wants. The XML will be delivered to the appropriate files in the default directory. Change the output directory in the configuration screen.
Creating and Running Queries CoreReader will create standard SOAP output on demand. This is most useful for developers debugging server installations.
__________________________________________________ Chapter Data Source Specifics __________________________________________________ If one of your data sources is covered in this section, it is recommended reading because people are frequently not aware of the shortcomings and eccentricities of their favorite data source. When a data source is missing from this section, it means only that nobody has taken the time to send a verifiable 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 Codd's foundation work.
_________________________ IBM has refused to cooperate with the CoreReader project, demanding payment for information, so CoreReader's example connection comes from Microsoft. When I worked with an AS400 ten years ago, DB2 was hidden inside it.
_________________________ AxleBase documentation is published at http://www.AxleBase.com/ CoreReader has its own embedded database manager. In addition to that, AxleBase must be installed on the system for its connections to work. Two parameters are needed for an AxleBase connection, the domain and the database. Other parameters are optional. 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.
_________________________ Refer to the InterBase notes. The data socket that was reportedly used to connect CoreReader to Firebird version 1 was the XTG driver (beta 16). 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. Maybe it's just 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:
_________________________ 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.
_________________________ The architecture of the HP3K allows you to connect to the server without opening a database. Therefore, it is possible for CoreReader to make a good HP3K connection and be unable to load a database. This may happen, for example, if a database password is wrong. This is the same interesting response that Paradox exhibits. A benefit of the HP3K architecture is that it allows CoreReader to work in multiple databases simultaneously.
_________________________ Please refer to the notes under the HP3000.
_________________________ 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.
_________________________ After reviewing my very old Delphi code, it appears 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 ). 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. )
_________________________ 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 earlier version of the data socket just plain did not work. This is the Microsoft method of marketing which withholds the limitations to lead one to invest in a product before knowing the facts. It wasted a lot of my time. 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 name requirements. 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. MySql is developing quickly, so feedback on problems encountered and problems cleaned up would be appreciated. This is a volatile industry where companies and products rise and die overnight, so these assessments can change quickly, but at the moment, MySql shows promise.
_________________________ This is recommended reading if you are new to Oracle ! Oracle is a great product for any large company that has a dedicated DBA staff, but it has some peculiarities. 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 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 preturn 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 allows only one. If multiple passwords are needed, a connection string or DSN may be needed. 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 will allow the following defaults to be overridden by a connection string.
_________________________ CoreReader has queried the S/390. Please see the notes under the mainframe section.
_________________________ 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 the table names.
_________________________ 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. PostgreSQL may be expected to present no problems. I will personally test it when I have time to bring another Linux box on line.
__________________________________________________ Chapter Errors __________________________________________________ If you are new to database work, prepare yourself for lots of error messages and frustrations in your work day.
_________________________ Level 1: Operator. The most common error is simply a result of incorrect syntax or erroneous parameters. The arcane complexity of running queries and making database connections precipitates errors. NOTE: Do not allow this group of errors to be a source of concern because CoreReader will not allow operator errors to harm anything other than your ego. Feel free to experiment. Level 2: Systemic. CoreReader's appearance of simplicity is superficial. Database activity is one of the most complex areas of computer operations, involving many complex interacting sub-systems that are known only to system engineers. This type of activity even stresses operating systems. Database activity has been known to overwhelm the infrastructure simply due to its volume and complexity. If errors begin to occur and they are definitly not level 1, they may be of a systemic nature. Address them first by refreshing the data connection. This sometimes clears software components that may have become hung, corrupted, or clogged on the local computer, the server computer, or systems in between. If that fails, try rebooting the computer to clear it. If those efforts fail, you may need to wait for the database server or intervening servers to be cleared. In some organizations, servers are automatically cleared once a day by server software. Some systemic failures require the attention of a system administrator or a database administrator. Level 3: Linked Software. The third group of errors comes from the many specialized sub-systems that are external to CoreReader but which must work with CoreReader. During testing, one of the data socket sub-systems was observed to fail repeatedly while the operating system continually reported it as a CoreReader fault. Although CoreReader operates those sub-systems, they are outside of CoreReader's control, so pinpointing the cause of such aberrant behavior is difficult. Before reporting a bug in CoreReader, be sure that the source of the problem is not in your driver, provider, server, etc. Error messages from other systems may be ambiguous, misleading, or just plain dumb. CoreReader attempts to translate but cannot catch everything. Some examples:
Level 4: CoreReader Internal. These are bugs within the CoreReader system. Finally, if you keep twenty or thirty connections on file, switching between servers and databases, running complex queries, be prepared for crashes. I have never built a system that was so prone to crashes, but I also have never built a system that interfaced with so many other systems for such powerful actions. The source of the problem may be beyond my control in the connectivity apparatus, but I continue to work on it.
_________________________ I work alone on this project. If you report a bug to me, it may take me some time to fix it. However, when somebody reports a problem, I take it as a personal communication, and will do all that I can to correct it for that person. When sending a bug report, the more details included in the report, the better. Also, please attach a copy of the verbose activity log surrounding the events, and copies of the system files and libraries. A friend tells me that CoreReader crashed whenever it displays an error message from an external system. That's rather funny, but please know the difference between the two. Finally, consider that CoreReader is designed to query any server on any computer in the world. Sometimes, I can handle problems that are external to CoreReader, so if you report a serious problem with an external system, I may consider addressing it. But be sure that you do not report such problems as bugs in my baby. |
|
Technology and web site
|
|
|
Web site is maintained with Notepad.
|
|