Portable SQL

IN THIS PAGE

SQL is Not a Standard Syntax
Portable SQL
The SQL::Query and SQL::Connection Objects

Description

This page contains a general overview of portable SQL and the SQL::Query and SQL::Connection Objects.

SQL is Not a Standard Syntax

Contrary to popular belief, SQL is not a standard syntax. Some terms:

SQL Syntax: A set of rules describing how a SQL command is to be formatted.
Native SQL: The SQL syntax specific to a particular vendor ("Oracle" syntax or "MySQL" syntax).
Portable SQL: An Alpha Anywhere supported SQL syntax intended to be translated into native SQL for execution.

To solve the problem of the lack of SQL standardization, Alpha developed Portable SQL. Portable SQL is a database-independent, standardized SQL syntax. Using Portable SQL, the same syntax will work across all database platforms. Alpha Anywhere invisibly translates Portable SQL Syntax to Native SQL syntax.

The following is a sample Interactive Window session that shows how different vendors implement common SQL database queries. First is a Portable SQL statement using the CONCATENATE() helper function.

? q.parse("select CompanyName, Concatenate(City, Region), CurrentTime() from customers")
= .T.

Then, the SQL::Query.GenerateNativeSyntax() function generates native SQL for different databases.

? q.generatenativesyntax("{a5api=access}")
= "SELECT CompanyName, City & Region, Time()FROM customers"

? q.generatenativesyntax("{a5api=SQLServer}")
= "SELECT CompanyName, City + Region, CURRENT_TIMESTAMP FROM customers"

? q.generatenativesyntax("{a5api=mysql}")
= "SELECT CompanyName, Concat(City, Region), CurTime()FROM customers"

? q.generatenativesyntax("{a5api=oracle}")
= "SELECT CompanyName, Concat(City, Region), LOCALTIMESTAMP FROM customers"

? q.generatenativesyntax("{a5api=OracleLite}")
= "SELECT CompanyName, Concat(City, Region), SYSDATE FROM customers"

The SQL::Query and SQL::Connection Objects

The SQL::Query Object processes Portable SQL.
The SQL::Connection Object processes both Portable and Native SQL.

The following Interactive Window code illustrates the use of the Query object.

dim q as SQL::Query
dim conn as SQL::Connection
dim SQL as C
dim cs as C
cs = "{A5API='Access', FileName='c:\northwind.mdb'}"
SQL = "select * from customers"
? q.Parse(SQL)
= .T.
? conn.Open(cs)
= .T.
? q.Execute(conn)
= .T.

This code illustrates the use of the Connection object with native SQL.

dim conn as SQL::Connection
dim SQL as C
dim cs as C
cs = "{A5API='Access', FileName='c:\northwind.mdb'}"
SQL = "select top 10 * from customers"
? conn.Open(cs)
= .T.
? conn.Execute(sql)
= .T.

This code illustrates the use of the Connection object with Portable SQL.

dim conn as SQL::connection
? conn.open("{A5API=Oracle, A5ProcedureLanguage=PLSQL, Server='alpha', Port=1522, Service='orcl102', UserName='system', Password='???'}")
= .T.
conn.portableSQLEnabled = .t.
importstatement = "import with replace into 'c:\testimport.dbf' FROM customers"
? conn.execute(importstatement)
= .T.

When you execute a query, you create a SQL::ResultSet Object. The ResultSet object has methods that are used to fetch the data returned by a query.

dim c as SQL::Connection
dim cs as C
dim q as SQL::Query
dim SQL as C
dim rs as SQL::ResultSet
cs = "{A5API='Access', A5Syntax='Access', FileName='C:\Program Files\a5v6\MDBFiles\Alphasports.mdb', UserName='Admin'}"
? c.Open(cs)
= .T.
SQL = "select * from customer"
? q.Parse(SQL)
= .T.
? q.Execute(c)
= .T.
rs = q.ResultSet
A5_SQLResultSetPreview(rs)