The following is scrubbed code I use to get a handle to an Oracle database. 

#!/usr/bin/perl
use strict;
use warnings;

use DBI;
require DBD::Oracle;

my $oracle = do{
    my $db_name = 'dbname';
    my $db_user = 'userid';
    my $db_pass = 'password';
    my @dbi_path = ("dbi:Oracle:$db_name",$db_user,$db_pass);
    DBI->connect(  @dbi_path,
                    {
                        PrintError => 1,
                        RaiseError => 1,
                        AutoCommit => 0
                    }
                )
        or die "Database connect to $db_name failed: $DBI::errstr";
};

[download]

The code in the do block is executed, keeping the connection parameters localized to the block and returning the last thing evaluated (ie the handle). Note you will have to modify those connection parameters to reflect your case, in particular all references to Oracle should be changed to reflect your chosen technology. What brand of database are you attempting to connect to? What are the diagnostics that tell you 'it doesn't work'? If you plan on doing much work in Perl, figuring out how to work with DBI is definitely time worth while.

On the question of the original approach, I suspect that you are not disconnecting from your database between update and select when you run the commands from the command line. If your database is set to automatically rollback changes (likely), this would cause issues just like yours to manifest. On the other hand, I believe MS SQL does not really support the rollback concept, which explains why changing technologies causes a change in behavior. What happens when you try the following in your original script:

$cmd = $connString."update [dbname].[record] set record = 7;commit\nGO\nquit\n";

In reply to Re^5: update db problem by kennethk
in thread update db problem by mybend

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.