OTRS using Mysql Query

Question

Hi I follow the OTRS developer manual for using sql queries inside the modules.

I run the following query but I only get first value from result.

  my $SQL = "SELECT id FROM roles ";

  $Self->{DBObject}->Prepare(SQL => $SQL, Limit => 15);

  my @Row = $Self->{DBObject}->FetchrowArray();

if I check the size of @Row array I get one but in reality I have many roles created in roles table.

Can some one tell me whats missing ?

Answer 1

I can't add the code snippet in the comment to the other answer but here goes; FetchrowArray() will return a list of columns for one row of the result, so you'd have to iterate over it like below; as pointed out. And if you want to add all results to an array, just use push.

$Self->{DBObject}->Prepare(
    SQL   => "SELECT id FROM table",
    Limit => 10
);

my @IDs;
while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
    push @IDs, $Row[0];
}

Answer 2

FetchrowArray() should be in a loop. This is an example from the OTRS doc:

$DBObject->Prepare(
    SQL   => "SELECT id, name FROM table",
    Limit => 10
);

while (my @Row = $DBObject->FetchrowArray()) {
    print "$Row[0]:$Row[1]\n";
}

I believe you should use SelectAll instead. SelectAll() return an array reference, not an array.