FORMAT=ODBC

ODBC is a Labeled Table format for series input (only), available in the Professional level of RATS, to read data from any database format that supports Open Database Connectivity (ODBC) and SQL.

RATS Instructions

data(format=odbc)	read series from a database into memory
store(convert=odbc)	copy series from a database to RATS format

Interface Operations

None

Details

Before reading data from a database using ODBC and SQL, you must set up an ODBC “Data Source” for the desired database. The process for doing this varies somewhat depending on the operating system in use. With recent versions of Windows, you can set up a Data Source by opening the Windows Control Panel and double-clicking on the “Data Sources (odbc)” control. This displays a dialog box you can use to define a “Data Source Name” for your database.

Once you have the Data Source defined, you can open a connection to the database in RATS by using a command of the form:

open odbc dsn

where “dsn” is the Data Source Name for the database.

You can then read data from the database by executing a DATA command. On the DATA instruction, you can either:

•use the SQL option to provide a short (255 characters or less) sql string directly on the DATA instruction, or

•use the QUERY option to process a more complex sql queries, either from a separate file or from the lines following the DATA instruction.

The DATA command creates a table (internally) which is then processed similar to spreadsheet files

For example, the following creates a table with date and total daily sales (summing the SUBTOTAL field by date to create the SALES field), then reads the data into RATS, creating the series SALES as monthly sums.

cal(m) 1995:1

open odbc "Sales"

data(format=odbc,compact=sum,

sql="select date,sum(subtot) as sales from invoice order by date") $

1995:1 2006:12

The code below does the same thing using QUERY:

cal(m) 1995:1

open odbc "Sales"

open sqlfile "c:\rats\sqlquery.txt"

data(format=odbc,compact=sum,query=sqlfile) 1995:1 2006:12

where the file SQLQUERY.TXT contains the following lines:

select date,sum(subtot)

as sales from invoice

order by date

;