Perl DBI and Show command resultsets


Perl DBI and Show command resultsets


I am trying to run a Show command following a SQL statements. In SQL Asstant it runs fine, getting me multiple of resultsets.  However in Perl it only returns the first part of the result in this case only first DDL.  I am using the ODBC with perl and I use, fetchrow_array().  I have also tried selectall_arrayref(q)

but no luck getting the complete resultsets. It seems somehow I am getting just one row or part of the first array list.

I tried loop through the resultsets as below:

 foreach $row (@$rows) {
      print join(", ", map {defined $_ ? $_ : "(null)"} @$row), "\n";


Also secondly I noticed with no column name it is difficult to force the recordset to spill the specific row. I am not sure what is the column name or names it retuns from a show command with sql via odbc.

Can someone point me to the right direction/s?

Thanks in advance.


Tags (2)
Teradata Employee

Re: Perl DBI and Show command resultsets

When a request can return multiple result sets, you will need an outer loop that checks "state" or "err" to determine if there are more results remaining to process.


Since there is only one column, you probably don't need the column name (though you should be able to obtain it from the metadata, e.g. NAME attribute). Depending on which "fetch" method you choose, try either "shift" (for a list) or accessing the [0] element (for an array).


Re: Perl DBI and Show command resultsets

@Fred Thanks for the helpful suggestion.  I was able to get the array element [0] and it's entire contents.  It looks like the array content was a condensed one line. With the following command I am able to split it ($st).  However it works fine completely with one ddl and soon I have multiple ddls (from multiple objects on the select, I don't see them on the resultset or on the array.) I tried to debug the array but all I see is only 1 ddl, even though there are multiples. I am not sure if fetcharray (I am using "selectall_arrayref" for the resultsets to get the array ) has any limitation or I will need to do something else....

foreach $h (@$rows)
$st= $h->[0];


Any thoughts?


Tags (1)
Teradata Employee

Re: Perl DBI and Show command resultsets

The "all" is "all rows from the first/next resultset", not "all rows from all resultsets".

You have to fetch again to move to the next resultset. (I don't think you can do that via selectall_arrayref.)