Fetching Column Data By Name

May 4, 2011

When using the DBI in a production setting, you will most often be reaching for the fetchrow_arrayref function to retrieve rows from your result set. The problem with using this function is that you have to reference the columns in the result set by index number, rather than by name. The DBI also provides a fetchrow_hashref function which provides for access to column data by name, but this function is significantly slower than the fetchrow_arrayref function. In fact, DBIx::JCL implements an interface to fetchrow_arrayref but does not implement an interface into fetchrow_hashref. Up until now you were only able to get to result set columns by knowing their index number, with the addition of two new DBIx::JCL functions you can now have the best of both worlds.

Release 2.42 implements two new functions db_col_names and db_col_index. Once you have prepared a query, you can call these functions to instantiate a named variable that contains the index of the desired column (or multiple variables for multiple columns). The example below demonstrates how you can use these two new functions to retrieve arrayref data by name.

After your query is prepared, you call db_col_names (line 7) to retrieve a ref to an array of column names, and a ref to a hash of column indexes by name. The db_col_index function (line 8) then uses the returned hash to help you instantiate a named variable that contains your desired index number. When you are iterating through your result set, you use your new variable to reference the desired column, as shown on line 12.

 1:  $sql = sys_read_job('sql','my_query_1');  # named sql mapped to conf file
 2:  $vcn = 'my_connection_1';  # named connection identifier mapped to conf file
 3:
 4:  db_connect($vcn);
 5:
 6:  db_prepare($vcn,$sql);
 7:  my ($cnames,$cindex) = db_col_names($vcn,'uc');
 8:  my $COLUMN_1 = db_col_index('COLUMN_1',$cindex);
 9:
10:  db_execute($vcn,$sql');
11:  while ( $row = db_fetchrow($vcn) ) {
12:      print ">>>@$row[$COLUMN_1]\n";  # using named column semantics
13:  }
14:
15:  db_disconnect($vcn);

For those of you that are new to DBIx::JCL, or may be considering using DBIx::JCL, one of the big advantages of using this module is the way it allows you to organize your database access applications. Line 1 gathers SQL from an external configuration file and line 2 references a named connection to a specific database, details of which are also maintained in an external configuration file. You never have to maintain SQL or connection parameters in your source files. If you are managing hundreds of database tasks, this turns out to be a daunting task, unless you are using DBIx::JCL. Four lines of code is all it takes to define a query, connect to the desired database, and prepare the query for execution.