wxbashtools - bash function interface to Kognitio

SYNOPSIS

source wxbashtools
connect <server> <username> <password>
<wxbashtools commands> ...
disconnect

DESCRIPTION

wxbashtools is a script containing a series of bash functions intended to simplify running SQL and fetching results in a bash script.

The functions are wrappers round wxsession, which is the client interface to the wxsessionagent daemon. A successful invocation of connect starts this daemon if it is not running in your environment.

Your bash script should run source wxbashtools, or alternatively . wxbashtools, to load the function definitions into your environment.

QUICK START EXAMPLE

# Load wxbashtools functions and connect to database server
source wxbashtools
connect <server> <username> <password>

# Run "SQL_query" and output results as CSV
run "SQL_query"

# Run "SQL_query", fetch rows one by one, output first three columns of each
execute "SQL_query";
while fetch;
do
  echo $C1 $C2 $C3;
done

# Disconnect from server
disconnect

COMMANDS

BASIC COMMANDS

These are the essential commands required to connect to the server, run queries, and control transactions.

connect server username [password]

If WX_SESSION_SOCK is not set, connect assumes this means no wxsessionagent daemon is running, and starts one up. This daemon connects to Kognitio using the credentials provided. connect also sets the environment variable WX_SESSION_SOCK, which contains the address of the Unix domain socket that wxsession will use to communicate with the agent. In the event of an error, connect returns 1, sets RESULT to 1 and sets SQLSTATE, ERROR_CODE and ERROR_TEXT with appropriate error information. This behaviour is common to all wxbashtools functions; see the ERROR HANDLING section for further information. If WX_SESSION_SOCK is already set, connect will create a new connection and push it on to the current connection handle stack. Subsequent operations will apply to this new connection handle until it is popped from the stack using disconnect, or a different connection stack is selected with useconn. If no password is given, then an attempt is made to authenticate using SSH keys, which is the same behaviour as other Kognitio client tools when run without a password. If this fails, then if standard input is a terminal the user is prompted for a password, otherwise connect fails.

disconnect

Instruct the agent to disconnect the current database connection and remove it from the connection stack. If that was the only connection on the stack, the stack is deleted. If that was the last connection stack, the agent terminates, in which case WX_SESSION_SOCK and WX_SESSION_AGENT_PID are unset. disconnect fails if WX_SESSION_SOCK is not set, or the socket it refers to does not exist, or there’s nothing listening on the other end.

disconnectall

Instruct the agent to disconnect all connection handles, destroy all connection handle stacks, and terminate. disconnectall is called automatically if the agent is still running when your bash process exits. It is also called if your bash process is interrupted by the signal SIGINT, SIGTERM, SIGQUIT or SIGHUP. In this case exit 1 will be called immediately afterwards.

run [-c] [SQL command]

The run command instructs the agent to run the given SQL command, then fetches any results returned and writes them to standard output as CSV. run does not leave the cursor open. If -c is given, an extra CSV record is included at the start of the output containing the column names. run returns 0 on success, and 1 on failure. SQLSTATE, ERROR_CODE and ERROR_TEXT are set appropriately (see ERROR HANDLING). ROW_COUNT is set to the number of rows returned, or the number of rows affected if the query did not return results. COLUMN_COUNT is set to the number of columns in the result set, or 0 if the query was not one that returns results. Note that if a SELECT query returns no results, COLUMN_COUNT is still set to the number of columns that would have been returned in the result set. If no SQL argument is given to run, the command is expected on standard input. This allows complex multi-line queries using bash’s hereis notation: .. code-block:: bash

# Fetch all schema, table and column names run <<EOF select s.name, t.name, c.name from sys.ipe_schema s, sys.ipe_table t, sys.ipe_column c where s.id = t.schema_id and t.id = c.table_id order by s.name, t.name, c.seq EOF

execute [SQL command]

Instructs the agent to run the given SQL command. Results are not fetched. If the query is a SELECT query, the cursor is left open. If the current statement handle has an open cursor before execute is called, it is silently closed. Exit status and error behaviour are the same as run. If the query was not one that returns rows, ROW_COUNT is set to the number of rows affected. In any case, COLUMN_COUNT is set to the number of columns in the result set yet to be fetched. If the query returns results, the column names are assigned to N1, N2 and so on for each column name. The column names also appear in the array variable NAMES. NAMES[1] is the first column, NAMES[2] the second and so on. NAMES[0] (or, equivalently, NAMES) contains all the column names as a CSV record. As with run, if no argument is given then SQL is expected on standard input.

