[Prev] [Menu] [Next]

Shell variables of Pgbash


    Pgbash uses the shell variables that the head of a string is 'SQL','OPTION' and 'POSTGRESQL'. These are a capital letter. So, if you want to use shell variables, it is better that you use the shell variable of a small letter.

  • Shell variable after the SQL execution.

      $SQLOID     :(int) the latest OID of INSERT.
     $SQLCODE    :(int) SQL error code (see "9.Error code")
                     0    --- Rnormal end.
                     100  --- EOF(Eond Of File)  
                     nagative value --- SQL error        
      $SQLERRMC   :(char) SQL error message(max. 70 chracters)
     $SQLERRML   :(int) Length of SQL error message(<70)
      $SQLERRD2   :(int) Number of rows of the retrieval results(value of PQntuples())
      $SQLERRD3   :(int) Number of column of the retreival results(value of PQnfields())
      $SQLNTUPLE  :(int) =SQLERRD2
      $SQLNFIELD  :(int) =SQLERRD3
    
      ${SQLFIELDNAME[i]} : column list(i = 0 -- SQLNFIELD-1 )
    


  • Shell variables for options.

    The value set by using SET option statement is kept by the shell variable for options.

      $OPTION_ECHO       : turn on/off ECHO query  
      $OPTION_QUIET      : turn on/off QUIET mode  
      $OPTION_HEADER     : turn on/off TOP_header  
      $OPTION_BOTTOM     : turn on/off BOTTOM_print
      $OPTION_ALIGNMENT  : turn on/off ALIGNMENT   
      $OPTION_FRAME      : turn on/off OUTER_FRAME 
      $OPTION_EXPANDED   : turn on/off EXPANDED    
      $OPTION_HTML       : turn on/off HTML mode   
      $OPTION_SEPARATOR  : set SEPARATOR character 
      $OPTION_NULLSTRING : set NULL string         
      $OPTION_ZEROSTRING : set NULL string         
      $OPTION_CAPTION    : set NULL string         
      $OPTION_TABLETAG   : set NULL string         
    


  • Shell variables for error code

    Pgbash sets the error code to the next shell variables. Error code can be displayed when inputting '??e' in the interactive environment.

      $SQL_OK                 :    0 : normal end. 
      $SQL_NOT_FOUND          :  100 : EOF(End Of File). 
      $SQL_SYSTEM_ERROR       : -200 : system error. 
      $SQL_TOO_MANY_ARGUMENTS : -201 : too many arguments in fetch_stmt. 
      $SQL_TOO_FEW_ARGUMENTS  : -202 : too few  arguments in fetch_stmt. 
      $SQL_CONNECT_ERROR      : -203 : Database connection error. 
      $SQL_INVALID_STMT       : -230 : invalid statements. 
      $SQL_READONLY_SHELLVAR  : -231 : can not set read-only shell variable. 
      $SQL_DB_NOT_OPEN        : -232 : DB not open. 
      $SQL_CNAME_NOT_FOUND    : -233 : connect-name not found. 
      $SQL_CNAME_ALREADY_USE  : -234 : connect-name already exist. 
      $SQL_INVALID_COMMAND    : -235 : invalid command. 
      $SQL_BAD_RESPONSE       : -400 : bad response(backend maybe died)." 
      $SQL_EMPTY_QUERY        : -401 : empty query (backend lost query). 
      $SQL_CONNECTION_BAD     : -402 : connection bad(disconnect backend)" 
      $SQL_FATAL_ERROR        : -403 : query fatal error   (SQL error on backend) 
      $SQL_NONFATAL_ERROR     : -404 : query nonfatal error(SQL error on backend) 
      $SQL_NULL               :   -1 : indicator is NULL. 
    

    For example, the error code can be used by using the (()) operand as follows.
    (例) insert into test values(111,'aaa','bbb');
         if(( SQLCODE < SQL_OK )); then
            echo $SQLERRMC
         fi
    
    In addition, the error code can be displayed by inputting '?s' after the SQL execution.
    (例) pgbash> ?s
         # SQL status 
         SQLCODE   = -403   (SQL error code)
         SQLNTUPLES= 0      (number of tuples)
         SQLNFIELDS= 0      (number of fields)
         SQLERRML  = 38     (length of SQLERRMC)     
         SQLERRMC  = ERROR:  testxxx: Table does not exist.
    

  • Other shell variables

    (1) $SQLNFILED、${SQLFILEDNAME[i]}
    It keeps the number of rows and number of column.

    (2) $SQLOID
    It keeps the latest OID of INSERT.

    (3) $POSTGRESQL_VERSION
    It keeps the PostgreSQL version(e.g. 7.0.3, 7.1, 7.2 etc.) when connecting to the database.
    (ex) VER1=${POSTGRESQL_VERSION:0:1}
         VER2=${POSTGRESQL_VERSION:2:1}
         if (( VER1 == 7 && VER2 == 1 )); then
            echo "PostgreSQL version 7.1."
         fi
    
    (4) Shell variables for the cookie
    It is possible to refer to the next cookie values in the web application.
     $HTTP_NCOOKIE        : Number of the cookie
     ${HTTP_COOKIEKEY[i]} : Key names of the cookie
     ${HTTP_COOKIEVAL[i]} : Values of the cookie
     (i = 0 -- HTTP_NCOOKIE-1)
    

[Prev] [Menu] [Next]