Re: [dba-users] How does one access two databases at the same time?

[Andrew Jensen]

SOTL wrote: On Saturday 11 March 2006 04:30 pm, Mechtilde Stehmann wrote: Hello, SOTL schrieb: Hi All I hope I am not asking a question that is obvious. I did check "How-Tos" and did look in the instructions but I did not see the answer. I have two test data bases: Test_Database-1 and Test_Database-2. Test_Database-1 has COMPANY-NAME, ADDRESS, CITY, STATE, ZIP for a small test set. Test_Database-2 has COMPANY-NAME, DATA-LIST-1, DATA-LIST-2 for some of the companies in Test_Database-1 but not all companies. I would like to sort select companies from Test_Database-2 and form a table of only those companies data and address including city and state into a table which may in futire test be used in some form of mail or email merge. True, Base does not support linked tables from a secondary datasource insideof a database file. OO.o however does allow one to work with multiple databases at a time. One example of this can be shown using your two test databases. If you open a writer document and bring up the control toolbars you can add a dataform control and then field controls for a single record from Test_Database-1 to this document. Now using the forms navigator add a child dataform control to the first and attach it to Test_Database-2. You can link these two dataform controls to each other via the COMPANY-NAME field in each. (You must recognize that there is no way for OO.o to insure that this relationship is valid, but so long as the data type of the fields match it will be accepted). Now finish up by adding a table grid to the form, belonging to this second dataform control. Run the form and you will see that indeed data will be presented to you from both databases. Now, you cannot duplicate the above form with an embedded form from any one of the Base files, as in this case the dataform controls must all reside in the same database. This could be considered a short coming, then again it rather makes sense as the form is now aggregating data from multiple source so perhaps should be seperate from any one of them. Another limitation is that you can not build a select statement joining tables from multiple databases. However, in the same vein as the form example you can indeed use selection criteria from one database to pull data from a second. In this case one would create multiple connection objects using your favorite scripting language. The simplest case would be to duplicate the work done by the above form. You could create a prepared statement that selects records from Test_Database-2 and uses a parameter for the key. The value for this parameter could be read from a connection to Test_database-2. This is interesting but not much more useful then the form. You could extend this however, instead of a simple select statement you could use an insert statement, or a select into statement. In this scenario the routine could be written to loop thru any number or identifying records in the first database and move data from the one into the other on an as needed bases, make the table that it is inserted into a TEMP table and it will always be so. Once the routine is finished you can use this TEMP table to do whatever data mining functions your require. The bottom line is that the lack of linked tables, while an inconvienence in some instances, does not mean one can not use OO.o  ( or Base more specifically ) as an aggregator of information from multiple datasource. The major problem in this type of system is one of relating data from the one database to the other, and for this even if linked tables are supported the problem is not alleviated. It will always be the job of the developer and business analyst to insure that this is done properly. The issue is I have no idea of how one opens both Test_Database-1 and Test_Database-2 so that one may select certain fields but not all fields into a table. I can open Test_Database-1 and Test_Database-2 in two windows but what I am speaking of would be into one window. Any help would be appreciated. Why do you use two databases and not two tables of one database? Mechtilde The current set up is a test set up so what you suggest is doable of course but being a test set up it is testing for some thing that is not doable. Look at it this way, you would like to connect to a commercial database where some firm or company inputs thousand and thousand of inputs in a hundred or so fields most of which is useless to you but that is the way the data is placed in the commercial database so what you need to do is connect and access only the fields you need. Now let this be data that changes daily, weekly, monthly et. For example daily sales data or inventory data or parts identification or stock price data. NOTE: In ALL of this data you have NO WAY TO INPUT DATA only access the data.This is an example of the type of data that one would in a real world setting be used place of Test_Database-2. Now on the other hand you have data such as client contacts et that have contact name and phone numbers et that is particular to your needs. This is data that you CAN and DO INPUT and that you CAN and DO MODIFY to suit your needs. This is an example of Test_Database-01. Now futher lets assume that Test_Database-2 is located on a server and is something like MySQL, PostgressSQL et. Then one is going to have to use a connector like JDBC or ODBC to access this data. Assuming that Test-Database-2 is located on a server then it can be you and it are on a local network and local server in which case the test I ran last week with Samba performing connections between the 5 different boxes A, B, C, D, and E become applicable and provide the needed connections. There is another possibility and that is that the server data is not stored on a local network but is on a web server. Then at that level you would be connecting to commercial data say supplied by the likes of Dow Jones [bad example as they are not agreeable people but you get the idea of the type of data] and personal data. One could take this to an even more logical conclusion and have commercial data [say from the likes of Dow Jones] from the web, corporate data from the local network and personal data from your own computer. You see with out the ability to connect to a number of different databases then OpenOffice database is quite useless because the data comes from a number of different sources. SOTL --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] In your last post I believe you are creating a bit of straw man. Base, or for that matter the database component of pretty any office suite you pick will not be the solution best suited to every data mining application. More often then not building the type of system you describe would best be done utilizing tools from a number of vendors. At a minimum it will require the creation of custom code to perform the data linking, extraction and aggregation - no matter whose tool you use. Drew Jensen