Working with Databases

SenseTalk can access numerous database types using Microsoft Open Database Connectivity (ODBC), and can also refer to Excel files as databases. For information on connecting to either of these database types, see the topics below:

• ODBC Administration
• Working With Excel

note

ODBC Only: If you want to use SQL instead of SenseTalk to manipulate your data, see Executing SQL Directly.

Topics in This Section

On this Page: Database Basics

Database Connections: To work with a database first requires establishing a connection to communicate with the database server. This section covers how to define database connections and more for the following:

• Open Database Connectivity (ODBC)
• Excel

Database Tables: Information on accessing tables within a database in order to manipulate their data. Includes information on:

• Table Expressions
• Database Table Properties
• The Is a Table Operator

Reading from a Database: Learn how to pull data from a database. Covers use of:

• Reading Table Contents
• Where Expressions (used to select desired records to manipulate)
• Iterating over Records
• Counting Records

Writing to a Database: Learn how to store information in a database, update that information, and otherwise manipulate it. This includes:

• Records vs Property Lists
• Updating Values in a Record
• Adding Records
• Deleting Records

Variations and Additional Information: These less-common approaches to accessing database information may be useful in specific situations. Read this information to understand these other techniques.

• Opening and Closing Connections
• Database Identifiers
• Executing SQL Directly
• Automatic vs. Manual Updates
• Database Information Functions

Database Basics

A database is a repository for storing information in a structured fashion. In a traditional relational database data is stored in tables. The information in each table is organized in rows and columns with each row holding one record. The columns in the table identify the different fields of information that are contained in each record.

For example, in a table for information about the members of a club, the columns might be called "first name", "last name", "member number", "birth date", and so on. The table would contain one row (referred to as a "record") for each club member.

firstName	lastName	memberNumber	birthDate	memberSince	expirationDate
Erica	Grayson	102	1982-07-02	2003	2014-08-05
Carol	Weinhart	48	1967-10-14	1999	2011-12-31
Ted	Hanover	186	1974-03-22	2010	2013-09-18
Justin	Fallcourt	171	1991-11-30	2009	2014-01-31
...	...	...	...	...	...

A database often contains many different tables holding different types of information. Typically a database is managed by a database server, which may be a process on the local computer, but is more often a central server available somewhere on the network.

In many cases, a single database server will hold many different databases, sometimes also called a schema. Each database schema defines a number of different tables.