Querying the Database

Optional PV-WAVE Documentation > Database Connection User Guide > Importing from a Database > Querying the Database

After a database connection is established, you can use the DB_SQL function to issue any single-line SQL command to the DMBS. DB_SQL takes two parameters: the DBMS ID (returned by DB_CONNECT) and a string containing the SQL command. The syntax is as follows:

result = DB_SQL(dbms_id, "sql_command")

If sql_command returns a result set (as in a SELECT statement), result contains the result set, placed in a PV‑WAVE table variable. In the cases where sql_command does not return a result set (as in INSERT, UPDATE, or DELETE statements), result contains a long value that indicates the success (result=0) or failure (result=–1) status of sql_command. The variable result can be manipulated and/or displayed by any PV‑WAVE routine. This includes creating PV‑WAVE tables which are subsets of the result set (with QUERY_TABLE, for example).

note

PV‑WAVE single-line SQL command support does not include the ability to execute Block SQL statements. Execution of stored procedures, however, is supported, so we recommend that users who wish to perform more complicated DBMS operations from PV‑WAVE enclose them in a DBMS stored procedure. For more info on creating stored procedures, contact your database administrator.

Example 1: Importing an Entire Table

The DB_SQL command shown below imports all of the data from the table called wave.wave_prop_trx in the Oracle database mydbserv. The table contains eight columns and 10000 rows.

; Connect to the Oracle database 'mydbserv', with username

; 'scott' and password 'tiger'

oracle_id = DB_CONNECT( ”ORACLE”, ”scott/tiger@mydbserv”)

table = DB_SQL( oracle_id, ”SELECT * FROM wave.wave_prop_trx”)

INFO, table

; TABLE           STRUCT    = -> TABLE_1855432390284244950984412

; Array(10000)

info, table, /Structure

;* Structure TABLE_1855432390284244950984412, 8 tags, 72 length:

; TRX_ID       LONG                 0

; PROP_TYPE    STRING    'OTHER               '

; PROP_ADDRESS STRING    ''

; PROP_POST_CD STRING    ''

; PROP_XGRID   DOUBLE        0.0075200000

; PROP_YGRID   DOUBLE           1.6357100

; TRX_AMT      DOUBLE           116383.00

; TRX_DATE     STRUCT    -> !DT Array(1)

As you can see, the data has been imported into an array of PV‑WAVE structures. The tag names in the structure correspond to the column names in the database table.

Example 2: Importing and Sorting Part of a Table

In this example, we wish to import and sort a subset of the data in wave.wave_prop_trx. The following set of commands limits both the number of rows and columns returned to PV‑WAVE.

oracle_id = DB_CONNECT( ”ORACLE”, ”scott/tiger@mydbserv”)

; Create the SQL command as a PV-WAVE variable First, create the

; column list

sql_command = ”SELECT trx_id, prop_type, ” + $
”trx_amt, trx_date ”     + ”FROM wave.wave_prop_trx ”

; Next, add a WHERE clause to limit the number of rows. This

; limits the subset to all dates between June 6, 1999 and June

; 6, 2001

sql_command = sql_command + $
”WHERE trx_date <= TO_DATE('2001/06/01', 'YYYY/MM/DD') ” + $
”  AND trx_date >  TO_DATE('1999/06/01', 'YYYY/MM/DD') ”

; Finally add an ORDER BY clause to sort the dates in order

sql_command = sql_command + ”ORDER BY trx_date”

sub_table = DB_SQL( oracle_id, sql_command)

INFO, sub_table

; SUB_TABLE       STRUCT    = -> TABLE_2080423439256551873139501

; Array(947)

INFO, sub_table, /Structure

;* Structure TABLE_2080423439256551873139501, 4 tags, 48 length:

; TRX_ID    LONG              7514

; PROP_TYPE STRING    'OTHER               '

; TRX_AMT   DOUBLE           206871.00

; TRX_DATE  STRUCT    -> !DT Array(1)

DT_TO_STR, sub_table(0).trx_date, tmp_date, tmp_time, $
Date_Fmt=5, Time_Fmt=-1

PRINT, tmp_date + ” ” + tmp_time

; 1999/06/01 22:20:37.000

