Original PDF Flash format Using-SQL-and-Backend-Databases-in-Alpha-Five  


Using SQL And Backend Databases In Alpha Five

Using SQL and Backend Databases in Alpha Five
Alpha Five® Version 8 makes the development of powerful SQL-based database
applications and reports fast and easy. Secure web applications with full
read/write and reporting capabilities can be built quickly against backend
databases such as Microsoft SQL Server®, DB2®, MySQL®, Cache®, Sybase®,
Oracle® and even Microsoft Access®, Microsoft Excel® and Quickbooks®.
The efficiency and usability that developers experience in using Alpha Five
Version 8 is achieved through the use of highly productive reusable components
whenever possible - and the use of Xbasic whenever necessary for any custom
part of the application.
In addition to a wide array of visual tools for working with SQL data, Alpha Five
Version 8 introduces “Data Access Objects” (Alpha DAO) which integrate tightly
with the development environment and application server to give you virtually
unlimited control over your SQL data.
This document covers the ways in which Alpha Five version can work with data
in a variety of remote backend databases.
Using Remote (SQL) Data Sources and Backends in Alpha Five
1
Database Explorer
The Database Explorer is a new tool in Version 8. It lets you see your tables,
their structure, and explore the data as well.

From the Database Explorer you can:
Add a database to the Database Explorer list.
Count table rows in a backend SQL database table.
Create a report, label, or letter layout for a backend SQL database table.
Create a passive-link table based on a backend SQL database table.
Using Remote (SQL) Data Sources and Backends in Alpha Five
2
Create an SQL expression by calling the SQL Command Window or SQL
Genie.
Create an Alpha Five .DBF table with the data from a backend SQL
database table.
Display a column's data from a backend SQL database table.
Display data from a backend SQL database table.
Generate SQL syntax for a backend SQL database table.
Open an Alpha Five layout for any Alpha Five table or set.
Remove a database from the Database Explorer listing.
Remove tables or views from the Database Explorer listing.
View the structure of any Alpha Five set
View the properties of any table field.
SQL Genie
The SQL Genie helps you create sophisticated multi-table SQL statements
through a point and click interface. You can create, test, and save SQL
statements that will retrieve data from SQL backend databases.
It operates as a two-way tool. You visually can build your query and see the SQL
that is automatically generated, or you can type in the SQL and the Genie will
“translate” that into the visual display.
Alpha Five supports the following databases:
FlexQuarters® QODBC
IBM® DB2
InterSystems® Cache
Microsoft
Access
Microsoft
Excel
Microsoft SQL Server
MySQL
Oracle XE, 9i, 10g
Using Remote (SQL) Data Sources and Backends in Alpha Five
3
Oracle Lite (9i, 10g)
Quantel®
Sybase Adaptive Server Enterprise
Sybase
SQLAnywhere®
In addition, you can use database for which you have an ODBC driver installed,
even if the database doesn't appear in the list above.
The following screenshots shows the SQL Genie in action. In this first screen, the
Customers, Orders and Shippers table have been joined together on the Tables
tab of the genie.


On the Columns tab, you select the fields you want to include
Using Remote (SQL) Data Sources and Backends in Alpha Five
4

You use the filter to select the records you want to include.

As you point and click, Alpha Five creates the SQL statement for you in the lower
pane.
Using Remote (SQL) Data Sources and Backends in Alpha Five
5
SQL Report Builder
The report builder lets you publish reports as PDF files or on the web or in print
on a printer. You can build reports against a backend data source for which a ,
including: FlexQuarters QODBC, IBM DB2, InterSystems Cache, Microsoft
Access, Microsoft Excel, Microsoft SQL Server, MySQL, Oracle XE, 9i, 10g,
Oracle Lite (9i, 10g), Quantel, Sybase Adaptive Server Enterprise, Sybase
SQLAnywhere as well as DBF files and data sources for which you have an
ODBC driver installed.

The report builder creates Alpha Five Reports, labels and mail-merges using live
data from remote databases. Plus you can combine data from multiple sources
(like Access, Excel, MS SQL Server, MySQL) into your reports.
SQL Query Genie with Portable SQL
Allows you to define highly sophisticated SQL statements by pointing and
clicking. The SQL that is produced is automatically adapted so that it works with
the backend database engine you are using.
Disconnected Table Caching
As long as you have connected once to your data source, Alpha Five can keep a
cached copy of data that you can use to design your reports. This means you
don’t have to be connected to the database (or even a network) to modify your
report design. This is great for notebook and laptop users, who don’t always have
constant connectivity.
Using Remote (SQL) Data Sources and Backends in Alpha Five
6
Automate Using Xbasic
Xbasic commands allow you to automate all aspects of printing your enterprise
reports, including supplying runtime filter, and order statements and parameter
values.
Passive-Linked Tables
Alpha Five lets you create tables using data from remote database sources,
which is ideal for reporting or analysis applications on remote data. You can
refresh data at any time by right clicking on the table or via Action-Scripting.
Passive-linked tables allow you to create reports that combine data from different
data sources. For example, you could create a P&L report that uses sales data
from a SQL Server database and expense data from an Excel spreadsheet.
Web Component Builder
Generate and customize sophisticated web pages and reusable components
based on the data from your queries.
Forget about tedious coding to build web applications against SQL databases.
Through an innovative point and Click approach, you define a series of re-
useable components to rapidly Generate and customize sophisticated web pages
and applications and reusable components based on the data from your queries.


