Query Helper Functions

rigDbAffectedRows()

Displays the number of affected rows, when doing "write" type queries (insert, update, etc.).

Note: In MySQL "DELETE FROM TABLE" returns 0 affected rows. The database library has a small hack that allows it to return the correct number of affected rows. By default this hack is enabled but it can be turned off in the database driver file.

rigDbCountAll(pTable)

Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example:

get rigDbCountAll("myTable")

# Produces an integer, like 25

rigDbPlatform()

Outputs the database platform you are running (mysql, postgre, sqlite):

get rigDbPlatform()

rigDbVersion()

Outputs the database version you are running:

get rigDbVersion()

rigDbLastQuery()

Returns the last query that was run (the query string, not the result). Example:

put rigDbLastQuery() into tStr

# Produces:  SELECT * FROM sometable....

The following two functions help simplify the process of writing database INSERTs and UPDATEs.

rigDbInsertString(pTable, pDataA)

This function simplifies the process of writing database inserts. It returns a correctly formatted SQL insert string. Example:

put tName into tArray["name"]
put tEmail into tArray["email"]
put tURL into tArray["url"]

put rigDbInsertString("tablename", tArray) into tStr

Parameters

• pTable: is the table name
• pDataA: is an associative array with the data to be inserted

The example above produces:

INSERT INTO tablename (name, email, url) VALUES ('Rick', 'rick@example.com', 'example.com')

Note: Values are automatically escaped, producing safer queries.

rigDbUpdateString(pTable, pDataA, pWhere)

This function simplifies the process of writing database updates. It returns a correctly formatted SQL update string. Example:

put tName into tData["name"]
put tEmail into tData["email"]
put tURL into tData["url"]

put "authorID = 1 AND status = 'active'" into tWhere

put rigDbUpdateString("tablename", tData, tWhere) into tStr

Parameters

• pTable: is the table name
• pDataA: is an associative array with the data to be updated
• pWhere: is the "where" clause which can be a string or an associative array

The example above produces:

 UPDATE tablename SET name = 'Rick', email = 'rick@example.com', url = 'example.com' WHERE authorID = 1 AND status = 'active'

Note: Values are automatically escaped, producing safer queries.

rigSqlDateTimeToIntDate(pDateTime)

This function converts SQL datetime format to Internet date format (RFC 2822). Example:

put "2016-06-27 15:45:30" into tDateTime
put rigSqlDateTimeToIntDate(tDateTime) into gData["dateTime"]
-- the value of gData["dateTime"] is: Mon, 27 Jun 2016 15:45:30 +0200

Parameters

• pDateTime: is a SQL DATETIME date and time value