note	Very long SQL statements may not fit in a single PV‑WAVE command string. For very long SQL statements, we recommend that you “build” the command in a PV‑WAVE string variable, which can be any length.

Example 3: Importing and Sorting Table Summary Data

The DB_SQL command shown below imports averages by property type from table wave.wave_prop_trx in the Oracle database mydb.

oracle_id = DB_CONNECT( 'ORACLE', 'scott/tiger@mydbserv')

; Select the average transaction amount for each property type,

; ordered by property type

amt_by_type = DB_SQL( oracle_id, 'SELECT prop_type, ' + $
'AVG(trx_amt) my_avg_amt, ' + 'SUM(trx_amt) my_total_amt ' + $
'FROM wave.wave_prop_trx ' + 'GROUP by prop_type ' + $
'ORDER by prop_type')

INFO, amt_by_type

; AMT_BY_TYPE     STRUCT    = -> TABLE_1990902712472184093171925

; Array(9)

INFO, amt_by_type, /Structure

;* Structure TABLE_1990902712472184093171925, 3 tags, 24 length:

; PROP_TYPE    STRING    '1BR_CONDO           '

; MY_AVG_AMT   DOUBLE           80501.404

; MY_TOTAL_AMT DOUBLE           87666029.

note	When using expressions or aggregate functions in an SQL SELECT column list, we recommend that you use a column alias. This will help ensure that the tag name is valid in the PV‑WAVE table variable.

This same data could also be generated with PV‑WAVE functions:

table = DB_SQL( oracle_id, 'SELECT * from wave.wave_prop_trx')

amt_by_type_2 = QUERY_TABLE( table, 'prop_type, ' + $
'AVG(trx_amt) my_avg_amt, ' + 'SUM(trx_amt) my_total_amt ' + $
'group by prop_type')

amt_by_type_2 = ORDER_BY( amt_by_type_2, 'prop_type')

INFO, amt_by_type_2

; AMT_BY_TYPE_2   STRUCT    = -> TABLE_3150083162320518139151666

; Array(9)

INFO, amt_by_type_2, /Structure

;* Structure TABLE_3150083162320518139151666, 3 tags, 24 length:

; PROP_TYPE    STRING    '1BR_CONDO           '

; MY_AVG_AMT   DOUBLE           80501.404

; MY_TOTAL_AMT DOUBLE           87666029.

PV‑WAVE supports some searching, sorting, and aggregate functions internally (with the WHERE and QUERY_TABLE functions, for example). In many cases, PV‑WAVE searching and sorting algorithms may be faster than performing them on the DBMS server (with DB_SQL). We recommend that you try importing data into PV‑WAVE with a minimum of sorting, and use PV‑WAVE functions to sort, group, and search the data.

Example 4: Importing Data from Multiple Tables

This example combines data from three different tables into one PV‑WAVE data set. The data is from air quality measurements from a number of fixed-location monitoring stations. One table contains the monitoring station location information (wave.wave_ts_location), one contains the dataset information (wave.wave_ts_dataset), and one contains the individual measurement data (wave.wave_ts_datapoint). Notice that the tag names in the PV‑WAVE table variable are the same as the column alias values given in the SELECT list.

note	We suggest that you use explicit SELECT lists (no wildcards) and column aliases when importing data through a multi-table join.

oracle_id = DB_CONNECT( ”ORACLE”, ”scott/tiger@mydbserv”)

; Create the SQL command as a PV-WAVE variable. This query

; combines data from 3 normalized tables

sql_command = ”SELECT dpnt.air_temp  air_temp, ” + $
”dpnt.humidity    humidity, ” + $
”dpnt.atm_press   atm_press, ” + $
”dpnt.o3_ppm      o3_ppm, ” + $
”dpnt.co_ppm      co_ppm, ” + $
”dpnt.no2_ppm     no2_ppm, ” + $
”dpnt.pm10_ug_m3  pm10_ug_m3, ” + $
”dset.dataset_id  dataset_id, ” + $
”dset.start_date  ref_date, ” + $
”dloc.grid_x      grid_x, ” + $
”dloc.grid_y      grid_y  ” + $
”FROM wave.wave_ts_datapoint dpnt, ” + $
”wave.wave_ts_dataset   dset, ” + $
”wave.wave_ts_location  dloc ”

