[PROB] 'Extended Ticket Editor' not working with SQL (all versions)

[Sparky]

Ok, it works in v3.01.769 using flat file method.

So it appears that the issue is something with using SQL...

Ok- now that's something.

Here's the problem... there is nothing in my code unique to either type.  Similar to my Ticket Merge Mod, people have reported success using these mods with SQL.

I am using the same routines as TTX to read/write to the DB.  TTX then handles the ugly details regarding which type of database is being used.

Weird, but at least now there is something specific to look at.   

(I'm wondering if the 2.24 version using SQL will work as this version is closer to the time I rolled this out)

Thank-you, other people reading this thread will appreciate your efforts.

[bjray]

That is interesting. I am using the Merge module just fine! (Great too btw.)

I will throw up 2.24 and see if I can get it to work with SQL. I'll report back on here.

[bjray]

Sparky,

I threw up v2.24.431 with the appropriate SQL Ticket's module for this revision. Before putting it in SQL ticket mode, I installed the edit mod (per this thread) and it worked again using the flat file method. I then executed the SQL .cgi to put it in SQL mode. After that, I experience the same thing; any edits/deletions would not save.

I just don't know what the deal is. I cannot get any version of TTX to work using this edit modification when using SQL for the tickets database.

Like I said though, I can and am using the Ticket merge mod with TTX v 3.01 and latest SQL module/mode as per this thread: http://forum.unitedwebcoders.com/index.php/topic,444.0.html

Thanks again, I'll keep perusing the code/setup to see what I might find.

[Sparky]

Perhaps the previous reports about it working with SQL were incorrect.  I don't yet see a reason why I shouldn't work with SQL but I'll have to make some time to get into this deeper.

[bjray]

Sparky! I think I may have found it. However, my perl is not to the level that it should be! So maybe you can shed some light on this.
I edited the TTXEditor.pm module right before the save() function.

Take a look at line 162 (around there) for this line: $t->{messages} = \@msg2;
I edited this to what the merge module does: push @{$t->{messages}}, @msg2; ---> However, this is not doing it exactly. It is creating multiple entries when I edit a ticket's message and add a one line message "test". It will create more entries.

Does this help any? It did actually save and insert the "test" entry into my SQL DB...just did it multiple times. I'll keep editing via trial and error.

On to something here!

Thanks.

[Sparky]

Weird!  Makes no sense yet.

I have the MySQL module... maybe I should install it and see what happens here.

[bjray]

OK. If I change in TTXEditor.pm (around line 162)
                  $t->{messages} = \@msg2;
to
                  push @{$t->{messages}}, @msg2;

It will make the edits (inserting them into the SQL DB, but it creates a new message on the ticket, leaving the original message unedited. Again, this is due to the lack of my expertise in perl, so I am doing trial and error here. I'll let you know if I get it completely.

[Sparky]

That's the push... it appends msg2 to the end.

That's why it makes no sense... that part of the code should have nothing to do with saving into the database... that's what the save() is for.

[Sparky]

I've split this issue into its own topic.

[Sparky]

I'm not seeing the problem which occurs someplace in here as you already figured out...

$t->{messages} = \@msg2;   
$tickets->save();

[Sparky]

For flat file, the "save()" subroutine is in the TTXFileTickets.pm module.

For MySQL, the "save()" subroutine is in the TTXMySQLTickets.pm module.

Now it's just a matter of figuring out why the second one is choking on the new array of messages.

[bjray]

Sparky, you were exactly right. There is some issue with saving the new array of messages when using the "save()" function with MySQL.

BUT, I have solved the issue and wanted to post it.

Add the red, comment out the blue.
Simply copy/paste the code in red into TTXEditor.pm and place it after the if ($save){:
      ## only save & refresh ticket if message delete or change
      if ($save) {
        my $sql;
        my $mode = $cfg->get('dbmode');
        if ($mode eq 'plaintext') {
          $tickets->save();
        } elsif ($mode eq 'mysql' || $mode eq 'mssql') {
          eval "use DBI";
          if ($@ eq undef) {
            my $dbh = $tickets->{DBH};
            my $sth = $dbh->prepare("SELECT MID, DATE FROM ".$tickets->{MSGTABLE}.
                 " WHERE TID = '$key' ORDER BY DATE");
            if ($sth eq undef || !$sth->execute()) {
              $data->{ERROR_MESSAGE} = '[%Database Error%] 1';
              return undef;
            }
            my $cnt = 0;
            my $dbmid;
            for (my @row = $sth->fetchrow_array(); @row; @row = $sth->fetchrow_array()) {
              if ($cnt == $mid) {
                $dbmid = $row[0];
                last;
              }
              ++$cnt;
            }
            $sth->finish();
            if ($dbmid eq undef) {
              $data->{ERROR_MESSAGE} = '[%Database Error%] 2';
              return undef;
            }
            if($del){
                $sql = "DELETE FROM ".$tickets->{MSGTABLE}." WHERE MID='$dbmid' LIMIT 1";
            }else{
                $sql = "UPDATE ".$tickets->{MSGTABLE}." SET MSG='@msg2[$mid]' WHERE MID='$dbmid' LIMIT 1";
            }
            if (!$dbh->do($sql)) {
              $data->{ERROR_MESSAGE} = '[%Database Error%] 3';
              #warn "Error saving ticket, ".$dbh->errstr;
              #traceit("Error saving ticket, ".$dbh->errstr);
              return undef;
            }
          }
        }
        TTXCommon::logit("MSGEDIT|".$user->get('login')."|$key|$mid");

          #$t->{messages} = \@msg2;
          #$tickets->save();