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)
SQLCA.SQLCODE (manual SQL Tutorial v11.50)
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
Copiado do arquivo sqlca.h
/* 0 - estimated number of rows returned */
/* 1 - serial value after insert or ISAM error code */
/* 2 - number of rows processed */
/* 3 - estimated cost */
/* 4 - offset of the error into the SQL statement */
/* 5 - rowid after insert */
Table 8-2. Fields of SQLERRD (manual SQL Tutorial v11.50)
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 é utilizado apenas com 4GL
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.SQLWARN[8] : char
Table 8-3. Fields of SQLWARN (manual SQL Tutorial v11.50)
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.
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
Verificar a existencia de alguma questão para trabalhar com o SQLSTATE
PARA 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)
LINKS
http://publib.boulder.ibm.com/infocenter/idshelp/v111/
index.jsp?topic=/com.ibm.esqlc.doc/esqlc214.htm
APOS INCLUIR UM REGISTRO COM CAMPO SERIAL
SQLCA.SQLERRD[1] ou SQLCA.SQLERRD2
APOS CARREGAR TABELA COM LOAD
SQLCA.SQLERRD[2] ou SLCA.SQLERRD3 : Quantidade de linhas carregadas
SQLCA.SQLERRD[6] ou SLCA.SQLERRD7 : Ultimo ROWID processado.
APOS 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.
1) If the first field of sqlca.sqlwarn is blank, then no warnings were
issued.
2) The second sqlca.sqlwarn field is set to the letter W if the database
that was opened supports transaction logging.
3) The third field is set to W if database is an ANSI-compliant database.
4) The fourth field is set to W if database is a Dynamic Server database.
5) The fifth field is set to W if database converts all floating-point
data to DECIMAL
format. (System lacks FLOAT and SMALLFLOAT support.)
6) 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.
7) 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.
- 120 leituras





Comentar