themis3db

From DavinciWiki

Contents: Description, Arguments, Usage, Examples, Sub-Functions, Related Functions, Source

Description

Used to generate a general query to the Mars Space Flight Facility THEMIS 3 MySQL database through a web-based servelet.

Arguments and Return Values

Arguments: SQL-query and optional formatting settings

Return Value: url to query the server with (e.g. to be read)

Usage

Syntax: themis3db( SQL_query [,psql=BOOL][,header=BOOL][,xformat=BOOL][,nullnum=value][,nullchar=string])

Required Input Parameter:

SQL_query-the SQL-query with appropriate characters (e.g. ",\ escaped by a \)

see appropriate procedure pages for help with SQL-query grammar and database layouts

Optional Parameters:

psql - set=1 to use MSFF-Mars PSQL database; set=0 to use MSFF-Themis3 MYSQL database (default psql=0)

Default public database access is available to all users

Specific MSQL database access is controlled through your ThemisDBCredentials

Specific PSQL database access is controlled by setting the DV_THM_DB_USER variable

header - returns the header row of column names based on the selection list in the query (Default is 0)

xformat - returns the records in the expanded format option (similar to the MYSQL \G query syntax)

nullnum - use to set the PSQL null option for numeric fields (Default is to return an empty string)

nullchar - use to set the PSQL null option for string fields (Default is to return an empty string)

Execution Hints:

Some users prefer to define the query as a variable, others prefer to embed the query string in the themis3db() function call
Both options are shown in the examples below
The themis3db() function only defines the URL; the query is sent to the server when the results are attempted to be read by Davinci
Results can be accessed by any Davinci read function: ascii, read_lines, load_vanilla, load_csv, copy, etc
Use formatting options appropriate to your read function
The QueryTool will return ERROR messages if your query fails; each read function will parse this message differently
It it highly recommended that you test for ERROR messages before attempting to parse the query results
SQL error messages can be very helpful in identifying typo errors and/or a problem in the SQL syntax
Copy/paste the URL returned by themis3db() into your favourite browser to preview the results

Examples

Using a single call to query for results from MYSQL

dv> data=ascii(themis3db("select file_id, local_solar_time, solar_longitude from \
qubgeom where point_id = \"CT\" and file_id rlike \"I\" and local_solar_time > \
12 and local_solar_time < 19 and lat> -60 and lat < 60;"),format=float)

Downloading.......done.
Apparent file size: 3x41941x1
Read ASCII file: 3x41941x1
3x41941x1 array of float, bsq format [503,292 bytes]

Using multiple steps to query the PSQL database, check for errors, and read in the results

dv> query="select file_id, local_solar_time, solar_longitude from qubgeom where \
point_id='CT' and file_id like 'I%' and local_solar_time > 12 and \
local_solar_time < 19 and lat between -60 and 60;"
dv> url=themis3db(query,header=1,psql=1)
dv> data=read_lines(url)

Downloading........done.
Read TEXT file: 41942 lines
Text Buffer with 41942 lines of text
   1: file_id  local_solar_time        solar_longitude
   2: I00816001        15.223333       329.633114
   3: I00816004        15.095  329.636722
   4: I00817002        14.909722       329.642339
   5: I00819004        15.158055       329.770657
   6: I00821002        14.9825 329.821698
   7: I00821006        14.860556       329.824562
   8: I00822002        14.945  329.867989
   9: I00822009        15.2425 329.904525
   10: I00823002       14.815833       329.915801
 
dv> grep(data,"ERROR")
 No Match

Using read_lines with extended format

dv> data=read_lines(themis3db("select file_id, local_solar_time, solar_longitude from \
qubgeom where point_id = \"CT\" and file_id like \"I%\" and local_solar_time > \
12 and local_solar_time < 19 and lat > -60 and lat < 60;",psql=1,xformat=1))

Downloading...............................done.
Read TEXT file: 167765 lines
Text Buffer with 167765 lines of text
   1: *************************** 1. row ***************************
   2: file_id: I00816001
   3: local_solar_time: 15.223333
   4: solar_longitude: 329.633114
   5: *************************** 2. row ***************************
   6: file_id: I00816004
   7: local_solar_time: 15.095
   8: solar_longitude: 329.636722
   9: *************************** 3. row ***************************
   10: file_id: I00817002

Note: this may be used with nearly any davinci read function

DavinciWiki Mini-Nav Bar

Contents