hbycer
Posts: 24
|
| Posted: 05/24/2004, 1:16 PM |
|
Using linked tables in MS Access, we are able to join a table from an Oracle database to a table in a SQL Server database in the same query using the MS Access query builder. With the CodeCharge Studio query builder, we do not have this luxuary even though we can define concurrent database connections.
Boy would this be great!!!!
Howard
|
 |
 |
peterr
Posts: 5971
|
| Posted: 05/24/2004, 6:16 PM |
|
Hi,
Let's say that this would be implemented in CCS. But what then?
CCS generates Web applications but I think that Web applications cannot execute one query against multiple databases, therefore CCS cannot generate such applications.
Possibly I'm missing something. How would you accesses multiple databases with a single query from a Web application? Any ASP or PHP code + SQL sample?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 05/24/2004, 6:18 PM |
|
BTW, if you use MS Access or other database that can link to multiple databaeses then you can use that database within CCS and should see the linked tables there.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
RonB
Posts: 228
|
| Posted: 06/08/2004, 1:53 PM |
|
Peter, codecharge already has this functionality but you have to hand code it in events.
let's say a grid contains three fields: message, date, emp_nr
it stores messages, the date and the employee number of the employee that wrote the message.
To link the emp_nr to an employee name stored in oracle with the message and date stored in mysql I use the befrore show event to make a trip to oracle for each row to get the employee name and put it in the grid:
$db2=new clsDBoracle();
$gridname->emp_nr->setvalue(CCGetDBValue(" select name from personell where pers_id is " .$gridname->emp_nr->Value ,$db2);
unset($db2);
It would be realy cool if the query wizzard were able to take care of this. The result would not be a singele query but several queries and php (my favourite language)
|
|
|
|
RonB
Posts: 228
|
| Posted: 06/08/2004, 1:54 PM |
|
$db2=new clsDBoracle();
$gridname->emp_nr->setvalue(CCGetDBValue(" select name from personell where pers_id is " .$gridname->emp_nr->Value ,$db2);
unset($db2);
should be:
$db2=new clsDBoracle();
$gridname->emp_nr->setvalue(CCGetDBValue(" select name from personell where pers_id is " .$gridname->emp_nr->Value ,$db2));
unset($db2);
|
|
|
|
peterr
Posts: 5971
|
| Posted: 06/08/2004, 10:54 PM |
|
Ron,
This is little different from joining multi-database tables "in the same query" as Howard asked.
Unfortunately your approach would require creating the whole SQL engine inside CCS, which is not feasible. Only an SQL engine could interpret an SQL statement and split it into multiple statement needed to connect to multiple databases and combine the data. This is rather the job of databases and that's why using a databases is the right approach. Otherwise we would need to create MySQL-like code (as an example) inside CCS to interpret the SQL and then process each part of the SQL accordingly, run and validate such queries within CCS and then produce appropriate SQL statements for the generated code. I'm not aware of any products that do this except databases themselves and maybe ODBC drivers, which is not something we want to get involved with.
Your solution is great as a manual inclusion into events and it is the standard way to retrieve additional values from another database or any other source. We believe that automating this approach would not be justified considering the large amount of work involved to achieve something that can already be handled manually and is rarely needed. There are also existing solutions, such as Howard pointed (via MS Access) therefore this would be only a convenience feature, while there are still a lot of things that CCS users desperately need and you will see those in version 3.0 
My response here is based on just an initial feedback I received from our team but if any realistic, potential solution comes up I may post again.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
RonB
Posts: 228
|
| Posted: 06/12/2004, 10:48 AM |
|
Hi Peter,
We "talked" about htis in an earlier post as well and i understand the need to weigh the amount of work against the need for a feature.
I'm just a bit confused about what you said:
Quote :"to achieve something that can already be handled manually and is rarely needed."
I take it there was a good reason to include the ability to create many connections in one project. You even find it important enough to remove it in the personall edition of codecharge where you can only use one connection per project.
What was the mindset of the development team when they added this feature? What did you think this was going to be used for?
Don't get me wrong, for me it was the main selling point and we use this feature a lot on our companies intranet where we publish management information that often combines data from several databases.
I think Yes should think bigger. There are other solutions out there but none that have the scalability and ease of use CCS could offer. Webapplications seem to be the thing of the future and many deveopers do not want or need a tool like visual studio. There is very little like CCS on the market and I think you underestimate the usability of CCS in a corporate enviroment. I know my boss is very happy with CCS since it produces webapps at a speed my visual studio collegue could only wish for. We try and keep things in the open source domain and the solutions you suggest are not. As for access, it's nice but I wouldn't recommend connecting to an intranet driven by access with 350+ users.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 06/14/2004, 1:24 AM |
|
Ron,
Multiple database connections can be used to connect to different databases, however this doesn't mean executing cross-database queries. A cross-database query could and should be executed via a single database connection if the database supports such feature.
The purpose of allowing multiple database connections was to provide more flexibility and ability for example to utilize different databases within different pages or forms. There are other uses possible via manual programming and it is more convenient to have several connection classes available.
I think though that this discussion might've started off a wrong foot. Basically this request probably will never be implemented in CCS because it is rare that someone manually joins the results of two queries and especially should not be needed in enterprise environment. Possibly due to some logical issues it may be difficult to explain this properly but since you brought up a good point about the corporate/enterprise environment, let me try explaining this again from that angle:
We're focused on making progress with professional, enterprise features, rather than trying to find workarounds for shortcomings of some of the databases. Therefore I'd recommend upgrading to an enterprise database rather than relying on programming code to achieve something that the database should be doing.
For example if a database doesn't offer the LIMIT/TOP feature then we also will not create a programming code that tries to artificially go around the database and use non-SQL programming code to access the database files and achieve such functionality that way. Such "features" would require us to move backwards in time before SQL was created and try to implement own database engine in PHP instead of utilizing SQL to retrieve data. In the same way the lack of cross-database queries in your database should not be patched by writing more programming code but resolved by your database vendor. Doing this programmatically would be much less efficient than the database engine can and should do, and therefore not applicable in some corporate environments.
The SQL was invented and is available so that the database engine can process the database commands instead of the programming language. If your database doesn't support cross-database queries or ceratin other features then doesn't this mean that it is not good enough for corporate environment? It seems that even MS Access is better from this point of view. I actually don't know if MS Access would not perform well if used only for linking to other databases, however various enterprise databasess can especially accomplish this.
If there are databases that do not offer such features then possibly their vendors have their own reasons, and my guess is that possibly they are the ones who think that this is rarely needed. You could also contact your database vendor and ask them what was their mindset when creating their database engine without such a feature and even cite MS Access as an example
I'm hoping that I didn't come across too strong, but after talking to our engineers I can see that there is almost no chance of implementing this in CCS because of our professional mindset and enterprise focus.
Look, we sure would like to satisfy everyone but it is much more fun to leave certain features to our competitors hoping that they tie their hands fixing things that aren't broken
Other visual tools for desktop app development like MS Access, Oracle, Delphi, etc. also cannot create applications that execute cross-database queries, unless the database engine itself takes care of this. Thus an application may internally utilize one cross-database SQL statement and if this works thanks to the database engine then you're OK, otherwise you'd have to use manual code as a workaround but at the expense of the performance. Various workarounds and tricks are occasionally needed in any enterprise, therefore doing it this way should not be a problem if the database can't do it for some reason.
On another hand, there could be an issue if you are already using a database that can execute cross-database queries but they don't work for you in CCS. If this is the case then we could take a look into such issues and see what needs to be done to make it work.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
RonB
Posts: 228
|
| Posted: 06/14/2004, 2:37 AM |
|
I understand. We are currently looking in to setting up a datawarehouse enviroment that would sove this problem for us. Probably using the database link features from oracle.
Thanks for your time.
|
|
|
|
marcwolf
Posts: 361
|
| Posted: 06/16/2004, 7:51 PM |
|
I agree with PeterR.
This can be a very difficult things unless you write an entire DB/SQL processor in CCS.
For other solutions we have writen VB DLL's and passed recordsets etc back the the ASP side as the main way of joining data from many sources.
Althoug MS-Access is an excellent 'glue' for a lot of this - it must be remebered that the Jet engine is inherantly non high processing.
Take Care
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
RonB
Posts: 228
|
| Posted: 06/17/2004, 10:04 AM |
|
re: dave
I wasn't talking about CCS handling the database stuf. I was thinking more along the lines of getting the query wizzard to automatically generate an action that does a CCGetDBvalue() for the field comming from another database. It's more of a gui thing then a warehouse feature I was hoping for.
That is what I'm doing manualy now. I understand the reasoning Peter gives so we are now setting up a datawarehouse enviroment with oracle that will handle all the different connections. Once we got this up and running we will only have one connection to that oracle server wich will handle the translation to the rest of the databases (MySQL, SQL server and two other oracle databases)
|
|
|
|
|
