Database Agent Tutorial

NOTE: We assume that the reader is familiar with setting up and using OAA and further has a functioning OAA installation. For information on installing and using OAA, see http://www.ai.sri.com/~oaa/. Working knowledge of PostgreSQL, MySQL, or Oracle and SQL is also assumed.

1. Before runningthe Database Agent, you must make sure a facilitator is running and that you have a setup.pl file that points to this facilitator installed at the correct location for your platform.



2. Change into the dist directory of the DBAgent distribution.



3. Modify the file db_agent.properties to reflect the settings for the database database you will be using. The examples that follow were based on a PostgreSQL database but users should be able to re-create similar results using any relational database for which JDBC drivers are available.



4. Start the DBAgent. On Windows, run runDBAgent.bat -oaa_listen "tcp(localhost,3331)" On Linux, run runDBAgent.sh -oaa_listen "tcp(localhost,3331)" Note: The OAA comand line parameter -oaa_listen "tcp(localhost,3331)" is used to allow the DBAgent to handled requests using the "Direct Connect" mechanism of OAA version 2.2 and later. If you omit this parameter, these tests will still work, but you will see slightly different informaion logged to standard output. Also, the port number "3331" is not "special" in anyway and you can specify any available port number.

   As the agent starts up, something like the following will be reported to standard output:

   INFO - LOG START: Thu Sep 26 10:53:58 PDT 2002 INFO - Connecting to the database.... INFO - JDBC Driver: org.postgresql.Driver INFO - JDBC URL: jdbc:postgresql://host:post/dbname INFO - Successful connection to database. INFO - OAA2 Java library Release : 2.2 INFO - BioSPICE_DBAgent Connecting INFO - Listening at tcp('www.xxx.yyy.zzz',3331) INFO - Connected to tcp(Host,Port) INFO - Connection Made INFO - Registering BioSPICE_DBAgent INFO - Registering agent listener: agent_listener(addr(tcp('www.xxx.yyy.zzz',vvvv),2),addr(tcp(... INFO - Registering agent listener host: agent_listener(name('BioSPICE_DBAgent'),addr(tcp(... INFO - Starting BioSPICE_DBAgent INFO - Ready.
5. To run the following examples, you must create the database tables "empty_table" and "simple_table" using the SQL file build_dbagent_test_tables.sql. (Note: This file has been tested with Oracle and PostgreSQL; here is a version that works with MySQL.)


6. Enter oaa_Solve(executeQuery('select * from simple_table', ResultSetID, Status), []) into the Icl Request box and submit the request by clicking on the button. Figure 1. shows the expected results of this query.
   
   Figure 1
   
   IMPORTANT  You might not get the same value for ResultSetID since this value is auto-generated by the DBAgent and depends how many previous requests the agent has handled.



7. Now, get the first row from this result set by entering this solve request: oaa_Solve(next('BioSPICE:RSID:1', Row, Status), []) Be sure to use the result set ID returned to you in Step 6 above -- it may be different from the 'BioSPICE:RSID:1' value we are using in this example. Figure 2 presents the expected results of this query.
   
   Figure 2
   
   

   Note that the ICL variable Row now has the value of the IclList

   ['1','Abe','1991-01-01'] and the Status variable has the value 0, for "good" status.




8. Finally, get all the remaining rows from this result set by entering this solve request:

   oaa_Solve(all('BioSPICE:RSID:1', AllRemainingRows, Status), []) Be sure to use the result set ID you got from Step 6 above -- it may be different from the 'BioSPICE:RSID:1' value we are using in this example. Figure 3 shows the expected result of this query.
   
   Figure 3
   
   


9. Note: The all solvable actually returns all the remaining rows from the result set. Also, since all will reach the end of the table, the result set is exhausted and the result set ID is no longer valid for use in future solve requests. (If you need to get at the data again, just repeat the appropriate query.) To see that this is indeed the case, repeat the exact all request from above; you should see:
   
   Figure 4
   
   Note that returned status value in Figure 4 is non-zero (1) indicating a failure. More information about this failure can be entering a lastException solve request. oaa_Solve(lastException('BioSPICE:RSID:1', ExceptionMessage, Status), []) As shown in Figure 5, the result set ID 'BioSPICE:RSID:1' was not found. The DBAgent in fact destroyed it after completing the first all request above. Result sets are always destroyed when the end of the table is reached.
   
   Figure 5
   
   



10. Optional  Experiment with your own queries and explore the other solvables such as nextN, columnNames, etc. See the DBAgent API documentation for a summary of solvables.



11. Optional  If you've made it this far, you have proved that the DBAgent is installed and working correctly. If you are interested in further testing, try running the SimpleDBClient program. Using runSimpleDBClient.bat (Windows) or runSimpleDBClient.sh (Linux).

    Try these tests from the within the dist directory.

    $ runSimpleDBClient.sh "select * from simple_table" Compare the results you see reported on standard output to the results in simple_table_results.txt. Again, your results will not be identical because of differences in result set ID values, machine names and port numbers, but they should otherwise agree.

    Try

    $ runSimpleDBClient.sh "select * from empty_table" and compare your results to empty_table_results.txt.
    
    

    Try querying a non-existant table...

    $ runSimpleDBClient.sh "select * from missing_table" and compare your results to missing_table_results.txt.



12. For more information on the DBAgent and SimpleDBClient program see the API documentation and the source code.