CODEWITHSUNDEEP
sql-serverperl
user15796482
user15796482

Reputation:

How to get the columns names along with resultset for Perl/SQL Server

I'm working with a Perl script that will connect to SQL server and get the data that I wanted. So I'm done with the Query that will show the result that I want, My problem is it only show the result set and not including the column names. I want to print also the column name along with the result set.

Here's my script

my $sql = "SELECT top (20) [code] AS Code
      ,Replace(Replace(Replace(Replace(Replace(Replace(Replace
      (Replace(Replace(Replace(Replace
      ([name],'\“','\"'),'\”','\"'),'<= ','&le;'),'>=','&ge;'),'<','&lt;'),'>','&gt;'),CHAR(10),'<br>'),'\n',' '),CHAR(13),' '),'–','-'),'’',''+NCHAR(39)+'') AS ShortDesc
      ,Replace(Replace(Replace(Replace(Replace(Replace(Replace
      (Replace(Replace(Replace(Replace
      ([description],'\“','\"'),'\”','\"'),'<= ','&le;'),'>=','&ge;'),'<','&lt;'),'>','&gt;'),CHAR(10),'<br>'),'\n',' '),CHAR(13),' '),'–','-'),'’',''+NCHAR(39)+'') AS LongDesc
      ,CASE WHEN isobsolete = 0 THEN 'NULL' ELSE 'Y' END AS Obsolete
      
FROM (
     SELECT ROW_NUMBER() OVER(PARTITION BY code ORDER BY effectivefromdate DESC) rn, * 
     FROM [CodingSuite_STG].[Codes].[Hcpcs] ) cs
     WHERE  rn=1 
     order by code asc";
     
my $sth = $dbh->prepare( $sql );

 
#Execute the statement
$sth->execute();


while ( my @row = $sth->fetchrow_array ) {
     print "@row\n";
}

#Close the connection
$sth->finish();
$dbh->disconnect();

just like this.

Upvotes: 0

Views: 640

Answers (2)

mscha
mscha

Reputation: 6840

You could use fetchrow_hashref:

while (my $row = $sth->fetchrow_hashref) {
    print join(', ', map { "$_=$row->{$_}" } sort keys %$row);
}

Upvotes: 2

choroba
choroba

Reputation: 241918

As mentioned in the documentation of DBI:

my @names = @{ $sth->{NAME} };

Upvotes: 2

Related Questions