ODBC SQL syntax

This section lists ODBC SQL syntax supported by USoft applications.

Queries and manipulations

SELECT     ...
FROM       ...
WHERE      ...
GROUP BY   ...
HAVING     ...
ORDER BY   ...

SELECT     ...
UNION
SELECT     ...

(if supported:)

SELECT     ...
FROM       ...
WHERE      ...
FOR UPDATE OF *columns*

INSERT INTO  ...
VALUES (     ...  )

INSERT INTO  ...
SELECT       ...

UPDATE       ...
SET          *column* = *expression*
,            ...
WHERE        ...

DELETE FROM  ...
WHERE        ...

Authorisation

GRANT       { ALL | SELECT | INSERT | UPDATE { *columns* | } | DELETE }
ON          ...
TO          ...

REVOKE      { ALL | SELECT | INSERT | UPDATE { *columns* | } | DELETE }
ON          ...
FROM        …

Data definition

CREATE TABLE   *table*
(     *column**data-type*   { NULL | NOT NULL }
,     ...
)

DROP TABLE      *table*

CREATE { UNIQUE | } INDEX  *index*
ON     column { ASC | DESC | }
[,     ... ]

DROP INDEX  *index*

CREATE VIEW    *view*
(     *column**data-type*   { NULL | NOT NULL }
,     ...
)
AS SELECT ...

DROP VIEW      *view*

(If supported:)

ALTER TABLE   *table*
...

RENAME        *table*
{ TO | }      ...

ODBC has no prescribed syntax for renaming a table. Most RDBMSs however are capable of renaming a table. To make optimal use of the RDBMS capabilities, USoft uses the above ALTER / RENAME TABLE syntax formats when creating application tables and "remembers" which syntax format succeeded, so it will be used from then on. When none of the above syntax's are available, a new (changed) table is created and the old table data inserted, then the old table is dropped.