(Chapter 10 continued)

1

• (Chapter 10 continued)
• Our examples feature MySQL as the database
  engine.
• It's open source and free.
• It's fully featured.
• And it's platform independent for the most part.
• These examples and concepts carry over to other
  database systems.
• The DBI (database Interface) module provides the
  objects used in the program to connect to,
  prepare, and deliver SQL statements to the
  database engine.
• The DBDmysql module handles the details of the
  connection behind the scenes.
• Connecting to a different database requires only
  a different DBD (Database Driver) module be
  installed.

2
Overview of Perllt---gtMySQL interface

• You construct SQL queries with the DBI module in
  a Perl program.
• The driver handles the connection details.
• Raw SQL is delivered to the database engine.
• Returned data is stored in a DBI object, which
  has methods for extracting the data.

3
Overview of objects provided by the DBI
module. Database object This is the object that
holds the connection to the desired database. It
works kind of like a file handle since it
references the open database connection. For that
reason, we will call this object the database
handle. Query object This object is created by
calling a method of the database handle. It is
through methods of this object that we are able
to send SQL statements to the database
system. Driver object This object is seldom
used. It is only used for performing
administrative tasks, such as creating or
destroying an entire database, which has nothing
to do with SQL. Note that we are not talking
about creating or destroying tables which is
done through SQL statements. We include this for
completeness, and will not have occasion to use
this object.
4

• The following discussion assumes we have already
  imported the DBI module into the Perl program
• use DBI
• Connecting to a database
• dbhandle
• DBI-gtconnect("DBIdriverdatabaseName",
• user, password)
• dbhandle is reference to the database object
  created by this constructor.
• This statement assumes the MySQL daemon is
  running on the same computer as the Web server.
  Otherwise, more information such as the server
  address and port number is sent to the
  constructor.

5
Example Connect to a MySQL database named store,
where storeuser is the user name and pass is the
password. dbhandle DBI-gt
connect("DBImysqlstore","storeuser", "pass")
or errorPage(DBI-gterrstr()) The errstr()
method returns a string containing useful error
messages on failed connection attempts. Closing
an open database connection is very similar in
concept to closing an open file. dbhandle -gt
disconnect()
6

• Now assume we have an open dbhandle.
• Using the query object
• There are 4 steps in executing a SQL query using
  DBI.
• Prepare the SQL statement.
• Execute the SQL statement.
• Fetch any returned results from the query object.
  This step is only necessary for SELECT queries.
  CREATE, INSERT, DELETE, and DROP queries don't
  return useful data.
• Clear the query object.

7

• Preparing an SQL statement.
• qObj
• dbhandle-gtprepare("SQL_statement_as_string")
• qObj is reference to a new query object.
• It it usually requires some work to build an SQL
  statement in a Perl program, so we typically
  store the SQL statement in a Perl variable such
  as sql.
• sql work to put together an SQL statement
• qObjdbhandle-gtprepare(sql)
• or errorPage(dbhandle-gterrstr())
• Also, it is important to send descriptive
  preparation messages where you can see them
  during development.

8

