DB_SQL Function

Queries the database currently connected to PV‑WAVE.

Usage

table = DB_SQL(dbms_id, 'sql_stmt' )

Input Parameters

dbms_id—The DBMS ID (handle) that was returned by the DB_CONNECT function.

sql_stmt—A string containing an SQL statement used to retrieve data from the database. The SQL statement must be a SELECT statement.

Note: If you are experiencing issues using the * operator in the sql_stmt SELECT statement, refer to the <RW_DIR>/wave/Tips.pdf file for further information on ways to resolve the issues.

Returned Value

table—A PV‑WAVE table.

Keywords

Null_Info—Returns an associative array containing information on nulls in the database query result.

Discussion

This function returns a PV‑WAVE table containing the requested data from the external database. You can then manipulate and visualize the imported data using any PV‑WAVE functions. When importing data from an external database, keep the following points in mind:

All supported data types from the database can be imported into PV‑WAVE variables.

Date/Time data is imported directly from the database into PV‑WAVE date/time format.

PV‑WAVE does not support database NULL values. NULL values are converted to zeros for numeric types, and NULL strings for type string.

For detailed information on working with tables in PV‑WAVE, see the PV‑WAVE User Guide.

Example 1

This example imports all of the data from the emp table in the ORACLE database mydb.

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

emp = DB_SQL(oracle_id, 'SELECT * from emp')

Example 2

This example imports the name, job, and salary of the managers whose salary is greater than $2800.

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

emp = DB_SQL(oracle_id, "SELECT ename, job," + $
"sal from emp where job = 'MANAGER' and " "SAL > 2800")

Example 3

This example imports the names and salaries of employees whose salary is between $1200 and $1400.

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

emp = DB_SQL(oracle_id, 'SELECT ename, sal' + $
'from emp where sal between 1200 and 1400')

Example 4

This example imports the names of employees and their commissions whenever the commission is not a NULL value.

oracle_id = DB_CONNECT('ORACLE''scott/tiger@mydb')

table=DB_SQL(oracle_id, 'SELECT ename' + $
'from emp where comm is not NULL')

Example 5

This example uses the Null_Info keyword.

table=db_sql(db_connect('oracle', 'scott/tiger'), $
'select * from blanktest', null_info=foo)

This returns the result ‘table’ from your query and the null info object associative array ‘foo’. Foo contains three elements:

N_ROWS = the number of rows returned in the query

N_COLS = the number of columns or fields returned

MISSING_DATA = the null info object associative array

The MISSING_DATA associative array contains the field name tags, each of which has the associated array listing the rows with missing data for the tag.

For more information on the null info object and to process and extract the null information array use the NULL_PROCESSOR function.