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 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:
Database Tables: Information on accessing tables within a database in order to manipulate their data. Includes information on:
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:
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.