TOCPREVNEXTINDEX

AcuODBC User's Guide
Version 6.0

5.3 SQL Syntax Supported by AcuODBC

According to specification, ODBC programs retrieve data exclusively through Structured Query Language (SQL) queries or commands. For this reason, one of the functions performed by AcuODBC is to translate SQL queries into calls that can be executed and interpreted by the COBOL data file system.

SQL is a very complex language, and most ODBC drivers do not support every SQL command. AcuODBC supports the most common commands needed to write a query: SELECT, INSERT, UPDATE, and DELETE. In addition to these commands, AcuODBC recognizes many SQL reserved words and supports many functions. These are listed in section 5.5, "SQL Reserved Words Recognized by AcuODBC," and section 5.6, "Functions Supported by AcuODBC."

The following section describes the SQL syntax of the four commands supported by AcuODBC and uses the following conventions:

Upper-Case

UPPER-CASE WORDS are keywords.

Brackets

Brackets ( [ ] ) enclose optional elements.

Ellipses

Ellipses (. . .) indicate repetition. The element immediately preceding an ellipsis may be repeated any number of times.

Other Characters

All the other characters, such as commas and dots, are required when they appear.

Numeric and Non-numeric Literals

A numeric literal is a string containing

Non-numeric literal
Interpretation
'Altman''s Lane'
Altman's Lane
''''
''

Date and Timestamp Literals

A date literal has the following format, where YYYY is the year expressed as a four-digit number and MM and DD are the month and day, respectively, expressed as two-digit numbers:

    {d 'YYYY-MM-DD'}   

For example, the following date literal represents June 18, 2001:

    {d '2001-06-18'}   

A timestamp literal is similar, but it indicates the time as well as the date. Use one of three formats for timestamp literals, depending the precision required:

    {ts 'YYYY-MM-DD HH:NN:SS'}   
    {ts 'YYYY-MM-DD HH:NN:SS.TT'}   
    {ts 'YYYY-MM-DD HH:NN:SS.TTTT'}   


Note: HH, NN, and SS are the hour, minute, and second, respectively, expressed as two-digit numbers. TT is the number of hundredths of a second expressed as a two-digit number, and TTTT is hundredths of seconds expressed as a four-digit number. This is based on a 24-hour clock, so there is no need to designate either "AM" or "PM".

For example, the following timestamp literal represents a time 57.1234 seconds after 5:42 PM on March 28, 2001:

    {ts '2001-03-28 17:42:57.1234'}   


Note: This section is intended to provide a brief introduction to SQL and to using AcuODBC. It is not intended to provide an all-encompassing description of SQL. Please refer to any of the currently available reference books for additional information on SQL and relational databases.

Sample Files

The examples in this chapter were developed using ACUCOBOL-GT and Microsoft Access 2000. In addition, the data has been linked, rather than imported. See section 4.1.3, "Accessing Data From Access 97 and 2000," for more information on linking versus importing and for information on bringing external data into the database.

This section contains examples based on four of the sample files shipped with AcuODBC ("accounts", "clients", "codes", and "pets"). These are part of the AcuODBC Datasource DSN included with AcuODBC. You may want to enter the queries in this chapter or experiment with some of your own. Note that the syntax of queries may be different if you are working in a different ODBC-enabled application.

If you haven't done so already, open the application that you want to use and perform the necessary steps to access the data in the sample files. If you are using Microsoft Access 2000, the procedure is as follows:

  1. Start Microsoft Access.
  2. Create a new database or open an existing one. If you create a new database, name and save it.
  3. Select Get External Data and then either Import or Link Tables from the File menu.
  4. Select ODBC Databases from the "Files of type:" drop-down list in the Import dialog box.
  5. Select AcuODBC Datasource from the Select Data Source dialog box.
  6. Select Select All from the Link Tables dialog box. Click OK.

The tables created by the sample COBOL files, and used in this chapter, appear in Access as follows.

Accounts table ("accounts")

Clients table ("clients")

Codes table ("codes")

Pets table ("pets")


Note: AcuODBC automatically converts hyphens to underscores; however, to improve performance, use underscores rather than hyphens in the names of tables.


Acucorp, Inc.
Voice: (800) 262-6585 (U.S.A. and Canada)
Voice: (858) 689-4500
Fax: (858) 689-4550

Please share your comments on this manual
or on any Acucorp documentation with the
Acucorp Communications Department.
TOCPREVNEXTINDEX
1988-2003 Acucorp, Inc.
All rights reserved.