env_handle = ODBC_INIT( )
connect_handle = ODBC_CONNECT(env_handle, "data_source_name"
[, "login_string"])
@ODBC_STARTUP
% ODBC_INITIALIZE: PV-WAVE ODBC Interface is initialized
henv = ODBC_INIT()
hcon = ODBC_CONNECT( henv, ”my_oracle_DSN”, ”scott/tiger”)
note | The ODBC driver must be installed and the data source name created in ODBC Administrator before you can access the data source from PV‑WAVE. If you have problems connecting to your data source, please contact your database administrator to confirm that ODBC has been installed and configured correctly for your data source. |
ODBC_DISCONNECT, connect_handle
hcon = ODBC_CONNECT( henv, ”my_oracle_DSN”, ”scott/tiger”)
ODBC_DISCONNECT, hcon
result = ODBC_SQL(connect_handle, “sql_command”)
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. |
note | On 64-bit Windows, 4-byte SQL_INTEGER values are returned in PV-WAVE INT32 variables. On 32-bit Windows these values are returned in PV-WAVE LONG variables. If your 32-bit Windows application depends on returned values of type LONG, you need to modify your application when migrating to 64-bit Windows. |
; Connect to the Oracle data source identified by DSN 'mydbserv',
; with username 'scott' and password 'tiger'
henv = ODBC_INIT()
hcon = ODBC_CONNECT( henv, "mydbserv", "scott/tiger")
table = ODBC_SQL( hcon, "SELECT * FROM wave.wave_prop_trx")
INFO, table
; TABLE STRUCT = -> TABLE_1052092784746223633327159 Array(10000)
INFO, table, /Structure
;* Structure TABLE_1052092784746223633327159, 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)
henv = ODBC_INIT()
hcon = ODBC_CONNECT( henv, ”mydbserv”, ”scott/tiger”)
; Create the SQL command in a string PV-WAVE variable.
; First add the column list to this variable.
sql_command = ”SELECT trx_id, prop_type, ” + $
”trx_amt, trx_date ” + ”FROM wave.wave_prop_trx ”
; Next, add WHERE clause to string to limit the number of rows.
; The WHERE clause limits 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 ORDER BY clause to string to sort dates in order.
sql_command = sql_command + ”ORDER BY trx_date”
sub_table = ODBC_SQL( hcon, sql_command)
INFO, sub_table
; SUB_TABLE STRUCT =->TABLE_5122903921219401793313087 Array(947)
INFO, sub_table, /Structure
;* Structure TABLE_5122903921219401793313087, 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. |
henv = ODBC_INIT()
hcon = ODBC_CONNECT( henv, ”mydbserv”, ”scott/tiger”)
amt_by_type = ODBC_SQL( hcon, ”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”)
; Select the average transaction amount for each property type,
; ordered by property type
INFO, amt_by_type
; AMT_BY_TYPE STRUCT=-> TABLE_9471101896788241082259344 Array(9)
INFO, amt_by_type, /Structure
;* Structure TABLE_9471101896788241082259344, 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. |
INFO, amt_by_type_2
; AMT_BY_TYPE_2 STRUCT = -> TABLE_2033126909298595681151922
; Array(9)
INFO, amt_by_type_2, /Structure
;* Structure TABLE_2033126909298595681151922, 3 tags, 24 length:
; PROP_TYPE STRING '1BR_CONDO'
; MY_AVG_AMT DOUBLE 80501.404
; MY_TOTAL_AMT DOUBLE 87666029.
note | PV‑WAVE supports some searching, sorting, and aggregate functions internally (with the WHERE and QUERY_TABLE functions, for example). In many cases, the PV‑WAVE searching and sorting algorithms may be faster than performing them on the DBMS server (with ODBC_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. |
note | We suggest that you use explicit SELECT lists (no wildcards) and column aliases when importing data through a multi-table join. |
henv = ODBC_INIT()
hcon = ODBC_CONNECT( henv, ”mydbserv”, ”scott/tiger”)
; 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 = ODBC_SQL( hcon, sql_command)
INFO, table
; TABLE STRUCT = -> TABLE_1817650741549729007289092
; Array(3400)
INFO, table, /Structure
;** Structure TABLE_1817650741549729007289092, 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. |
TEST_STRING TEST_DATE TEST_NUM
----------- --------- --------
<NULL> 04-JUL-1776 3.14
<NULL_STRING> <NULL> 0
Not null! 04-JUL-1776 <NULL>
henv = ODBC_INIT()
hcon = ODBC_CONNECT( henv, ”mydbserv”, ”scott/tiger”)
table=ODBC_SQL(hcon, ”SELECT * FROM wave.wave_conv_test_nulls”)
INFO, table
; TABLE STRUCT = -> TABLE_2464312442611796883049150
; Array(3)
INFO, table, /Structure
;* Structure TABLE_2464312442611796883049150, 3 tags, 48 length:
; TEST_STRING STRING ''
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE 3.1400000
INFO, table(1), /Structure
;* Structure TABLE_2464312442611796883049150, 3 tags, 48 length:
; TEST_STRING STRING ''
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE 0.00000000
INFO, table(2), /Structure
;* Structure TABLE_2464312442611796883049150, 3 tags, 48 length:
; TEST_STRING STRING 'Not null!'
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE 0.00000000
table_2 = ODBC_SQL(hcon, $
”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_2196927880451215918776427, 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_2196927880451215918776427, 3 tags, 48 length:
; TEST_STRING STRING ''
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE 0.00000000
INFO, table_2(2), /Structure
;* Structure TABLE_2196927880451215918776427, 3 tags, 48 length:
; TEST_STRING STRING 'Not null!'
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE -999999.98
table_3 = ODBC_SQL(hcon, ”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_1775756501227746018662168, 6 tags, 72 length:
; TEST_STRING STRING ''
; TEST_STRING_I DOUBLE -1.0000000
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_DATE_I DOUBLE 0.00000000
; TEST_NUM DOUBLE 3.1400000
; TEST_NUM_I DOUBLE 0.00000000
INFO, table_3(1), /Structure
;* Structure TABLE_1775756501227746018662168, 6 tags, 72 length:
; TEST_STRING STRING ''
; TEST_STRING_I DOUBLE 0.00000000
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_DATE_I DOUBLE -1.0000000
; TEST_NUM DOUBLE 0.00000000
; TEST_NUM_I DOUBLE 0.00000000
INFO, table_3(2), /Structure
;* Structure TABLE_1775756501227746018662168, 6 tags, 72 length:
; TEST_STRING STRING 'Not null!'
; TEST_STRING_I DOUBLE 0.00000000
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_DATE_I DOUBLE 0.00000000
; TEST_NUM DOUBLE 0.00000000
; TEST_NUM_I DOUBLE -1.0000000