Loading ...
Sorry, an error occurred while loading the content.

Re: [PBML] Formatting SQL query output

Expand Messages
  • david wright
    Howdy, I ve actually never used this feature of perl and in fact Perl Best Practices (Conway) advises against ever using it, preferring a simple text-based
    Message 1 of 4 , Jan 31, 2008
    • 0 Attachment
      Howdy,

      I've actually never used this feature of perl and in fact "Perl Best Practices" (Conway) advises against ever using it, preferring a simple text-based report. (or when perl6, Perl6::Form)

      The feature is called 'format', a search provided this:
      http://www.tutorialspoint.com/perl/perl_format.htm
      http://perldoc.perl.org/functions/format.html
      http://www.perl.com/doc/manual/html/pod/perlform.html
      http://www.webreference.com/programming/perl/format/


      -Dave

      --- On Thu, 1/31/08, Ward.P.Fontenot@... <Ward.P.Fontenot@...> wrote:

      > From: Ward.P.Fontenot@... <Ward.P.Fontenot@...>
      > Subject: [PBML] Formatting SQL query output
      > To: perl-beginner@yahoogroups.com
      > Date: Thursday, January 31, 2008, 11:32 AM
      > Hi list,
      >
      > Can someone point me to reference that will allow me to
      > take the output
      > of this script and format it using something like this
      >
      > format SQL_OUTPUT =
      >
      > Date Hostname
      > Facility Priority Message
      > ==================== ==================== ==========
      > ==========
      > ===============================================================
      > @<<<<<<<<<<<<<<<<<<
      > @<<<<<<<<<<<<<<<<<<
      > @<<<<<<<<<
      > @<<<<<<<<
      > @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
      > $msg_sent_time, $hostname, $msg_facility, $msg_priority,
      > $msg_text
      >
      > .
      >
      > I can't for the life of me remember how to bring the
      > two together
      >
      > #!/usr/bin/perl -w
      > #
      > #
      > use strict;
      > use DBI;
      >
      > ########################################################################
      > #
      > # Strict variable declarations
      > ########################################################################
      > #
      > my ($dbname, $dbh, $dbpass, $dbuser, $hostname,
      > $msg_facility,
      > $msg_id, $msg_priority, $msg_rcv_time, $msg_sent_time,
      > $msg_text,
      > $sql, $sth);
      >
      > ########################################################################
      > #
      > # Database settings
      > ########################################################################
      > #
      > $dbname = 'DBNAME';
      > $dbuser = 'DBUSER';
      > $dbpass = 'DBPASS';
      >
      > $dbh = DBI->connect("dbi:Pg:dbname=$dbname",
      > "$dbuser", "$dbpass",
      > {AutoCommit => 0});
      >
      > $sql = qq{
      > SELECT * FROM msg_table
      > WHERE msg_facility = 'auth'
      > AND msg_priority = 'notice'
      > };
      >
      > ########################################################################
      > #
      > # SQL stuff
      > ########################################################################
      > #
      > $sth = $dbh->prepare("$sql");
      > $sth->execute;
      >
      > $sth->bind_columns( undef, \$msg_rcv_time,
      > \$msg_sent_time, \$hostname,
      > \$msg_facility, \$msg_priority, \$msg_text,
      > \$msg_id );
      >
      >
      > while( $sth->fetch() ) {
      >
      > print "$msg_sent_time $hostname $msg_facility
      > $msg_priority $msg_text
      > \n";
      >
      > }
      >
      > $sth->finish;
      > $dbh->disconnect;
      >
      > Paul Fontenot
      > Wells Fargo
      > Cryptography Services - PKI
      > Email: ward.p.fontenot@...
      > Phone: (480) 437-7795
      >
      > This message may contain confidential and/or privileged
      > information. If
      > you are not the addressee or authorized to receive this for
      > the
      > addressee, you must not use, copy, disclose, or take any
      > action based on
      > this message or any information herein. If you have
      > received this
      > message in error, please advise the sender immediately by
      > reply e-mail
      > and delete this message. Thank you for your cooperation.
      >
      >
      >
      >
      > Unsubscribing info is here:
      > http://help.yahoo.com/help/us/groups/groups-32.html
      > Yahoo! Groups Links
      >
      >
      >
    • Ward.P.Fontenot@wellsfargo.com
      I know what it s called and how to use it - I just don t recall how to take the output of my sql query and put it in the formatted output. From:
      Message 2 of 4 , Jan 31, 2008
      • 0 Attachment
        I know what it's called and how to use it - I just don't recall how to
        take the output of my sql query and put it in the formatted output.



        From: perl-beginner@yahoogroups.com
        [mailto:perl-beginner@yahoogroups.com] On Behalf Of david wright
        Sent: Thursday, January 31, 2008 10:51 AM
        To: perl-beginner@yahoogroups.com
        Subject: Re: [PBML] Formatting SQL query output



        Howdy,

        I've actually never used this feature of perl and in fact "Perl Best
        Practices" (Conway) advises against ever using it, preferring a simple
        text-based report. (or when perl6, Perl6::Form)

        The feature is called 'format', a search provided this:
        http://www.tutorialspoint.com/perl/perl_format.htm
        http://perldoc.perl.org/functions/format.html
        http://www.perl.com/doc/manual/html/pod/perlform.html
        http://www.webreference.com/programming/perl/format/

        -Dave

        --- On Thu, 1/31/08, Ward.P.Fontenot@...
        <mailto:Ward.P.Fontenot%40wellsfargo.com>
        <Ward.P.Fontenot@...
        <mailto:Ward.P.Fontenot%40wellsfargo.com> > wrote:

        > From: Ward.P.Fontenot@...
        <mailto:Ward.P.Fontenot%40wellsfargo.com>
        <Ward.P.Fontenot@...
        <mailto:Ward.P.Fontenot%40wellsfargo.com> >
        > Subject: [PBML] Formatting SQL query output
        > To: perl-beginner@yahoogroups.com
        <mailto:perl-beginner%40yahoogroups.com>
        > Date: Thursday, January 31, 2008, 11:32 AM
        > Hi list,
        >
        > Can someone point me to reference that will allow me to
        > take the output
        > of this script and format it using something like this
        >
        > format SQL_OUTPUT =
        >
        > Date Hostname
        > Facility Priority Message
        > ==================== ==================== ==========
        > ==========
        > ===============================================================
        > @<<<<<<<<<<<<<<<<<<
        > @<<<<<<<<<<<<<<<<<<
        > @<<<<<<<<<
        > @<<<<<<<<
        > @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        > $msg_sent_time, $hostname, $msg_facility, $msg_priority,
        > $msg_text
        >
        > .
        >
        > I can't for the life of me remember how to bring the
        > two together
        >
        > #!/usr/bin/perl -w
        > #
        > #
        > use strict;
        > use DBI;
        >
        >
        ########################################################################
        > #
        > # Strict variable declarations
        >
        ########################################################################
        > #
        > my ($dbname, $dbh, $dbpass, $dbuser, $hostname,
        > $msg_facility,
        > $msg_id, $msg_priority, $msg_rcv_time, $msg_sent_time,
        > $msg_text,
        > $sql, $sth);
        >
        >
        ########################################################################
        > #
        > # Database settings
        >
        ########################################################################
        > #
        > $dbname = 'DBNAME';
        > $dbuser = 'DBUSER';
        > $dbpass = 'DBPASS';
        >
        > $dbh = DBI->connect("dbi:Pg:dbname=$dbname",
        > "$dbuser", "$dbpass",
        > {AutoCommit => 0});
        >
        > $sql = qq{
        > SELECT * FROM msg_table
        > WHERE msg_facility = 'auth'
        > AND msg_priority = 'notice'
        > };
        >
        >
        ########################################################################
        > #
        > # SQL stuff
        >
        ########################################################################
        > #
        > $sth = $dbh->prepare("$sql");
        > $sth->execute;
        >
        > $sth->bind_columns( undef, \$msg_rcv_time,
        > \$msg_sent_time, \$hostname,
        > \$msg_facility, \$msg_priority, \$msg_text,
        > \$msg_id );
        >
        >
        > while( $sth->fetch() ) {
        >
        > print "$msg_sent_time $hostname $msg_facility
        > $msg_priority $msg_text
        > \n";
        >
        > }
        >
        > $sth->finish;
        > $dbh->disconnect;
        >
        > Paul Fontenot
        > Wells Fargo
        > Cryptography Services - PKI
        > Email: ward.p.fontenot@...
        <mailto:ward.p.fontenot%40wellsfargo.com>
        > Phone: (480) 437-7795
        >
        > This message may contain confidential and/or privileged
        > information. If
        > you are not the addressee or authorized to receive this for
        > the
        > addressee, you must not use, copy, disclose, or take any
        > action based on
        > this message or any information herein. If you have
        > received this
        > message in error, please advise the sender immediately by
        > reply e-mail
        > and delete this message. Thank you for your cooperation.
        >
        >
        >
        >
        > Unsubscribing info is here:
        > http://help.yahoo.com/help/us/groups/groups-32.html
        > Yahoo! Groups Links
        >
        >
        >





        [Non-text portions of this message have been removed]
      • Ward.P.Fontenot@wellsfargo.com
        I have figured it out and included the solution in this email as the final script with output #!/usr/bin/perl -w # # use strict; use DBI;
        Message 3 of 4 , Jan 31, 2008
        • 0 Attachment
          I have figured it out and included the solution in this email as the
          final script with output



          #!/usr/bin/perl -w

          #

          #

          use strict;

          use DBI;



          ########################################################################
          #

          # Strict variable declarations

          ########################################################################
          #

          my ($dbname, $dbh, $dbpass, $dbuser, $hostname, $msg_facility,

          $msg_id, $msg_priority, $msg_rcv_time, $msg_sent_time, $msg_text,

          $server, $sql, $sth);



          ########################################################################
          #

          # Database settings

          ########################################################################
          #

          $dbname = 'DBNAME';

          $dbuser = 'DBUSER';

          $dbpass = 'DBPASS';



          $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "$dbuser", "$dbpass",
          {AutoCommit => 0});



          $sql = qq{ SELECT msg_sent_time, hostname, msg_facility, msg_priority,
          msg_text FROM msg_table

          WHERE msg_facility = 'auth'

          AND msg_priority = 'notice'

          };



          ########################################################################
          #

          # SQL stuff

          ########################################################################
          #

          $sth = $dbh->prepare("$sql");

          $sth->execute;



          $sth->bind_columns( undef, \$msg_sent_time, \$hostname, \$msg_facility,
          \$msg_priority, \$msg_text );



          while( $sth->fetch() ) {



          write;



          }



          format STDOUT_TOP =

          Date Hostname Facility Priority Message Text

          ==================== ==================== ======== ========
          ========================================================================
          ================================================

          .



          format STDOUT =

          @<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<< @<<<<<<< @<<<<<<<
          @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
          <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

          $msg_sent_time, $hostname, $msg_facility, $msg_priority, $msg_text

          .



          $sth->finish;

          $dbh->disconnect;



          Output:



          Date Hostname
          Facility Priority Message Text

          ==================== ==================== ======== ========
          ========================================================================
          ================================================

          2008-01-25 21:41:32 fontenwp-laptop auth
          notice sudo: (pam_unix) authentication failure; logname= uid=0
          euid=0 tty=pts/2 ruser= rhost= user=fontenwp

          2008-01-30 07:52:51 fontenwp-laptop auth
          notice sudo: (pam_unix) authentication failure; logname= uid=0
          euid=0 tty=pts/4 ruser= rhost= user=fontenwp

          2008-01-30 08:31:34 fontenwp-laptop auth
          notice sudo: (pam_unix) authentication failure; logname= uid=0
          euid=0 tty=pts/4 ruser= rhost= user=fontenwp



          From: perl-beginner@yahoogroups.com
          [mailto:perl-beginner@yahoogroups.com] On Behalf Of Fontenot, Ward P.
          Sent: Thursday, January 31, 2008 11:01 AM
          To: perl-beginner@yahoogroups.com
          Subject: RE: [PBML] Formatting SQL query output



          I know what it's called and how to use it - I just don't recall how to
          take the output of my sql query and put it in the formatted output.

          From: perl-beginner@yahoogroups.com
          <mailto:perl-beginner%40yahoogroups.com>
          [mailto:perl-beginner@yahoogroups.com
          <mailto:perl-beginner%40yahoogroups.com> ] On Behalf Of david wright
          Sent: Thursday, January 31, 2008 10:51 AM
          To: perl-beginner@yahoogroups.com
          <mailto:perl-beginner%40yahoogroups.com>
          Subject: Re: [PBML] Formatting SQL query output

          Howdy,

          I've actually never used this feature of perl and in fact "Perl Best
          Practices" (Conway) advises against ever using it, preferring a simple
          text-based report. (or when perl6, Perl6::Form)

          The feature is called 'format', a search provided this:
          http://www.tutorialspoint.com/perl/perl_format.htm
          http://perldoc.perl.org/functions/format.html
          http://www.perl.com/doc/manual/html/pod/perlform.html
          http://www.webreference.com/programming/perl/format/

          -Dave

          --- On Thu, 1/31/08, Ward.P.Fontenot@...
          <mailto:Ward.P.Fontenot%40wellsfargo.com>
          <mailto:Ward.P.Fontenot%40wellsfargo.com>
          <Ward.P.Fontenot@...
          <mailto:Ward.P.Fontenot%40wellsfargo.com>
          <mailto:Ward.P.Fontenot%40wellsfargo.com> > wrote:

          > From: Ward.P.Fontenot@...
          <mailto:Ward.P.Fontenot%40wellsfargo.com>
          <mailto:Ward.P.Fontenot%40wellsfargo.com>
          <Ward.P.Fontenot@...
          <mailto:Ward.P.Fontenot%40wellsfargo.com>
          <mailto:Ward.P.Fontenot%40wellsfargo.com> >
          > Subject: [PBML] Formatting SQL query output
          > To: perl-beginner@yahoogroups.com
          <mailto:perl-beginner%40yahoogroups.com>
          <mailto:perl-beginner%40yahoogroups.com>
          > Date: Thursday, January 31, 2008, 11:32 AM
          > Hi list,
          >
          > Can someone point me to reference that will allow me to
          > take the output
          > of this script and format it using something like this
          >
          > format SQL_OUTPUT =
          >
          > Date Hostname
          > Facility Priority Message
          > ==================== ==================== ==========
          > ==========
          > ===============================================================
          > @<<<<<<<<<<<<<<<<<<
          > @<<<<<<<<<<<<<<<<<<
          > @<<<<<<<<<
          > @<<<<<<<<
          > @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
          > $msg_sent_time, $hostname, $msg_facility, $msg_priority,
          > $msg_text
          >
          > .
          >
          > I can't for the life of me remember how to bring the
          > two together
          >
          > #!/usr/bin/perl -w
          > #
          > #
          > use strict;
          > use DBI;
          >
          >
          ########################################################################
          > #
          > # Strict variable declarations
          >
          ########################################################################
          > #
          > my ($dbname, $dbh, $dbpass, $dbuser, $hostname,
          > $msg_facility,
          > $msg_id, $msg_priority, $msg_rcv_time, $msg_sent_time,
          > $msg_text,
          > $sql, $sth);
          >
          >
          ########################################################################
          > #
          > # Database settings
          >
          ########################################################################
          > #
          > $dbname = 'DBNAME';
          > $dbuser = 'DBUSER';
          > $dbpass = 'DBPASS';
          >
          > $dbh = DBI->connect("dbi:Pg:dbname=$dbname",
          > "$dbuser", "$dbpass",
          > {AutoCommit => 0});
          >
          > $sql = qq{
          > SELECT * FROM msg_table
          > WHERE msg_facility = 'auth'
          > AND msg_priority = 'notice'
          > };
          >
          >
          ########################################################################
          > #
          > # SQL stuff
          >
          ########################################################################
          > #
          > $sth = $dbh->prepare("$sql");
          > $sth->execute;
          >
          > $sth->bind_columns( undef, \$msg_rcv_time,
          > \$msg_sent_time, \$hostname,
          > \$msg_facility, \$msg_priority, \$msg_text,
          > \$msg_id );
          >
          >
          > while( $sth->fetch() ) {
          >
          > print "$msg_sent_time $hostname $msg_facility
          > $msg_priority $msg_text
          > \n";
          >
          > }
          >
          > $sth->finish;
          > $dbh->disconnect;
          >
          > Paul Fontenot
          > Wells Fargo
          > Cryptography Services - PKI
          > Email: ward.p.fontenot@...
          <mailto:ward.p.fontenot%40wellsfargo.com>
          <mailto:ward.p.fontenot%40wellsfargo.com>
          > Phone: (480) 437-7795
          >
          > This message may contain confidential and/or privileged
          > information. If
          > you are not the addressee or authorized to receive this for
          > the
          > addressee, you must not use, copy, disclose, or take any
          > action based on
          > this message or any information herein. If you have
          > received this
          > message in error, please advise the sender immediately by
          > reply e-mail
          > and delete this message. Thank you for your cooperation.
          >
          >
          >
          >
          > Unsubscribing info is here:
          > http://help.yahoo.com/help/us/groups/groups-32.html
          > Yahoo! Groups Links
          >
          >
          >

          [Non-text portions of this message have been removed]





          [Non-text portions of this message have been removed]
        Your message has been successfully submitted and would be delivered to recipients shortly.