# Use the environment variable to retrieve the default
# database.  This is either the database specified in
# the DB tab of the Options window, or it is the
# database associated with your channel if you are 
# using Iguana.

# Note: if database support is turned off, you'll still
# get a database object back.
db = environment.get_default_database()

# Here's a string that we'd likely want to escape
unescapedString = "Henry's"
log("Unescaped String: " + unescapedString)

# Here's how we escape that string using the escaping
# appropriate for the type of database you are working
# with.
escapedString = db.escape_string(unescapedString)
log("Escaped String: " + escapedString)

# Now we insert values "Henry's", "pass" and 1 into our
# Users table by first building up the SQL command and
# then using the execute() method.
sql = "insert into Users (UserID, Password, SuperUser) values (" + escapedString + ", 'pass', 1)"
log("Issuing SQL insert: " + sql)
db.execute(sql)

# Here we create a default result set to use in the 
# case that our Database support has been turned off
# in the DB tab of the Options window. Note that it has 
# three columns because of the string we pass into the
# function get_empty_default_result_set.
defaultResultSet = db.get_empty_default_result_set( "UserID, Password, SuperUser" ) 

# Add a row of data to the default result set.
newRow = defaultResultSet.add_row()
newRow.set_string_value(0, "Robert")
newRow.set_string_value(1, "MyPassword")
newRow.set_integer_value(2, 1)

# Add another row of data to the default result set.
newRow = defaultResultSet.add_row()
newRow.set_string_value(0, "Robert1")
newRow.set_string_value(1, "MyPassword1")
newRow.set_integer_value(2, 0)

# Now we're going to execute a select against the
# database and log our results.  Note: this python
# code will NOT throw an exception in the case that 
# database support is turned off because we're
# going to use our default result set in such a case.
selectSQL = "select * from Users where SuperUser = 1"

# Since we want a result set back, we're going to
# call execute_select() instead of execute()
tempResultSet = db.execute_select(selectSQL)

if (tempResultSet == 1):
   resultSet = defaultResultSet
else:
resultSet = tempResultSet

# For more information about result sets, see "Database 
# Result Set Object" and "Database Result Set Row Object"
# in the manual.
for RowIndex in range(0, resultSet.count_of_row() ): 
   log(resultSet.value(RowIndex, "UserID"))
   log(resultSet.value(RowIndex, "Password"))
   if (resultSet.value(RowIndex, "SuperUser") == 1):
      log("YES")
   else:
      log("NO")