Running SQL Queries in DataShopper

Document ID : KB000055826
Last Modified Date : 14/02/2018
Show Technical Document Details

The DataShopper application allows users to run SQL SELECT statements from their browser. For example, a business user group would like to view the contents of the Authors table in the SQL Server Pubs database. One way to implement this in the repository is to allow DataShopper users to launch a "SELECT * FROM AUTHORS" SQL statement from their browser. The result will be a clear display like the following:

Figure A

The information in DataShopper is read-only and only supports SELECT statements. Do not attempt to issue other SQL statements because the ASP code will not display the results. It is designed for SELECT statements only.

Queries can be added to the Repository using the SQL Scanner or manually using the Maintain Metadata Tool in the Repository Client. The following example shows how to accomplish this using the Maintain Metadata tool.

To use the Maintain Metadata Tool in the Repository Client to implement the above Query with the restriction that you only want to see Authors living in Oakland, follow these steps:

  1. Go to Maintain Metadata in your Repository Client, select "SQL Statement" from the list of metadata Object Types, and select OK.

  2. Select the "Create a new object" button [+] and the "Add new 'SQL Statement'" dialog will appear.

  3. Create a new SQL Statement called "Authors in Oakland" and add the SQL query text to the "SQL Text" field. In this example, the SQL query text is:
     select * from authors where city = ' OAKLAND'
    Figure B

  4. Select the OK button and then Save the SQL Statement.

  5. Switch to Object/Relate mode as in the following screenshot of Target Relationships. You will need to select the "Query.Contains SQL Statement.SQL Statement" Target Relationship.

    Figure C

    Once done, then select the OK button.

  6. In Object/Relate mode, you then need to add a new instance of the Source Object Type: Query. Select the "Create a new object" button [+] and the "Add new 'Query'" dialog will appear.

    Figure D

    The important field here is the Location. This references the ODBC data source name (DSN) and the login and password to be used to access the Authors table. The syntax is:
     DSN=OdbcDsnName;UID=YourUserName;PWD=UserPassword 
    where DSN is local to the IIS server. Once done, then select the OK button.

  7. Once you Save your new Source Object Query (instance), you will need to create a relationship between it and the Target Object "Authors from Oakland". With the Source Object row "Authors in Oakland" highlighted, select the "Create a new object" button [+] in the "Relate Object Type" section and the "Confirm" dialog will appear.

    Figure E

    In the example above, the relationship name that is automatically assigned is "Authors in Oakland.Authors from Oakland".

  8. Select the "Yes" button and Save the additions.

  9. While still in Object/Relate mode, select the Source Object row "Authors in Oakland" and add it to a Container using the green cylindrical icon on the taskbar. This will enable you to go to the hotlinks feature of containers to see all the listed queries in one place.

  10. Now in DataShopper, go to Hotlinks for the specified container (in the example it is called Author Locations) and you should see something similar to the following:

    Figure F

  11. If you click on the Query "details" link, it will show you the following:

    Figure G

  12. To run the query listed in the Query Text section, click on the highlighted "Launch Query!" link and you should see the following result:

    Figure H

NOTE: You may get the following type of error when you first try to run a new query:

Please be patient. Query is executing...

Microsoft OLE DB Provider for ODBC Drivers

error '80004005'

Microsoft ODBC Driver Manager Data source name is not found and no default driver specified

/DataShopper/GenericQuery.ASP, line 103

This type of error is generally due to the DSN in the query location field not existing on the IIS server. It can also be the result of insufficient privileges for the Anonymous user account in the Windows Registry. Review Microsoft Knowledge Base Article - 306345 in the section titled "Using an ODBC DSN".