Database Queries

Accessing External Data

samples/basic/tutorial/products_2.kb and products.mdb

Some knowledgebases need to reason over data that is stored in an external database. To do this under Windows, KnowledgeWright provides sql objects. These objects allow you to issue SQL queries to any ODBC accessible database.

In this example we will make a second version of products_1.kb that uses an external ODBC database instead of an internal data_table.

To illustrate this, we will query a database to determine the upgrade price for a product. Before you can start writing the knowledgebase, you need to register products.mdb as an ODBC data source:

1. Open your control panel and double-click on the ODBC icon.
2. Click on the System DSN tab.
3. Click on 'Add'.
4. Highlight Microsoft Access Driver, then click on 'Finish'.
5. Enter 'products' into the Data Source Name.
6. Click on 'Select' to find the products.mdb file in knowledgewright4/samples/basic/tutorial.
7. Click OK then OK again.

Now to build the knowledgebase. Our knowledgebase asks three questions, old_product, old_version and new_product. Using the answers to those questions, it issues a SQL query to get the price. The price is displayed in a short document.

1. Create a new knowledgebase named products.
2. Set the goal in the main knowledgebase object to upgrade_price.
3. Set the odbc data source in the main knowledgebase to products.
4. Create the three questions:
• A menu_single_choice_display_separate question named old_product, that prompts the user for the last product purchased. The choices are:
  

  rule_text	display_text
  ap1pc	Amzi! Prolog + Logic Server, Personal Edition
  apxpc	Amzi! Prolog + Logic Server, Professional Edition or Commercial License

• A menu_single_choice question named old_version that prompts the user for the last version number purchased. The choices are 4.0, 4.1, and 5.0.
• A menu_single_choice_display_separate question named new_product, that prompts the user for the product to upgrade to. The choices are the same as old_product (above).
5. Create a text object named upgrade_price with text that reads:
   The price for this upgrade is %price%.
6. Now to create the sql object. The SQL query must be correctly formatted for ODBC, so note carefully the use of quotes ('). This query retrieves the price which is a floating point number (because it has a decimal point) from the table named upgrades in the ODBC data source name products (which is also the name of the Access database).
   
   

   id	price
   path	/
   query	select price from upgrades where old_product = '%old_product%' and version = '%old_version%' and new_product = '%new_product%'
   type	float
   length	20

7. Run the knowledgebase. If ODBC is not set-up correctly you will get an error message indicating this. Otherwise, you should get the upgrade price based on the answers to the questions.

If you need to select multiple values from the same table, you will need to create one sql object for each one.