O'Reilly - Safari Books Online - 1565926994 - Programming the Perl DBI - The Diigo Meta page

• 24 Sep 08
  

  Philip Guth

  jlearning perl dbi sql PD

  • Retrieving data from a database using DBI is essentially a four-stage cycle:

        

    1. The prepare stage parses an SQL statement, validates that statement, and returns a statement handle representing that statement within the database.

    2. Providing the prepare stage has returned a valid statement handle, the next stage is to execute that statement within the database. This actually performs the query and begins to populate data structures within the database with the queried data. At this stage, however, your Perl program does not have access to the queried data.

    3. The third stage is known as the fetch stage, in which the actual data is fetched from the database using the statement handle. The fetch stage pulls the queried data, row by row, into Perl data structures, such as scalars or hashes, which can then be manipulated and post-processed by your program.

       The fetch stage ends once all the data has been fetched, or it can be terminated early using the finish() method.

       If you'll need to re-execute() your query later, possibly with different parameters, then you can just keep your statement handle, re-execute() it, and so jump back to stage 2.

    4. The final stage in the data retrieval cycle is the deallocation stage. This is essentially an automatic internal cleanup exercise in which the DBI and driver deallocate the statement handle and associated information. For some drivers, that process may also involve talking to the database to tell it to deallocate any information it may hold related to the statement.

       All this is done for you automatically, triggered by Perl's own garbage collection mechanism.
  • ### Prepare the SQL statement ( assuming $dbh exists ) $sth = $dbh->prepare( "  SELECT meg.name, st.site_type   FROM megaliths meg, site_types st  WHERE meg.site_type_id = st.id  " );  ### Execute the SQL statement and generate a result set $sth->execute();  ### Fetch each row of result data from the database as a list while ( ( $name, $type ) = $sth->fetchrow_array ) {  ### Print out a wee message....  print "Megalithic site $name is a $type "; }

  • You could also fetch the data via fetchrow_array() into an array variable instead of a list of scalar variables by writing:

        

    while ( @row = $sth->fetchrow_array ) {     ### Print out a wee message     print "Megalith site $row[0] is a $row[1] "; }
  • dump_results( ) internally uses the neat_list( )  utility function (described in the previous chapter) for the actual formatting operation. Because of this, you should not use the output of dump_results( ) for any data transfer or data processing activity. It's only meant for human consumption.

  2 more annotations...

Would you like to comment?

Join Diigo for a free account, or sign in if you are already a member.