fetch

Fetch one row from the open cursor. It is an error (HY010 Function sequence error) to call this when the current statement handle does not have a cursor open. If there is a row to return, it is fetched. The individual column values are placed in environment variables C1, C2 and so on for each column. The values are also placed in the array variable RECORD; RECORD[1] contains the first column’s value, RECORD[2] the second and so on. RECORD[0], or RECORD, contains the CSV representation of the record. If a row was returned successfully, fetch returns 0. If there were no more rows to return, fetch returns 100. At this point the agent automatically closes the cursor, so further calls to fetch will fail. If an error occurred, 1 is returned.

close

Close the cursor on the current statement handle. This will succeed even if the current statement handle does not have an open cursor.

autocommit on|off

Instruct the agent to switch the connection handle’s autocommit mode on or off. By default, autocommit mode is on. This means the Kognitio ODBC driver will autocommit any time a cursor is closed provided there are no other open cursors on the connection handle, and any time a non-SELECT statement is executed. When autocommit mode is off, a transaction is only committed when commit is called. Disconnecting while not in autocommit mode automatically rolls back any open transaction.

commit

Instruct the agent to commit any open transaction on the current connection. This will close any open cursors. It will succeed even if the connection handle has not opened a transaction.

rollback

Instruct the agent to roll back any open transaction on the current connection. This will close any open cursors. It will succeed even if the connection handle has not opened a transaction.

killagent

Kill the agent whose PID is specified by WX_SESSION_AGENT_PID, an environment variable which is set by connect. If successful, unset WX_SESSION_SOCK and WX_SESSION_AGENT_PID. This is a considerably less polite version of disconnectall. The agent should still clean up its socket file, however.

die [message]

Print message to standard error, print the contents of SQLSTATE and ERROR_TEXT to standard error, and call exit with status 1. If no message is given, a suitable generic message is given instead. This is useful for basic error checking, like so: .. code-block:: bash

connect myserver bob fishcake || die “Couldn’t connect to server”

METADATA COMMANDS

These are the commands used for performing metadata queries, which give information about tables and schemas on the database. They are mostly wrappers round the appropriate ODBC calls.

tables [schema[.*table*]]

Execute the ODBC SQLTables() function. This executes a command which, when fetched from with fetch, returns details of base tables, views and system tables on the system. schema and table are treated as SQL wildcards, so tables SYS.FOO% returns details for all tables in the SYS schema beginning with the word FOO. The results of the query are as defined by the ODBC SQLTables() function, summarised as follows.

COLUMN

NAME

DESCRIPTION

1

TABLE_CAT

Catalogue name. Empty in Kognitio.

2

TABLE_SCHEM

Schema name.

3

TABLE_NAME

Table name.

4

TABLE_TYPE

Table type: TABLE, VIEW or SYSTEM TABLE.

5

REMARKS

Table description, if applicable.

columns [schema[.*table*[.*column*]]]

Execute the ODBC SQLColumns() function. As with tables, this prepares a result set which can be fetched with fetch. If column is omitted, information is returned for all columns in the table or tables which match the schema.table expression. One row is returned for each matching column in each matching table in each matching schema. The results returned are those returned by the ODBC SQLColumns() call, summarised as follows.

COLUMN

NAME

DESCRIPTION

1

TABLE_CAT

Catalogue name. Empty in Kognitio.

2

TABLE_SCHEM

Schema name.

3

TABLE_NAME

Table name.

4

COLUMN_NAME

Column name.

5

DATA_TYPE

ODBC data type code for this column.

6

TYPE_NAME

Data type name for this column, e,g. VARCHAR or INT.

7

COLUMN_SIZE

Maximum number of characters for CHAR or VARCHAR columns, number of digits allowed for numeric columns, or number of characters in string representation for dates/times.

8

BUFFER_LENGTH

Maximum byte length of data transferred in default ODBC conversion. Not useful in wxbashtools.

9

DECIMAL_DIGITS

Number of digits to the right of the decimal point in a DECIMAL, TIME or TIMESTAMP column.

10

NUM_PREC_RADIX

Base of COLUMN_SIZE and DECIMAL_DIGITS. This is always 10 in Kognitio.

