How to Query ClearQuest database using SQL

The place I work at uses IBM's ClearQuest for defect tracking. Since right now I'm working on automating our software, I'm constantly trying to find new ways of minimizing user interaction before, during, and after script execution. My latest side project was writing some functions that will interact with ClearQuest's database, which is Oracle, and pull out defect numbers.

Before we begin, there are couple of things you will need:
1) InstantClient with SQL+ (get it from here)
2) tnsnames.ora file (ask your ClearQuest administrator to provide you with one.)
3) Table structure (ask your ClearQuest administrator). This is used to construct your queries.

After you got all the pieces, let's begin!

Install Instant Client

To install InstantClient follow these simple steps:
1) Unzip InstantClient to C:\Oracle. If you've downloaded InstantClient 10.2, you will get the following folder created: C:\Oracle\instantclient_10_2
2) Create a new folder with the following path: C:\Oracle\instantclient_10_2\NETWORK\ADMIN
3) Place your tnsnames.ora file in folder from step 2
4) Execute odbc_install.exe, which is located in C:\Oracle\isntantclient_10_2
5) Modify your Path variable by adding ;C:\Oracle\instantclient_10_2 at the end
6) Create a new USER VARIABLE with name TNS_ADMIN and set its value to C:\Oracle\instantclient_10_2\NETWORK\ADMIN

Configure ODBC Data Source

Now that you've installed Instant Client, you're half way there. All you need to do is create an ODBC connection. This can be accomplished by following these instructions:
1) Open Data Sources (ODBC) by going to Start | Settings | Control Panel | Administrative Tools | Data Sources (ODBC)
2) You will end up with a window that looks like this: Data Sources window
3) Click Add button and select Oracle in instantclient10_2 like shown in the following image: Connection Driver selection
4) Finally fill out required information in a dialog that looks like this: Instant Client configutation
5) Don't forget to test your connection and if all is well, click OK to exit out of the driver configuration.
6) Close Data Sources window and you are ready to use your new ODBC connection.

Test your connection using SQL+

If you downloaded Instant client with SQL+ support, you could test both your connection and some queries by following following steps:
1) I usually like to start my SQL+ client by running a command like this:
sqlplus -L username@connection_id[/password]
In the command above, flags used are:

  • -L is to prevent sql+ from retrying to connect in case of a wrong password
  • connection_id Name of the connection. Can be found on the first line of the tnsnames.ora file
  • [/password] Password used to connect.
    NOTE: If you will specify it in the command line, make sure that there is NO space between connection_id and '/'!
  • 2) Type in your query to search for a defect. Here are some sample queries from our database. Yours will probably be different!

    -- search by defect id
    SELECT field1, field2 from DBName.DefectsTable WHERE id='defect number';
    -- search by defect title
    SELECT field1, field2 from DBName.DefectsTable WHERE HEADLINE='defect title';
    -- Search by contents of description:
    SELECT field1, field2 from DBName.DefectsTable WHERE Dbms_Lob.Instr(description, 'Search String') > 0;
    -- Search by state and get state id from a different table
    SELECT field1, field2
      from DBName.DefectsTable d, DBName.StatesTable s
      where AND
      d.headline like 'some search';