«

I am writing a client server utility in perl. The idea is for user to submit a
read query on a production database from development machines. The query will be
passed to a machine via tcp which can connect to production database and then
pass the result back to the client.

This model works great. here is the code of my prototype program. My problem and
question after the code.

Client program
#! /usr/bin/perl -w
use strict ;
use IO::Socket;
my $sock = new IO::Socket::INET (
PeerAddr => '127.0.0.1',
PeerPort => '7070',
Proto => 'tcp',
);
die "Could not create socket: $!\n" unless $sock;
$sock->autoflush(1);
my $sql_no = 0 ;
while (1) {
$sql_no++;
print "SQL[$sql_no]: " ;
my $sql = <STDIN> ;
print $sock "$sql" ;
if ( substr($sql,0,4) eq "exit" ) {
last ;
}
while (my $ret_line = <$sock> ) {
chomp($ret_line);
last if ($ret_line eq '<END>' ) ;
print "$ret_line\n" ;
}
}
print $sock "<EXIT>" . "\n" ;
close($sock);

Server program


#! /usr/bin/perl -w
use strict ;
use warnings;
use DBI ;
my $new_sock ;

use IO::Socket;
my $sock = new IO::Socket::INET (
LocalHost => '127.0.0.1',
LocalPort => '7070',
Proto => 'tcp',
Listen => 10,
Reuse => 1);
die "Could not create socket: $!\n" unless $sock;
$sock->autoflush(1);
$SIG{CHLD} = 'IGNORE' ;
while ( $new_sock = $sock->accept()) {
my $pid = fork();
die "Cannot fork: $!" unless defined($pid);
if ($pid == 0) { # only child process
$sock->autoflush(1);
&process_sql();
system("kill -9 $$") ;
}
}
close ($sock);

sub process_sql() {
my $line ;
my $ret_line ;
my $dbh = DBI->connect('dbig:dbname=testdb','','',
{'RaiseError' => 1, 'PrintError' => 1});
$dbh->{AutoCommit} = 0 ;
$dbh->{RaiseError} = 0 ;
$dbh->{PrintError} = 0 ;
while ($line = <$new_sock>) {
chomp($line) ;
last if ( $line eq "exit" ) ;
my $chk_line = uc $line ;
if ( substr($chk_line,0,6) ne "SELECT") {
print $new_sock "Error: only select is allowed\n" ;
print $new_sock "<END>" . "\n" ;
next ;
}
my $sth = $dbh->prepare($line) ;
$sth->execute() ;
if ( $DBI::err) {
print $new_sock "$DBI::errstr\n";
print $new_sock "<END>" . "\n" ;
next ;
}
while ( my @data = $sth->fetchrow_array() ) {
$ret_line = join('|',@data);
print $new_sock $ret_line . "\n" ;
}
print $new_sock "<END>" . "\n" ;
$sth->finish();
}
$dbh->disconnect();
}

Now I am facing the program to change the program to 3 tier. The reason is that
we won't get an open port from dev to any of the machines which is on the prod
network. So what I need to do is to send the request from the client machine to
a proxy server which will route the query to another machine which will connect
to the database and give the result back to the proxy server, which will pass it
back to the client program.

What I did was to change the port of the above mentioned server program to 7071
and introduced another program. This program will act as server to the client
programs (on port 7070) and as a client to the db server running on port 7071.

#! /usr/bin/perl -w
use strict ;
use warnings;
use IO::Socket ;
my $new_sock ;

my $ssock = new IO::Socket::INET (
LocalHost => '127.0.0.1',
LocalPort => '7071',
Proto => 'tcp',
) ;
die "Could not create socket 7071 for app server: $!\n" unless $ssock;

my $sock = new IO::Socket::INET (
LocalHost => '127.0.0.1',
LocalPort => '7070',
Proto => 'tcp',
Listen => 10,
Reuse => 1);
die "Could not create socket: $!\n" unless $sock;

The server program on 7071 starts fine. (the same code pasted before as "server
program"). But when the above mentioned new program app.pl tries to start, it
errors out "Could not create 7071 for app server: Address already in use".

Why? I am using the same logic on what is working, except that this new script
app.pl is both a server and a client.

Is there a restriction on IO::Socket as only port it can use in a script.

TIA.

On 25 sep, 01:30, perl...@dba.invalid.com wrote:

[...]
> Server program
>
> #! /usr/bin/perl -w
> use strict ;
> use warnings;
> use DBI ;
> my $new_sock ;
>
> use IO::Socket;
> my $sock = new IO::Socket::INET (
> * *LocalHost => '127.0.0.1',
> * *LocalPort => '7070',
> * *Proto => 'tcp',
> * *Listen => 10,
> * *Reuse => 1);
> die "Could not create socket: $!\n" unless $sock;
> $sock->autoflush(1);
> $SIG{CHLD} = 'IGNORE' ;
> while ( $new_sock = $sock->accept()) {
> * * my $pid = fork();
> * * die "Cannot fork: $!" unless defined($pid);
> * * if ($pid == 0) { *# only child process
> * * * * $sock->autoflush(1);
> * * * * &process_sql();
> * * * * system("kill -9 $$") ;
> * * }}
>
> close ($sock);

[...]

> What I did was to change the port of the above mentioned server program to 7071
> and introduced another program. This program will act as server to the client
> programs (on port 7070) and as a client to the db server running on port 7071.
>
> #! /usr/bin/perl -w
> use strict ;
> use warnings;
> use IO::Socket ;
> my $new_sock ;
>
> my $ssock = new IO::Socket::INET (
> * * * *LocalHost => '127.0.0.1',
> * * * *LocalPort => '7071',
> * * * *Proto => 'tcp',
> * * * ) ;
> die "Could not create socket 7071 for app server: $!\n" unless $ssock;
>
> my $sock = new IO::Socket::INET (
> * *LocalHost => '127.0.0.1',
> * *LocalPort => '7070',
> * *Proto => 'tcp',
> * *Listen => 10,
> * *Reuse => 1);
> die "Could not create socket: $!\n" unless $sock;
>
> The server program on 7071 starts fine. (the same code pasted before as "server
> program"). But when the above mentioned new program app.pl tries to start, it
> errors out "Could not create 7071 for app server: Address already in use"..
>
> Why? I am using the same logic on what is working, except that this new script
> app.pl is both a server and a client.
>
> Is there a restriction on IO::Socket as only port it can use in a script.

In the server program, you changed the listen-port LocalPort to 7071,
which will bind the server to port 7071. Next, in the client program,
you also specify LocalPort => 7071, which also tries to bind to the
same port, which of course fails, because it is already in use by the
server.

You should specify PeerHost and PeerPort in the client program, and
let the socket call fiddle out the Local address by itself.

In article <357303f4-f54f-4c94-9fbd->,
>In the server program, you changed the listen-port LocalPort to 7071,
>which will bind the server to port 7071. Next, in the client program,
>you also specify LocalPort =3D> 7071, which also tries to bind to the
>same port, which of course fails, because it is already in use by the
>server.
>
>You should specify PeerHost and PeerPort in the client program, and
>let the socket call fiddle out the Local address by itself.

yup that was the cause. thanks. Looks like I did cut-n-paste badly,
took the code from my working server script instead of taking it
from the client script.