11

NULLABLE

If column accepts nulls then 1, otherwise 0.

12

REMARKS

Description of the column, if applicable.

13

COLUMN_DEF

Default value of the column.

14

SQL_DATA_TYPE

ODBC data type code for this column.

15

SQL_DATETIME_SUB

ODBC subtype code for dates/times.

16

CHAR_OCTET_LENGTH

Maximum byte length for string or BINARY columns.

17

ORDINAL_POSITION

Position of the column in the table. The first column is column 1.

18

IS_NULLABLE

If column accepts nulls then YES, otherwise NO.

19

CHARSET_ID

Character set ID of string column.

Column 19, CHARSET_ID, is a Kognitio extension.

If no argument is given to columns, this is equivalent to “%.%.%”, which returns information on every column in every table in every schema in the system. Results are ordered by TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION, in that order.

tableprivs [schema[.*table*]]

Call the ODBC SQLTablePrivileges() function for the given schema and table wildcard expressions. The results prepared for fetching after a successful call to tableprivs are those returned by the ODBC call SQLTablePrivileges(), which are as follows.

COLUMN

NAME

DESCRIPTION

1

TABLE_CAT

Catalogue name. Always empty in Kognitio.

2

TABLE_SCHEM

Schema name.

3

TABLE_NAME

Table name.

4

GRANTOR

Name of the user who granted this privilege. This is not recorded in Kognitio, so is always blank.

5

GRANTEE

Name of the user who has this privilege.

6

PRIVILEGE

Privilege owned, e.g. INSERT, UPDATE…

7

IS_GRANTABLE

YES if the grantee is allowed to grant this privilege to others, NO otherwise.

One row is returned for every privilege on every matching table in every matching schema.

columnprivs [schema[.*table*[.*column*]]]

Call the ODBC function SQLColumnPrivileges for the given schema, table and column wildcard expressions. The results prepared for fetching after a successful call to columnprivs are those returned by the ODBC call SQLColumnPrivileges(), which are as follows.

COLUMN

NAME

DESCRIPTION

1

TABLE_CAT

Catalogue name. Always empty in Kognitio.

2

TABLE_SCHEM

Schema name.

3

TABLE_NAME

Table name.

4

COLUMN_NAME

Column name.

5

GRANTOR

Name of the user who granted this privilege. This is not recorded in Kognitio, so is always blank.

6

GRANTEE

Name of the user who has this privilege.

7

PRIVILEGE

Privilege owned, e.g. INSERT, UPDATE…

8

IS_GRANTABLE

YES if the grantee is allowed to grant this privilege to others, NO otherwise.

One row is returned for every column-specific privilege on every matching column in every matching table in every matching schema. Results are ordered by TABLE_SCHEM, TABLE_NAME, COLUMN_NAME and PRIVILEGE, in that order.

dependedonby [schema.]table

Execute a query to return the names of the tables and views on which the specified table or view depends. In contrast with tables, columns, tableprivs and columnprivs, schema and table are not taken as wildcard expressions. If the schema is not specified, the current schema is assumed. A record is returned for every table or view on which the specified object depends. The format of rows returned is as follows.

COLUMN

NAME

DESCRIPTION

1

DEPENDEE_SCHEMA

Schema containing the table which is depended on.

2

DEPENDEE_TABLE

Name of the table depended on.

3

DEPENDEE_TYPE

Type of the table depended on:

T base table

V view

S system table

4

DEPENDENCY

Type of dependency:

F foreign key dependency

V view dependency

dependedonby orders results by DEPENDEE_SCHEMA and DEPENDEE_TABLE, in that order. If the specified table does not exist, dependedonby gives an error.

dependson [schema.]table

Execute a query to return a list of tables and views that depend on the specified table or view. As with dependedonby, schema and table are not taken as wildcard expressions. If the schema is not specified, the current schema is assumed. A record is returned for every table or view which depends on the specified table. The format of rows returned is as follows. The meaning of the DEPENDER_TYPE and DEPENDENCY columns are the same as with dependedonby.

COLUMN

NAME

DESCRIPTION

1

DEPENDER_SCHEMA

Schema containing the depending table.

2

DEPENDER_TABLE

Name of the depending table.

3

DEPENDER_TYPE

Type of the depending table:

T base table

V view

S system table

4