Using Remote (SQL) Data Sources and Backends in Alpha Five
7


Complete Web Security Framework
Define and manage access to your application by user, group and web page.
The Security Framework allows you to easily add robust security to your web
application. It creates log in pages for users to log into the application and it lets
you specify security for each page in your application.
Using Remote (SQL) Data Sources and Backends in Alpha Five
8


Web Application Security Components
Given the importance of security for your Internet or intranet applications, Version
8 comes with a complete web security framework that does not require any
coding. Yet it still gives you enormous control over setting up security for your
applications, ensuring that only authorized people are viewing or editing the data.
You set your security parameters with the Security Settings, Page Security
Assignment, and Login Component genies, then the Application Server tests
every page and file request and handles all of the security internally. You may
enable or disable security with a single setting in the Application Server Control
Panel. Security settings are unique to each project.
A user may belong to one or more groups. You may grant access to a page to
one or more groups. Whether or not a user belongs to a group depends on
whether it is important to selectively provide access to specific pages.
Data Access Objects (Alpha DAO)
Alpha DAO is a high level family of objects (each with properties and methods),
that make it possible to read from and write to SQL backend databases. Scripting
SQL was never this easy. Execute queries with named arguments, manage
result sets, and import and export data all with a few lines of code. With Version
8, you have Xbasic program access to SQL databases.
This functionality extends familiar Alpha Five database features that previously
supported only .DBF files. AlphaDAO provides the following SQL objects. Each
object has properties and methods.
Using Remote (SQL) Data Sources and Backends in Alpha Five
9
SQL::Argument
SQL::Arguments
SQL::CallResult
SQL::Connection
SQL::DataTypeInfo
SQL::IndexColumnInfo
SQL::IndexInfo
SQL::ResultSet
SQL::Row
SQL::Schema
SQL::TableInfo

The screen below is a desktop example using AlphaDAO to write to a SQL
Database. Alpha Five Version 8 web components take advantage of AlphaDAO.
Using Remote (SQL) Data Sources and Backends in Alpha Five
10

Portable SQL
Lets you create SQL that is automatically converted to the syntax of the database
you connect to at run-time: The benefit of Portable SQL is that your applications
become easily transportable across multiple SQL back end databases.
Includes literal values, naming formats named arguments, portable functions.
Named argument support works with native SQL syntax too.
With tools like these, you will see results early and maintain your site easily. As
the complexity of your web site grows, Alpha Five has the depth, power and
extensibility to continue meeting your needs.
Using Remote (SQL) Data Sources and Backends in Alpha Five
11
For details see:
support.alphasoftware.com/alphafivehelpVersion
8/AlphaDAO/Portability_Functions.htm


Portable SQL in Action
Below shows a simple Portable SQL statement in which the FIRSTNAME field is
added to the LASTNAME field to create a new field alias called Name. You'll see
that in Portable SQL, the Concatenate() function is used. This is the same syntax
used in MySQL (shown in the lower picture). But in the upper picture, DB2 is
selected as the backend database and so concatenate() is replaced with ||, which
is the correct Syntax for DB2. With Alpha Five, you don't need to learn different
SQL for different backends. The SQL you create is portable.

Using Remote (SQL) Data Sources and Backends in Alpha Five
12


Xbasic New functions for working with SQL Backends
Create multiple passive-link tables based on data in a SQL backend
database.
Create a new layout based on a DataSource definition.
Update the data in a 'Passive-Link' table.
Display up to 100 rows of data stored in a SQL::ResultSet object.
Import multiple standard Alpha Five .DBF tables from a backend
database.
Import the data specified by a Data Source name to an Alpha Five table.
Display the SQL Genie to help you define a SQL SELECT statement.
Display up to 100 rows from a SQL::ResultSet.
Copy a local .DBF table to a backend database.
Using Remote (SQL) Data Sources and Backends in Alpha Five
13
Create an SQL::TableInfo object from a local .DBF table.
Create a local table from an SQL::TableInfo object.

Using Remote (SQL) Data Sources and Backends in Alpha Five
14