Database Quick Start: Example Code

The following page contains example code showing how the database library is used. For complete details please read the individual pages describing each method.

Initializing the Database Library

The following code loads and initializes the database library based on your configuration settings:

get loadDatabase()

Once loaded the library is ready to be used as described below.

Note: If all your pages require database access you can connect automatically. See the connecting page for details.

Standard Query With Multiple Results

put dbQuery("SELECT name, title, email FROM myTable") into tQuery

repeat with i = 1 to tQuery["numrows"]
    put dbRow(i) into tRowData
    put tRowData["title"] & comma after tResultData
    put tRowData["name"] & comma after tResultData
    put tRowData["email"] & return after tResultData
end repeat

The above dbQuery() function returns an array.

Testing for Results

If you run queries that might not produce a result, you are encouraged to test for a result first:

put dbQuery("YOUR QUERY") into tQuery

# CHECK IF THE QUERY RESULT HAS DATA
if tQuery["numrows"] > 0 then

   repeat with i = 1 to tQuery["numrows"]
     put dbRow(i) into tRowData
     put tRowData["title"] & comma after tResultData
     put tRowData["name"] & comma after tResultData
     put tRowData["body"] & return after tResultData
   end repeat
end if

Standard Query With Single Result

get dbQuery("SELECT name FROM myTable LIMIT 1")

put dbRow(1) into tRow
put tRow["name"] into tName

Standard Insert

put "INSERT INTO mytable (title, name)
        VALUES (" & dbEscape(tTitle) & ", " & dbEscape(tName) & ")" into tSQL

get dbQuery(tSQL)

put dbAffectedRows() into tAffectedRows

The function dbAffectedRows() returns an integer or a boolean.

Active Record Query

The Active Record Pattern gives you a simplified means of retrieving data:

put dbGet("tableName") into tQuery

repeat with i = 1 to tQuery["numrows"]
    put dbRow(i) into tRowData
    put tRowData["title"] & comma after tTitles
end repeat

The above dbGet() function retrieves all the results from the supplied table. The Active Record library contains a full compliment of methods for working with data.

Active Record Insert

put tTitle into tData["title"]
put tName into tData["name"]
put tDate into tData["date"]

get dbInsert("mytable", tData)

-- Produces: "INSERT INTO mytable (title, name, date) VALUES ('" & tTitle & "', '" & tName & "', '" & tData & "')"