DEPENDENCY

Type of dependency:

F foreign key dependency

V view dependency

dependson orders results by DEPENDER_SCHEMA and DEPENDER_TABLE, in that order. If the specified table does not exist, dependson gives an error.

     default (3):
          top  myserver as alice
            2  myserver as bob
            3  myserver as charlie
     alternate_conn_stack:
          top  myserver as dave

If **-vv** is given, even more information is shown. Next to each
connection is shown a list of statement handle stacks separated by
spaces. If a statement handle stack has more than one statement in
it, the number of statements it has is shown in brackets. The current
statement handle stack in a connection is denoted by (*). For
example:
.. code-block:: bash

     default (3):
          top  myserver as alice:  default(*)
            2  myserver as bob:  default(2) anotherstmtstack(3*)
            3  myserver as charlie:  default yetanotherstmtstack(*)
     alternate_conn_stack:
          top  myserver as dave:  default(*)

STATEMENT HANDLE STACKS

A statement handle stack is a data structure of statement handles. New statement handles may be “pushed” (placed on top of the stack), and existing statement handles can be “popped” (removed from the top of the stack and destroyed). Only the topmost statement handle on the stack is accessible at any one time, and it is an error to attempt to pop a statement handle if it’s the only one remaining on the stack.

The stack that is automatically created when the session agent starts up is called default. It is an error to attempt to drop this statement handle stack.

Very often, a single statement handle will suffice for simple operations so the user will not even have to use pushstmt and popstmt. Some applications, notably those which need to keep a cursor open while executing another statement, require two or more statement handles, which is where the stack becomes useful. See the “Drop all tables matching a wildcard” example in the EXAMPLES section for further information.

Sometimes, however, the stack model is insufficient. If you create a statement handle and push it onto the stack, for as long as that statement exists it is impossible to do anything with the other statements on the stack. If an application needs to perform many interleaved reads on two cursors on two different statement handles, this becomes a problem.

For this reason, wxbashtools provides for the manipulation of alternative, named, statement handle stacks. The initial stack is called default, but the user is allowed to create other stacks with createstmt. This newly-created stack can be switched to with usestmt. From then on all pushes and pops are performed on that stack, and all statement operations are performed on the topmost statement handle on that new stack.

The following example demonstrates interleaved reads from two different cursors.

# Open this cursor on the default statement handle
execute "select a,b,c from table1"

# Create a new statement handle (actually a statement
# handle stack, but since we won't push any more handles
# on to it we might as well think of it as just a statement
# handle).
createstmt otherstmt

# Switch to this new stack, and open a cursor on its statement
usestmt otherstmt
execute "select a,b,c from table2"

# Fetch ten rows from each table, interleaving them
for ((i=0; i<10; i++)); do
    # Fetch a row from the first statement
    usestmt default
    fetch
    if [ $RESULT -eq 0 ]; then
        echo "[table1] a=$C1, b=$C2, c=$C3"
    fi

    # Fetch a row from the second statement
    usestmt otherstmt
    fetch
    if [ $RESULT -eq 0 ]; then
        echo "[table2] a=$C1, b=$C2, c=$C3"
    fi
done

# Close second cursor
close

# Destroy our alternate stack - the current stack will
# automatically switch back to the previously-selected
# one, which was "default".
dropstmt otherstmt

# Close first cursor
close

CONNECTION HANDLE STACKS

Analogously with statement handle stacks, wxbashtools provides support for connection handle stacking. The connection handle stack default is provided automatically when the agent starts, and this stack, like any other newly created stack, starts with one connection handle on it.

Simple scripts should only need to use one connection - this default connection which is created on connect and destroyed on disconnect.

For more complex applications, connection handle stacks might be useful. For example, suppose your application consists of shell scripts that call other shell scripts. It is good practice to put a connect at the start and a disconnect at the end of each shell script, sharing login credentials between the scripts using exported environment variables. This means a subsidiary script cannot close cursors or change to and from autocommit mode in a way that confuses the main script; all its operations are performed in isolation with the main script’s connection.

As an example, here is a main script which calls frobnicate.sh:

# Main script: myapp.sh

source wxbashtools
export SERVER=myserver
export USERNAME=bill
export PASSWORD=burble

# Create a new connection
connect $SERVER $USERNAME $PASSWORD || die "failed to connect"

autocommit off

