FW: William Blunn’s DBIX::MULTIROW – UPDATING MULTIPLE DATABASE ROWS QUICKLY AND EASILY

William Blunn wrote a good article on DBIx::MultiRow.

A requirement arises in many systems to update multiple SQL database rows. For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement for each row that requires an update. But if there are a large number of rows that require an update, then the overhead of issuing large numbers of UPDATE statements can result in the operation as a whole taking a long time to complete.

Go to William Blunn’s blog to read the blog post 🙂

He will be adding the Perl module to CPAN soon.

Share Button

Get it here! Perl DBD::Sybase 1.14.01 for Active State Perl 5.16 Windows XP/Vista/7/8 32bit AND 64bit

Assuming that you installed Sybase SDK OpenClient 15.7.

Sybase Openclient is included in the Sybase Developer’s Kit, and ASE PC Client. If you don’t have a license, you can download the ASE 15.7 Developer Edition for Windows which will include it.

Install ActiveState Perl from http://www.activestate.com (free) and install DBI if it isn’t already installed.  It should be but you never know…

  1. Start -> ActiveState Perl -> Perl Package Manager
  2. install DBI
  3. exit

Now, the easy part.  Install the DBD-Sybase-1.14 1.14 PPM:

  1. download DBD-Sybase-1.14
  2. extract zip file to temporary directory (e.g. c:\test)
  3. Start -> Run -> cmd.exe (as Administrator if Vista or Windows 7)
  4. cd \test
  5. ppm install DBD-Sybase.ppd
  6. exit

That’s it :)

It should automatically install the DBD::Sybase for Perl 5.16 32bit or 64bit depending on which version of Active State Perl you have installed.

UPDATE (March 6th, 2013):

The PPM is fixed now. Please let me know of any issues.

Share Button

ISUG Technical Journal Sep/Oct 2008 is out! Includes my “Perl in the Shell” article

The ISUG Technical Journal Sep/Oct 2008 is out.

This issue contains:

  • Sybase IQ and Sybase WorkSpace Part II By Mike Crocker
  • Perl in the shell By Jason Froebe
  • Taming the process nightmare: Consolidating a Horde of Process Diagrams with PowerDesigner By Mike Nicewarner
  • Information overload: Weapons-grade data analysis comes of age By Teresa Foster
  • Maximizing PowerBuilder and SQLAnywhere Part III SQLAnywhere, a PowerBuilder Developer’s perspective
    By Chris Pollach
  • select random (stuff) from SYBASE Command shell by proxy By Bill Grant
  • Mobile Enterprise Everywhere: Taking Sybase on the road By Adrian Bridgwater
  • Sybase TechWave 2008: Time for the technology tsunami

You need to be an ISUG member to receive the ISUG Technical Journal.  Join ISUG today!

International Sybase User Group
International Sybase User Group
Share Button

Perl Problem with filtered Mason code & db connection

Hi,

In chapter 5 (Advanced Features pgs 82,83) of Embedding Perl in HTML with Mason from O’Reilly, the"a simple SQL select expressed in something like a taglib style" example appears to be straight forward.  It is but it doesn’t seem to work too well.

The premise is that the ".components/sql/select" will filter the chunk of html code:

< &| .components/sql/select, query => 'SELECT name, type FROM sysobjects' &>
  <tr>
     <td>%name</td>
     <td>%type</td>
  </tr>

The select returns data correctly but the ".components/sql/select" doesn’t appear to be printing the code hmmmm….  see the very bottom for the answer… I didn’t catch it for awhile but later saw the cause and could have kicked myself.

produced HTML code:

<html>
<head>
  <title> Databases</title>
</head>
<body>
   <table border="1">
 	<tr>
 		<th>Server</th><th>Service Type</th>
	</tr>
    </table>

    <center><img src="/images/seal.png" height="170" width="170"/></center>
</body>
</html>

test.html:

