Connecting to a Data Source

ODBC Connection User Guide > Getting Started > Connecting to a Data Source

The functions ODBC_INIT and ODBC_CONNECT are used to establish a connection to your data source from PV‑WAVE. ODBC_INIT returns an ODBC environment handle, which is then used as an input argument for ODBC_CONNECT. ODBC_CONNECT returns an ODBC connection handle, which is used to identify the connection for other data source operations. The syntax is as follows:

env_handle = ODBC_INIT( )

connect_handle = ODBC_CONNECT(env_handle, "data_source_name"
[, "login_string"])

The value of data_source_name must be the same as the name specified for your data source on the DSN tab of the ODBC Administrator application. If login_string is specified, it must contain the username and password information required for the data source. The format of this string is dependent upon the DBMS used to administer the data source. If login_string is not specified, then PV‑WAVE ODBC Connection uses the default values specified in ODBC Administrator.

Making a Connection to the Data Source

Assume that you would like to import some data from an Oracle data source into PV‑WAVE.

First, you must load the PV‑WAVE ODBC Connection routines into your PV‑WAVE session by executing the ODBC_STARTUP command file:

@ODBC_STARTUP

% ODBC_INITIALIZE:    PV-WAVE ODBC Interface is initialized

Next, create an environment handle and make the connection. Let’s assume the data source name is specified as “my_oracle_DSN” in ODBC Administrator, and that you wish to log in as user “scott”, with password “tiger”:

henv = ODBC_INIT()

hcon = ODBC_CONNECT( henv, ”my_oracle_DSN”, ”scott/tiger”)

You are now ready to import the data, using either ODBC_SQL, or ODBC_PREPARE and ODBC_FETCH. For more information, please refer to the PV‑WAVE ODBC Connection documentation on the ODBC_INIT and ODBC_CONNECT functions.

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.

Disconnecting from the Data Source

Use the ODBC_DISCONNECT procedure to disconnect from the data source when you have finished interacting with it. This step can be very important if there are a limited number of license seats for connecting to the data source. Disconnecting from the data source frees a license seat so that another user can connect to the data source. If there is only one license seat available for the data source, and you wish to establish a connection to the same data source as a different user, you must end the first connection.

ODBC_DISCONNECT takes one parameter, the connect_handle that was returned by ODBC_CONNECT. The syntax is:

ODBC_DISCONNECT, connect_handle

For example, if you connected to the data source with the following ODBC_CONNECT call:

hcon = ODBC_CONNECT( henv, ”my_oracle_DSN”, ”scott/tiger”)

you can end the connection with the following ODBC_DISCONNECT call:

ODBC_DISCONNECT, hcon

This call frees the data source connection license seat for another user. For more information, please refer to the description of the ODBC_DISCONNECT procedure.

Querying the Data Source

After a connection to a data source has been established, you can use ODBC_SQL to issue any single-line SQL command to the DMBS. ODBC_SQL takes two parameters: the connection handle (returned by ODBC_CONNECT) and a string containing the SQL command. The syntax is:

result = ODBC_SQL(connect_handle, “sql_command”)

The parameter sql_command is a string containing an SQL command to execute on the data source. 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. Once result contains a result set, result can be manipulated and/or displayed by any PV‑WAVE routine. For instance, you can create PV‑WAVE tables that are subsets of the result set (using 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.

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.

Example 1: Importing an Entire Table

The ODBC_SQL command shown below imports all of the data from the table called wave.wave_prop_trx in an Oracle data source with the DSN
mydbserv. The table contains 8 columns and 10000 rows.

; 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)

As you can see, the data has been imported into an array of PV‑WAVE structures. The tag names in the structures 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.

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.

Example 3: Importing and Sorting Table Summary Data

The ODBC_SQL command shown below imports averages by property type from table wave.wave_prop_trx in the Oracle data source mydbserv.

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.

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

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.

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.

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.

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:

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

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 = 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

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 = 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

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

Version 2017.1