• Database Guide
• Preface
• Notices
  • Trademarks
• About this book
  • Who this book is for
  • What's new?
    • VAST Platform 2022 (version 11.0.0)
  • 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
        • Connections, connection specifications, and aliases
        • Access sets
        • Defining an access set
        • Creating a connection specification
        • Adding a connection specification to an access set
        • Establishing a database connection
        • Changing the database (or data source) name in the .ini configuration file
    • Defining a database query
      • Creating a query
        • Adding a Multi-row Query part
        • Defining the settings
      • Using the SELECT Details window
      • Creating static DB2 queries
      • Setting fetch and update policies
        • Setting a fetch policy
        • Setting an update policy
    • 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
        • Specifying Package Specification Options
  • Developing database server applications
    • Developing DB2 applications
      • Setting up DB2 for workstation for Smalltalk development
      • Accessing DB2 services
        • Using the DB2 Smalltalk classes
          • Obtaining an active database connection
          • Cleaning up after yourself
          • Running dynamic SQL
          • Running non-SELECT statements statically
          • Running SELECT statements statically
          • Deleting a row from a table
          • Updating a row in a table
          • Outputting messages
      • Writing SQL statements using Smalltalk
        • Using the Trailblazer browser to code query specs
        • Using embedded SQL to code SQL statements
          • Selecting and running Smalltalk code with embedded SQL
          • Embedding SQL statements
          • Shape statements
          • Host variables
          • SQLCA
          • Restrictions when embedding SQL
      • Using SQL statements in your DB2 application
        • Developing your DB2 application on the workstation
          • Step 1: Creating an SQL statement
          • Step 2: Coding SQL statements into a Smalltalk application
          • Step 3: Precompiling your access sets for DB2 for workstation
          • Step 4: Testing your application on DB2 for workstation
        • Deploying your application on the target environment
          • Step 1: Releasing all classes to your application
          • Step 2: Separating strings in your application
          • Step 3: Making the passive image the current image
          • Step 4: Loading your application into the passive image
          • Step 5: Precompiling your SQL packages for DB2 for MVS
          • Step 6: Binding to create a plan
          • Step 7: Packaging and running your application
      • Developing a DB2 CLI application using nonvisual parts
        • Step 1: Develop the server application
        • Packaging a database server application
        • Running a database application
      • Using stored procedures in a DB2 CLI application
        • An overview of implementing stored procedures
        • The support you need to implement stored procedures
        • Implementing Smalltalk stored procedures
          • Creating the C wrapper DLL
          • Creating the Smalltalk stored procedure
          • Registering the stored procedure
          • Wiring the stored procedure part
        • Important considerations
• 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
        • Temporarily overriding the default error routine
        • Permanently overriding the default error routine
        • Restoring the default error routine
      • 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
        • ODBC.INI
      • For UNIX users
        • ODBC.INI
        • Starting the ODBC Administrator
    • Error messages
      • UNIX error handling
  • Installing an ODBC text database
• GLORP Tutorial
  • Environment
    • A Database
    • Loading GLORP
    • Running GLORP Unit Tests
  • Simplest Possible GLORP Example
  • Simple Object One Table Example
    • Some Table Details
      • Defining Column Types
      • Column or Type Modifiers
    • Primary Keys
      • Explicit Declaration of Primary Key
      • Multi-Column Primary Key
      • Auto-Generated Primary Key
    • Direct Instance Variable Access or Accessor Methods
  • Reading, Writing, Deleting Data
    • Reading
    • Selection Criteria in where clause
    • Deleting
    • Rereading
    • Read Only Mappings
    • Transactions
  • Some Common Problems
    • Cached Values
    • Creating Tables
  • Complex Objects and Tables
    • Instance Variable with a table - One-to-one
      • Duplicate Rows
      • Orphan Rows
      • PseudoVariables
    • Collections as Instance Variable - One-to-many
      • Order of the Email Addresses
      • Specifying the Type of Collection
    • Dictionaries as Instance Variables - One-to-many
    • Embedded Values – Two Objects One table
    • One Object – Multiple Tables
  • Cascading
  • Detached Objects
  • Inheritance and Polymorphic Queries
    • Table for each Concrete Class
    • One Table for all Classes
  • In Brief
    • Advanced Reading - Query Objects for Improved Queries
    • Reading Parts of Objects
    • Dynamic Descriptors
• 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
        • Opening Database connection specifications
      • <<Add
      • New
      • Update
      • Delete
      • Close
      • New database connection specification
        • Opening New database connection specification
      • Connection alias
      • Access set
      • Database Manager
      • Database name
      • Prompt for logon information
      • Update database connection specification
        • Opening Update database connection specification
      • Database logon
        • Opening Database logon
      • User ID
      • Password
      • Server
    • SQL statement editors
      • SELECT Details
        • Opening 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
        • Opening 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
        • Opening 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
        • Opening 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
        • Opening SQL Details
      • Query name (on SQL Details)
      • Description (on SQL Details)
      • SQL Statement
      • Computed Column Details
        • Opening the Computed Column Details window
      • 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
        • Opening SQL Statement
    • SQL clause editors
      • WHERE Details
        • Opening 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
        • Opening GROUP BY Details
      • Columns (on GROUP BY Details)
      • GROUP BY sequence
      • HAVING Details
        • Opening 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
        • Opening the ORDER BY Details window
      • Columns (on ORDER BY Details)
      • ORDER BY sequence
      • Select a query
        • Opening 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
        • Opening 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)
        • Opening Stored Procedure Specifications - Settings
        • Name
        • Call type
        • Description
        • Procedure name
        • Input string label
        • Input/Output variables
      • Stored Procedure Specifications - Settings (ODBC and Oracle)
        • Procedure name
        • Input/Ouput variables
    • Data type dialogs
      • Host variables for
        • Opening Host variables for
      • Host variables for
      • Name
      • Description
      • Edit host variable
        • Opening Edit host variable
      • Host variable type
      • Stored Procedure Host Variable
        • Opening 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 Spec Tab for Multi-row Query
        • Fetch Tab for Multi-row Query
        • Update Tab for Multi-row Query
    • 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

Database Guide

Index