Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: Update statement/perl

    Hi All,

    I am having a little problem, with my update statement, I am fairly new to perl, sql,unix and am trying to run a perl script.. in unix, am getting a number of errors.
    This is my query, is it correct?

    $query = UPDATE Digi_Master
    SET enterprise_id = '$data[0]', enterprise_name = '$data[1]', group_id = '$data[2]' ,group_name = '$data[3]', device_name = '$data[4]', device_type = '$data[5]'
    WHERE date = Digi_Master'$date'AND julianday = Digi_Master'$julianday' AND mac_address = Digi_Master'$mac_address';


    Thanks

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    I like to format my sql statements so they are a little more easily read, just a preference, Try this statement, Using the double quotes in perl will allow you to use constants(i.e hardcoded values) and perl variables which perl will use the actual value:
    Code:
    my $query;
    my @data         =  ( 123456, "'ent_name'", 123456, "'grp_name'", "'dev_name'", "'dev_type'" );
    my $date         =  "2012-03-16-13.00.00";
    my $julianday    =  "076";
    my $mac_address  =  "'2C-3F-45-02-1B-32'";
    
    ## For this examples sake, I'm assuming oracle sql, if it's not then the sql syntax might be different:
    $query = "" .
    "update digi_master\n" .
    "  set enterprise_id    =  $data[0],\n" .
    "      enterprise_name  =  $data[1],\n" .
    "      group_id         =  $data[2],\n" .
    "      group_name       =  $data[3],\n" .
    "      device_name      =  $data[4],\n" .
    "      device_type      =  $data[5]\n" .
    " where date            =  to_date( $date, 'yyyy-mm-dd-hh24.mi.ss' )\n" .
    "  and  julianday       =  $julianday\n" .
    "  and  mac_address     =  $mac_address\n";
    
    print $query;

  3. #3
    Join Date
    Mar 2012
    Posts
    12
    Content copied from Perl with Databases by Bill Cowan

    Quite often I see a lot of string concatenation statements being used to build the string for an SQL command, e.g. SQL SELECT statement:

    HTML Code:
           $Stmt = "select *\n"
            . "    from $UserTable\n"
            . "    where $QueryColumn = '$UserInput'";
    This gets much worse for a long INSERT statement with 10 columns.

    Suggestion is to use Perl HERE-Doc literal:
    HTML Code:
            my $SQL_String = <<__END_OF_HERE_DOC__;
            select *
            from  $UserTable
            where $QueryColumn = '$UserInput'
    __END_OF_HERE_DOC__
    As a post-script to Bill Cowan's advice (above), the absence of quotes means that Perl variables will be interpolated, even the one for $UserInput, above. If you want no variables to be interpolated (suppose you need to address v$session in Oracle), then wrap the __END_OF_HERE_DOC__ in single quotes: '__END_OF_HERE_DOC__';

    HTH.

    Eric

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •