Using the SQL Genie

The SQL Genie helps you write SQL expressions. To start the SQL Genie :

  1. Create a SQL::Connection object and open a connection to AlphaSports.mdb.

  2. Pass the SQL::Connection object to the SQL_Query_Builder() function.

dim qry as C

dim conn as SQL::Connection

conn.open("{A5API=Access,FileName='C:\Program Files\a5v8\MDBFiles\Alphasports.mdb',UserName='Admin'}")

qry = sql_query_builder(conn)

This opens the SQL Genie and displays its Tables tab.

  1. Click Add Table to display the Add Table dialog.

  2. Select "Customer" and click OK. When you return to the SQL Genie, you will see the start of your SQL expression in the SQL window. [Picture]

  1. Display the Columns tab.

  2. Select the "FIRSTNAME", "LASTNAME", "PHONE", and "FAX" entries in the Available Columns list and click to move them into the Selected Columns list. Note how the SELECT statement in the SQL window updates to reflect your latest choices. [Picture]

  1. Display the Filter tab.

  2. Click to display the Select Column or Expression dialog.

  3. Select "PHONE" in the Columns list and click OK. Note how the SELECT statement in the SQL window updates to reflect your latest choices. [Picture]

  1. Click to display the Operator list.

  2. Select "is not null". [Picture]

  1. Display the Order tab.

  2. Select "LASTNAME" in the Available Columns list and click .

  3. Select "FIRSTNAME" in the Available Columns list and click . Note how the SQL window displays the latest version of the SQL SELECT statement.

  4. Click OK to return to the Interactive window. [Picture]

  1. Execute the query and list the data you get back. The data contains 4 fields, filtered, and ordered as you specified.

conn.execute(qry)

dim data as C

data = conn.resultset.ToString()

? data

= Winston Abrams (402) 555-6112

Randi Adams (808) 555-6721 (808) 555-7500

John Baker (914) 555-8400

Tom Barber (914) 555-8700

Jeffrey Bernstein (914) 555-0987

George Boschetti (803) 555-6143

...

  1. Close the connection.

conn.close()

Supported By

Alpha Five Version 8 Enterprise Edition

Limitations

Desktop applications only.

See Also

Working with SQL Databases, SQL Genie