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