# Run a query and bring back one row, but leave the cursor open
execute "select something, somethingelse from sometable"
fetch

# Call some other script to do something with the fetched values
./frobnicate.sh $C1 $C2

# Fetch more rows, safe in the knowledge that frobnicate hasn't
# closed our cursor.
fetch; fetch; fetch

# Disconnect and exit
disconnect

exit 0

frobnicate.sh, shown below, needs to be careful to do everything in its own connection handle so that it doesn’t close cursors owned by the calling script.

# Subsidiary script: frobnicate.sh

# Create a new connection, which goes to the top of the connection
# handle stack - all our frobnicate operations will be done on this
# connection until we call disconnect

connect $SERVER $USERNAME $PASSWORD || die "failed to connect"

run "insert into frobnicated values ($1, $2)"

# Mess about all we like with transactions and cursors - because
# we've got our own connection, it doesn't affect that of our
# calling script.
commit
autocommit off
createstmt foo
createstmt bar

# Disconnect, which destroys our connection handle and pops
# it off the stack. Now everything is in the same state it
# was when the calling script called us.
disconnect

exit 0

As with statement handle stacks, when you use connect to push a new connection onto the stack, any other connections on the stack are inaccessible until you pop your connection off again with disconnect.

If you need arbitrary interleaved access to two or more connection handles, you need to create additional connection handle stacks. A connection handle stack is created with the createconn command. It takes a connection stack name, a Kognitio data source name, a user name and a password, and creates a connection handle stack with one connection handle already on it.

You can switch between connection handle stacks using the useconn command or by setting the WXCONN environment variable. For an example of how and why this might be used, see “Running logging statements in a separate connection handle” in the EXAMPLES section.

ERROR HANDLING

In the event that a function fails, SQLSTATE is set to the ODBC SQLSTATE returned as a result of failure to make an ODBC call. ERROR_CODE contains the six-character Kognitio error code if applicable, and ERROR_TEXT contains a fuller error message.

A failed function will always return nonzero and set RESULT to this value.

Example values for the above variables are as follows. They are shown after an attempt was made to select from a nonexistent table.

if [ -z "$WXBASHTOOLS" ]; then
    source wxbashtools
fi

This code snippet can be included in all scripts without having to know or care whether it’s the main script or a script that might be called by another script.

ENVIRONMENT VARIABLES

The wxbashtools functions set a number of environment variables for the purposes of result retrieval and error diagnosis. They are listed below.

C1, C2, C3…

Set by fetch.

Environment variables C1, C2 etc are set to the same as RECORD[1], RECORD[2] and so on.

COLUMN_COUNT

Set by execute, run and fetch. Contains the number of columns in the result set returned by the last SQL command to be run, or 0 if the last SQL command isn’t one that returns results.

ERROR_CODE

Set by most functions that require an ODBC call. It contains the six-character Kognitio error code, which consists of two letters followed by four hexadecimal digits.

ERROR_TEXT

Set by any function that can fail in a non-trivial way. It contains the full error message returned by ODBC, or a suitable explanatory message if the error was not produced by ODBC.

N1, N2, N3…

Set by execute.

Environment variables N1, N2 etc are set to the same as NAMES[1], NAMES[2] and so on.

NAMES

Set by execute.

NAMES is a bash array variable containing the names of the columns returned by a select query. Its zeroth element, referenced by ${NAMES[0]} or $NAMES, contains a comma-separated list of column names. Subsequent elements contain the individual names; ${NAMES[1]} gives the first column name, ${NAMES[2]} the second, and so on.

RECORD

Set by fetch.

RECORD is a bash array variable containing the record most recently fetched using fetch. Its zeroth element, referenced by ${RECORD[0]} or equivalently RECORD, contains a CSV representation of the record. Subsequent elements contain the record’s individual fields. ${RECORD[1]} contains the first field, ${RECORD[2]} contains the second, and so on.

RESULT

General-purpose success/failure variable. Set by every function except die, it contains the return code of the last wxbashtools function to be called.

ROW_COUNT

Set by execute for a non-SELECT query, and run for any query. Incremented on every successful fetch.

SQLSTATE

Set by most functions that require an ODBC call. It contains the five-character ODBC diagnostic code, or SQLSTATE.

Contains the number of rows returned by a run call, the number of rows affected by a non-SELECT execute, or the number of rows fetched so far using fetch.

