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

The first parameter is the table name, the second is an associative array with the data to be inserted. The above example 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

The first parameter is the table name, the second is an associative array with the data to be updated, and the third parameter is the "where" clause which can be a string or an associative array. The above example 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

The parameter is a SQL DATETIME date and time value.