Update begins: I realized that you'd also save yourself some headaches if you just convert this entire process to a single SQL statement, and let SQL Server do the code-work.

UPDATE category SET 
  chrCat_Full_Name=UPPER(LEFT(chrCat_Full_Name,1))
  +LOWER(RIGHT(chrCat_Full_Name,DATALENGTH(chrCat_Full_Name)+1))
[download]

That's untested, but should do the job, no Perl required.:update Ends

You'll save yourself a lot of headaches talking to MSSQL server (as you mentioned you are, in one of the replies above) via ODBC using DBI with the DBD::ODBC module. That setup allows you to do the above with placeholders, which are far easier to read and debug than what you've got above.

use DBI;
require DBD::ODBC; ## will be done auto, but doesn't hurt to check.

my $db  = DBI->connect($dsn,'','',{RaiseError=>1});
my $db2 = DBI->connect($dsn2,'','',{RaiseError=>1});

my $sql ="SELECT * FROM category";
my $sth = $db->prepare($sql);
$sth->execute();

## ? is a placeholder that will be auto-quoted later!
my $sql2 = 'update category set chrCat_Full_Name=? where chrCategory_C
+ode=?';

## now we get the DBI ready to execute that statement many times with 
+different placeholders
my $sth2 = $db2->prepare($sql2);


while (my $data = $sth->fetchrow_hashref()) {
   ## the parameters passed will be quoted and substituted
   ## for the placeholders in order
   $sth2->execute(
      ucfirst( lc($data->{'chrCat_Full_Name'}) ),
      $data->{'chrCategory_Code'}
   );

   ## this won't show your parameters, to do that you can trace
   ## or just explicitly print them.
   print "$sql2 $data->{'chrCat_Full_Name'}\n";
}

$db->disconnect;
$db2->disconnect;
[download]

The {RaiseError=>1} in the call to connect() causes the app to die() with the error if one is encountered during any DB operation. You'll probably want to handle those eventually.

Updates:

• 2005-08.Aug-29 : SQL solution added

• use strict and use warnings
• what's the error you're getting?
• what does $db->Error() say?
• what are the values of chrCat_Full_Name and chrCategory_Code ? (specifically, anything bad like a single quote that will mess up the sql creation?)
• consider switching to DBI/DBD so that you can use placeholders ..

This is the example of that query:


update category set chrCat_Full_Name='Vinyl powder free gloves' where 
+chrCategory_Code='VINYLPF' Vinyl powder free gloves
[download]

(note: i'm going to assume that trailing "Vinyl powder free gloves" is a copy/paste error)

what does $db2->Error() say?

Taking a closer look at your debugging, you have:

$db2->Sql($sql2)|| die("Query error");
[download]

Reading the docs for Win32::ODBC, it says that ->Sql() "Returns ? on success, or an error number on failure." .. apparently (after looking at the source) they meant s/\?/undef/ ... BUT you have that '||' in there, so on _success_ it's going to die. You need to change it to something like:

  my $rc = $db2->Sql($sql2);
  die sprintf("Query error!! RC: %s ; Error: %s ; SQL: %s", $rc, $db2-
+>Error(), $sql2) if $rc;
[download]


As i mentioned above, DBI/DBD is far superior for this because of placeholders... and radiantmatrix below was kind enough to post a sample porting of your code -- be sure to review it...

Update: oops .. forgot the "if $rc" on there ... and changed $Sql->Error() to $db2->Error()

x %data
[download]

I`m using MSSQL. For example if take that query and put it into the mssql client(replace $vars with names )then it works fine. Its just when i make call from the script it doesnt work. The error says:
Query error at F:\perl\rozne\ucfirst.pl line 20. I can pull data from that table without any problem.

I normally ask questions about debugging because it seems to be an area of discontent for some Win32 users. Perhaps it is an aversion to the command line? You start a debugging session like so:

perl -d scream.pl
[download]

And you wind up in a strange screen with line numbers and package information and what-not on the left hand side. Type the letter l ("L" and not the number one) to see the current line:

  DB<1> l
5==>    my $text = "my text [and maybe|and here's] more text [the end|
+stop]";
6:      my @arr  = parseit($text);
7:      print "$_\n" for @arr;
8
9       sub parseit {
...
[download]

And use b to set a breakpoint. The small letter r will run you to that breakpoint:

  DB<1> b 7
  DB<2> r
main::(scream.pl:7):    print "$_\n" for @arr;
  DB<2>
[download]

Now, to look at variables you can print them out, using p:

  DB<2> p @arr
my text and maybe more text the endmy text and maybe more text stopmy 
+text and h
ere's more text the endmy text and here's more text stop
  DB<3>
[download]

Or, much better, you can examine them using x:

  DB<3> x @arr
0  'my text and maybe more text the end'
1  'my text and maybe more text stop'
2  'my text and here\'s more text the end'
3  'my text and here\'s more text stop'
[download]

When you are done you can use the single letter q to get out of the debugging session. There is more to it, but that is the basics. Perldoc perldebug tells you all this and so much more. Give it a whirl.

Celebrate Intellectual Diversity