WXCONN

Set by useconn and used by functions that perform any operation on a connection handle. It specifies the name of the connection stack to use. If unset, the default connection stack is used.

WX_AGENT_TIMEOUT

The agent exits after $WX_AGENT_TIMEOUT seconds of inactivity. Inactivity is defined as the absence of any connections to the agent, not connections from the agent to the database server. A long-running query will not trigger this timeout because wxbashtools maintains a connection to the agent while an SQL query is running so it can fetch results when the query has finished. This timeout is intended to clean up stale agents which have somehow continued running after your script has exited, as can happen if the script is given a KILL signal, for example.

This must be set before the first connect (that is, before the agent starts) to have any effect. Unset the variable, or set it to 0, to disable the inactivity timeout. The default is 7200 (2 hours).

WX_AGENT_QUERY_TIMEOUT

If this environment variable is set, the agent will exit if a command takes longer than $WX_AGENT_QUERY_TIMEOUT seconds. This is intended to be used if it’s likely that your script might be ungracefully killed in the middle of a long-running query, which would leave the agent running.

This must be set before the first connect (that is, before the agent starts) to have any effect. If the variable is set to 0, or not set at all, the query timeout is disabled. This is the default.

WX_SESSION_AGENT_PID

Set by connect and unset by disconnect and killagent.

WX_SESSION_AGENT_PID contains the process ID of the Kognitio session agent.

WX_SESSION_SOCK

Set by connect and unset by disconnect and killagent.

WX_SESSION_SOCK contains the filename of the Unix domain socket used to communicate with the Kognitio session agent.

EXAMPLES

Before you can use the wxbashtools functions, you need to load the function definitions into your environment, like this:

source wxbashtools

or, alternatively:

. wxbashtools

wxsessionagent and wxsession need to be accessible from your PATH. This should be the case on any Kognitio AP or DB node running version 7.2 or later.

The following examples assume that wxbashtools has already been loaded as above, but that connect has not yet been called.

Determine system ID

Requirement: establish the system ID and output a message saying what it is.

# Connect to the server as user bob with the password fishcakes.
# Bail out if we can't.
connect myserver bob fishcakes || die "failed to connect"

# Results will be output as CSV, but since we've only got one
# column and one row, it'll just be a value. So put the output
# straight into a variable.
SYSTEM_ID=`run "select system_id from sys.ipe_boot"` || die

echo "The system ID is $SYSTEM_ID"

disconnect

Check for login failures, email results

Send an email to the admin if there have been any login failures today, with a table giving details.

This can be implemented using wxbashtools as follows.

# Connect to the server, and bail out if we can't
connect myserver sys spaghetti || die "failed to connect"

# Execute a query to get all of today's login failures
execute <<EOF
select user_id, ldate, ltime, net_address, client_name
from sys.ipe_login
where operation = 15
and ldate = current_date
order by ldate desc, ltime desc
EOF
if [ $? -ne 0 ];  then die "couldn't execute"; fi

# Print a heading
echo "Login failures so far today:" > /tmp/report.txt
printf "%-18s%-22s%-16s%-23s\n" \
    "USER NAME" \
    "LOGIN TIME" \
    "IP ADDRESS" \
    "HOSTNAME" >> /tmp/report.txt

# While there are still rows to fetch, fetch them and prettyprint
# them to the file. We can use $C1 or ${RECORD[1]}, they're
# interchangeable.
while fetch; do
    printf "%-18s%-22s%-16s%-23s\n" \
        "$C1" "$C2 $C3" "$C4" "$C5" >> /tmp/report.txt
done

# Make sure the last error code was 100 (end of results) and
# not anything else, which would be an error.
if [ $RESULT -ne 100 ]; then die; fi

# If there was at least one row, send out an email
if [ $ROW_COUNT -gt 0 ]; then
    mail -s "$ROW_COUNT login failures today" \
        admin@mydomain.com < /tmp/report.txt
fi

# Tidy up our temporary file
rm /tmp/report.txt

disconnect

Drop all tables matching a wildcard

Suppose we want to drop all tables in our default schema that begin with the word JUNK. We can do this as follows.

# Connect to the server, failing if this can't be done
connect myserver fred atropine || die

# We need autocommit mode off for this, or every time we drop
# a table we'll close our cursor
autocommit off

