Solved

Covert Sybase DBLIB calls to Postgres

Posted on 2013-01-30
2
433 Views
Last Modified: 2013-02-13
I need to convert Sysbase calls in Perl to Postgres. How should I go about, do I need to use the DBI module?

 execute($dbh, qq!CREATE TABLE tempdb..$gSensGrpParam{$sensType}{tmpSensTable} ( $gTmpSensTableDefinition )!);

 @result = RunSqlCmdWithResult($dbh, $cmd);

   
   
      @result = RunSqlCmdWithResult($dbh, $cmd);

   
   
      @result = RunSqlCmdWithResult($dbh, $cmd);

   
   
      @result = RunSqlCmdWithResult($dbh, $cmd);

   
   
      @results = RunSqlCmdWithResult($dbh, $cmd);

   
   
      @results = RunSqlCmdWithResult($dbh, $cmd);

   
   
      bcpDataFile($filename);

   
   
      if (@data = $dbh->dbnextrow) {

   
   
      if (@data = $dbh->dbnextrow) {

   
   
      LogError("Command FAILED!!\n\t$bcp_cmd\n");

   
   
      LogError("Command FAILED!!\n\t$bcp_cmd\n");

   
   
      LogInfo("BCP command

    \n $cmd");

   
   
      LogInfo("BCP command

    \n $cmd");

   
   
      LogInfo("BCP filename

     $filename");

   
   
      my $result = RunSqlCmd($dbh, $cmd);

   
   
      my $result = RunSqlCmd($dbh, $cmd);

   
   
      my $result = RunSqlCmd($dbh, $cmd);

   
   
      my $status = $dbh->dbuse($database);

   
   
      my @results = RunSqlCmdWithResult($dbh, $cmd);

   
   
      my @results = RunSqlCmdWithResult($dbh, $cmd);

   
   
      while ( @data = $dbh->dbnextrow ) {

   
   
      while ( @data = $dbh->dbnextrow ) {

   
   
      while ( @data = $dbh->dbnextrow ) {
   
   
      while ( @data = $dbh->dbnextrow ) {
   
   
     # bcp the file
   
   
     # bcp the files
   
   
     # bcp the files
   
   
     # bcp the files
   
   
     # bcp to load data
   
   
     # get the directory name for saving bcp files

   
   
     #$betaTable => $betaBCP,

   
   
     #$volTable => $volBCP

   
   
     $bcpfiledir = $bcpfiledir . "/" . $line;

   
   
     $betaTable => $betaBCP,

   
   
     $bskTable => $bskBCP,

   
   
     $cmd = "bcp $DBName.$DBOwner.$aggrTable in $aggrBCP -U $DBUser -P $DBPass -S $DBServer  -c -t \\;";

   
   
     $cmd = "bcp $DBName.$DBOwner.$sensTable in $sensBCP -U $DBUser -P $DBPass -S $DBServer  -c -t \\;";

   
   
     $dbh->dbclose();

   
   
     $dbh->dbclose();

   
   
     $dbh->dbclose();

   
   
     $dbh->dbclose();

   
   
     $dbh->dbclose();

   
   
     $dbh->dbclose();

   
   
     $dbh->dbcmd($sql) == SUCCEED  || die "Fail to dbcmd $sql";

   
   
     $dbh->dbresults == SUCCEED || die "Fail to dbresults $sql";

   
   
     $dbh->dbsqlexec == SUCCEED || die "Fail to dbsqlexec $sql";

   
   
     $dir = $localRoot. "/".  $bcpDataDirName;

   
   
     $doBcp = true;

   
   
     $filename = $restoreRoot. "/". $bcpDataDirName. "/". $businessDate. ".tar.gz";

   
   
     $lookupBCP = $lookupBCP . "-" . $runId;

   
   

BackTestBetaAdj.pl:use Sybase::DBlib;
BackTestBetaAdj.pl:my $dbh;
BackTestBetaAdj.pl:$dbh=Sybase::DBlib->dblogin($sUserId, $sPassword, $sServer) or
BackTestBetaAdj.pl:$dbh->dbuse($sDatabase);
BackTestBetaAdj.pl:     $s1 = $dbh->dbcmd($sSqlCmd);
BackTestBetaAdj.pl:     $s2 = $dbh->dbsqlexec;
BackTestBetaAdj.pl:     $s3 = $dbh->dbresults;
BackTestBetaAdj.pl:     while( @data = $dbh->dbnextrow )
BackTestBetaAdj.pl:             $s1 = $dbh->dbcmd($sqlSelect);
BackTestBetaAdj.pl:             $s2 = $dbh->dbsqlexec;
BackTestBetaAdj.pl:             $s3 = $dbh->dbresults;
BackTestBetaAdj.pl:             while( @data = $dbh->dbnextrow )
BackTestBetaAdj.pl:     $s1 = $dbh->dbcmd($sqlUpdate);
BackTestBetaAdj.pl:     $s2 = $dbh->dbsqlexec;
BackTestBetaAdj.pl:     $s3 = $dbh->dbresults;
BackTestBetaAdj.pl:                     $s1 = $dbh->dbcmd($sqlSelect);
BackTestBetaAdj.pl:                     $s2 = $dbh->dbsqlexec;
BackTestBetaAdj.pl:                     $s3 = $dbh->dbresults;
BackTestBetaAdj.pl:                             while( @data = $dbh->dbnextrow )
BackTestBetaAdj.pl:        $dbh->dbclose();

3      bcpCommSens.pl            
bcpCommSens.pl:# Name:  bcpsens.pl
bcpCommSens.pl:# Description: Script to bcp commodity sensitivity and aggregation files to database
bcpCommSens.pl:use Sybase::DBlib;
bcpCommSens.pl:my $sensBCP = $rc->getValue($configType . ".SensitivityBCPFile");
bcpCommSens.pl:my $aggrBCP = $rc->getValue($configType . ".AggregationBCPFile");
bcpCommSens.pl:#$sensBCP =~ s/\@CCYYMMDD/$businessDate/g;
bcpCommSens.pl:#$aggrBCP =~ s/\@CCYYMMDD/$businessDate/g;
bcpCommSens.pl:LogDebug("Bcp file for db table $sensTable is $sensBCP.");
bcpCommSens.pl:LogDebug("Bcp file for db table $aggrTable is $aggrBCP.");
bcpCommSens.pl:LoadBCPFiles();
bcpCommSens.pl:# load bcp files into database
bcpCommSens.pl:sub LoadBCPFiles
bcpCommSens.pl: LogDebug("calling LoadBCPFiles.");
bcpCommSens.pl: my $dbh = Sybase::DBlib->dblogin($DBUser, $DBPass, $DBServer);
bcpCommSens.pl: my $status = $dbh->dbuse($DBName);
bcpCommSens.pl: my $result = RunSqlCmd($dbh, $cmd);
bcpCommSens.pl: $result = RunSqlCmd($dbh, $cmd);
bcpCommSens.pl: $dbh->dbclose();
bcpCommSens.pl: # bcp to load data
bcpCommSens.pl: $cmd = "bcp $DBName.$DBOwner.$sensTable in $sensBCP -U $DBUser -P $DBPass -S $DBServer  -c -t \\;";
bcpCommSens.pl: LogInfo("BCP command:\n                   $cmd");
bcpCommSens.pl: $cmd = "bcp $DBName.$DBOwner.$aggrTable in $aggrBCP -U $DBUser -P $DBPass -S $DBServer  -c -t \\;";
bcpCommSens.pl: LogInfo("BCP command:\n                   $cmd");
bcpCommSens.pl:}        # end of LoadBCPFiles
0
Comment
Question by:rockrem
2 Comments
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 38836083
Your best bet is to use DBI.  I'm surprised you're still using Sybase::DBLib - it has (I think) been deprecated for a *long* time (we moved off it (to DBI and DBD::Sybase) over a decade ago).

One potential major gotcha is that Sybase supports multiple sql statements in a single query whereas almost every other db does not (MS SQL Server is the only other one I know of that supports this).  If that is the case, you will likely have to do some potentially major restructuring to your sql.

Also, the bcp commands will need to change to whatever Postgres uses.
0
 

Author Comment

by:rockrem
ID: 38886197
Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question