• One important issue when building SQL statements
  is that special characters like (' and ) are
  properly escaped in the data.
• The database object has a quote() method to
  handle that job for you.
• sql dbhandle-gtquote("somestring")

9
Example Building an SQL statement where the
data comes from user input. This adds a row to
some_table, which has only one column. sql
"INSERT INTO some_table VALUES (" .
dbhandle-gtquote(formHash"someName") .
")" If you look at the resulting string, it is
an SQL statement squashed into one line.
"INSERT INTO some_table VALUES (someValue)"
Here someValue would be the data submitted by
the user and properly quoted using the quote()
method.
10

• 2. Executing the SQL statement.
• The result of the prepare() method of the
  database object is that the query object is
  constructed and the SQL statement is stored in
  the query object in some format.
• The execute() method of the query object takes
  that internally stored SQL statement and sends it
  to the database engine (with the help of the
  proper driver).
• qObj-gtexecute() or errorPage(qObj-gterrstr())
• Upon a successful execution, the query object
  contains data returned from the database query.
  Again, we are usually only concerned with the
  returned data when it is the result of a SELECT
  query which returns a sub-table of data..

11

• 3. Fetching the results from the query object.
• The fetchrow_array() method returns one row at a
  time out of the query object, with the columns
  already split into an array.
• _at_first_row qObj-gtfetchrow_array()
• You can fetch out one row at a time like this.
• But, often you need to iterate over the rows.
• while(_at_row qObj-gtfetchrow_array())
• do something with _at_row

12

• A row can also be fetched as a hash using the
  fetchrow_hashref() method.
• rowhashref qObj-gtfetchrow_hashref()
• row rowhashref
• As its name indicates, this method returns a
  scalar reference to a hash which is de-referenced
  into a hash variable.
• The hash contains one row of the returned
  sub-table.
• The keys are the column names (table keys).

13

• For SQL statements that only change the table,
  such as UPDATE and DELETE, you can get a count of
  how many rows were affected using the rows()
  method.
• number_changed qObj-gtrows()
• When qObj contains a sub-table returned from a
  SQL SELECT statement, the rows method works a bit
  differently. The query object only knows how
  many rows have been returned after all rows have
  been fetched out of the object. Presumably, the
  query object counts the rows as they are fetched
  out of the object. Fortunately, there is a
  simple trick to enable you to quickly determine
  the number of returned rows.
• qObj-gtfetchall_arrayref()
• number_of_rows qObj-gtrows()

14

• The fetchall_arrayref() method returns a scalar
  reference to a two dimensional array that
  contains the whole table, fetched out of the
  object. But that is not important, because we do
  not intend to use the array!! The important
  thing is that now the query object knows how many
  rows were returned. Note that if you want to
  re-fetch the rows, starting from the first, after
  the above code has executed, you must re-execute
  (step 2) thereby refreshing the query object with
  a newly returned sub-table.

15

• 4. Clear the query object.
• qObj-gtfinish()
• Omitting this step is usually not fatal. We
  will endeavor to remember this step as a matter
  of good programming practice.

16

• Database Example state table
• Like other state-keeping tools we have seen (our
  toolkit functions and the CGISecureState
  module), database utilities to handle
  state-keeping needs should
• Generate long session IDs.
• Create state records (formerly state files)
  identified by a long ID.
• Police the state table (formerly file cache) so
  that it doesn't accumulate records without bound.
• Be able to read a state record (into a hash or
  hash-like format).
• Be able to overwrite (update) a state record.
• Be able to provide session timeouts.

17
Example state table keeping state records for the
online quiz example from Chapter 7.

• Will use Perl's time function to set the age of
  a state record.
• The difference between the current value
  returned by time and the value in the state
  record will be the age of the record.
• The difference will be in seconds, due to the
  way time works.

• The states table would need to exist in advance
  and the Web application merely adds (and deletes)
  state records.

18

• Signature for the database version of
  get_long_id
• sub get_long_id_db
• my (dbhandle, table_name, cache_limit,
• file_life_span) _at__
• The dbhandle (connection to the database)needs
  to exist in advance.
• The table_name is analogous to the name of a
  state file directory.
• The cache_limit works as before.
• We still give the file_life_span is given in
  days. (We convert to seconds as needed in the
  function.)

19

• Logic of get_long_id_db See source file
  toolkit_db.pl
• Count the number of records in the table SELECT
  an entire column and count the rows.
• If the number of rows exceeds the cache_limit
• DELETE FROM table_name WHERE
• Again, count the number of records in the table.
• If the cache_limit is still exceeded
• Deny session / warn server administrator
• Create a new state record with an ID and time
  stamp
• Return the ID
• Note Before we created a state file with a call
  to write_state. Here, the state record needs to
  exist before we can do an SQL UPDATE to it with
  the new write_state_db function.