dreman has asked for the wisdom of the Perl Monks concerning the following question:

I don't know how to use db connection for 2 different Sybase servers within the same program.
How can I capture information from 2 different servers. Here is a sample of the actual code.

Thanks for your ideas. 

#!/usr/local/bin/perl -w

use warnings;
use DBD::Sybase;
use DBI;

sub SQL {
my $parm1       = $_[0]; #sql statement
my $parm2       = $_[1]; #server
my $parm3       = $_[2]; #username
my $parm4       = $_[3]; #password
my $parm5       = $_[4]; #dbname
my $parm6       = $_[5]; #task type
my $parm7       = $_[6]; #object_name


my ( @data, $data );
<P>

</P>
my $dbh=DBI->connect("dbi:Sybase:$parm2","$parm3", "$parm4" ) or die D
+BI::errstr;
$dbh->do("use ${parm5}");
$sth = $dbh->prepare(${parm1});
$sth->execute;

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


$sth->finish;
$dbh->disconnect();

}

###MAIN####
&SQL("select \@\@servername", "syb_arlu9_new","testman","testman06", "
+master");
sleep 10;
&SQL("select \@\@servername", "syb_arlu10_new","testman","testman06", 
+"master");

[download]

*****************
** My Results: **
*****************

testSQL.pl
syb_arlu9_new
syb_arlu9_new

[download]

20061201 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

Replies are listed 'Best First'.
Re: database connection output
by themage (Friar) on Nov 30, 2006 at 16:53 UTC
    hi dreman,

    All you need is to create two diferent $dbhs. Example: 
    my $dbh1=DBI->connect("dbi:Sybase:$server1","$user1", "$pass1" ) or di
+e DBI::errstr;
my $dbh2=DBI->connect("dbi:Sybase:$server2","$user2", "$pass2" ) or di
+e DBI::errstr;

$dbh1->do("SOME SQL ON SERVER 1");
$dbh2->do("SOME SQL ON SERVER 2");

    [download]
    It was not that hard, was it? Wasn't this a ask first, think later question?

    TheMage
    Talking Web
[reply]
[d/l]
      Wasn't this a ask first, think later question?

      Given that his variables names are $dbh and $sth and so on, it may be that he is coding by copy-and-paste and may not fully understand what a DBI handle is.

      Sanity? Oh, yeah, I've got all kinds of sanity. In fact, I've developed whole new kinds of sanity. You can just call me "Mister Sanity". Why, I've got so much sanity it's driving me crazy.
[reply]
      All you need is to create two diferent $dbhs

      Am I missing something here? It looks to me as tho' he _is_ creating two different database handles. The $dbh variable is lexically scoped to the &SQL subroutine so a new one is created each time the subroutine is called.

      --
      <http://dave.org.uk>

      "The first rule of Perl club is you do not talk about Perl club."
      -- Chip Salzenberg

[reply]
Re: database connection output
by grep (Monsignor) on Nov 30, 2006 at 18:21 UTC
    To add to the other comments:

    A database handle is an object the represents your connection to a database. It is represented by the scalar you define it to. So as was pointed out earlier, you can create as many as you would like. 

    my $dbname   = 'production';
my $username = 'code_monkey';
my $password = '!#@&*';

# You can also specify the database here instead of using the '$dbh->d
+o("use $dbname");'
my $dbh_foo = DBI->connect("dbi:Sybase:server=Foo;database=$dbname",$u
+sername,$password );

# Here you can call a second one
my $dbh_bar = DBI->connect("dbi:Sybase:server=Bar;database=$dbname",$u
+sername,$password );

# You can even call a completely different RDBMS
my $dbh_baz = DBI->connect("dbi:Pg:host=baz;dbname=$dbname",$username,
+$password );

    [download]

    The same goes for $sth Statement Handles (which belong to their respective Database Handle). So you can create several statement handles or reuse the same one (generally with Placeholders and Bind Values). Since you don't appear to be using Placeholders and Bind Values, I will say that they are highly encouraged, as they handle quoting correctly, offer a performance increase for multiple calls, and prevent possible SQL Injection attacks. 

    my $pet = '5" Stuffed Camel';
my $sql = 'SELECT id,name FROM users WHERE has_pet = ?'; 
# '?' signifies a placeholder

my $sth_sel = $dbh->prepare($sql);
$sth_sel->execute($pet);
# Now $pet is properly quoted

    [download]
    Or reuse Statement Handles for some performance gain: 
    my $sql = 'SELECT id,name FROM users WHERE has_pet = ?';
my $sth_sel = $dbh->prepare($sql);
foreach my $pet (qw/ monkey bear tiger /) {
  $sth_sel->execute($pet);
}

    [download]

    I can't stress enough that you should be using Placeholders and Bind Values for slmost everything DBI related.

    grep
    XP matters not. Look at me. Judge me by my XP, do you?

[reply]
[d/l]
[select]
Re: database connection output
by throop (Chaplain) on Nov 30, 2006 at 17:41 UTC
    jonadab has commented ably on a solution to your problem. I have some other suggestions.

    Consult Writeup Formatting Tips. In it, you will learn to encase your code in a <code> block. Note that where you meant to write 

    $_[1]

    [download]
    your entry presents
    $_1
    Mouse over the '1' and Note how the itis hyperlinked to perlmonks node 1. The square brackets link to articles unless they are enclosed in <code>

    use strict. That would have told you that your $data variable was never used.

    You are just obfusciating your code by defining 

    my $parm1 = $_[0]; #sql statement
my $parm2 = $_[1]; #server
my $parm3 = $_[2]; #username
my $parm4 = $_[3]; #password
my $parm5 = $_[4]; #dbname
  # etc

    [download]
    Either just use the @_ variables directly, or give them meaningful variable names: 
    my($sql_statement, $server, $username, $password, $dbname) = @_;

    [download]
    Putting a variable in doublequotes by itself (as you're doing with parm3 and parm4) is pernicious. 
    my $dbh=DBI->connect("dbi:Sybase:$parm2","$parm3", "$parm4" ) or die D
+BI::errstr;

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom