Prev: FAQ 8.32 How can I write expect in Perl?
Next: Comparing two files
From: Sharif Islam on 7 Apr 2008 15:25
Hi,
I have two queries -- I pass the result of the SELECT query to a stored
procedure. The result from the SELECT query is different during every
pass and I somehow need to remember the old value and pass that to the
store procedure when next time the perl script runs. I would appreciate
some help.


#!/usr/bin/perl -w
use strict ;
use DBI qw/:sql_types/;

my $SQL_server = "mssql" ;
my $SQL_username = "user" ;
my $SQL_password = "XXXXX" ;

my $dbh = DBI->connect("dbi:Sybase:server=$SQL_server",
$SQL_username,
$SQL_password,
{PrintError => 1});

die "Unable for connect to server $DBI::errstr" unless $dbh;

$dbh->do("use MyDatabase"); # Set the database to use.
my $last ; # this is where I would store the old value, need to
remember it from the previous run

my $qry="SELECT Col1 from Table" ;
my $sth = $dbh->prepare($qry);
$sth->execute ;
#new value
my @row = $sth->fetchrow_array ;

#stored procedure
my $sp_qry = "EXEC Stored_Procedure ?" ;
my $sth1 = $dbh->prepare($sp_qry) ;
$sth1->bind_param(1, $row[0], SQL_VARCHAR);
$sth1->execute
or die "Couldn't execute statement: " . $sth1->errstr;
print "\n";
while (my @rs = $sth1->fetchrow_array)
{
print "Results: $rs[0]\n" ;
}

$sth1->finish() ;
$dbh->disconnect() ;
From: J. Gleixner on 7 Apr 2008 15:46
Sharif Islam wrote:
> Hi,
> I have two queries -- I pass the result of the SELECT query to a stored
> procedure. The result from the SELECT query is different during every
> pass and I somehow need to remember the old value and pass that to the
> store procedure when next time the perl script runs. I would appreciate
> some help.

Write it to a file:

perldoc -f open
perldoc perlopentut

Or store it in a table by date, or some other relevant key, if needed,
then query the table on your next run.

From: Sharif Islam on 7 Apr 2008 17:14
J. Gleixner wrote:
> Sharif Islam wrote:
>> Hi,
>> I have two queries -- I pass the result of the SELECT query to a
>> stored procedure. The result from the SELECT query is different during
>> every pass and I somehow need to remember the old value and pass that
>> to the store procedure when next time the perl script runs. I would
>> appreciate some help.
>
> Write it to a file:
>
> perldoc -f open
> perldoc perlopentut
>
> Or store it in a table by date, or some other relevant key, if needed,
> then query the table on your next run.
>

thanks, the file method worked fine.

--s
From: xhoster on 7 Apr 2008 18:00
Sharif Islam <mislam(a)spam.uiuc.edu> wrote:
> Hi,
> I have two queries -- I pass the result of the SELECT query to a stored
> procedure. The result from the SELECT query is different during every
> pass and I somehow need to remember the old value and pass that to the
> store procedure when next time the perl script runs. I would appreciate
> some help.

That is an interesting requirement. If the thing to be given to the
prepared statement on execution N is the thing returned by the select
in execution N-1, then what is to be given to the prepared statement
when N==1?

Since you are already using a database, I'd use the database to store
what needs to be stored between executions. That way you have to worry
less about what happens in case of crashes.

But most of all I'd seriously reconsider if this is really the best way
to go about solving whatever it is that I was trying to solve.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.
 | 
Pages: 1
Prev: FAQ 8.32 How can I write expect in Perl?
Next: Comparing two files