mybend has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I have a code which is suppose to update a single entry in the db. The connection to the db is fine, it works and displays the correct data. The sql query is fine as well, it does update the db if I use it in MS SQL Server. Problem: the code does not update the db entry. 

$mssql_server_name = "server_name";
$url = "http://url;
$userName = "user_name";
$password = "passwd";

$connString = "tsql -H $mssql_server_name -p 1234 -U $userName -P $pas
+sword  -t '\<newrow\>' -o qh \<\< quit\n";

#Query to grab records created 
$cmd = $connString."update [dbname].[record] set record = 7\nGO\nquit\
+n";


$cmd = $connString."select * from [dbname].[record]\nGO\nquit\n";

$result = `$cmd`;

print "RESULT $result\n";

[download]

Many thanks!

Mybend

Replies are listed 'Best First'.
Re: update db problem
by kennethk (Abbot) on Jun 14, 2010 at 15:13 UTC
    I suspect your problem is that you are not committing changes. I believe you will get your desired result by modifying your commmand to read:

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

    but am not certain because I never do this with raw SQL(untested). Why are you not using the DBI module that provides an incredibly useful and powerful API?

[reply]
[d/l]
      Adding commit didn't work unfortunately. I'm quite new to perl. I'm gonna look up at the DBI module! Thanks!
[reply]
        This is not a Perl issue (perl is the program, Perl is the language). This is a database issue. As it stands, you are essentially using Perl to assemble commands for your database and then dispatching them using the command line. Therefore, if you want to debug statements, it makes sense to get them working on the command line first.
[reply]
Re: update db problem
by Generoso (Prior) on Jun 14, 2010 at 17:01 UTC

    I do not have access to MS SQL so I can not test en see if this code works, but you have a running double quote in the line that starts with $url. And I have to agree that is better to use DBI

    $mssql_server_name = "server_name";
$url = "http://url"; # <--------- runing doble quote
$userName = "user_name";
$password = "passwd";

$connString = "tsql -H $mssql_server_name -p 1234 -U $userName -P $pas
+sword  -t '\<newrow\>' -o qh \<\< quit\n";

#Query to grab records created 
$cmd = $connString."update [dbname].[record] set record = 7\nGO\nquit\
+n";


$cmd = $connString."select * from [dbname].[record]\nGO\nquit\n";

$result = `$cmd`;

print "RESULT $result\n";

    [download]
[reply]
[d/l]
      Sorry, but I really don't understand what that line with double quote has to do with anything... I guess I'm missing something. I actually just forgot to delete it, it's not relevant, my apologies...
[reply]
Re: update db problem
by pajout (Curate) on Jun 15, 2010 at 07:39 UTC
    It is trivial error: 
    $cmd = $connString."update [dbname].[record] set record = 7\nGO\nquit\
+n";


$cmd = $connString."select * from [dbname].[record]\nGO\nquit\n";

$result = `$cmd`;

    [download]
    => $cmd does NOT contain any update, just connstring and select...
[reply]
[d/l]
      The "Update" query is one above it
[reply]
        But it never gets executed.
[reply]

Back to Seekers of Perl Wisdom