; Join and data limits. Only plot data for grid ID = 1. And for

; datasets which started during 1997 through 2002

sql_command = sql_command + $
”WHERE dset.dataset_id = dpnt.dataset_id ” + $
”AND dset.start_date >= TO_DATE('19970101', 'YYYYMMDD') ” + $
”AND dset.start_date < TO_DATE('20030101', 'YYYYMMDD') ” + $
”AND dloc.loc_id = dpnt.loc_id ” + $
”AND dloc.start_date <= dset.start_date ” + $
”AND (   dloc.end_date > dset.start_date ” + $
”     OR dloc.end_date IS NULL)  ” + $
”AND dloc.grid_id = 1 ”

; Perform the query

table = DB_SQL( oracle_id, sql_command)

INFO, table

; TABLE           STRUCT    = -> TABLE_2808314677754116534184991

; Array(3400)

INFO, table, /Structure

;* Structure TABLE_2808314677754116534184991, 11 tags, 72

; length:

; AIR_TEMP   FLOAT           29.2000

; HUMIDITY   FLOAT           26.7000

; ATM_PRESS  FLOAT           753.520

; O3_PPM     FLOAT         0.0434300

; CO_PPM     FLOAT           3.61000

; NO2_PPM    FLOAT         0.0347400

; PM10_UG_M3 FLOAT           21.1800

; DATASET_ID LONG                 6

; REF_DATE   STRUCT    -> !DT Array(1)

; GRID_X     FLOAT          -1.46000

; GRID_Y     FLOAT           6.15000

note	PV‑WAVE only supports table JOINs during data import. JOINs are not allowed on PV‑WAVE table data after import.

Example 5: Importing NULL Values

PV‑WAVE does not support NULL values in table variables. If PV‑WAVE encounters a NULL value in a DBMS result set, it will replace it with zero (for numeric types), a NULL string (for strings), or an empty structure (for date/time values). In the following example, we use the table wave.wave_conv_test_nulls, which contains the following values:

TEST_STRING        TEST_DATE         TEST_NUM

-----------        ---------         --------

<NULL>             04-JUL-1776       3.14

<NULL_STRING>      <NULL>            0

Not null!          04-JUL-1776       <NULL>

In this table, <NULL> represents the database NULL value, and <NULL_STRING> is the zero-length string (‘’). The following example indicates how this table could cause problems in PV‑WAVE:

oracle_id = DB_CONNECT( ”ORACLE”, ”scott/tiger@mydbserv”)

table = DB_SQL(oracle_id, ”SELECT * FROM $
wave.wave_conv_test_nulls”)

INFO, table

; TABLE           STRUCT    = -> TABLE_2251731550291596501887914

; Array(3)

INFO, table, /Structure

;* Structure TABLE_2251731550291596501887914, 3 tags, 48 length:

; TEST_STRING STRING    ''

; TEST_DATE   STRUCT    -> !DT Array(1)

; TEST_NUM    DOUBLE           3.1400000

INFO, table(1), /Structure

;* Structure TABLE_2251731550291596501887914, 3 tags, 48 length:

; TEST_STRING STRING    ''

; TEST_DATE   STRUCT    -> !DT Array(1)

; TEST_NUM    DOUBLE           0.0000000

INFO, table(2), /Structure

;* Structure TABLE_2251731550291596501887914, 3 tags, 48 length:

; TEST_STRING STRING    'Not null!                     '

; TEST_DATE   STRUCT    -> !DT Array(1)

; TEST_NUM    DOUBLE           0.0000000

In row 0 and row 1, the column test_string has the same value in PV‑WAVE. However, in the database, the row 0 value is NULL and the row 1 value is the NULL string ‘’. Similarly, the values of test_num are the same in rows 1 and 2, even though they are different in the database.

If NULL-valued data is significant, one approach is to replace the NULL with a substitute value in the SELECT list. The following example indicates how this can be accomplished:

table_2 = DB_SQL(oracle_id, $
”SELECT NVL(test_string, '_NULL_') test_string, ” + $
”NVL(test_date, TO_DATE('29991231', 'YYYYMMDD')) $
test_date,” + ”NVL(test_num,    -999999.98) test_num ” + $
”FROM wave.wave_conv_test_nulls”)

