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

Hi all working on adding a control panel to my existing db driven website. I have my table setup and it should allow me to view the current file and then make changes and save it, however I cannot seem to get this to work as desired. My table structure is as follows..autonumber field, active (bit 0 or 1 controls visibility)path (path on webserver to actual file) filename (name of file of course) body(contents of file) there are differing levels of permission here so switch statements are used to determine rights. this is not the whole file, but should be enough for one of the enlightened esteemed wise ones here to point me in the right direction. 

<script @Language="PerlScript" RunAt="Server">
use warnings;



##########################################
#### THIS IS THE BANE OF MY EXISTANCE ####
####                 MINE TOO         ####
##########################################
sub mAdmin
{
    use OLE;
    my $str = "";
    if ($Session->{'adminRights'} || $Session->{'uCLass'} == 10)
    {
        $str .= mLeftIndex();
    }
            
    my $boxWidth = ($g_docWidth/5*4-(2*$g_docSpace)); 
    if (!$str)
    {
        $boxWidth = $g_docWidth -(2*$g_docSpace);
    }

    my $Conn = CreateObject OLE "ADODB.Connection";
    $Conn->Open("PROVIDER=SQLOLEDB;DATA SOURCE=$Session->{'sqlServer'}
+;UID=$Session->{'sqlUser'};PWD=$Session->{'sqlPass'};DATABASE=$Sessio
+n->{'dbName'}");
    my $RS = $Server->CreateObject("ADODB.Recordset"); 

    if ($Request->item("View")->item() eq "ModifySSR")
    {

    
    SWITCH:
    {
        if ($Session->{'usrClass'} == 6)
        {
        
        #### Edit Alerts ####

            if ($Request->item("View")->item() eq "ModifySSR")
            {
                $FileName = "$Session->{'usrSystem'}".xml;
                my $content="";
                my $body = "";
                my $path="content/ssresources";
                SWITCH:
                {
            
                ################### Action View ###################
            
                    if ($Request->item("Action")->item() eq "View")
                    {              
                        my $sql =     "SELECT idXMLFile,
                                Active,
                                LastModify,
                                Path,
                                FileName,
                                Body,
                                FROM XMLFileData
                                WHERE     Active = '1' AND 
                                        Path='$path' AND
                                        FileName='$FileName'";

                        if ($Session->{'trackDB'}){lg("DB","$sql");}
                        $RS->Open($sql, $Conn);
                        if ($RS)
                        {
                            #$title = $RS->Fields('FileName')->{Value}
+;
                            
                            while(!$RS->EOF())
                            {
                                my $path = $RS->Fields('Path')->{Value
+};
                                #if (!$account){$account = "All";}
                  
                                $content = "<Resource id =\"".$RS->Fie
+lds('idXMLFile')->{Value}."\"
                                Active=\"".$RS->Fields('Active')->{Val
+ue}."\"
                                LastModify=\"".$RS->Fields('LastModify
+')->{Value}."\"
                                Path=\"".$RS->Fields('Path')->{Value}.
+"\"
                                Filename=\"".$RS->Fields('FileName')->
+{Value}."\"
                                <![CDATA[".$RS->Fields('Body')->{Value
+}."]]>
                                </Resource>";

                                $RS->MoveNext();
                            } #end while(!$RS->EOF())
                        $RS->Close();
                        #$content .= "<acts>".getSystemAccounts("SEF")
+."</acts>";
                        } #end if ($RS)
                            last SWITCH;
                    } #end if ($Request->item("Action")->item() eq "Vi
+ew")
                    $Response->write("$RS");
                    
                    
                
                
                
                
                
                
                
                ################### Action Copy ###################
              
                    if ($Request->item("Action")->item() eq "Copy")
                    {
                        my $sql = "SELECT idXMLFile,
                                Active,
                                LastModify,
                                Path,
                                FileName,
                                Body,
                                FROM XMLFileData
                                WHERE     Active = '1' AND 
                                        Path='$path' AND
                                        FileName='$FileName'";


                        if ($Session->{'trackDB'}){lg("DB","$sql");}
                        $RS->Open($sql, $Conn);
                        if ($RS)
                        {
                            $title = "Copy New XmlFile";
                            while(!$RS->EOF())
                            {
                                my $account = $RS->Fields('Account')->
+{Value};
                                if (!$account){$account = "All";}
                                $content = "<AlertForm method=\"POST\"
+ action=\"$Session->{'this'}\" id=\"AlertForm\" submit=\"Add\">";
                                $content .= "<inputH name=\"Tab\" valu
+e=\"Admin\"/>";
                                $content .= "<inputH name=\"View\" val
+ue=\"ModifySSR\"/>";
                                $content .= "<inputH name=\"Action\" v
+alue=\"Add\"/>";
                                $content .= "<txttitle label=\"Filenam
+e:\">".$RS->Fields('FileName')->{Value}."</txttitle>";
                                $content .= "<txtbody label=\"Body:\">
+<![CDATA[".$RS->Fields('body')->{Value}."]]></txtbody>";
                                $content .= "</AlertForm>";
                                $RS->MoveNext();
                            } #end while(!$RS->EOF())
                        $RS->Close();
                        } #end if ($RS)
                    last SWITCH;
                    } #end if ($Request->item("Action")->item() eq "Co
+py")    
              
                ################### Action New ###################
              
                    if ($Request->item("Action")->item() eq "New")
                    {
                        $title = "Create New XMLFile";
                
                        $content = "<AlertForm method=\"POST\" action=
+\"$Session->{'this'}\" id=\"AlertForm\" submit=\"Add\">";
                        $content .= "<inputH name=\"Tab\" value=\"Admi
+n\"/>";
                        $content .= "<inputH name=\"View\" value=\"Edi
+tSSR\"/>";
                        $content .= "<inputH name=\"Action\" value=\"A
+dd\"/>";
                        $content .= "<txttitle label=\"Filename:\">".$
+RS->Fields('FileName')->{Value}."</txttitle>";
                        $content .= "<txtbody label=\"Body:\"><![CDATA
+[".$RS->Fields('body')->{Value}."]]></txtbody>";
                        $content .= "</AlertForm>";

                        last SWITCH;
                    } #end if ($Request->item("Action")->item() eq "Ne
+w")
              
                ################### Action Modify ###################
              
                    if ($Request->item("Action")->item() eq "Modify")
                    {
                my $sql =     "SELECT idXMLFile,
                                Active,
                                LastModify,
                                Path,
                                FileName,
                                Body,
                                FROM XMLFileData
                                WHERE     Active = '1' AND 
                                        Path='$path' AND 
                                        FileName='$FileName'";
                                        


                        if ($Session->{'trackDB'}){lg("DB","$sql");}
                        $RS->Open($sql, $Conn);
                        
                        if ($RS)
                        {
                            $title = "Modify XmlFile \#".$Request->ite
+m("idXMLFile")->item()." (".$RS->Fields('idXMLFile')->{Value}.")";
                            #my $account = $RS->Fields('Account')->{Va
+lue};
                            #if (!$account){$account = "All";}
                            $content = "<AlertForm method=\"POST\" act
+ion=\"$Session->{'this'}\" id=\"AlertForm\" submit=\"Modify\">";
                            $content .= "<inputH name=\"Tab\" value=\"
+Admin\"/>";
                            $content .= "<inputH name=\"View\" value=\
+"EditSSR\"/>";
                            $content .= "<inputH name=\"Action\" value
+=\"Update\"/>";
                            $content .= "<inputH name=\"XMLFileid\" va
+lue=\"".$RS->Fields('idXMLFile')->{Value}."\"/>";
                            $content .= "<txttitle label=\"Title:\">".
+$RS->Fields('FileName')->{Value}."</txttitle>";
                            $content .= "<txtbody label=\"Body:\"><![C
+DATA[".$RS->Fields('body')->{Value}."]]></txtbody>";
                            $content .= "</AlertForm>";

                            $RS->Close();
                        } #end if ($RS)
                    last SWITCH;
                    } #end if ($Request->item("Action")->item() eq "Mo
+dify")
              
                ################### Action RemoveConfirm #############
+######
              
                    if ($Request->item("Action")->item() eq "RemoveCon
+firm")
                    {
                        $title = "Remove Alert Confirmation";
                        $content = "<![CDATA[<table width=\"100%\" bor
+der=\"0\" cellpadding=\"0\" cellspacing=\"0\">
                                      <tr>
                                       <td>Are you sure you want to Re
+move this File?</td>
                                         <td><a href=\"".$Session->{'t
+his'}."?Tab=".$Request->item("Tab")->item()."&View=".$Request->item("
+View")->item()."&Action=".Remove."&idXMLFile=".$Request->item("idXMLF
+ile")->item()."\">Yes</a></td>
                                         <td><a href=\"".$Session->{'t
+his'}."?Tab=".$Request->item("Tab")->item()."&View=".$Request->item("
+View")->item()."\">No</a></td>
                                       </tr>
                                     </table>]]>";
                        last SWITCH;
                    } #end if ($Request->item("Action")->item() eq "Re
+moveConfirm")
              
                ################### Action Remove ###################
              
                    if ($Request->item("Action")->item() eq "Remove")
                    {
                        my $sql = "UPDATE XMLFileData
                                    SET Active = '0', 
                                    FileName='$FileName',
                                    LastModify= { fn NOW() }
                                    WHERE (Active = '1') AND
                                    path='$path', AND
                                    FileName='$FileName'";

                        if ($Session->{'trackDB'}){lg("DB","$sql");}
                        $RS = $Conn->Execute($sql);
                    } #end if ($Request->item("Action")->item() eq "Re
+move")
              
                ################### Action Add ###################
              
                    if ($Request->item("Action")->item() eq "Add")
                    {      
                        my $sql = "INSERT INTO XMLFileData
                                    (Active, LastModify, Path, FileNam
+e, Body)
                                    VALUES     
                                    ('1', 
                                    { fn NOW() }, 
                                    '".$path."',
                                    '".$Request->item("txtbody")->item
+()."');";
                              
                        if ($Session->{'trackDB'}){lg("DB","$sql");}
                        $RS = $Conn->Execute($sql);
                    } #end if ($Request->item("Action")->item() eq "Ad
+d")

[download]

Replies are listed 'Best First'.
Re: adding control
by oko1 (Deacon) on Mar 26, 2008 at 01:50 UTC 
    > I cannot seem to get this to work as desired.

    Perhaps you'd care to give a little more detail? I seriously doubt that anyone will be able to read your mind (since Brain::Telepathy is still in the pre-alpha stages), intuit the exact problem, and figure out - from your incomplete code, no less - where to even begin looking.

    (Hint: Reading "How to Ask Smart Questions" is a very, very good idea if you plan on posting questions in a public forum. Assuming that you actually want to get an answer, that is. :)

[reply]
      lol keep me posted on Brain::Telepathy I really hope they get that one posted to cpan.. thanks for the input, I will kick it around a bit further before posting again.
[reply]
Re: adding control
by starbolin (Hermit) on Mar 26, 2008 at 06:18 UTC

    Yum. Multi-page switch statements. I thing you need to break your code up into smaller routines, test those routines and then call the routines from your switch statement. This gets the 'do' part of the code separate from the 'which' part of the code. This would allow you to stub out the subroutines and just exercise your switch logic.

    I'd also like to see all the sql statements defined ... I don't know, elsewhere? SQL is not my strong point and I find it difficult to read perl code having to skip over multi-line SQL command strings every other line.

    On your Open() and CreateObject() calls you need to check if the calls succeed before proceeding. You could be working with invalid object references.

    s//----->\t/;$~="JAPH";s//\r<$~~/;{s|~$~-|-~$~|||s |-$~~|$~~-|||s,<$~~,<~$~,,s,~$~>,$~~>,, $|=1,select$,,$,,$,,1e-1;print;redo}
[reply]
Re: adding control
by apl (Monsignor) on Mar 26, 2008 at 12:16 UTC
    A good rule of thumb is that a piece of code should occupy one screen. It lets you see the overall logical flow of what's going on.

    To make your code more understandable to the Monks (and more importantly, yourself), you should break the code up into a series of subroutines. Each comment line is a good indicator of where a subroutine should start. As a result, you'd have: 

      if ($Request->item("View")->item() eq "ModifySSR")
    {
      $FileName = "$Session->{'usrSystem'}".xml;
      my $content = "";
      my $body    = "";
      my $path    = "content/ssresources";

      if ($Request->item("Action")->item() eq "View")
        { DoView(); }
      else
        { ProcessModify(); }
# etc. etc.
    }

sub ProcessModify
{
  my $req = $Request->item("Action")->item();

  if ( $req eq "View" )
    { DoView(); }

  elsif ( $req eq "Copy" )
    { DoCopy(); }

  elsif ( $req eq "New" )
    { DoNew(); }

# etc. etc.
}

    [download]

    Then you can look at the over-all flow and determine where your change is needed.

[reply]
[d/l]

Back to Seekers of Perl Wisdom