How can I handle parameterized queries in Drupal?

Question

We have a client who is currently using Lotus Notes/Domino as their content management system and web server. For many reasons, we are recommending they sunset their Notes/Domino implementation and transition onto a more modern platform--such as Drupal.

The client has several web applications which would be a natural fit for Drupal. However, I am unsure of the best way to implement one of the web applications in Drupal. I am running into a knowledge barrier and wondered if any of you could fill in the gaps.

Situation

The client has a Lotus Domino application which serves as a front-end for querying a large DB2 data store and returning a result set (generally in table form) to a user via the web. The web application provides access to approximately 100 pre-defined queries--50 of which are public and 50 of which are secured. Most of the queries accept some set of user selected parameters as input. The output of the queries is typically returned to users in a list (table) format. A limited number of result sets allow drill-down through the HTML table into detail records.

The query parameters often involve database queries themselves. For example, a single query may pull a list of company divisions into a drop-down. Once a division is selected, second drop-down with the departments from that division is populated--but perhaps only departments which meet some special criteria--such as those having taken a loss within a specific time frame. Most queries have 2-4 parameters with the average probably being 3.

The application involves no data entry. None of the back-end data is ever modified by the web application. All access is purely based around querying data and viewing results.

The queries change relatively infrequently, and the current system has been in place for approximately 10 years. There may be 10-20 query additions, modifications, or other changes in a given year. The client simply desires to change the presentation platform but absolutely does not want to re-do the 100 database queries.

Once the project is implemented, the client wants their staff to take over and manage future changes. The client's staff have no background in Drupal or PHP but are somewhat willing to learn as necessary.

How would you transition this into Drupal? My major knowledge void relates to how we would manage the query parameters and access the queries themselves. Here are a few specific questions but feel free to chime in on any issue related to this implementation.

1. Would we have to build 100 forms by hand--with each form containing the parameters for a given query? If so, how would we do this?
2. Approximately how long would it take to build/configure each of these forms?
3. Is there a better way than manually building 100 forms? (I understand using CCK to enter data into custom content types but since we aren't adding any nodes, I am a little stuck as to how this might work.)
4. Would it be possible for the internal staff to learn to create these query parameter forms--even if they are unfamiliar with Drupal today? Would they be required to do any PHP programming?
5. How would we take the query parameters from a form and execute a query against DB2? Would this require a custom module? If so, would it require one module total or one module per query? (Note: There is apparently a DB2 driver available for Drupal. See http://groups.drupal.org/node/5511.)

Note: I am not looking for CMS recommendations other than Drupal as Drupal nicely fits all of the client's other requirements, and I hope to help them standardize on a single platform.

Any assistance you can provide would be helpful. Thank you in advance for your help!

Answer 1

A couple things come to mind. You can use Table Wizard to expose any custom tables to views. Views basically gives you a UI for writing custom sql queries. Once your tables are exposed to views you can use filters to "parameterize" the query. Also, views supports many display options including tables and pagers.

If you do need to write your own forms for advanced queries, take a look at the Drupal Form API which makes creating custom php forms a peice of cake.

Views is fairly easy to learn and, in my experience, most clients pick it up quickly. It really depends on the complexity of the query.

Form API should be easy for a developer to pick up, but does require a basic knowledge of php and writing Drupal code.

Answer 2

Does Lotus provide a web service to query it? If so, you can easily do this using a custom form (in your own module) and use Services module to query the data.