Functions of esProc/R/Python/Perl in Structured Data Process by Comparison :Chapter 16.Writing to DB

esProc

In esProc, data from Table sequence can be updated to the database by the following two ways:

  1. It is more flexible to use execute, and with sql, you can write any code to insert and update what you want, and also execute stored procedures with input parameters. A is a Table sequence. The function will loop through each record of A.
  2. execute(A,sql,…) sql statement will be executed in turn for A in the DB; … is the parameter of sql

 @k  After completed, the transaction will not be committed, but done so by default, it is fit for any execute

@s   No preparation for the transaction in advance

  1. Use update only to execute update operation on the table of the database, and only for one data table
  2. update(A,tbl,F:x,…;P,…) Use A to update field Fi of tbl table which corresponds to xi; if F/x is omitted, use A’s field name/value to update the table, P,…is the primary key of tbl, if omitted, read from tbl; if not read out, use A’s

@u    Only generate UPDATE statement

 @i     Only generate INSERT statement

   @a    Empty the target table before executing

@k    The transaction is not submitted after completed, but done so by default

@1    The first field is Auto incremental one without appropriate expression

Perl

@form is assumed to be a 2D array used for store data of 2D table

    $dbh = DBI->connect(“dbi:Oracle:host=192.168.1.11;sid=ORCL”,$oracleid,”,{AutoCommit=>0})||

    die “Don’t connect database ! “;              #Create a database connection

    $sth = $dbh->prepare(‘insert into emptable (id, name) values (?, ?)’);
    $error=””;

    foreach(@form){               #Repeat the operation row by row, and write data to the database

         eval{

    $sth->execute($_[0], $_[1]);

         };

    if ($@){

    print “Transaction aborted:$@”;

    $error=$@;

    $dbh->rollback();

    exit;

   }

   }

    if($error==””) $dbh->commit();

    $dbh->disconnect;

As can be seen from the above codes, a module used to auto write a 2D array to the database is unavailable in Perl, and you have to write an appropriate program to achieve it. However, such a relatively lengthy code will get you into trouble.

Secondly, try{}catch{}finally{} mechanism of Java is also unavailable here, so that it is not easy to capture error messages and perform subsequent processes. Obviously, eval{} is not as good as try, for which you need to write a bunch of if statements.

Python

As what esProc can do, Python also provides an executemany function, which can execute multi-row insertion for a 2D array at a time.

    sql=’insert into dave (id,name,phone)values(:1,:2,:3)’

    dt=[[5,’dave’,138888888888],

            [6,’oracle’,13888888888],

            [7,’anqing’,13888888888]]

    curs=db.cursor()

    curs.prepare(sql)

    curs.executemany(None,dt)         # Inert multiple rows once

    curs.commit()

    curs.close()

    curs.close()

R

    data<-read.table(“d:/T2.txt”,sep=”\t”)  # Read the data from a text file

    library(RODBC)         # Import a class package

    conn<-odbcConnect(“sqlsvr”,uid=”sa”,pwd=”root”)      #Connect the database

    sqlSave(conn,data,’NEW_TABLE_NAME’,append=TRUE)   #Write data to the database; if append is true, it indicates to append to the existing table, while false indicates to create a new table

    odbcClose(conn)      #Close the database

R features accessibility as good as esProc does, and also extends a capability to Auto Create the Table.

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in esProc/R/Python/Perl, Structured Data Process and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s