Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DBI update function

DBI update function

DBI update function

I am looping through a batch of data. I am creating a output header and detail record for each record that has a status of 112584.

After I creat the header and detail output records I want to change the status field from 112584 to 112586.

I do not want to effect the loop I am in. I think I have the code correct but I was wondering if somebody that has done this could review this and see if I have it correct.


$dbh=DBI->connect($dsn, $user, $pass) or die;

    $sth = $dbh->prepare("SELECT * FROM CVIS.LAW_INV_ITEM_TRX")
            or die "Couldn't prepare statement: " . $dbh->errstr;
    $rv = $sth->execute or die "Couldn't execute: " . $dbh->errstr;

    open(OUT, ">>$vPath1$vName1") or die "Couldn't open: " . $dbh->errstr;

## Loop through each line of the table

    while (@data = $sth->fetchrow_array()) {

... other code ...

        if ($data[8] eq 112584) {

## map and load the header line

            $sLin2  = $vComp . $vICLoc . "40" . "IS" . $vDesc
                   .= $vSeq . $vSeq . $vCRD1 . $sNStri20 . $vICLoc
                   .= $sNStri313 . "\n";

            print OUT $sLin2;

## map and load the detail line

            $sLin3  = $vComp . $vICLoc . "40" . "IS" . $vDesc
                   .= $vLine . $vSeq . $vItem . $vQty . $vQtySign
                   .= $sNStri36 . $vSUOM . $sNStri260 . "\n";

            print OUT $sLin3;

            $sLin4 = $data[8] . "\n";

            print $sLin4;

## update the TRX_STATUS for this record to 112586 export completed

    $st2 = $dbh->prepare("UPDATE CVIS.LAW_INV_ITEM_TRX
                SET $data[8] = 112586
                WHERE $data[10] = $vSysTrxId
    $st2->execute or die "Could't update data: " . $DBH->errstr;



RE: DBI update function


This looks really bad :

Quote (jcarrott):


    $st2 = $dbh->prepare("UPDATE CVIS.LAW_INV_ITEM_TRX
                SET $data[8] = 112586
                WHERE $data[10] = $vSysTrxId
Syntactically :
  • Unclosed string.
  • Unclosed parenthesis.
  • Unclosed statement.
Logically :
  • $data[8] is supposed to be a value and due to the if condition it will always be 112584. So the SET 112584 = 112586 clause will fail.
  • Probably the same thing applies to $data[10].
  • Prepared statements have two big benefits : by preparing once and using multiple times improves the speed; by binding values to placeholders ensures correct formatting of the values. There you just trashed both benefits.
Minor ugliness :
  • If you will have to touch that code after a couple of months you will not know what $data[8] is. By using fetchall_hashref() instead of fetchrow_array() you could use $data->{'field_name'} instead of $data[8].
Theoretically the above quoted code should look like this :

CODE --> Perl ( fragment )

# only once, before the while loop
    $st2 = $dbh->prepare("UPDATE CVIS.LAW_INV_ITEM_TRX
                SET field_name = 112586
                WHERE other_field = ?"

# every time its needed, inside the while loop
    $st2->bind_param(1, $data[10]);
    $st2->execute or die "Could't update data: " . $DBH->errstr;
Note that I not really understood your goal, so the above code is an example, not a solution.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close