This section sets out the ODBC and SQL conformance for the Easysoft ODBC-SQI SQL Engine, listing the ODBC API function calls and the SQL components that are supported.
Refer to this section if you are using the Easysoft ODBC-SQI SQL Engine in conjunction with (for example):
This section does not describe ODBC API calls or the different components of SQL.
If you require more detailed information about implementing ODBC and SQL, the following publications may be of interest to you:
For more general information about ODBC and SQL, please refer to the many reference books available on these subjects.
ODBC API function calls can be used by programmers to enable applications to connect to data in databases.
By including an SQL statement as an argument of an ODBC function call, programmers can also develop features that allow end users to manipulate data in their databases.
| The Purpose column in the following tables is taken from the Microsoft ODBC 3.0 Programmer's Reference Volumes 1 and 2 (Microsoft Press, 1997). |
The Easysoft ODBC-SQI SQL Engine provides the following ODBC API function calls:
| SQLAllocHandle | Core | Obtains an environment, connection, statement or descriptor handle. |
| SQLBindCol | Core | Assigns storage for a result column and specifies the data type. |
| SQLBindParameter | Core | Assigns storage for a parameter in an SQL statement. |
| SQLBrowseConnect | Level 1 | Returns successive levels of connection attributes and valid attribute values. When a value has been specified for each connection attribute, connects to the data source. |
| SQLCancel | Core | Cancels an SQL statement. |
| SQLCloseCursor | Core | Closes a cursor that has been opened on a statement handle. |
| SQLColAttribute | Core | Describes attributes of a column in the results set. |
| SQLColumnPrivileges | Level 2 | Returns a list of columns and associated privileges for one or more tables. |
| SQLColumns | Core | Returns the list of column names in specified tables. |
| SQLConnect | Core | Connects to a specific driver by data source name, user ID and password. |
| SQLCopyDesc | Core | Copies descriptor information from one descriptor handle to another. |
| SQLDescribeCol | Core | Describes a column in the result set. |
| SQLDescribeParam | Level 2 | Returns the description for a specific parameter in a statement. |
| SQLDisconnect | Core | Closes the connection. |
| SQLDriverConnect | Core | Connects to a specific driver by connection string or requests that the Driver Manager and driver display connection dialog boxes for the user. |
| SQLEndTran | Core | Commits or rolls back a transaction. |
| SQLExecDirect | Core | Executes a statement. |
| SQLExecute | Core | Executes a prepared statement. |
| SQLFetch | Core | Returns multiple result rows. |
| SQLFetchScroll | Core | Returns scrollable result rows. |
| SQLForeignKeys | Level 2 | Returns a list of column names that make up foreign keys, if they exist for a specified table. |
| SQLFreeHandle | Core | Releases an environment, connection, statement or descriptor handle. |
| SQLFreeStmt | Core | Ends statement processing, discards pending results, and, optimally, frees all resources associated with the statement handle. |
| SQLGetConnectAttr | Core | Returns the value of a connection attribute |
| SQLGetCursorName | Core | Returns the cursor name associated with a statement handle. |
| SQLGetData | Core | Returns part or all of one column of one row of a result set (useful for long data values). |
| SQLGetDescField | Core | Returns the value of a single descriptor field. |
| SQLGetDescRec | Core | Returns the values of multiple descriptor fields |
| SQLGetDiagField | Core | Returns additional diagnostic information (a single field of the diagnostic data structure). |
| SQLGetDiagRec | Core | Returns additional diagnostic information (multiple fields of the diagnostic data structure). |
| SQLGetEnvAttr | Core | Returns the value of an environment variable. |
| SQLGetFunctions | Core | Returns supported driver functions |
| SQLGetInfo | Core | Returns information about a specific driver and data source |
| SQLGetStmtAttr | Core | Returns the value of a statement attribute. |
| SQLGetTypeInfo | Core | Returns information about supported data types. |
| SQLMoreResults | Level 1 | Determines whether there are more result sets available and, if so, initializes processing for the next result set. |
| SQLNativeSql | Core | Returns the text of an SQL statement as translated by the driver. |
| SQLNumParams | Core | Returns the number of parameters in a statement. |
| SQLNumResultCols | Core | Returns the number of columns in the result set. |
| SQLParamData | Core | Used in conjunction with SQLPutData to supply parameter data at execution time. (Useful for long data values.) |
| SQLPrepare | Core | Prepares and SQL statement for later execution. |
| SQLPrimaryKeys | Level 1 | Returns the list of column names that make up the primary key for a table. |
| SQLProcedureColumns | Level 1 | Returns the list of input and output parameters, as well as the columns that make up the result set for the specified procedures. |
| SQLProcedures | Level 1 | Returns the list of procedure names stored in a specific data source. |
| SQLPutData | Core | Sends part or all of a data value for a parameter. (Useful for long data values.) |
| SQLRowCount | Core | Retuns the number of rows affected by an insert, update or delete request. |
| SQLSetConnectAttr | Core | Sets a connection attribute |
| SQLSetCursorName | Core | Specifies a cursor name. |
| SQLSetDescField | Core | Sets a single descriptor field. |
| SQLSetDescRec | Core | Sets multiple descriptor fields. |
| SQLSetEnvAttr | Core | Sets an environment variable. |
| SQLSetPos | Level 1 | Positions a cursor within a fetched block of data, and allows an application to refresh data in the rowset, or update or delete data in the result set. |
| SQLSetStmtAttr | Core | Sets a statement attribute. |
| SQLSpecialColumns | Core | Returns information about the optimal set of columns that uniquely identifies a row in a specified table, or the columns that are automatically updated when any value in the row is updated by a transaction. |
| SQLStatistics | Core | Returns statistics about a single table and the list of indexes associated with the table. |
| SQLTablePrivileges | Level 2 | Returns a list of tables and the privileges associated with each table. |
| SQLTables | Core | Returns the list of table names stored in a specific data source. |
The following functions are provided by the ODBC Driver Manager:
| SQLDataSources | Core | Returns the list of available data sources |
| SQLDrivers | Core | Returns the list of installed drivers and their attributes |
The following functions are provided by the Setup DLL:
| SQLConfigDriver | Core | Loads the driver-specific setup DLL. |
| SQLConfigDSN | Core | Adds, modifies or deletes a data source. |
The following functions have been superceded, but are still supported by theEasysoft ODBC-SQI SQL Engine:
| SQLAllocConnect | Core | ODBC 2.x function superseded by SQLAllocHandle |
| SQLAllocEnv | Core | ODBC 2.x function superseded by SQLAllocHandle |
| SQLError | Core | ODBC 2.x function superseded by SQLGetDiagRec |
| SQLGetConnectOption | Core | ODBC 2.x function superseded by SQLGetConnectAttr |
| SQLSetParam | Core | ODBC 1 function, superseded by SQLBindParameter |
The following function is currently not supported, but are planned for future release:
| SQLBulkOperations | Level 1 | Performs bulk insertions and bulk bookmark operations, including update, delete and fetch by bookmark. |
The Easysoft ODBC-SQI SQL Engine supports the following SQL statements:
The Table reference list in a SELECT query can contain all or any of:
The Easysoft ODBC-SQI SQL Engine supports the following constructs:
Correlated and non-correlated subqueries
TRIM, SUBSTRING, CHARACTER_LENGTH, BIT_LENGTH, OCTET_LENGTH and POSITION value functions
The SQL-92 CAST function and the ODBC CONVERT function for conversion between compatible data types.
SELECT * from x LEFT OUTER JOIN y ON x.a = y.a
SELECT * from {oj x LEFT OUTER y ON x.a = y.a }
The Easysoft ODBC-SQI SQL Engine supports the following predicates:
Subqueries in predicates can be correlated or non-correlated:
SELECT a FROM b WHERE c = ALL ( SELECT x FROM y WHERE z = a )
SELECT a FROM b WHERE c = ALL ( SELECT x FROM y WHERE z = 12 )
The Easysoft ODBC-SQI SQL Engine provides all the functions required by ODBC and also functions from SQL92.
Functions can be specified in SQL92 or ODBC format.
SELECT CURRENT_DATE, EXTRACT( YEAR FROM
Employee.data_of_birth ) FROM Employee
SELECT {fn CURRENT_DATE()}, {fn EXTRACT( YEAR
FROM Employee.data_of_birth )} FROM Employee
| The Description column in the following tables is taken from the Microsoft ODBC 3.0 Programmer's Reference. |
The following functions are supported by the Easysoft ODBC-SQI SQL Engine:
| ASCII(string_exp) | Returns the ASCII code value of the leftmost character of string_exp as an integer. |
| BIT_LENGTH(string_exp) | Returns the length in bits of the string expression. |
| CHAR(code) | Returns the character that has the ASCII code value specified by code. The value of code should be between 0 and 255, otherwise the return value is data source-dependent. |
| CHAR_LENGTH(string_exp) | Returns the length in characters of the string expression, if the string expression is of a character data type, otherwise returns the length in bytes of the string expression (the smallest integer not less than the number of bits divided by 8). This is the same function as CHARACTER_LENGTH. |
| CHARACTER_LENGTH | See CHAR_LENGTH |
| CONCAT(string_exp1, string_exp2) | Returns a character string that is the result of concatenating string_exp2 to string_exp1. The resulting string is DBMS-dependent. |
| DIFFERENCE(string_exp1, string_exp2) | Returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2. |
| INSERT(string_exp1, start, length, string_exp2) | Returns a character string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp, beginning at start. |
| LCASE(string_exp) | Returns a string equal to that in string_exp with all uppercase characters converted to lowercase. |
| LEFT(string_exp, count) | Returns the leftmost count characters of string_exp. |
| LENGTH(string_exp) | Returns the number of characters in string_exp, excluding trailing blanks. |
| LOCATE(string_exp1, string_exp2[,start]) | Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first character position in string_exp2 unless the optional argument, start, is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_exp2 is indicated by the value1. If string_exp1 is not found within string_exp2, the value 0 is returned. |
| LTRIM(string_exp) | Returns the characters of string_exp, with leading blanks removed. |
| OCTET_LENGTH(string_exp) | Returns the length in bytes of the string expression. The result is the smallest integer not less than the number of bits divided by 8. |
| POSITION(char_exp IN char_exp) | Returns the position of the first character expression in the second character expression. The result is an exact numeric with an implementation-defined precision and a scale of 0. |
| REPEAT(string_exp, count) | Returns a character string composed of string_exp repeated count times. |
| REPLACE(string_exp1, string_exp2, string_exp3) | Search string_exp1 for occurrences of string_exp2 and replace with string_exp3. |
| RIGHT(string_exp, count) | Returns the rightmost count characters of string_exp. |
| RTRIM(string_exp) | Returns the characters of string_exp with trailing blanks removed. |
| SOUNDEX(string_exp) | Returns a data source-dependent character string representing the sound of the words in string_exp. |
| SPACE(count) | Returns a character string consisting of count spaces. |
| SUBSTRING(string_exp, start, length) | Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters. |
| TRIM | This is an SQL92 version of the ODBC LTRIM/RTRIM functions. |
| UCASE(string_exp) | Returns a string equal to that in string_exp with all lowercase characters converted to uppercase. |
| CURRENT_DATE() | Returns the current date. |
| CURRENT_TIME[(time-precision)] | Returns the current local time. The time-precision argument determines the seconds precision of the returned value. |
| CURRENT_TIMESTAMP[(timestamp-precision)] | Returns the current local date and local time as a timestamp value. The timestamp-precision argument determines the seconds precision of the returned timestamp. |
| CURDATE() | Returns the current date. |
| CURTIME() | Returns the current local time. |
| DAYNAME(date_exp) | Returns a character string containing the data source-specific name of the day for the day portion of date_exp. |
| DAYOFMONTH(date_exp) | Returns the day of the month based on the month field in date_exp as an integer value in the range 1-31. |
| DAYOFWEEK(date_exp) | Returns the day of the week based on the week field in date_exp as an integer value in the range of 1-7 where 1 represents Sunday. |
| DAYOFYEAR(date_exp) | Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366. |
| EXTRACT(extract-field FROM extract-source) | Returns the extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression. The extract-field argument can be one of the YEAR, MONTH, DAY, HOUR, MINUTE, SECOND keywords.The precision of the returned value is implementation-defined. The scale is 0 unless SECOND is specified, in which case the scale is not less that the fractional seconds precision of the extract-source field. |
| HOUR(time_exp) | Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23. |
| MINUTE(time_exp) | Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59. |
| MONTH(date_exp) | Returns the month based on the month field in date_exp as an integer value in the range 1-12. |
| MONTHNAME (date_exp) | Returns a character string containing the data source-specific name of the month for the month portion of date_exp. |
| NOW() | Returns the current date and time as a timestamp value. |
| QUARTER(date_exp) | Returns the quarter in date_exp as an integer value in the range of 1-4. |
| SECOND(time_exp) | Returns the second based on the second field in time_exp as an integer value in the range of 0-59. |
| TIMESTAMPADD (interval, integer_exp, timestamp_exp) | Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR where fractional seconds are expressed in billionths of a second.If time_stamp is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp.If timestamp_exp is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp.An application determines which intervals a data source supports by calling SQLGetInfo with the SQL_TIMEDATE_ADD_INTERVALS option. |
| TIMESTAMPDIFF (interval, timestamp_exp1, timestamp_exp2) | Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. The keywords SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR are valid values of interval, where fractional seconds are expressed in billionths of a second.If either timestamp expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that timestamp is set to the current date before calculating the difference between timestamps.If either timestamp expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that timestamp is set to 0 before calculating the difference between timestamps.An application determines which intervals a data source supports by calling SQLGetInfo with the SQL_TIMEDATE_DIFF_INTERVALS option. |
| WEEK(date_exp) | Returns the week of the year based on the week field in date_exp as an integer value in the range of 1-53. |
| YEAR(date_exp) | Returns the year based on the year field in date_exp as an integer value. The range is data source-dependent. |
| DATABASE() | Returns the name of the database corresponding to the connection handle. |
| IFNULL(exp, value) | If exp is null, value is returned. If exp is not null, exp is returned. The possible data type or types of value must be compatible with the data type of exp. |
| USER() | Returns the user name in the DBMS. This may be different from the login name. |
| CURRENT_USER | This is an SQL92 version of the ODBC USER function. |
The Easysoft ODBC-SQI SQL Engine supports the following set functions:
For example, the following query would return the maximum price from the PRODUCT table:
SELECT MAX (PRICE) FROM PRODUCT
| If your ODBC application does not return any results when using a set function, try inserting an AS clause into your query. For example: |
The following SQL data types are supported, as described in Appendix D of the Microsoft ODBC 3.0 Programmer's Reference.
All SQL92 and ODBC literals are supported and can be specified in either form. For example:
DATE '1999-01-02', INTERVAL '10-2' YEAR TO MONTH
{d '1999-01-02'}, {INTERVAL '10-2' YEAR TO MONTH}
The Easysoft ODBC-SQI SQL Engine supports both the SQL92 CAST function and the ODBC CONVERT FUNCTION for conversion between compatible data types.
The following table shows how compatible data types are converted.
| For conciseness, the SQL_ prefix has been omitted from all data types in the Supported data type conversions table. |
The following table shows which numeric data type is adopted when arithmetic operations (+-*/) are performed on two numerics of different data types.
A string data type (SQL_CHAR, SQL_VARCHAR) can be concatenated with another string data type using the concat operator (|| or +).
If either of the operands are of variable length, then the result will also be of varying length.
When adding or subtracting an integral numeric value to or from an interval value where the interval value is a single field, the interval type does not change.
For example, if you have the interval value 3 DAY and you add 6, the result will be 9 DAY - the interval is not recalculated as a number of weeks and days.
The Easysoft ODBC-SQI SQL Engine performs several optimizations to improve performance, including query and table ordering optimizations:
The WHERE clause of a query will be rewritten into a form that allows more efficient processing of data. For example the query:
SELECT * FROM x WHERE ( a = 10 or b = 20 ) and c = 30
will be rewritten as the equivalent:
SELECT * FROM x WHERE a = 10 and c = 30
SELECT * FROM x WHERE b = 20 and c = 30 and a <> 10
In cases where indexes are present on tables, the Easysoft ODBC-SQI SQL Engine will, if necessary, rearrange the sequence in which tables are processed in order to enable an index to be used.
This will minimize the number of reads and positions executed and lead to huge increases in performance.
The Easysoft ODBC-SQI SQL Engine exposes an informational schema view of the tables supplied by the target data sources, which consists of the following system tables:
To get information about all the tables in the data source, use:
SELECT * FROM INFO_SCHEMA.TABLES
To get information about all the columns in the data source, use:
SELECT * FROM INFO_SCHEMA_COLUMNS
To get information about all the columns in a specific table, use:
SELECT * FROM INFO_SCHEMA_COLUMNS WHERE
The Easysoft ODBC-SQI SQL Engine supports FORWARD ONLY, STATIC and KEYSET cursors (but not DYNAMIC cursors).
The Easysoft ODBC-SQI SQL Engine supports:
The ODBC Programmer's Reference lists the functions that can be executed asynchronously. The Easysoft ODBC-SQI SQL Engine supports asynchronous operation for these functions although SQL_STILL_EXECUTING will not be returned for a function which happens immediately because of an earlier function. For example, SQLGetData will always return immediately whereas SQLExecute may not.
For example, the following query would probably not execute immediately:
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
