The DataFlex Connectivity Kit for ODBC handles several types of errors. We distinguish between driver level and database level errors.
Driver level errors are detected at Connectivity Kit level. Trying to set an attribute for a non-existing column, for example, will trigger a driver level error. Database level errors are raised by the ODBC Data Source and passed on by the Connectivity Kit to the API. Not having sufficient rights to perform a certain operation will trigger a database level error.
Driver Level Errors
The Connectivity Kit raises the driver level errors when it detects an illegal operation. In general the Connectivity Kit is as verbose as possible to identify the cause of the error. If a program tries to set a field attribute in table MyTable of a non-existing column with number 12 the kit will report the following message: “Field number out of range [MyTable, 12]”. The text between square brackets is intended as extra information to help identify the cause of the error and is referred to as verbosity term.
Most of the time the verbosity term will identify a table name or number. Sometimes however the term may not be clear at first sight, those terms are listed in the following table. In the following table we use abbreviations TN table name, CN column name, AN attribute number, IN index number, ISN index segment number.
ERROR
VERBOSITY TERM
Setting a non supported attribute
[<TN>,<AN >] [<TN>,<CN >,<AN>] [<TN>,index.<IN >,<AN >]
[<TN>,index.<IN>,segment.<ISN>,<AN>] Field number out of range [<TN>, <IllegalFieldNumber>]
Index number out of range [<TN>, index.<IN>] Index segment out of range [<TN>,index.<IN>,<ISN>]
E
rro
r H
an
dl
in
g
ERROR
VERBOSITY TERM
Bad attribute value [<TN>,<AN >,<BadValue>] [<TN>,<CN >,<AN >,<BadValue>] [<TN>,index.<IN>,<AN >,<BadValue>] [<TN>,index.<IN>,segment.<ISN>,<AN>,< BadValue.]
Index is not available [<TN>,index.<IN>]
Invalid intermediate file value [<TN>,<Keyword>,<IllegalValue>] Invalid intermediate file
keyword [<IllegalKeyword>]
Number to large for field [<TN>,<CN>] ODBC Error Class
The DataFlex error system defines several classes of errors: user, system and utility errors. Every driver adds an error class to the DataFlex error classes. The DataFlex Connectivity Kit for ODBC adds the following errors:
NUMBER
TEXT
DESCRIPTION
12289 General error Database level error entry point, see the paragraph on database level errors.
12290 Can’t initialize The Connectivity Kit is unable to initialize. Check the client setup. 12291 Can’t de-initialize The Connectivity Kit is unable to
de-initialize and free up the environment. Check the client setup.
12192 Bad or no primary
index specified. The primary index is bad. This canbe caused by several reasons. Either the index does not exist or it contains more than one segment. 12293 Login unsuccessful Unable to login to the specified
server. Either the client is not properly setup or the user does not
Er
ro
r handl
in
g
NUMBER
TEXT
DESCRIPTION
have sufficient privileges to login to the server.
12294 Logout unsuccessful Unable to logout from the server. Check the client setup.
12295 Table not in connection
An attempt is made to open a table that cannot be found on the specified server for the specified owner.
12296 Null value not allowed
An attempt has been made to put a NULL value into a column does not allow NULL values.
12297 Segment number out of range
An attempt has been made to set or get an attribute of a non-existing index segment.
12298 Index number out of
range An attempt has been made to set orget an attribute of a non-existing index.
12299 Login attribute must
be set Structure changes have been madeto the table. The Structure_End operation is missing login
information for the table and is unable to save the changes. Make sure to set the DF_FILE_LOGIN attribute of the table.
12300 Physical name must
be set Trying to create a new table. TheStructure_End operation is missing the physical name of the table. This is the name the table will get in ODBC. Make sure to set the DF_FILE_PHYSICAL_NAME attribute.
12301 Invalid registration
file The registration file is invalid. 12302 License expired A temporary license is expired. 12303 Deadlock or timeout The current transaction was
E
rro
r H
an
dl
in
g
NUMBER
TEXT
DESCRIPTION
of a deadlock or timeout. For more information about handling
deadlocks and/or timeouts, see the Transaction chapter.
12304 Embedded SQL error General Embedded SQL error. 12305 Invalid SQL
statement handle A statement using the handlecannot be found for the specified connection.
12306 Invalid SQL connection handle
A connection using the handle cannot be found.
12307 Invalid SQL Connectivity Kit Identifier
The Connectivity Kit identifier is illegal.
12308 Invalid SQL bind file The specified bind file is not open, it is not an ODBC table or the
piBindFile property has not been set.
12309 Invalid SQL column A column with the specified number does not exist in the result set. 12310 Invalid attribute The attribute identifier is illegal. 12311 Invalid buffer The file passed to
FetchActivatesBuffer is not open. 12312 Invalid configuration
keyword A keyword in the globalconfiguration file odbc_drv.int is invalid.
12313 Unique index
required for restructure
When restructuring an existing table at least one unique index is required.
Database Level Errors
The ODBC error handling mechanism is quite different from the DataFlex mechanism. Ideally, we would be able to project the ODBC error system on to the DataFlex system. Unfortunately, this is not possible. The identification mechanism in both systems is incompatible. DataFlex uses error number
Er
ro
r handl
in
g
where ODBC uses SQL State, a 5-byte character string.
DataFlex needs an error number when reporting an error to a program. We have chosen to use one number for every ODBC error that is reported to the Connectivity Kit. This number is 12289. Database level errors will always be formatted:
12289 -- <SQL State> (<Native Error>)--<Component identifier> <Error text>
SQL State
SQL States are used in SQL environments to identify a certain exception condition that has occurred. SQL states are 5 character strings using only uppercase letters A – Z and the digits 0 – 9. The string is divided into two components. The first two characters are the class code; the last three are the subclass code. For more information on SQL States, see your database’s documentation.
Native Error
The native error number reported by the backend. Component identifier
The component identifier is supposed to help you identify the component that causes the error. The communication to the ODBC Data Source uses a number of components, each of which can raise an error. For errors and messages that occur outside of the data source the component identifier format is:
[Vendor identifier][Component identifier]
For errors that occur inside the data source the component identifier format is: [Vendor identifier][Component identifier][Data source identifier]
Error text
Contains the text of the error or message. Truncated error text
The error messages generated by ODBC Data Sources tend to be a bit larger than is common in a traditional DataFlex environment. The object oriented
E
rro
r H
an
dl
in
g
error handlers within a DataFlex environment can handle these long texts without problems. Procedural programs however tend to reserve one line of 80 positions that is used for error reporting. If the error text is larger than 80 positions, it will be truncated. If the error text is truncated, use your database’s documentation to find the SQL State.
Alternatively you can set the Error_Debug_Mode configuration keyword, see Appendix C – Configuration file ODBC_DRV.INT for details on the
configuration file. SQL State table
SQLSTATE
ERROR
01000 General warning
01001 Cursor operation conflict 01002 Disconnect error
01003 NULL value eliminated in set function 01004 String data, right truncated
01006 Privilege not revoked 01007 Privilege not granted
01S00 Invalid connection string attribute
01S01 Error in row
01S02 Option value changed
01S06 Attempt to fetch before the result set returned the first rowset
01S07 Fractional truncation 01S08 Error saving File DSN 01S09 Invalid keyword 07002 COUNT field incorrect
07005 Prepared statement not a cursor-specification 07006 Restricted data type attribute violation
07009 Invalid descriptor index
Er
ro
r handl
in
g
SQLSTATE
ERROR
08001 Client unable to establish connection 08002 Connection name in use
08003 Connection does not exist 08004 Server rejected the connection 08007 Connection failure during transaction 08S01 Communication link failure
21S01 Insert value list does not match column list
21S02 Degree of derived table does not match column list 22001 String data, right truncated
22002 Indicator variable required but not supplied 22003 Numeric value out of range
22007 Invalid datetime format 22008 Datetime field overflow 22012 Division by zero 22015 Interval field overflow
22018 Invalid character value for cast specification 22019 Invalid escape character
22025 Invalid escape sequence 22026 String data, length mismatch 23000 Integrity constraint violation 24000 Invalid cursor state
25000 Invalid transaction state 25S01 Transaction state 25S02 Transaction is still active 25S03 Transaction is rolled back 28000 Invalid authorization specification 34000 Invalid cursor name
E
rro
r H
an
dl
in
g
SQLSTATE
ERROR
3D000 Invalid catalog name 3F000 Invalid schema name 40001 Serialization failure
40002 Integrity constraint violation 40003 Statement completion unknown 42000 Syntax error or access violation 42S01 Base table or view already exists 42S02 Base table or view not found 42S11 Index already exists
42S12 Index not found 42S21 Column already exists
42S22 Column not found
44000 WITH CHECK OPTION violation
HY000 General error
HY001 Memory allocation error HY003 Invalid application buffer type HY004 Invalid SQL data type
HY007 Associated statement is not prepared HY008 Operation canceled
HY009 Invalid use of null pointer HY010 Function sequence error HY011 Attribute cannot be set now HY012 Invalid transaction operation code HY013 Memory management error
HY014 Limit on the number of handles exceeded HY015 No cursor name available
HY016 Cannot modify an implementation row descriptor
Er
ro
r handl
in
g
SQLSTATE
ERROR
HY018 Server declined cancel request
HY019 Non-character and non-binary data sent in pieces HY020 Attempt to concatenate a null value
HY021 Inconsistent descriptor information HY024 Invalid attribute value
HY090 Invalid string or buffer length HY091 Invalid descriptor field identifier HY092 Invalid attribute/option identifier HY095 Function type out of range HY096 Invalid information type HY097 Column type out of range HY098 Scope type out of range HY099 Nullable type out of range
HY100 Uniqueness option type out of range HY101 Accuracy option type out of range HY103 Invalid retrieval code
HY104 Invalid precision or scale value HY105 Invalid parameter type
HY106 Fetch type out of range HY107 Row value out of range HY109 Invalid cursor position HY110 Invalid driver completion HY111 Invalid bookmark value
HYC00 Optional feature not implemented
HYT00 Timeout expired
HYT01 Connection timeout expired
IM001 Driver does not support this function
E
rro
r H
an
dl
in
g
SQLSTATE
ERROR
specifiedIM003 Specified driver could not be loaded
IM004 Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed IM005 Driver’s SQLAllocHandle on SQL_HANDLE_DBC failed IM006 Driver’s SQLSetConnectAttr failed
IM007 No data source or driver specified; dialog prohibited IM008 Dialog failed
IM009 Unable to load translation DLL IM010 Data source name too long IM011 Driver name too long
IM012 DRIVER keyword syntax error IM013 Trace file error
IM014 Invalid name of File DSN IM015 Corrupt file data source
Version 2.2 133
ODBC Escape
sequenc
es
A
AAppendix A – ODBC Escape sequences
To setup default values, ODBC Escape Sequences can be used. ODBC defines a number of escape sequences of which we can use the literal and scalar function escape sequence.
An ODBC escape sequence is enclosed in curly brackets {}. The escape sequence is regocnized by the data source and translated into the data source equivalent.
Literals
The ODBC literal escape sequence is used to define date, time and datetime literals. These are defined by using {literaltype ‘literal’}. The following literal types are defined:
Type Meaning Format of value
d Date yyyy-mm-dd
t Time hh:mm.ss
ts Timestamp yyyy-mm-dd hh:mm:ss
Scalar functions
The ODBC scalar function escape sequence is used to define a function call to a scalar function. A scalar function returns a value for every row. Function calls are defined by using {fn scalar-function}. Several different types of functions have been defined: string functions, numeric functions, date time functions, system functions and data type conversion functions.
ODBC Escape
sequenc
es
String Functions
String Function Description
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.
CHAR_LENGTH (string_exp) CHARACTER_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. CONCAT (string_exp1,
string_exp2) Returns a character stringthat is the result of concatenating string_exp2 to string_exp1.
DIFFERENCE (string_exp1,
string_exp2) Returns an integer value thatindicates 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 stringwhere length characters have been deleted from
string_exp1 at start and where string_exp2 has been inserted into string_exp1 at start.
ODBC Escape
sequenc
es
LCASE (string_exp) Returns a string where all uppercase characters in string_exp have been converted to lowercase. LEFT (string_exp, count) Returns the count leftmost
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 positionof the first occurrence of string_exp1 within
string_exp2. The search begins on the first character position in string_exp2 unless the optional argument start is specified. If start is specified the search begins on character position indicated by the value of start.
Character positions start at 1. The function returns 0 if string_exp1 is not found in string_exp2.
LTRIM (stirng_exp) Returns string_exp with
leading blanks removed. OCTET_LENGTH
(string_exp) Returns the length in bytes ofthe string_exp. POSITION (string_exp1 IN
string_exp2) Returns the position ofstring_exp1 in string_exp2. REPEAT (string_exp, count) Returns a string composed of
string_exp repeated count times.
REPLACE (stirng_exp1,
string_exp2, string_exp3) Replace occurences ofstring_exp2 in string_exp1 with string_exp3.
ODBC Escape
sequenc
es
RIGHT (string_exp, count) Returns the count rightmost characters of string_exp.
RTRIM (string_exp) Returns string_exp with
trailing blanks removed. SOUNDEX (string_exp) Returns a character string
representing the sound of the words in string_exp.
SPACE (count) Returns a string consisiting of
count spaces. SUBSTRING (string_exp,
start, length) Returns the substring fromstring_exp beginning at start that is length characaters long.
UCASE (string_exp) Returns a string where all lowercase characters in string_exp have been converted to uppercase.
Numeric Functions
Numeric Function Description
ABS (numeric_exp) Absolute value.
ACOS (float_exp) Arc cosine as an angle
expressed in radians
ASIN (float_exp) Arc sine as an angle
expressed in radians
ATAN (float_exp) Arc tangent as an angle
expressed in radians ATAN2 (float_exp1,
float_exp2) Arc tangent of the x- and y-coordinates specified by flloat_exp1 and float_exp2 as an angle expressed in radians
ODBC Escape
sequenc
es
CEILING (numeric_exp) Smallest integer greater than or equal to numeric_exp.
COS (float_exp) The cosine of float_exp
where float_exp is an angle expressed in radians.
COT (float_exp) The cotangent of float_exp
where float_exp is an angle expressed in radians. DEGREES (numeric_exp) Converts to the number of
degrees where numeric_exp is an angle expressed in radians.
EXP (float_exp) Exponential value of
float_exp
FLOOR (numeric_exp) Largest integer less than or equal to numeric_exp.
LOG (float_exp) Natural logarithm of
float_exp.
LOG10 (float_exp) Base 10 logarithm of
float_exp. MOD (integer_exp1,
integer_exp2) Remainder (modulus) ofinteger_exp1 divided by integer_exp2.
PI () Constant value of pi.
POWER (numeric_exp,
integer_exp) Numeric_exp to the power ofinteger_exp. RADIANS (numeric_exp) Converts to the number of
radians where numeric_exp is an angle expressed in
degrees.
RAND ([integer_exp]) Random number using the
optional integer_exp as seed value.
ODBC Escape
sequenc
es
integer_exp) integer_exp positions right of
the decimal separator. SIGN (numeric_exp) Returns an indicator of the
sign of numeric_exp. If numeric_exp is less than zero, -1 is returned. If numeric_exp is zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned.
SIN (float_exp) The sine of float_exp where
float_exp is an angle expressed in radians.
SQRT (float_exp) Square root of float_exp.
TAN (float-exp) The tangent of float_exp
where float_exp is an angle expressed in radians. TRUNCATE (numeric_exp,
integer_exp) Numeric_exp truncated tointeger_exp positions right of the decimal separator.
Time, Date and Interval Functions
Time, Date, Interval
Function Description
CURRENT_DATE () Current date.
CURRENT_TIME
([time_precision]) Current local time, thetime_precision optional argument determines the seconds precision of the returned value.
CURRENT_TIMESTAMP ([timestamp_precision])
Current local data and local time as a timestamp value, the timestamp_precision
ODBC Escape
sequenc
es
optional argument determines the seconds precision of the returned value.
CUR_DATE () Current date.
CURTIME () Current local time.
DAYNAME (date_exp) The name of the day of the
passed date.
DAYOFMONTH (date_exp) The number of the day in the month in date_exp in the range of 1 – 31.
DAYOFWEEK (date_exp) The number of the day of the week in date_exp in the range 1-7 where 1 is Sunday.
DAYOFYEAR (date_exp) The number of the day of the year in date_exp in the range 1- 366.
EXTRACT (extract_field FROM extract_source)
Extracts the extract_field portion of the extract_source. Extract_source is a datetime or interval expression. Extract_field can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
HOUR (time_exp) The hour of the time_exp in
the range 0 – 23.
MINUTE (time_exp) The minute in time_exp in
the range 0 – 59.
MONTH (date_exp) The month in date_exp in the
range 1 – 12.
MONTHNAME (date_exp) The name of the month in date_exp.
ODBC Escape
sequenc
es
NOW () Current date and time as a
timestamp value.
QUARTER (date-exp) The quarter in date_exp in
the range 1 – 4. Where quarter 1represents January 1 through March 31.
SECOND (time_exp) The seconds in time_exp in
the range 0 – 59. TIMESTAMPDIFF (interval,
timestamp_exp1, timestamp_exp2)
The integer number of intervals that
timestamp_exp2 is greater than timestamp_exp1. Interval can be one of 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.
WEEK (date_exp) The weeknumber of the year
in date_exp in the range 1 – 53.
YEAR (date_exp) The year of date_exp.
System Functions
System Function
Description DATABASE
ODBC Escape
sequenc
es
IFNULL
(exp, value) If exp is null, value is returned. If exp is not null,exp is returned. USER () The urser name. This may be different from the
login name.
Data Type Conversion Functions.
ODBC defines one explicit data type conversion function, convert. Its syntax