SPAWK
Home
Tutorial
Download
About

SPAWK Reference

The SPAWK API consists of a small number of AWK functions and an even smaller number of AWK global variables.

SPAWK functions

spawk_submit() – Query submission

spawk_submit(query[, fis])

Description

Function spawk_submit() is used from AWK to submit an SQL query to the database server for execution. If a database server is already available to accept new queries, then this server will be used, else a new server is pushed in the server stack for processing the query. By the way, speaking of servers actually mean database connections. Queries submitted from AWK, are passed through these connections to the main database server of the DBMS in use.

Parameters

Parameter query is the query to be submitted as a string. Queries can be of any type, either DQL, DML or DDL. DQL queries produce result set (even empty), while DML and DQL queries don't produce any result set.

Parameter fis is the field indexing scheme for the result set rows to be returned (if any). There are basically two basic field indexing schemes, numeric and associative. Whenever a submitted query produces a result set, then the rows will be returned to AWK as AWK arrays. Field indexing schemes affect the just the indexing for the returned array.

In numeric indexing schemes the whole row returned is indexed 0 in the array returned. Various fields of the returned row will be indexed as 1, 2, 3 etc. For example, for the following query: 

SELECT `login`, `registration`, `name`, `email` FROM `user`

each row will be returned as follows: row[1] is the login name, row[2] is the registration date, row[3] is the user name, and row[4] is the email address. If an associative indexing scheme is used instead, then row["login"] is the login name, row["registration"] is the registration name, row["name"] is the user name, and row["email"] is the email address.

The following indexing schemes are valid:

0 or NONE
This is the most simple field indexing scheme where the whole row is returned in the 0 indexed element of the returned array. There are no other returned elements in the array, so whenever the individual field values are not needed, 0 is the recommented fis to use.

1 or NUM (default)
The whole row is returned in the 0 indexed element, while individual field values will be returned as 1, 2, 3,… indexed elements of the returned array.

2 or ASSOC
The whole row is returned in the "" indexed element, while individual field values will be returned as field1, field2, field3,… indexed elements of the returned array, where fieldi are the field names given in the submitted query. These field names might be unknown at the query submisiion time, as is the case of SELECT * where the selected field list is not literally specified. In such cases the 2 fis is recommented, or else one cannot safely map the returned elements to the correspondig fields.

3 or BOTH
This is the same as the NUM and the ASSOC indexing schemes together, so there are both numeric AND string indexed elements returned in the array.

If fis parameter is not specified, then 1 is assumed.

Return value

Function spawk_submit() returns the following values:

1
An error occured, no result set has been produced and the spawk_result global variable is be set to 0. The server processed the query is ready to accept another query.

2
A DML/DDL query has been submitted succesfully. In that case there is no result set produced and the spawk_result global variable is set to 0. The server processed the query is ready to accept another query.

3
A DQL query has been submitted succesfully. In that case a result set is produced (even empty) and the spawk_result global variable is set to 1. The server processed the query cannot accept any new queries until all rows of the result set are returned to AWK, or freed. If a new query is submitted while the result set is still active, another server will be used to process the new query; if there is no such server active, then a new server will be spawned from AWK and pushed into the server stack and will stay active until the SPAWK session is alive or the spawk_reset() is called.

Example

The following scripts: 

