Pervasive-Oracle 1
1 Pervasive Software Pervasive-Oracle / Pervasive Oracle Pervasive-Oracle ISV Pervasive-Oracle Pervasive.SQL Oracle 2
Pervasive-Oracle Pervasive-Oracle Pervasive.SQL Oracle Open Database Connectivity ODBC SQL ODBC ODBC 1 API SQL API SQL Pervasive Oracle ODBC ODBC 2.5 Pervasive-Oracle ODBC Level 1 Level 2 A ODBC API API SQL Pervasive-Oracle ANSI 92 SQL ODBC 2.5 SQL Minimum Extended 3 Pervasive-Oracle Minimum SQL Extended B SQL SQL SQL ODBC 2.5 C SQL Pervasive-Oracle Pervasive Oracle Pervasive-Oracle 3
A - ODBC API Pervasive-Oracle ODBC API Microsoft ODBC 2.5 ODBC SQLAllocConnect SQLAllocEnv SQLAllocStmt SQLBindCol SQLBindParameter Level 1 SQLBrowseConnect Level 2 SQLCancel SQLColAttributes SQLColumns Level 1 SQLColumnPrivileges Level 2 SQLConnect SQLDataSources Level 2 SQLDescribeCol SQLDescribeParam Level 2 SQLDisconnect SQLDriverConnect Level 1 SQLDriversd Level 2 SQLError SQLExecDirect SQLExecute SQLFetch SQLForeignKeys Level 2 SQLFreeConnect SQLFreeEnv SQLFreeStmt SQLGetConnectOption Level 1 SQLGetCursorName SQLGetData Level 1 SQLGetFunctions Level 1 SQLGetInfo Level 1 SQLGetStmtOption Level 1 SQLGetTypeInfo Level 1 SQLMoreResults Level 2 SQLNativeSql Level 2 SQLNumResultCols SQLNumParams Level 2 SQLParamData Level 1 SQLPrepare SQLPrimaryKeys Level 2 SQLProcedures Level 2 SQLProcedureColumns Level 2 SQLPutData Level 1 4
SQLRowCount SQLSetConnectOption Level 1 SQLSetCursorName SQLSetStmtOption Level 1 SQLSpecialColumns Level 1 SQLStatistics Level 1 SQLTables Level 1 SQLTablePrivileges Level 2 SQLTransact B - SQL Pervasive-Oracle SQL E SQL 3 DDL DML DDL DDL DDL ALTER TABLE CREATE INDEX CREATE TABLE CREATE VIEW DROP INDEX DROP TABLE DROP VIEW CREATE PROCEDURE. CREATE TRIGGER DROP PROCEDURE DROP TRIGGER GRANT REVOKE 5
DML DML DML DML DELETE INSERT SELECT UPDATE COMMIT ROLLBACK SAVEPOINT 6
C - Pervasive-Oracle / SQL_BIT 1 SQL_TINYINT - signed 3 SQL_TINYINT - unsigned 3 SQL_SMALLINT - signed 5 SQL_SMALLINT - unsigned 5 SQL_INTEGER - signed 10 SQL_INTEGER - unsigned 10 SQL_BIGINT - signed 19 SQL_BIGINT - unsigned 20 SQL_DECIMAL 64 SQL_BINARY 255 SQL_VARBINARY 254 SQL_LONGVARBINARY 20 2G SQL_FLOAT 7 SQL_REAL 7 SQL_DOUBLE 15 SQL_DATE 10 SQL_TIME 8 SQL_TIMESTAMP 19 SQL_CHAR 255 SQL_VARCHAR 254 SQL_LONGVARCHAR 20 2G D - Pervasive-Oracle ODBC Pervasive-Oracle 7
MOD CONCAT LCASE LENGTH LTRIM RTRIM SUBSTRING UCASE CURRENT_TIMESTAMP CURTIME DATABASE IF NULL USER DBNAME USERNAME CONVERT E - Oracle-Pervasive SQL SQL ALTER TABLE ALTER TABLE table-name alter-option-list table-name ::= user-defined-name alter-option-list ::= alter-option ( alter-option [, alter-option ] ) alter-option ::= ADD column -definition ADD table-constraint-definition DROP CONSTRAINT constraint-name [ CASCADE ] DROP PRIMARY KEY [ CASCADE ] column -definition ::= column -name data-type [ DEFAULT default-value ] [ column -constraint [ column -constraint ] ] column -name ::= user-defined-name 8
data-type ::= data-type-name [ ( precision [, scale ] ) ] data-type-name ::= C precision ::= integer scale ::= integer default-value ::= literal column -constraint ::= [ CONSTRAINT constraint-name ] col-constraint constraint-name ::= user-defined-name col-constraint ::= NOT NULL UNIQUE PRIMARY KEY REFERENCES table-name [ ( column -name ) ] [ referential-actions ] referential-actions ::= referential-delete-action referential-delete-action ::= ON DELETE CASCADE table-constraint-definition ::= [ CONSTRAINT constraint-name ] table-constraint table-constraint ::= UNIQUE ( column -name [, column -name ] ) PRIMARY KEY ( column -name [, column -name ] ) FOREIGN KEY ( column -name [, column -name ] ) REFERENCES table-name [ ( column -name [, column -name ] ) ] [ referential-actions ] COMMIT COMMIT [ WORK ] CREATE INDEX CREATE [ UNIQUE ] INDEX index-name ON table-name ( column -name [ ASC DESC ] [, column -name [ ASC DESC ] ] ) index-name ::= user-defined-name CREATE PROCEDURE CREATE PROCEDURE procedure-name ( parameter [, parameter ] ) start-proc-stmt proc-stmt procedure-name ::= user-defined-name parameter ::= parameter-name [ IN OUT IN_OUT ] data-type parameter-name ::= user-defined-name 9
proc-expr ::= IF ODBC start_proc_stmt ::= AS proc- stmt ::= compound-statement CALL procedure-name ( proc-expr [, proc-expr ] ) CASE proc-expr WHEN proc-expr THEN proc-stmt [ ; proc-stmt ] [ WHEN proc-expr THEN proc-stmt [ ; proc-stmt ] ] END CASE CASE WHEN proc-search-condition THEN proc-stmt [ ; proc-stmt ] [ WHEN proc-search-condition THEN proc-stmt [ ; proc-stmt ] ] END CASE cursor-statement DECLARE variable-name data-type [ DEFAULT proc-expr ] delete-statement IF proc-search-condition THEN proc-stmt [ ; proc-stmt ] [ ELSE proc-stmt [ ; proc-stmt ] ] END IF insert-statement loop-statement RETURN [ proc-expr ] transaction-statement select-statement-with-into select-statement assignment-statement update-statement compound-statement ::= [ label-name : ] BEGIN [ ATOMIC ] [ proc-stmt [ ; proc-stmt ] ] END [ label-name ] label-name ::= user-defined-name cursor-name ::= user-defined-name variable-name ::= user-defined-name proc-search-condition ::= search-condition assignment-statement ::= SET variable-name = proc-expr cursor-statement ::= CLOSE cursor-name DECLARE cursor-name CURSOR FOR select-statement [ FOR UPDATE FOR READ ONLY ] DELETE WHERE CURRENT OF cursor-name FETCH cursor-name [ INTO variable-name [, variable-name ] ] OPEN cursor-name UPDATE SET column -name = proc-expr [, column -name = proc-expr ] WHERE CURRENT OF cursor-name loop-statement ::= [ label-name : ] LOOP proc-stmt [ ; proc-stmt ] END LOOP [ label-name ] [ label-name : ] WHILE proc-search-condition DO [ proc-stmt [ ; proc-stmt ] ] END WHILE [ label-name ]. 10
select-statement-with-into ::= SELECT [ ALL DISTINCT ] select-list INTO variable-name [, variable-name ] FROM table-reference [, table-reference ] [ WHERE search-condition ] [ GROUP BY expression [, expression ] [ HAVING search-condition ] ] CREATE TABLE CREATE TABLE table-name ( table-element [, table-element ] ) table-element ::= column -definition table-constraint-definition CREATE TRIGGER CREATE TRIGGER trigger-name before-or-after ins-upd-del ON table-name [ REFERENCING referencing-alias ] FOR EACH ROW [ WHEN proc-search-condition ] proc-stmt trigger-name ::= user-defined-name before-or-after ::= BEFORE AFTER ins-upd-del ::= INSERT UPDATE DELETE referencing-alias ::= OLD [ AS ] correlation-name [ NEW [ AS ] correlation-name ] NEW [ AS ] correlation-name [ OLD [ AS ] correlation-name ] correlation-name ::= user-defined-name CREATE VIEW DELETE CREATE VIEW view-name [ ( column -name [, column -name ] ) ] AS query-specification view-name ::= user-defined-name DELETE FROM table-name [ WHERE search-condition ] DROP INDEX DROP INDEX [ table-name. ] index-name DROP PROCEDURE DROP PROCEDURE procedure-name DROP TABLE DROP TABLE table-name [ CASCADE ]. 11
DROP TRIGGER DROP TRIGGER trigger-name DROP VIEW GRANT DROP VIEW view-name GRANT CREATETAB TO public-or-user-group-name [, public-or-user-group-name ] GRANT table-privilege ON table-name TO user-or-group-name [, user-or-group-name ] table-privilege ::= ALL SELECT [ ( column -name [, column -name ] ) ] UPDATE [ ( column -name [, column -name ] ) ] INSERT DELETE ALTER REFERENCES INSERT public-or-user-group-name ::= PUBLIC user-group-name user-group-name ::= user-name group-name user-name ::= user-defined-name INSERT INTO table-name [ ( column -name [, column -name ] ) ] insert-values insert-values ::= VALUES ( expression [, expression ] ) query-specification ROLLBACK ROLLBACK [ WORK ] [ TO SAVEPOINT savepoint-name ] SAVEPOINT SELECT SAVEPOINT savepoint-name query-specification [ [ UNION [ ALL ] query-specification ] [ ORDER BY order-by-expression [, order-by-expression ] ] order-by-expression ::= expression [ ASC DESC ] 12
query-specification ::= ( query-specification ) SELECT [ ALL DISTINCT ] select-list. FROM table-reference [, table-reference ] [ WHERE search-condition ] [ GROUP BY expression [, expression ] [ HAVING search-condition ] ] select-list ::= * select-item [, select-item ] select-item ::= expression [ [ AS ] alias-name ] table-name. * table-reference ::= table-name [ [ AS ] alias-name ] search-condition ::= search-condition AND search-condition search-condition OR search-condition NOT search-condition ( search-condition ) predicate predicate ::= expression [ NOT ] BETWEEN expression AND expression expression comparison-operator expression-or-subquery expression [ NOT ] IN ( query-specification ) expression [ NOT ] IN ( value [, value ] ) expression [ NOT ] LIKE value expression IS [ NOT ] NULL expression comparison-operator ANY ( query-specification ) expression comparison-operator ALL ( query-specification ) expression comparison-operator SOME ( query-specification ) EXISTS ( query-specification ) comparison-operator ::= < > <= >= = <> expression-or-subquery ::= expression ( query-specification ) value ::= literal USER? expression ::= expression expression expression + expression expression * expression expression / expression ( expression ) -expression +expression column -name? literal set-function scalar-function { fn scalar-function } : user-defined-name set-function ::= COUNT (*) COUNT ( expression ) SUM ( [ ALL ] expression ) AVG ( [ ALL ] expression ) MIN ( [ ALL ] expression ) MAX ( [ ALL ] expression ) 13
UPDATE scalar-function ::= D UPDATE table-name SET column -name = expression [, column -name = expression ] [ WHERE search-condition ] alter-option ::= alter-option-list ::= ADD column -definition ADD table-constraint-definition DROP CONSTRAINT constraint-name [ CASCADE ] DROP PRIMARY KEY [ CASCADE ] alter-option ( alter-option [, alter-option ] ) assignment-statement ::= SET variable-name = proc-expr before-or-after ::= BEFORE AFTER col-constraint ::= NOT NULL UNIQUE PRIMARY KEY REFERENCES table-name [ ( column -name ) ] [ referential-actions ] column -constraint ::= [ CONSTRAINT constraint-name ] col-constraint column -definition ::= column -name data-type [ DEFAULT default-value ] [ column -constraint [ column -constraint ] ] column -name ::= user-defined-name comparison-operator ::= < > <= >= = <> compound-statement ::= [ label-name : ] BEGIN [ ATOMIC ] [ proc-stmt [ ; proc-stmt ] ] END [ label-name ] constraint-name ::= user-defined-name correlation-name ::= user-defined-name cursor-name ::= user-defined-name 14
cursor-statement ::= CLOSE cursor-name DECLARE cursor-name CURSOR FOR select-statement [ FOR UPDATE FOR READ ONLY ] DELETE WHERE CURRENT OF cursor-name FETCH cursor-name [ INTO variable-name [, variable-name ] ] OPEN cursor-name UPDATE SET column -name = proc-expr [, column -name = proc-expr ] WHERE CURRENT OF cursor-name data-type ::= data-type-name [ ( precision [, scale ] ) ] data-type-name ::= C default-value ::= literal expression ::= expression expression expression + expression expression * expression expression / expression ( expression ) -expression +expression column -name? literal set-function scalar-function { fn scalar-function } : user-defined-name expression-or-subquery ::= expression ( query-specification ) index-name ::= user-defined-name ins-upd-del ::= INSERT UPDATE DELETE insert-values ::= VALUES ( expression [, expression ] ) query-specification label-name ::= user-defined-name loop-statement ::= [ label-name : ] LOOP proc-stmt [ ; proc-stmt ] END LOOP [ label-name ] [ label-name : ] WHILE proc-search-condition DO [ proc-stmt [ ; proc-stmt ] ] END WHILE [ label-name ] order-by-expression ::= expression [ ASC DESC ] parameter ::= parameter-name [ IN OUT IN_OUT ] data-type parameter-name ::= user-defined-name 15
precision ::= integer predicate ::= expression [ NOT ] BETWEEN expression AND expression expression comparison-operator expression-or-subquery expression [ NOT ] IN ( query-specification ) expression [ NOT ] IN ( value [, value ] ) expression [ NOT ] LIKE value expression IS [ NOT ] NULL. expression comparison-operator ANY ( query-specification ) expression comparison-operator ALL ( query-specification ) expression comparison-operator SOME ( query-specification ) EXISTS ( query-specification ) proc-expr ::= IF ODBC proc-search-condition ::= search-condition proc- stmt ::= compound-statement CALL procedure-name ( proc-expr [, proc-expr ] ) CASE proc-expr WHEN proc-expr THEN proc-stmt [ ; proc-stmt ] [ WHEN proc-expr THEN proc-stmt [ ; proc-stmt ] ] END CASE CASE WHEN proc-search-condition THEN proc-stmt [ ; proc-stmt ] [ WHEN proc-search-condition THEN proc-stmt [ ; proc-stmt ] ] END CASE cursor-statement DECLARE variable-name data-type [ DEFAULT proc-expr ] delete-statement IF proc-search-condition THEN proc-stmt [ ; proc-stmt ] [ ELSE proc-stmt [ ; proc-stmt ] ] END IF insert-statement loop-statement RETURN [ proc-expr ] transaction-statement select-statement-with-into select-statement assignment-statement update-statement procedure-name ::= user-defined-name public-or-user-group-name ::= PUBLIC user-group-name query-specification ::= ( query-specification ) SELECT [ ALL DISTINCT ] select-list FROM table-reference [, table-reference ] [ WHERE search-condition ] [ GROUP BY expression [, expression ] [ HAVING search-condition ] ] referencing-alias ::= OLD [ AS ] correlation-name [ NEW [ AS ] correlation-name ] NEW [ AS ] correlation-name [ OLD [ AS ] correlation-name ] 16
referential-actions ::= referential-delete-action referential-delete-action ::= ON DELETE CASCADE scalar-function ::= D scale ::= integer search-condition ::= search-condition AND search-condition search-condition OR search-condition NOT search-condition ( search-condition ) predicate select-item ::= expression [ [ AS ] alias-name ] table-name. * select-list ::= * select-item [, select-item ] select-statement-with-into ::= SELECT [ ALL DISTINCT ] select-list INTO variable-name [, variable-name ] FROM table-reference [, table-reference ] [ WHERE search-condition ] [ GROUP BY expression [, expression ] [ HAVING search-condition ] ] set-function ::= COUNT (*) COUNT ( expression ) SUM ( [ ALL ] expression ) AVG ( [ ALL ] expression ) MIN ( [ ALL ] expression ) MAX ( [ ALL ] expression ) start_proc_stmt ::= AS table-constraint ::= UNIQUE ( column -name [, column -name ] ) PRIMARY KEY ( column -name [, column -name ] ) FOREIGN KEY ( column -name [, column -name ] ) REFERENCES table-name [ ( column -name [, column -name ] ) ] [ referential-actions ] table-constraint-definition ::= [ CONSTRAINT constraint-name ] table-constraint table-element ::= column -definition table-constraint-definition table-name ::= user-defined-name table-privilege ::= ALL SELECT [ ( column -name [, column -name ] ) ] UPDATE [ ( column -name [, column -name ] ) ] INSERT DELETE ALTER REFERENCES 17
table-reference ::= table-name [ [ AS ] alias-name ] trigger-name ::= user-defined-name user-group-name ::= user-name user-name ::= user-defined-name value ::= literal USER? variable-name ::= user-defined-name. view-name ::= user-defined-name [ Document End ] 18