in reply to Win32::ODBC - Update SQL statement

Looks like you are seperating your statements with newline. Is that right, or should it be semi-colon (;) for your database?

Replies are listed 'Best First'.
Re: Re: Win32::ODBC - Update SQL statement
by Anonymous Monk on Jul 26, 2001 at 09:41 UTC
    Thanks monks! for thy replies!

    The reason I use ODBC instead of DBI/DBD is that it gives me DATABASE independence
    (so that I can use the same module for updating records into other RDBMS backend with no or little tweaking around with the code!).
    I know connecting, updating the database and disconnecting for time the loop is excuted is dumb,
    but had to get it to work
    As far the semicolon at the end of the statement is concerned -
    it's NOT necessary (oracle seems to be comfortable without semicols)!
    I am getting the SQL statements generated into a log file and it's perfect!
    And I have put up the code for printing the error if any during the Sql statement execution

    So that my code looks like this now:
    #@updatequeryarray is the array containing the Update statements
    #for eg.: UPDATE EMPLOYEE SET REMARKS='Test now for Ryan' WHERE EMP_NO='295' AND EMP_JOIN_YEAR='200102'
    $dsn=$config->{'DSN'};
    foreach my $query (@updatequeryarray) {
    $db = new Win32::ODBC("$dsn");
    if (!$db) {
    print "Cannot connect to DSN $dsn\n";
    return 0;
    }
    $dbgstr.="\n $query";
    if ($db->Sql($query)) {
    print "Database Updatation Error".$db->error;
    }
    $db->Sql("commit");
    $db->Close();
    }

    if (!open(DBGRESP,">>$config->{'PATH-RESPONSES'}/SQL")) {
    print "Error Appending DBG file\n";
    return 0;
    }
    print DBGRESP "$dbgstr";
    close DBGRESP;
[reply]
      Your goal (database independence) is right, but I am not so sure about your way to achieve it.
      DBI.pm is the way to database independent data access, and was designed along ODBC guidelines - early adopter. DBI allows you to move your code from Win PC with some ODBC-compliant database to i.e. Linux and database which may or may not be ODBC-compliant, just DBI-compliant driver will be enough.

      You are using two layers of interfaces: (1) from your program to local database access client, and (2) from PC to database server (which might be on the same PC, LAN, or internet).
      Your approach is to use Win32::ODBC for task (1), and ODBC for task (2). But, if you decide to move your code to different machine/OS, you may or may not have available Win32::ODBC there, because Win32 in module name suggests it is windows-specific module. DBI most likely will be there, so you can use DBI calls exactly as on PC.
      As an added advantage, sometimes native DBD drivers (DBD::Oracle) might implement some DBI calls more efficently than standard ODBC, or you may add some nifty time-saving feature just for ORACLE, using ORACLE's SQL extension, and implementing it for other databases in less eficient, but more standard SQL.
      Also, much more programmers know DBI calls, so you may have more help (dbi-user email list etc) and excellent "Programming the Perl DBI" book written by author of DBI, Tim Bunce, with Alligator Descartes (the "Cheetah book"). It describes DBI from basics to other esoteric stuff like reading SQL cursors (sets of records) to arrays of hashes, which you can use for HTML::Template etc.

      I might be wrong here with performance issue (you did not mentioned how important it is for you), so you may want to impement both, and benchmark. Or maybe somebody here can advise on performance issue - or ask in dbi-users mail list.

      I myself prefer DBI and am happy with it.
      Just my $0.02.

      pmas
      To make errors is human. But to make million errors per second, you need a computer.

[reply]

In Section Seekers of Perl Wisdom