# Execute a query to get all table names beginning with JUNK
execute <<EOF
select name
from sys.ipe_table
where schema_id = current_schema_id
and name like 'JUNK%'
EOF
if [ $? -ne 0 ]; then die "execute failed"; fi

# For each row we fetch, drop the table by that name. We need
# to do this in a new, temporary statement handle, because if
# we use our existing statement handle then the cursor we're
# reading from will be closed.
while fetch; do
    pushstmt
    execute "drop table \"$C1\"" || die "couldn't drop $C1"
    echo "Dropped $C1"
    popstmt
done

if [ $RESULT -ne 100 ]; then
    die "fetch gave unexpected return code $RESULT"
fi

# Commit our transaction and disconnect
commit
disconnect

For further brevity we could have used subexecute instead of manually calling pushstmt, execute and popstmt, but the above example illustrates the principle.

Output all objects which depend on a given table

This example uses dependson in a recursive function printdeps to print a tree of dependencies containing all the objects that depend on the table specified as an argument to the script.

#!/bin/bash

source wxbashtools

# Recursive function printdeps() which prints the
# dependencies of the table $1.
# $2 is a recursion depth counter.
printdeps() {
    # Do this in our own statement handle, so we don't
    # interfere with open cursors on previous recursion levels
    pushstmt

    # Run the query
    dependson "$1"
    if [ $RESULT -ne 0 ]; then
        return $RESULT
    elif [ $2 -eq 1 ]; then
        # Print the table name if this is the first call
        echo "$1"
    fi

    # For each dependency...
    while fetch; do
        printf "%*s" $(($2 * 2)) ""      # pad with 2*depth spaces
        echo "$C1.$C2 ($C3)"             # write table name & type
        printdeps "$C1.$C2" $(($2 + 1))  # recurse
    done

    popstmt
    return 0
}

# Start of script
EXIT_CODE=0

# Check we actually have an argument to the script
if [ -z "$1" ]; then
        echo "Usage: $0 SCHEMA.TABLE" 1>&2
        exit 1
fi

# Connect to the database server
connect myserver fred wakizashi || die "Couldn't connect"

# Print the tree of dependencies
printdeps "$1" 1
if [ $? -ne 0 ]; then
    echo "printdeps failed" 1>&2
    EXIT_CODE=1
fi

# Disconnect and exit
disconnect
exit $EXIT_CODE

The above example can be amended to produce a tree of objects on which the given table depends by changing dependson to dependsonby.

Running logging statements in a separate connection handle

Suppose you have a script that performs some SQL commands. If any of these commands fail, you want to be able to roll back the whole transaction.

However, when each command is executed, you want to write a log entry to another table, and you always want these log entries to be committed, not rolled back. The logging queries need to be done on a different connection handle, and because access to it is required alongside the main connection, this needs to be its own connection stack. An example script is as follows:

# Connect to Kognitio server
connect myserver peter wobblement || die "failed to connect"

# Switch off autocommit mode
autocommit off

# Create a "logging" connection stack
createconn logging myserver peter wobblement

# Make sure the logging connection is in autocommit mode
WXCONN=logging autocommit on

# Increase the prices of all items by 2%, after we've logged
# that we're doing that.
WXCONN=logging run <<EOF
insert into logtable values (current_timestamp,
        'increasing prices by 2%')
EOF
run "update items set price = price * 1.02"
if [ $RESULT -ne 0 ]; then
        rollback; exit 1
fi

# Decrease the price of widgets by 10 pounds, again after
# logging that we're going to do that.
WXCONN=logging run <<EOF
insert into logtable values (current_timestamp,
        'taking 10 pounds off widgets')
EOF
run "update items set price = price - 10 where name = 'Widget'"
if [ $RESULT -ne 0 ]; then
        rollback; exit 1
fi

# We've finished, so commit the main transaction.
commit

# Logging connection is in autocommit mode, so we can just drop it
# and all the changes it made will persist
dropconn logging

disconnect
exit 0

EXIT STATUS

The general rule for functions defined by wxbashtools is that they return zero on success and nonzero on failure. Refer to the descriptions on the individual functions above.

An exit code of 11 from any function indicates the connection stack named by WXCONN does not exist. Other, less likely, exit statuses are detailed in the EXIT STATUS section of the wxsession man page, and any wxbashtools function may fail with one of these.

AUTHOR

Kognitio Limited