|
Course VII — SQL Programming Series
Covers all the Most Popular SQL Backends
Course 7 of the Training on Demand series starts in with a with a refresher on SQL and then takes you all the way to expert level. For each backend the course covers (MS SQL, SQL Anywhere, Oracle, Firebird, PostgreSQL, MySQL, Pervasive SQL, and ODBC to connect to any backend) the course discusses each backends advantages and disadvantages, application design best practices, customizing connections using Driver strings, the use of Prop:SQL to implement direct SQL Queries (both how-to and when-to), the use of TurboSQL for optimal performance, how to call Stored Procedures, the differences between server-side Triggers and client-side Triggers, proper Date-Time field handling, designing the proper KEY and INDEX files, and optimizing Browses and update Forms.
Module |
Topic Outline |
Session 1: Introduction to SQL
What is SQL? How can I use SQL with Clarion? Understand the general terminology used with SQL, the declarative statements common to all SQL databases. Develop an understanding of SQL and the statements necessary to effectively retrieve information from SQL supported databases. Be familiar with the Clarion language support for SQL. Understand the common techniques in Clarion for communicating with a target SQL table, and much more. |
- Introduction to SQL
- Why Use SQL?
- Which SQL backend should I Use?
- A Quick introduction to SQL syntax
- Data Manipulation Language (DML)
- Data Definition Language (DDL)
- Data Control Language (DCL)
- Common SQL Functions
- Summarizing information: Aggregates, Totals, and SubTotals
- SQL Extensions - Programming Constructs
- Stored Procedures
- Beyond the SQL Standards
- How a SQL Query is Processed
- An SQL Starter Tutorial
- Using SQL with Clarion Applications
- Clarion Language Support for SQL
- Driver Communication Techniques
- Handling NULLs
- Summary & Self Test
- Flash Movies
- How to use this Interactive Self-Study System
- Course 7 Introduction
|
Session 2 — IDE SQL Tools
Be familiar with the Clarion IDE tools available when accessing SQL tables, including: Built-In Driver and Dictionary Support, Driver Strings, OWNER (Connection) Strings, User Options, Column Options (Driver Switches), External Name, Relationships (Server Side), File Import and Conversion Wizard, DCT Synchronizer, Application Template Support, Extended Browse Options, Template Query Tools, Add-On Driver Support, Understand the process to use when migrating ISAM tables to a target SQL database. |
- IDE SQL Tools
- Introduction
- Lesson Objectives
- Getting Started
- Dictionary Support
- File Import and Conversion Wizards
- SQL Table Properties
- The Driver Owner String Builder
- SQL Column and Key Properties
- User Options
- Relationships and Triggers
- Dictionary Synchronizer
- The Dictionary Property Editor
- SQL Application Support
- Template Supported SQL Queries
- Template Support for SQL Aggregate Functions
- Template Query Tools
- SoftVelocity's Power Drivers with SQL
- Tutorial
- Converting from ISAM to SQL
- Convert the Data Definitions (Create
- an SQL Script)
- Converting the data
- Lesson Summary and Self Test
- Flash Movies
- Single File Convert
- QBE Button Control Template
- QBE List Control Template
|
Session 3: Clarion and Microsoft SQL
A detailed look at Clarion's support of Microsoft SQL. History and Overview of Microsoft SQL. Advantages and Disadvantages using Microsoft SQL. Installation, Client and Server Configuration. Importing tables into Clarion. Application Best Practices. Customizing using Driver Strings. Targets latest SQL Server 2005 version, including MARS and Identity support. |
- Clarion and Microsoft SQL
- Introduction
- A Brief Overview of Microsoft SQL
- Getting Started
- Configuring the Server
- Configuring the Client
- Importing Tables
- Customizing the Table Properties
- Choosing the Best Client/Server
- Data Types
- Creating the right KEY and INDEX files
- Date-Time field Handling
- Importing the new SQL Server 2005 Schemas
- Stored Procedures
- Triggers
- Using TurboSQL
- Tips for working with Microsoft SQL
- Optimizing Browse Procedures
- Example Application
- Summary & Self-Test
- Flash Movies
- Creating a Trigger
- Example Program Part I
- Example Program Part II
|
Session 4: Clarion and Pervasive.SQL
A detailed look at Clarion's support of Pervasive.SQL. History and Overview of Pervasive.SQL. Advantages and Disadvantages using Pervasive.SQL. Installation, Client and Server Configuration. Importing tables into Clarion. Application Best Practices. Customizing using Driver Strings and use of PROP:SQL with Stored Procedures.
|
- Clarion and Pervasive.SQL
- Introduction
- Lesson Objectives
- A Brief History of Pervasive.SQL
- Getting Started
- Configuring the Server
- Configuring the Client
- Importing Tables
- Customizing the Table Properties
- Choosing the Best Client/Server Data
- Creating the Right KEY and INDEX files
- Date-Time Field Handling
- Stored Procedures
- Triggers
- Using TurboSQL
- Avoidiing Pitfalls
- Example Applications
- Summary & Self-Test
- Flash Movies
- How to use this Interactive Self-Study System
- New Database
- New Table via SQL
|
Session 5: Clarion and Oracle
A detailed look at Clarion's support of Oracle. History and Overview. Installation, Client and Server Configuration. Importing tables into Clarion. Customizing using Driver Strings and PROP:SQL. Optimize the data dictionary to use only the fields you need, clarify key names, manipulate dates. Adding the 'IsIdentity' Column Option. Client-Side Triggers. Application Best Practices. Use native SQL code as filter criteria with SQL().Use Group By, Order By, and Aggregate Functions. Much more!
|
- Clarion and Oracle
- Introduction
- Lesson Objectives
- A Brief History of Oracle
- Getting Started
- Configuring the Server
- Configuring the Client
- Importing Tables
- Optimization of the Dictionary
- Creating the right KEY and INDEX files
- Date-Time field Handling
- Stored Procedures
- Triggers
- Example Application
- Tips and Pitfalls
- Summary & Self-Test
- Flash Movies
- Oracle Example Application; putting it all together
|
Session 6: Clarion and SQL Anywhere (Sybase)
A detailed look at Clarion's support of Sybase. Overview of Sybase. Advantages and Disadvantages using Sybase. Installation, Client and Server Configuration. Importing tables into Clarion. Application Best Practices. Customizing using Driver Strings, PROP:SQL and Stored Procedures.
|
- Clarion and SQL Anywhere (Sybase)
- Introduction
- Lesson Objectives
- A Brief History of SQL Anywhere
- Getting Started
- Configuring the Server
- Configuring the Client
- Importing Tables
- Choosing the Best Client/Server Data
- Creating the right KEY and INDEX files
- Date-Time field Handling
- Stored Procedures
- Triggers
- SQL Anywhere with Prop:SQL
- Using TurboSQL
- Tips and Pitfalls
- Summary & Self Test
- Flash Movies
- How to use this Interactive Self-Study System
|
|
