Database Guide
Database Guide
Preface
Notices
Trademarks
About this book
Who this book is for
What's new?
Conventions used in this book
Tell us what you think
VA Smalltalk database guide
Adding database support
Creating the application
Accessing a database management system
Loading database features
Connecting to a database manager
Defining a database query
Creating a query
Using the SELECT Details window
Creating static DB2 queries
Setting fetch and update policies
Using the results of a query
Tearing off results
Using quick forms
Running a query
Working with the packeting container details part
Using a host variable
Running a query - host variables
Precompiling static SQL
Extra practice
What to watch for
More database techniques
Formatting query results
Displaying a result column
Displaying rows as strings
Creating more complex SELECT statements
Using high-level qualifiers
Sorting result table rows
Restricting result rows
Using a dynamic WHERE clause
Nesting SELECT statements
Using the SQL Statement part
Defining an UPDATE statement
Defining an INSERT statement
Defining a DELETE statement
Using the Single-Row Query part
Using stored procedures
Using the Stored Procedure part
Running stored procedures
Handling result sets from stored procedures
Using static SQL
Adding database queries to packages
Developing database server applications
Developing DB2 applications
Setting up DB2 for workstation for Smalltalk development
Accessing DB2 services
Writing SQL statements using Smalltalk
Using SQL statements in your DB2 application
Developing a DB2 CLI application using nonvisual parts
Using stored procedures in a DB2 CLI application
Database programming guide
Database basics
Base database classes
Database definition classes
Data manipulation classes
Data link support classes (DB/2 only)
Accessing database management systems
Database connection concepts
Connecting to databases
Working with connection specifications
Working with logon specifications
Establishing database connections
Working with active connections
Working with database managers
Creating and deleting database tables
Preparing to use the code samples
Creating and accessing tables
Adding rows and data
Deleting tables
Querying databases
Writing SELECT statements
Selecting a row from a table
Selecting a row
Selecting rows from multiple tables (join operation)
Using a GROUP BY clause
Writing UPDATE, INSERT, and DELETE statements
Updating rows in a table
Inserting rows in a table
Deleting rows from a table
Using database classes for scripts
Error detection and other tips
Handling error objects
Ensuring row schema consistency
Intercepting SQL 000 codes
VA Smalltalk ODBC guide
ODBC support
Loading ODBC support into your image
Creating an ODBC.INI file and setting up database drivers
Sample database applications provided
To run the samples
To edit the samples
Environment-specific information
For Windows users
For UNIX users
Error messages
UNIX error handling
Installing an ODBC text database
GLORP Tutorial
Environment
Simplest Possible GLORP Example
Simple Object One Table Example
Reading, Writing, Deleting Data
Some Common Problems
Complex Objects and Tables
Cascading
Detached Objects
Inheritance and Polymorphic Queries
In Brief
Getting Started with PostgreSQL
Database Help
Database windows and dialogs
Database options
Accessing Database Options
Preferred high-level qualifier
Use Parameter
Thread all database calls
Database connection windows and dialogs
Database connection specifications
<<Add
New
Update
Delete
Close
New database connection specification
Connection alias
Access set
Database Manager
Database name
Prompt for logon information
Update database connection specification
Database logon
User ID
Password
Server
SQL statement editors
SELECT Details
Query name (on SELECT Details)
Description (on SELECT Details)
Tables/views (on SELECT Details)
Columns (on SELECT Details)
Computed columns
Column sequence
DISTINCT
SELECT *
Show SQL (on SELECT Details)
UPDATE Details
Query name (on UPDATE Details)
Description (on UPDATE Details)
Table/view (on UPDATE Details)
Columns (on UPDATE Details)
SET columns
Column value (field on UPDATE Details)
WHERE clause (on UPDATE Details)
Show SQL (on UPDATE Details)
INSERT Details
Query name (on INSERT Details)
Description (on INSERT Details)
Table/view (on INSERT Details)
Columns (on INSERT Details)
INSERT columns
Tear-Off Attribute
Column value (field on INSERT Details)
Show SQL (on INSERT Details)
DELETE Details
Query name (on DELETE Details)
Description (on DELETE Details)
Table/view (on DELETE Details)
WHERE clause (on DELETE Details)
Show SQL (on DELETE Details)
SQL Details
Query name (on SQL Details)
Description (on SQL Details)
SQL Statement
Computed Column Details
Optional column name (field on Computed Column Details)
Left operand (list on Computed Column Details)
Operator (on Computed Column Details)
Right operand (list on Computed Column Details)
Expression (on Computed Column Details)
Unary operator (on Computed Column Details)
Left operand (choice on Computed Column Details)
Right operand (choice on Computed Column Details)
Add to operand lists (on Computed Column Details)
SQL Statement
SQL clause editors
WHERE Details
Left operand (list on WHERE Details)
Operator (on WHERE Details)
Right operand (list on WHERE Details)
Expression (on WHERE Details)
Add to operand lists (on WHERE Details)
GROUP BY Details
Columns (on GROUP BY Details)
GROUP BY sequence
HAVING Details
Left operand (list on HAVING Details)
Operator (on HAVING Details)
Right operand (list on HAVING Details)
Expression (on HAVING Details)
Add to operand lists (on HAVING Details)
ORDER BY Details
Columns (on ORDER BY Details)
ORDER BY sequence
Select a query
Access set (on Select a query)
Query name (on Select a query)
SQL statement (on Select a query)
Create (on Select a query)
Expression Details
Left operand (list on Expression Details)
Operator (on Expression Details)
Right operand (list on Expression Details)
Expression (on Expression Details)
Add to operand lists (on Expression Details)
Stored procedure windows
Stored Procedure Specifications - Settings (DB/2)
Stored Procedure Specifications - Settings (ODBC and Oracle)
Data type dialogs
Host variables for
Host variables for
Name
Description
Edit host variable
Host variable type
Stored Procedure Host Variable
Nulls allowed
Sample SQL statements
Creating a SELECT statement - example 1
Creating a SELECT statement - example 2
SELECT statement
SELECT statement
Computed column
WHERE clause
HAVING clause
Creating an UPDATE statement
UPDATE statement
Column value expression
Creating an INSERT statement
INSERT statement
Creating a DELETE statement
DELETE statement
SQL SELECT statement
SELECT clause of SELECT
FROM clause of SELECT
WHERE clause of SELECT
GROUP BY clause of SELECT
HAVING clause of SELECT
ORDER BY clause of SELECT
SQL UPDATE statement
UPDATE clause of UPDATE
SET clause of UPDATE
WHERE clause of UPDATE
SQL INSERT statement
INSERT INTO clause of INSERT
VALUES clause of INSERT
SQL DELETE statement
DELETE FROM clause of DELETE
WHERE clause of DELETE
Function
Expression
Compound expression
SQL predicate
Logical expression
Compound logical expression
Database Package Specifications
Database menus
Query
Create
LOB definitions
Query
Create
UPDATE
INSERT
DELETE
Edit
Import
Export
Manual create
Manual edit
Host variables
Options
High-level qualifiers
Pop-up menu for adding and deleting data fields
Add before
Add after
Edit
Delete
Get Schema
Pop-up menu for adding data fields
Get schema
Unary operator
Left operand
Right operand
Nested SELECT
Unary operator
Left operand
Right operand
Move before
Move after
Column value
Select all
Deselect all
Ascending (ASC)
Descending (DESC)
Move before
Move after
Move before
Move after
Select all
Deselect all
Select all in table
Deselect all in table
Select all
Deselect all
Create
Edit
Delete
System values
Clause
WHERE
GROUP BY
HAVING
ORDER BY
Column value
Specify expression
Unary operator
Left operand
Right operand
Nested SELECT
Pop-up menu for Database Query and Stored Procedure parts
Pop-up menu for Query Result Table and Current Row parts
Database Functions Category
Multi-row Query
Multi-Row Query - Settings
Query Result Table
Current Row
Single-Row Query
Single-Row Query - Settings
Result Row
SQL Statement
SQL Statement - Settings
Stored Procedure
Stored Procedure - Settings
Pop-up menu item descriptions
Edit part
Open Settings
Promote Part Feature
Change Name
Delete
Layout
Create Deferred Update Part
Connect
Browse Connections
Reorder Connections From
Quick Form
Tear-Off Attribute
Distribute
Snap To Grid
Match Size
Connection Specs
Opening a settings view window
Access set
Columns to use as index
Columns to Update
Connection alias
Description
Defer updates
Enable packeting
SQL Statement
Enable blocked fetches
Lock row on edit
Query name
Auto commit
Blocked fetch size
Package Specs
Use high level qualifiers
LOB Definitions
Maximum number of rows to fetch
Stored Procedure Spec Name
Stored Procedure Description
Show SQL
Read-only
New
Edit
Delete
Static Package
Database Tasks
See some basics about VA Smalltalk database support
Database parts
Database managers
Connection specifications
Tip: Changing managers using an alias
Access sets
Load a database feature
Define ODBC data sources
Load ODBC Samples
Connect to a database manager
Define an access set
Create a connection specification
Add a connection specification to an access set
Establish a database connection using the connection specification
Quickly try some database examples
Load the database examples
Launch the database examples
Browse the database examples
Work with multi-row queries
Add a multi-row query part
Select a connection specification
Create a package specification
Setting a fetch policy
Fetch Options - Page 1 of 2
Fetch Options - Page 2 of 2
Setting an update policy
Update Options - Page 1 of 2
Update Options - Page 2 of 2
Tip: Use Auto commit
Execute queries before opening a window
Execute queries with a push button
Tear off a result table
Use a quick form
Tip: Getting a quick form
Display a result column
Modify database data
Tip: Apply before committing
Change a query
Delete a query
Work with SQL statements
Add an SQL statement part
Use the SQL statement settings window
Create an SQL statement interactively
Edit an SQL statement
Delete an SQL statement
Use the SQL editors
Use the SELECT Details window
Tip: Limiting the number of tables
Example: SELECT statements
SELECT statement 1
SELECT statement 2
Use the UPDATE Details window
Use the INSERT Details window
Use the DELETE Details window
Use the manual SQL editor
Work with stored procedures
Use a stored procedure part
Complete stored procedure fields
Execute stored procedures
Share SQL statements
Import and export individual queries
Import and export entire access sets
File out archival code for access sets
Use the SELECT Details window
Select or deselect multiple columns
Create, edit, or delete a computed column
Specify a computed column
Move a column or computed column
Specify a WHERE clause
Specify a nested SELECT statement
Specify a GROUP BY clause
Specify a HAVING clause
Specify an ORDER BY clause
Appendixes
Installing Database Feature
Running Oracle Samples
Stored Procedures
Stored procedure using Date, String, and Number Parameters
Stored Procedure using an array of numbers
Stored procedure using an array of strings
LOB Manipulation
General Information
Reading and Writing a LOB to/from a Buffer
Reading and Writing a LOB to/from a File
BFiles
Progammatic Execution of SQL statements
SQL Statements
Anonymous PL/SQL Blocks
Passing data to a pl/sql block and inserting it into a table
Retrieving data from a pl/sql block using a select statement
Retrieve the system time from a pl/sql block
Function Calls using Native Oracle
Function with a string as a parameter and returns a string
Function that has an integer as an in parameter and returns a float
A no parameter function that returns a string
A function that has a timestamp as a parameter and returns a timestamp
Index