<table border="1">
    <tr>
        <th>Server</th>
        <th>Service Type</th>
    </tr>
    < &| .components/sql/select, query => ‘SELECT name, type FROM sysobjects’ &>
        <tr>
            <td>%name</td>
            <td>%type</td>
        </tr>
    
</table>

.components/sql/select:

    < %args>
        $query
    
    < %init>
        my $sth = $dbh->prepare($query);

        while (my $row = $sth->fetchrow_hashref) {
            my $content = $m->content;

            $content =~ s/%(w+)/$row->{$1}/g;
            $m->print($content);
        }

        $sth->finish;

Solution:

     < %init>
        my $sth = $dbh->prepare($query);

        while (my $row = $sth->fetchrow_hashref) {

should be:

    < %init>
        my $sth = $dbh->prepare($query);

        $sth->execute();  # dang typos in the book Cry

        while (my $row = $sth->fetchrow_hashref) {
Share Button

Perl DBD::Sybase and signal handling

There appears to a bug with DBD::Sybase or perhaps Sybase OpenClient ctlib (threaded) that causes custom signal handlers to segfault.  This tripped up a monitoring script that I wrote.

I’ve asked the perl module maintainer, Michael Peppler, whether this is a DBD::Sybase bug or an Openclient bug.

Update:

looks like this is a DBD::Sybase bug not an OpenClient ctlib as the example $SYBASE/$SYBASE_OCS/sample/ctlibrary/multthrd.c with an added signal handler works fine:
$ diff multthrd.c $SYBASE/$SYBASE_OCS/sample/ctlibrary/multthrd.c

150,151d149
< #include <signal.h>
< 217,223d214
< void leave(int sig);
<
< void leave(int sig) {
<     printf("caught SIGINT\n");
<     exit(-1);
< }
<
258,259d248
<       (void) signal(SIGINT,leave);
<
344,348d332
<        for(;;) {
<         printf("Ready...\n");
<         (void)getchar();
<     }
<&#91;/perl&#93;

&#91;text gutter="false"&#93;
.....
Thread_2:All done processing rows - total 116.
Ready...
caught SIGINT&#91;/text&#93;
<h3>Bug text:
#!/usr/local/bin/perl

use strict;
use DBI;
use DBD::Sybase;

$SIG{'INT'} = sub {print "hi there\n";exit();};

print "go\n";

while (1) {
  sleep(1);
}

If I run this and then type ^C I get a segmentation fault. If I comment out

the ‘use DBD::Sybase;’ line, it works fine

Workaround:

Build DBD::Sybase against the nonthreaded (libct.so not libct_r.so) openclient libraries.

Share Button

Using Perl, Mason, mod_perl and DBI: Result sets

I’m new to Mason and am having trouble printing a simple html table pulling data from a table on a database.  I know this has to do with scope but there has to be an easier way other than putting the entire dbms call (including printing the table) within a perl code section.

One thing I have noticed is the apparent lack of any useful information of using Mason with DBI connections.

This is how you do it if you wanted a single file to connect to the dbms and then spit out the result set:

< %shared>
    my %serverDump;

    my $dbh = DBI->connect("dbi:Sybase:server=mydb", ‘info’, ‘infopwd’, { RaiseError => 1, PrintError => 1 } );
    $dbh->do("use sybase_dba");

    my $query = "select serverName, min(dumpdate) from dbLastBackup group by serverName";

    my $sth = $dbh->prepare($query);
    $sth->execute or die "Error: unable to run query! " . $dbh->errstr;
    
<table Border=1>
  % while (my $row = $sth->fetchrow_arrayref ) {

    <tr>
      <td>< % $row->[0] %></td><td>< % $row->[1] %></td>
    </tr>

  % }
</table>

Note, don’t forget to uncomment "PerlSetVar MasonErrorMode fatal".  For some reason, no errors were being sent to the browser. 🙁

 

Share Button