#sql [option] sql command [#endsql]

---

DESCRIPTION

For SQL SELECT commands, an option may be given; available options (described below) are #load, #processrows, and several variations of #dump. If no option is given, #processrows is the default. No option should be used with SQL INSERT, UPDATE, or DELETE.

sql command may use a single line construct or a multi-line construct formatted as in examples 2 or 3 below. #endsql must be used to terminate a multi-line construct.

For all SQL commands except SELECT, the status of the command execution will be available in the @_STATUS script variable (0 indicates success; for pocketSQL 1 indicates requested record not found; 15 indicates locked table; etc).

Two separate SQL database connections are available. For connection 1, use #sql; for connection 2, use #sql2 instead of #sql (all other syntax is the same). When using the SQL functions the connection may be specified using the dbc parameter (see below).

Using #sql in ploticus scripts

EXAMPLES

#sql insert into transactions values ( "TR6824", 155.43, "K" )

Example 2 shows the proper way to set up a multi-line construct:

 #sql 
   update transactions
   set amount = 158.23, type = "P"
   where tracknum = "@TNUM"
 #endsql

Example 3 will load all retrieved fields as script variables. It uses the 2nd database connection. Query should retrieve one row.

 #sql2 #load
   select * from people where people_id = @people_id
 #endsql

Example 4 is like above except written as a single line:

#sql2 #load select name as venue from venues where venue_id = @v

OPTIONS

#load

The retrieved fields will be loaded as script variables. Query should retrieve one row; if it retrieves multiple rows the first one will be used. If the query got no results, the variables will be set to a zero length string (""). The following example would set script variables username and pw to the retrieved values:

 #sql #load 
 select username, password as pw 
  from users 
  where session_id = "@SESS_ID"
 #endsql

#dumptab

All rows of results will be streamed in tab-delimited format. Example:

 #sql #dumptab select prog_id, prog_name from proglist

#dumphtml

All rows of results will be streamed in HTML table format. Example:

 #sql #dumphtml select * from venues

#dump

All rows of results will be streamed with no automatic field separation. The SQL SELECT command must supply any necessary field separation. Example:

 #sql #dump select id, "	", lastname, ", ", firstname from people

#dumpsilent

All rows of results will be retrieved then discarded. Used to get a count of the number of rows. Example:

 #sql #dumpsilent select * from venues
 #set N = $sqlrowcount()

#processrows

Indicates that results will be retrieved and processed later one row at a time using the $sqlrow() function (see below). When processing rows, retrieved fields are available as script variables (if a name has an embedded periods (.) these are converted to underscores since script variable names cannot contain periods). Example:

 	#sql #processrows
 	  select meascode, measdesc, value from mstats 
 	  where project_id = @PROJID
 	#endsql
 
 	// get the result rows..
 	#while $sqlrow = 0
 	  // access the fields as variables..
 	  #if @meascode like A*
 	    ... etc...
 
 	#endloop

SQL-RELATED FUNCTIONS

$sqlrow( dbc )

Retrieve next row of results. After this is called, result fields will be accessible by @name. NULL fields will be converted to "". Returns 0 to indicate success, 1 to indicate no row retrieved and no more rows, or an error code.
Example: #call $sqlrow()
Example: #call $sqlrow( 2 )

$sqlrowcount( dbc )

Return number of rows retrieved/affected by most recently executed sql command. The exact behavior of row count may depend on the type of database being used.

With pocketSQL , the row count is available immediately after an #sql executes. This row count is exact except when SELECT is used with DISTINCT or LIMIT and result rows are retrieved individually using $sqlrow() in which case the count will be exact after all rows have been retrieved.

Example: #if $sqlrowcount( ) == 0
Example: #if $sqlrowcount( 2 ) == 0

$sqlgetnames( dbc, outmode )

Using the most recently submitted sql SELECT command, write result field names to standard output. outmode may be dumptab for tab delimited field names, or dumphtml for an html table row. Returns 0 to indicate success, or a non-zero error code.
Example: #call $sqlgetnames( dumptab )

$sqlprefix( dbc, prefix )

Set a prefix to be prepended to all result field names retrieved by the next SELECT command. This may be used to prevent result field names from colliding with existing script variable names. Must be used after the #sql statement and before $sqlrow(). The prefix remains in effect for the current query only.
Example: #call $sqlprefix( "s" )

$sqlstripprefix( dbc, prefix )

Indicates that prefix should be removed from the beginning of any result field name where it appears, for example to remove table name prefix from join results. Must be used after the #sql statement, and before $sqlrow(). Remains in effect for the current query only.
Example: #call $sqlstripprefix( "people." )

NOTES

Interfaces to databases are implemented in dbinterface.c

#sql cannot be intermingled with #shell.