4.5  :  Interfacing IDL with the Millennium Database

Here we provide some simple IDL scripts, created by Ben Panter, to interface with the databases using the wget method. Help is available here.

Latest versions:

query_sql_str.pro

query_sql.pro

SQL_QUERY.zip file including help

Instructions:

This program interfaces witht the GAVO Millennium / SDSS servers to perform SQL queries and parse the results into IDL structs.

To use, put your query in a string:

IDL> query = 'select * from agebin'

then run the search:

IDL> result = query_sql_str(query)

You'll need to login for the first query of each session, but shouldn't after that. If you have the wrong login details and need to replace them, simply type /login as a keyword when you call query_sql:

IDL> result = query_sql_str(query, /login)

If you're using a different database, you may need to supply the URL of a seperate server. Something like:

IDL> result = query_sql_str(query, server='http://gavo.mpa-garching.mpg.de/MyMillennium')

Note that there is no final slash on the URL!

The code automatically chooses the tag names of the structure to reflect the field names returned by the query. If there are duplicates, it appends the number of the field to the end of the tag name. If this is not the format you require, or if the variable formatting is incorrect, you can supply a base struct to be replicated using the my_struct=.... keyword. The mystruct structure must have the same number of tags as the query returns. A simpler approach is to supply column names in your query, hence

select alice.specobjid, bob.specobjid

becomes

select alice.specobjid as a_id, bob.specobjid as b_id

If you find that your query is not as long as you expected it is possible the server timed out before completion. In this case supply a variable for the truncated keyword. This will allow you to inspect the last completed line of the query and resubmit with that as a condition when combined with a sort command

result = query_sql_str('select * from agebin', truncated=truncated)

The truncated variable now contains the last line recovered. If the query included a statement to sort on the first column it can be resubmitted starting at the record after that.

keywords:

  • server - Change the server from the default value to something else (this can be changed permanently in sql_query.pro if desired)

  • login - Force a new attempt at logging in.

  • no_cookies - Do not use cookies to maintain a session on the server. For compatability with early wget versions
  • my_struct - supply you own struct to avoid problems with incorrect format guessing or provide your own field names
  • truncated - this allows you to read the last complete line if a query was truncated due to a time out. You may then resumbit the query using that information if your original query included a sort statement.
  • quiet - suppresses any messages to the shell, both by IDL and wget.

The codes are always evolving as we trace more bugs or improve functionality. To check for updates refer to the 'version' line in your own version and the version here.

Caveats:

There is no concept of null in IDL. If a query returns null it will be interpreted as 0.0000. We welcome suggestions for an alternative way to do this.

If you intend to use non HTTP compliant characters in your query, you must encode them. A guide to encoding is given on wikipedia. Should this page become unavailiable, a websearch on Percent Encoding should provide a list. In particular, + should be encoded %2B and / %2F.