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

My script below needs to insert a record if it does not exists in my database and write it to a file if it does exist also to write the total counts for each in file.

use strict;
use warnings; 
use DBI;
#use Win32::ODBC;
use DBD::ODBC; 

# Global Variables
my @statement;    # Array of sql statements that will be used for Sql
my @data;        # Array of table rows read from Sybase
my @message;    # Array of error messages used for debugging Sql 
#my $destination = /errorlog.txt 
my $errcounter = 0;
my $succounter = 0;
my $UniKey;
# SQL Connection            
my $dbh = DBI->connect( "dbi:ODBC:RETQC_SQL", "sa", "",
  {RaiseError => 1, PrintError => 1, AutoCommit => 0} ) or
die "Unable to connect: ";#. $DBI::errstr . "\n";



# Sybase Connection
my $dbh2 = DBI->connect( "dbi:ODBC:BPSA", "", "", 
  {RaiseError => 1, PrintError => 1, AutoCommit => 0} ) or 
die "Unable to connect: ";#. $DBI::errstr . "\n";

open( OUTFILE, "> c:/errorlog.txt" ) or die "Couldn't open file for re
+ading.$!\n";


 #Create the sql statement for Sybase and execute it
$sth = $dbh2->prepare("SELECT (dbo.tbookkeeping_trans.branch_cd + dbo.
+tbookkeeping_trans.account_cd)As AccountNo,
'TRI'as TransferFlag,'NCT' as TransferType, 'C' as RejectType, dbo.tbo
+okkeeping_trans.security_adp_nbr  as AdpNumber,
dbo.tbookkeeping_trans.share_trans_qty  as Shares,dbo.tbookkeeping_tra
+ns.processing_dt
FROM dbo.tbookkeeping_trans   
WHERE (dbo.tbookkeeping_trans.client_nbr='0030' AND dbo.tbookkeeping_t
+rans.entry_cd IN ('JNL', 'JRL', 'REC') AND
dbo.tbookkeeping_trans.branch_cd >'248') ORDER BY  1");
$sth->execute;




my $insh=$dbh->prepare("INSERT INTO ClearTransferData(AccountNo, Trans
+ferFlag, TransferType, RejectType, AdpNumber, Shares, ProcessingDate)
+ VALUES ( ?, ?, ?, ?, ?, ?, ? )");

eval {
  while((@data) = $sth->fetchrow_array)
  {
 my $ncount = 0;
 $ncount = "Select Count(*) From ClearTransferData    Where AccountNo 
+= $data[0] and  TransferFlag = $data[1] and TransferType = $data[2] a
+nd RejectType = $data[3] and AdpNumber = $data[4] and Shares = $data[
+5] and ProcessingDate = $data[6] ";

  If $ncount gt 0;
   {
   print OUTFILE @data;
   my $errcounter++;
   }
   $insh->execute(@data);
   my$succounter++;
   
  }
};
if($@)
{
  print STDERR "The insert died: $@";
}
$dbh2->commit();
$dbh->commit();

 #Close the sql DB Connection
$dbh->disconnect;

 #Close the Sybase DB connection
$sth->finish;
$dbh2->disconnect;

[download]

edited: Wed May 14 20:54:23 2003 by jeffa - code tags and a readmore

Replies are listed 'Best First'.
Re: Reading from databse and writing to file.
by runrig (Abbot) on May 15, 2003 at 01:48 UTC
    Some of what you're doing makes no sense. You set $ncount to zero, then to a sql statement (and never prepare it, let alone execute it), then you check to see if that statement is greater than '0' (perl has both 'gt' and '>' depending on string or numeric comparison).

    Your errcounter and succounter variables are declared at the global level, then you redeclare them in a very limited scope (you need to check out what my really does), and you don't do anything with them in this snippet anyway, so why include in this example?

    Instead of asking "Does this work?", it would be better if you actually tried it, see what it does, and then try to fix it. If you are having trouble with any particular part, give some detail about what the problem is with the smallest amount of code possible (and to reduce noise, make up an example with simpler SQL statements, with just a few columns), and we'll still be here :-)

    Oh, and welcome to the monestery :-)

[reply]
      What I am trying to do with $ncount is reset it to 0 each time in the loop. Then I want to do a query on my database to see if the record is there. The result number should be assigned to the $ncount and if the count is gt 0 then do something else something else.
[reply]
        What I am trying to do with $ncount is reset it to 0 each time...
        You need to separate your count from your sql statement. And you need to prepare, execute, and fetch from your sql statement (you can do all at once, see the selectrow_array method in the DBI docs).
[reply]
      This is the error I get.
      DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverCOUNT field incorrect or syntax error (SQL-07001)(DBD: st_execute/SQLExecute err=-1) at Bookkeeping.pl line 51.
      How can I fix it.
[reply]
        This is the error I get.
        DBD::ODBC::st execute failed: ... How can I fix it.
        You print the sql statement, and see what's wrong with it. If it doesn't work when you cut and paste it into a SQL query window (or whatever query utility you have), then its not going to work in perl. Maybe you need to quote some/all of your arguments, maybe you should use placeholders (see the DBI docs).
[reply]
Re: Reading from databse and writing to file.
by WhiteBird (Hermit) on May 15, 2003 at 01:03 UTC
    Part of the problem with code like this (for me) is the tangle of database statements and perl code. Database coding can be strange because the syntax often depends on the database you're connecting to. What might work with Sybase might not work with MS SQL Server. I'm not familiar with Sybase. It looks like your basic DBI calls are correct--except it appears that you're opening both a SQL connection and a Sybase connection. Why two? Do you mean to do that?

    Can Sybase manage stored procedures? I've sometimes found it helpful to put complicated data calls into stored procedures and then just run the procedure from the perl script. MS-SQL would look something like:
    $sql = "{call procedure_name( '$any-input-values')}";

    #call the procedure
$db->Sql($sql);

    [download]

    The rest of it seems conceptually workable, but I'm not experienced enough to advise you there. Just a minor point, you never close your OUTFILE. I always thought it was good practice to do so.

[reply]
[d/l]
[select]
      Coming in a little late on this, but just an FYI - MS-SQL is derived from Sybase code (MS purchased the code from Sybase back in 1992 or so), so yes, Sybase can do stored procedures.

      Michael

[reply]
Re: Reading from databse and writing to file.
by Fletch (Bishop) on May 14, 2003 at 20:02 UTC

    Perhaps if you stated what was going wrong or not behaving as you expected someone might have a clue where to start offering advice . . .

[reply]
Re: Reading from databse and writing to file.
by SQLMan (Novice) on May 15, 2003 at 00:36 UTC
    First of all Can somebody tell me if my code is correct. second for what I want to do is this the right way. I am new to perl. Thanks for your help
[reply]
      Can somebody tell me if my code is correct.

      Well, can you tell whether it's correct? If you do "perl -cw this_script.pl", does it report errors or warnings? If so, what are they? If not, it should run, so when you run it, does it do what you expect, or something else instead? (If there are run-time errors, what are they? etc)

[reply]
        This is the message that it is giving me. Useless use of string gt in void context at bookkeeping.pl line 51.
[reply]

Back to Seekers of Perl Wisdom