RonB
Posts: 228
|
Posted: 01/31/2004, 2:02 AM |
|
It would be very nice to be able to use multiple connections in one query. Example:
db1 has a table users (let's say an oracle database),db2 has a table articles(let's say a mysql database). I now have to do an event code to join the two tables in a grid.
It would be very cool if I could use the query builder to do this. Something like:
select db1.users.user_id, db2.articles.* from db1.users,db2.articles where db1.users.user_id=db2.articles.article_by
This would save me from having to do an event to get these values together from two (or more) databases.
Our comapny has 4 main databases:
-Oracle ->client registration software
-Oracle -> human resource software
-Mysql -> all intranet stuff
-SQL server-> network adnministration (net logins etc.)
I often have to combine data from all the databases to generate management info.
Ron
|
|
|
peterr
Posts: 5971
|
Posted: 01/31/2004, 12:52 PM |
|
Ron,
Sorry, I don't understand this request. A query is a database query that is sent to the database and the database returns results. Which of the databases is supposed to process such queries? And which database actually has capabilities to retirieve data from other databases?
Using multiple connections is not possible because an SQL query is just a text string/command send to a database. We have to use one connection to send it to a database since programs don't understand SQL (and shouldn't).
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
RonB
Posts: 228
|
Posted: 01/31/2004, 3:24 PM |
|
I'm not suggesting this should be done in pure sql because, as you stated correctly, this can't be done. One might invisage an extension to the querybuilder that would combine sql and code(php,asp,java etc) to do what I described. Maybe it's not a query builder issue but a multi connection grid wizard issue (just thinking out loud here). The point is that multiple connections with the ability to combine data from different database flavours is exactly what makes CCS such an excelent tool for what I do at work. As I said I frequently have to combine data from two differen oracle databases with data from an sql server database and a mysql database to provide management info. I just have this feeling that this could be done more efficiently then handcoding a before show event for each label.
|
|
|
RonB
Posts: 228
|
Posted: 01/31/2004, 3:28 PM |
|
By the way I do believe oracle has the option to use tables from a different database (as long as it is another oracle database) And I think it's possible to something simular in acces. It's not in straight sql but as I stated above that was not what I meant to imply
|
|
|
peterr
Posts: 5971
|
Posted: 02/02/2004, 1:12 PM |
|
Sorry, I believe that there is nothing we can do in CCS about this. It is just not feasible and could take more time than anything else we've done. And we don't really see much need for this since various databases already support it. You should be able to use just one connection in CCS but in your database create links to various other databases. For example I have an MS Access database with links to MySQL and this works fine. I could also create such links to MS SQL, Oracle and other databases, then probably combine them in single queries (haven't tried but logically should work).
MS SQL has similar functionality: http://msdn.microsoft.com/library/default.asp?url=/libr...p_adda_8gqa.asp
In other words, unless I don't understand something correctly, this feature already exists but this is a database feature, not CCS feature
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
RonB
Posts: 228
|
Posted: 02/02/2004, 1:54 PM |
|
Being an open source minded person I'm not realy into high priced solutions like ms sql. Acces is nice but There are 400+ users on our companies intranet. Not something you'd like to expose acces to.
I understand your argument that it would mean a lot of work for something like this to implement so I understand the reasoning.
CCS is offering, for us as it's main selling point, the option to connect to several different databases with a project and even within a grid. To us being able to do this from a wizzard looked like the next logical step.
We like to keep things central so we'll keep coding events to combine data as we are paying enough to Oracle and MS as it is
|
|
|
|