Solved

Covert Sybase DBLIB calls to Postgres

Posted on 2013-01-30
2
432 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
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…

831 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