spawk_submit("SELECT * FROM `user` WHERE `login` = 'panos')
spawk_fetchrow(user)


spawk_submit("SELECT * FROM `user` WHERE `login` = 'panos', 1)
spawk_fetchrow(user)


spawk_submit("SELECT * FROM `user` WHERE `login` = 'panos', "NUM")
spawk_fetchrow(user)

will return: 

user[1] = panos
user[2] = 2004-11-30 11:58:44
user[3] = Panos Papadopoulos
user[4] = panos@spawk.info
user[5] = MALE
user[6] = 1962
user[7] = 6367c48dd193d56ea7b0baad25b19455e529f5ee

while the following scripts: 

spawk_submit("SELECT * FROM `user` WHERE `login` = 'panos', 2)
spawk_fetchrow(user)


spawk_submit("SELECT * FROM `user` WHERE `login` = 'panos', "ASSOC")
spawk_fetchrow(user)

will return: 

user["login"] = panos
user["email"] = panos@spawk.info
user["password"] = 6367c48dd193d56ea7b0baad25b19455e529f5ee
user["name"] = Panos Papadopoulos
user["registration"] = 2004-11-30 11:58:44
user["gender"] = MALE
user["byear"] = 1962

spawk_fetchrow() – Retrieve next row of a result set

spawk_fetchone() – Retrieve just one row of a result set

spawk_fetchrow(array [, idx])
spawk_fetchone(array [, idx])

Description

Function spawk_fetchrow() retrieves next row of a result set into an array. The field indexing scheme (fis) of the array is either numeric, meaning that the individual field values are stored as array[1], array[2],… respectively, or associative, meaning that the individual field values are stored as array[FIELD1], array[FIELD2],… respectively, or both, meaning that both of the above indexing schemes will be used. The field indexing scheme is specified in the query submission via spawk_submit() function (default numeric).

Calling spawk_fetchrow() when no result set exists is a fatal error. All rows of the result set must be retrieved in order to free current client to accept new queries. If no more rows are needed from the result set, the set may be freed by calling the spawk_freerest() function.

Function spawk_fetchone() is just the same as spawk_fetchrow(), but frees the result set after the row retrieval.

Parameters

Function spawk_fetchrow() accepts at least one parameter, namely the name of the array where the next row of the result set will be retrieved.

The second parameter is the whole row index. If not given, then no whole row element is returned, except for 0 valued fis, when the whole row is returned indexed by 0.

Return value

The number of fields of the retrieved row is returned except of field indexing scheme 0 where 1 is returned. If there are no more rows in the result set, 0 is returned.

Example (1)

if (!spawk_submit("SELECT `login`, `name`, `email` FROM `user`"))
return

while (spawk_fetchrow(user))
print "login:", user[1], "name:", user[2], "email:", user[3]

Example (2)

if (!spawk_submit("SELECT * FROM `user`"), 2)
return

while (spawk_fetchrow(user))
print "login:", user["login"], "name:", user["name"], "email:", user["email"]

spawk_freerest() – Free result set

spawk_freerest()

Description

Function spawk_freerest() frees the result set and releases current server to accept new queries.

Return value

Function spawk_freerest() returns no value.

spawk_reset() – Reset server stack

spawk_reset()

Description

Function spawk_reset() closes all servers spawned by AWK and resets the server stack.

Return value

Function spawk_reset() returns the number of servers closed.

spawk_escape() – Escape quotes

spawk_escape(string[, unenclosed])

Description

Function spawk_escape() accepts a string argument and returns the string with single quotes escaped. If unenclosed is true, the string is returned with escaped single quotes, but without enclosing quotes.

Return value

Function spawk_escape() returns the string with single quotes escaped. The returned string is enclosed in single quotes so it can be used in queries, unless unenclosed non-zero parameter is passed.

Example

login = "Smith's"
spawk_submit("SELECT * FROM `user` WHERE `login` = " spawk_escape(login)")

is equivalent to 

spawk_submit("SELECT * FROM `user` WHERE `login` = 'Smith\'s'")

spawk_getpass() – Prompt for password

spawk_getpass([prompt])

Description

Function spawk_getpass() causes AWK to pause and prompt for a password from the control terminal. Usually needed for database root access, but can be used for normal users too.

Return values

Function spawk_getpass() returns the specified password.

Example

spawk_sesami("root")
spawk_password = spawk_getpass("Enter root password: ")

The same could be achieved by: 

spawk_sesami("root", spawk_getpass("Enter root password: "))

SPAWK global variables

Besides the functions, the SPAWK API contains a couple of AWK variables. Some of them are intrisic and it's an error to modify their value by hand, such as spawk_sqlerrno, spawk_affected etc, but most of them are set by the user and controls many of the SPAWK functions, e.g. in order to access a MySQL/MariaDB database, specific spawk_sesami array elements must previously be set.

spawk_sesami – Database authentication

spawk_sesami["dbuser"] spawk_sesami["dbpassword"] spawk_sesami["dbname"] spawk_sesami["dbcharset"]

Description

Array spawk_sesami is used to provide AWK with the necessary database connection information that will be used whenever a new server is spawned from AWK. If any of the array elements are missing, then the usual MySQL/MariaDB authentication methods will be used (cnf files etc). Usually you set these elements once in the BEGIN section of your AWK script, but you can change the connection parameters in the middle of the SPAWK session; doing so the already spawned servers will keep accepting requests, but any new server pushed in the stack will be connected with the new connection information.

Notice!
If you want to close all active servers use spawk_reset() function.

Valid indices
dbuser

The database user to be used for the connection with the database server.

dbpassword

The password of the database user to be used for the connection with the database server.

dbname

The default database to use.

dbhost

The default host machine for the database server.

dbcharset

The default character set to be used for the database connection, e.g. "utf8".

Example

@load "spawk.awk"

BEGIN {
	spawk_sesami["dbuser"] = "chat"
	spawk_sesami["dbpassword"] = "xxx"
	spawk_sesami["dbname"] = "chat"
	...

The above SPAWK snippet sets chat user with xxx password to be used for connecting to the chat database. The connections will be established whenever a new server is spawned from AWK.

spawk_OFS – Field separator

spawk_OFS

Description

By default selected fields are separated by OFS, but spawk_OFS variable can be used to set another separator for selected fields.

Default value

If neither spawk_OFS nor OFS are set, then the tab character is used.

spawk_null – Null value string

spawk_null

Description

spawk_null variable is a string used for null database valued fields. Null valued fields are not the same as empty or zero valued fields. The user can change spawk_null value either in the BEGIN section of the AWK script, or using -v command line option.

Default value

Control-N (ASCII 016 octal)

spawk_sqlerrno, spawk_sqlerror – SQL error codes/messages

spawk_sqlerrno spawk_sqlerror

Description

spawk_sqlerrno and spawk_sqlerror are set to the corresponding values after query failure. These values are not printed by default, except if spawk_verbose flag is set to a non-zero value.

Example

@load "spawk.awk"

...
if (spawk_submit(query) == 1) {
	print spawk_sqlerrno, spawk_sqlerror >"/dev/stderr"
	return
}
...

spawk_verbose – Verbose mode flag

spawk_verbose

Description

If set to non-zero value, sets SPAWK to verbose mode. In verbose mode various messages are printed while SPAWK program is running.

spawk_affected – Affected rows count

spawk_affected

Description

After submitting DML query, affected rows count is stored in spawk_affected global variable.

spawk_insertid – Autoincrement inserted row ID

spawk_insertid

Description

After inserting new row with autoincrement primary key column, the generated ID of the newly inserted row is stored in spawk_insertid global variable.

spawk_maxconn – Maximum number of servers

spawk_maxconn

Description

Is the maximum number of database servers (connections) allowed for SPAWK. Default value is 10 and maximum value is 100. Can be set only from the command line.

Example

awk -v spawk_maxconn=20 -f test.awk

Apr 03, 2022