INFO, table_2, /Structure

;* Structure TABLE_3088719732127463461882630, 3 tags, 48 length:

; TEST_STRING STRING    '_NULL_                        '

; TEST_DATE   STRUCT    -> !DT Array(1)

; TEST_NUM    DOUBLE           3.1400000

INFO, table_2(1), /Structure

;* Structure TABLE_3088719732127463461882630, 3 tags, 48 length:

; TEST_STRING STRING    ''

; TEST_DATE   STRUCT    -> !DT Array(1)

; TEST_NUM    DOUBLE           0.0000000

INFO, table_2(2), /Structure

;* Structure TABLE_3088719732127463461882630, 3 tags, 48 length:

; TEST_STRING STRING    'Not null!                     '

; TEST_DATE   STRUCT    -> !DT Array(1)

; TEST_NUM    DOUBLE          -999999.98

Another approach is the concept of indicator variables. An indicator variable has a value of –1 if the associated variable is NULL, and a value of zero otherwise. For an Oracle database, the following example code can be used to generate indicator variables in PV‑WAVE:

table_3 = DB_SQL(oracle_id, ”SELECT test_string, ” + $
”DECODE(test_string, NULL, -1, 0) test_string_i, ” + $
”test_date, ” + $
”DECODE(test_date, NULL, -1, 0) test_date_i, ” + $
”test_num, ” + $
”DECODE(test_num, NULL, -1, 0) test_num_i ” + $
”FROM wave.wave_conv_test_nulls”)

INFO, table_3, /Structure

;* Structure TABLE_2739531713696126301209217, 6 tags, 72 length:

; TEST_STRING   STRING    ''

; TEST_STRING_I DOUBLE          -1.0000000

; TEST_DATE     STRUCT    -> !DT Array(1)

; TEST_DATE_I   DOUBLE           0.0000000

; TEST_NUM      DOUBLE           3.1400000

; TEST_NUM_I    DOUBLE           0.0000000

INFO, table_3(1), /Structure

;* Structure TABLE_2739531713696126301209217, 6 tags, 72 length:

; TEST_STRING   STRING    ''

; TEST_STRING_I DOUBLE           0.0000000

; TEST_DATE     STRUCT    -> !DT Array(1)

; TEST_DATE_I   DOUBLE          -1.0000000

; TEST_NUM      DOUBLE           0.0000000

; TEST_NUM_I    DOUBLE           0.0000000

INFO, table_3(2), /Structure

;* Structure TABLE_2739531713696126301209217, 6 tags, 72 length:

; TEST_STRING   STRING    'Not null!                     '

; TEST_STRING_I DOUBLE           0.0000000

; TEST_DATE     STRUCT    -> !DT Array(1)

; TEST_DATE_I   DOUBLE           0.0000000

; TEST_NUM      DOUBLE           0.0000000

; TEST_NUM_I    DOUBLE          -1.0000000

Once the indicator variables have been created, it is a simple matter to create indices (with the WHERE function) which can be used to isolate or exclude the NULL values.

Connecting to a Database from a PV-WAVE Routine

You can place database connection functions in a PV‑WAVE routine. Use the ON_IOERROR function to trap errors that occur while connecting, importing, and disconnecting from the DBMS. ON_IOERROR is described in the PV-WAVE Reference. The following example demonstrates this technique:

FUNCTION Read_Dept

; Read the employee name and department number from the

; database and return a new table.

ON_IOERROR, Bad

; Connect To DBMS

;===============

PRINT, 'DB_CONNECT:'

oracle_id=DB_CONNECT('ORACLE', 'scott/tiger')

PRINT, 'Ok'

; Import data from the database.

;=============================

PRINT, 'DB_SQL:'

table = DB_SQL(oracle_id, 'SELECT ename,' + 'deptno from emp')

PRINT, 'Ok'

; Disconnect from the DBMS.

;========================

PRINT, 'DB_DISCONNECT: '

DB_DISCONNECT, oracle_id

PRINT, 'Ok'

PRINT, 'End'

RETURN, table

Bad:

PRINT, 'Bad'

END