Quais as verificaçoes a fazer no SQLCA ?
SQLCA = SQL Control Area
Estrutura do SQLCA
SQLCA : struct SQLCA.SQLCODE : integer SQLCA.SQLERRD : array[6] of integer # se 4GL SQLCA.SQLWARN : array[8] of char # senao SQLCA.SQLCAW_S: struct SQLCA.SQLCAW_S.SQLWARN0 - SQLWARN7 : char(1) SQLCA.SQLERRM : char(72) SQLCA.SQLERRP : char(8)
Detalhes de cada opção
# Todas as informações abaixo foram copiadas # do manual SQL Tutorial v11.50 ou headers de bibliotecas SQLCA.SQLCODE Table 8-1. Values of SQLCODE Return value Interpretation ------------------ ----------------------------------------------------- value < 0 Specifies an error code. value = 0 Indicates success. 0 < value < 100 After a DESCRIBE statement, an integer value that represents the type of SQL statement that is described. 100 After a successful query that returns no rows, indicates the NOT FOUND condition. NOT FOUND can also occur in an ANSI-compliant database after an INSERT INTO/SELECT, UPDATE, DELETE, or SELECT... INTO TEMP statement fails to access any rows. SQLCA.SQLERRD[6] of integer Table 8-2. Fields of SQLERRD Field Interpretation ----------- ---------------------------------------------------- First After a successful PREPARE statement for a SELECT, UPDATE, INSERT, or DELETE statement, or after a select cursor is opened, this field contains the estimated number of rows affected. Second When SQLCODE contains an error code, this field contains either zero or an additional error code, called the ISAM error code, that explains the cause of the main error. After a successful insert operation of a single row, this field contains the value of any SERIAL value generated for that row. Third After a successful multirow insert, update, or delete operation, this field contains the number of rows that were processed. After a multirow insert, update, or delete operation that ends with an error, this field contains the number of rows that were successfully processed before the error was detected. Fourth After a successful PREPARE statement for a SELECT, UPDATE, INSERT, or DELETE statement, or after a select cursor has been opened, this field contains the estimated weighted sum of disk accesses and total rows processed. Fifth After a syntax error in a PREPARE, EXECUTE IMMEDIATE, DECLARE, or static SQL statement, this field contains the offset in the statement text where the error was detected. Sixth After a successful fetch of a selected row, or a successful insert, update, or delete operation, this field contains the rowid (physical address) of the last row that was processed. Whether this rowid value corresponds to a row that the database server returns to the user depends on how the database server processes a query, particularly for SELECT statements. Seventh The SQL is not executed because SET EXPLAIN ON AVOID_EXECUTE is set. # Obs.: O 7o campo descrito acima, não é válido com 4GL # Copiado do arquivo sqlca.h # /* sqlerrd0 - estimated number of rows returned */ # /* sqlerrd1 - serial value after insert or ISAM error code */ # /* sqlerrd2 - number of rows processed */ # /* sqlerrd3 - estimated cost */ # /* sqlerrd4 - offset of the error into the SQL statement */ # /* sqlerrd5 - rowid after insert */ SQLCA.SQLWARN[8] : char Table 8-3. Fields of SQLWARN FIELD WHEN OPENING OR CONNECTING TO A DATABASE ------ ---------------------------------------------------------- First Set to W when any other warning field is set to W. If blank, others need not be checked. Second Set to W when the database now open uses a transaction log. Third Set to W when the database now open is ANSI compliant. Fourth Set to W when the database server is Dynamic Server. Fifth Set to W when the database server stores the FLOAT data type in DECIMAL form (done when the host system lacks support for FLOAT types). Sixth Reserved. Seventh Set to W when the application is connected to a database server that is the secondary server in a data-replication pair. That is, the server is available only for read operations. Eighth Set to W when client DB_LOCALE does not match the database locale. For more information, see the IBM Informix GLS User?s Guide. FIELD ALL OTHER OPERATIONS ------ ---------------------------------------------------------- First Set to W when any other warning field is set to W. Second Set to W if a column value is truncated when it is fetched into a host variable using a FETCH or a SELECT...INTO statement. On a REVOKE ALL statement, set to W when not all seven table-level privileges are revoked. Third Set to W when a FETCH or SELECT statement returns an aggregate function (SUM, AVG, MIN, MAX) value that is null. Fourth On a SELECT...INTO, FETCH...INTO, or EXECUTE...INTO statement, set to W when the number of items in the select list is not the same as the number of host variables given in the INTO clause to receive them. On a GRANT ALL statement, set to W when not all seven table-level privileges are granted. Fifth Set to W after a DESCRIBE statement if the prepared statement contains a DELETE statement or an UPDATE statement without a WHERE clause. Sixth Set to W following execution of a statement that does not use ANSI-standard SQL syntax (provided the DBANSIWARN environment variable is set). Seventh Set to W when a data fragment (a dbspace) has been skipped during query processing (when the DATASKIP feature is on). Eighth Reserved. # Copiado do arquivo SQLCA.H # char sqlwarn0; /* = W if any of sqlwarn[1-7] = W */ # char sqlwarn1; /* = W if any truncation occurred or # database has transactions or # no privileges revoked */ # char sqlwarn2; /* = W if a null value returned or # ANSI database */ # char sqlwarn3; /* = W if no. in select list != no. in into list or # turbo backend or no privileges granted */ # char sqlwarn4; /* = W if no where clause on prepared update, delete or # incompatible float format */ # char sqlwarn5; /* = W if non-ANSI statement */ # char sqlwarn6; /* = W if server is in data replication secondary mode */ # char sqlwarn7; /* = W if database locale is different from proc_locale*/ SQLCA.SQLERRM : char(72) Contém dados sobre o erro ocorrido, como nomes de tabela, nome de indice, nome de constraints, etc. Se a mensagem passar de 72 caracteres, os dados são truncados SQLCA.SQLERRP : char(8) Não é utilizado
Utilizando o SQLSTATE
Quando a variável SQLSTATE é suportada, ela é compatível com padrão X/Open e ANSI. Pode-se ler ela com o comando GET DIAGNOSTICS logo após a execução do SQL.
Conforme manual (SQL Tutorial) é recomendável utilizar o SQLSTATE como forma primária para tratamento de erros.
Como verificar o SQLCA
4GL : Utilizar a variavel SQLCA
ESQL/C : Utilizar a estrutura sqlca definida no arquivo sqlca.h, GET DIAGNOSTICS
SQL : Utilizar a funçao dbinfo('sqlca.sqlerrd1')
SPL : Utiliza variavel local SQLCODE (new ids 11.50)
Após incluir um registro com campo serial
SQLCA.SQLERRD[1] ou SQLCA.SQLERRD2
Após carregar tabela com load
SQLCA.SQLERRD[2] ou SLCA.SQLERRD3 : Quantidade de linhas carregadas
SQLCA.SQLERRD[6] ou SLCA.SQLERRD7 : Ultimo ROWID processado.
Após conectar no banco
SQLCA.SQLWARN Settings Immediately after DATABASE Executes (ESQL/C) Immediately after DATABASE executes, you can identify characteristics of the specified database by examining warning flags in the sqlca structure.
- If the first field of sqlca.sqlwarn is blank, then no warnings were issued.
- The second sqlca.sqlwarn field is set to the letter W if the database that was opened supports transaction logging.
- The third field is set to W if database is an ANSI-compliant database.
- The fourth field is set to W if database is a Dynamic Server database.
- The fifth field is set to W if database converts all floating-point data to DECIMAL format. (System lacks FLOAT and SMALLFLOAT support.)
- The seventh field is set to W if database is the secondary server (that is, running in read-only mode) in a data-replication pair.
- The eighth field is set to W if database has DB_LOCALE set to a locale different from the DB_LOCALE setting on the client system.
- 376 leituras
Comentar