Quais as verificaçoes a fazer no SQLCA ?

Versão para impressãoEnviar para amigoVersão PDF

SQLCA = SQL Control Area


  1. Estrutura do SQLCA
  2. Utilizando o SQLSTATE
  3. Como verificar o SQLCA
    1. Após incluir um registro com campo serial
    2. Após carregar tabela com load
    3. Após conectar no banco


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.
  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.

4
Média: 4 (3 votos)
Sua avaliação: Nenhum
Tags:

Comentar

O conteúdo deste campo é privado não será exibido ao público.
  • Endereços de páginas de internet e emails viram links automaticamente.
  • Você pode usar tags BBCode no texto.
  • Tags HTML permitidas: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

Mais informações sobre as opções de formatação

CAPTCHA
Este teste é para bloquear programas automatizados e previnir spams
CAPTCHA de Imagem
Digite o texto exibido na imagem.