Internal Server Error after SQL Prepare

[bitos]

I'm trying to do some custom coding in my OTRS installation, which is going pretty well so far. Especially in regards to my zero-knowledge-level of perl.
However, I've been trying to get the following code to work for a couple of hours now, but I seem to be stuck and am out of ideas.

The Sub GetPriceList gets a list of prices from the database, which contains a product_id. The product_id corresponds to the queue.id.
I want to retrieve the name of the queue using the product_id by calling the sub GetQueueNameByID.

I keep getting an Internal Server Error when the sub GetQueueNameByID calls the $Self->{DBObject}->Prepare(SQL => $SQL,); statement.
If I comment out the SQL prepare and fetch stuff in GetQueueNameByID, and just let the sub return some test string, it works.

Can someone please explain to me why this happens and how I can fix it? Thanks!

Code: Select all

sub GetPriceList {
    my ( $Self, %Param ) = @_;

	# create SQL query
     my $SQL = 'SELECT * FROM my_prices';
	
	# ask database
     $Self->{DBObject}->Prepare(
         SQL => $SQL,
     );
	
	# fetch the result
    my @PijzenList;
    while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
		my %PijzenData;
		$PijzenData{product_id}  = $Row[0];
		#$PijzenData{product_naam} = $Self->{BitosPrijzenObject}->GetQueueNameByID(
		#	#QueueID => $Row[0],
		#	QueueID => 0,
		#);
		$PijzenData{product_naam}  = &GetQueueNameByID($Row[0]);
		$PijzenData{werkuren_oc}       = $Row[1];
		$PijzenData{werkuren}    = $Row[2];
		$PijzenData{rijtijd_oc}    = $Row[3];
		$PijzenData{rijtijd}    = $Row[4];
		$PijzenData{consultancy_oc}    = $Row[5];
		$PijzenData{consultancy}    = $Row[6];
		$PijzenData{kost_kilometers}    = $Row[7];

        # add service data to service list
        push @PijzenList, \%PijzenData;
    }
	
    return \@PijzenList;
}

Code: Select all

sub GetQueueNameByID {
	my ( $Self, %Param ) = @_;

	# create SQL query
       my $SQL = 'SELECT name FROM queue WHERE id = ' . $_[0];
	
	# ask database
     $Self->{DBObject}->Prepare(
         SQL => $SQL,
     );
    
    my $Result = '';
	       while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
	          $Result = $Row[0];
	       }
		
    return $Result;
}

[reneeb]

What's the error message?

[bitos]

The browser just gives the generic 500 Internal server error, which doesn't explain much.
I don't know where I should look for a more specific error?

Code: Select all

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, webmaster@somenet.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.

[reneeb]

The browser just gives the generic 500 Internal server error, which doesn't explain much.
I don't know where I should look for a more specific error?

Code: Select all

More information about this error may be available in the server error log.

[crythias]

Code: Select all

       my $SQL = 'SELECT name FROM queue WHERE id = ' . $_[0];
   
   # ask database
     $Self->{DBObject}->Prepare(
         SQL => $SQL,
     );

might be better stated:

Code: Select all

       my $SQL = 'SELECT name FROM queue WHERE id = ?';
   
   # ask database
     $Self->{DBObject}->Prepare(
         SQL => $SQL,
         Bind => [ \$Param{QueueID}]
     );

Though .. you should by best practices check if the QueueID is actually provided before attempting to query it.

[bitos]

Of course. I forgot to check the apache log.

Code: Select all

[Fri Dec 13 16:53:04 2013] [error] Can't use string ("1") as a HASH ref while "strict refs" in use at C:/PROGRA~2/OTRS/OTRS//Kernel/System/BitosPrijzen.pm line 92.\n

I guess I'll have to kind of convert the value?

[bitos]

Hi crythias,

I tried taking your suggestions into account, but i'm still not able to get this simple thing to work.
Would you (or someone else) mind pointing me into the right direction? I'm starting to lose valueable time here.

Code: Select all

sub GetBitosPrijzenLijst {
    my ( $Self, %Param ) = @_;

	# create SQL query
     my $SQL = 'SELECT * FROM bitos_prijzen';
	
	# ask database
     $Self->{DBObject}->Prepare(
         SQL => $SQL,
     );
	
	# fetch the result
    my @PrijzenList;
    while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
        my %PrijzenData;
        $PrijzenData{product_id}  = $Row[0];
        $PrijzenData{product_naam}  = &GetQueueNameByID(
                QueueID => $Row[0],
        );
        $PrijzenData{werkuren_oc}       = $Row[1];
        $PrijzenData{werkuren}    = $Row[2];
        $PrijzenData{rijtijd_oc}    = $Row[3];
        $PrijzenData{rijtijd}    = $Row[4];
        $PrijzenData{consultancy_oc}    = $Row[5];
        $PrijzenData{consultancy}    = $Row[6];
        $PrijzenData{kost_kilometers}    = $Row[7];

        # add price data to price list
        push @PrijzenList, \%PrijzenData;
    }
	
    return \@PrijzenList;
}

Code: Select all

sub GetQueueNameByID {
	my ( $Self, %Param ) = @_;

	# create SQL query
	my $SQL = 'SELECT name FROM queue WHERE id = ?';
	
	# ask database
    $Self->{DBObject}->Prepare(
        SQL => $SQL,
        Bind => [ \$Param{QueueID}]
    );
	
    my $Result = '';
	while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
		$Result = $Row[0];
	}
	
    return $Result;
}

Still get the error:

Code: Select all

[Mon Dec 16 15:11:37 2013] -e: Odd number of elements in hash assignment at C:/PROGRA~2/OTRS/OTRS//Kernel/System/BitosPrijzen.pm line 84.
[Mon Dec 16 15:11:37 2013] [error] Can't use string ("QueueID") as a HASH ref while "strict refs" in use at C:/PROGRA~2/OTRS/OTRS//Kernel/System/BitosPrijzen.pm line 91.\n

The weird thing is though, when I use the following query (without using a variable Queue ID), I still get the same error. How is that possible?

Code: Select all

my $SQL = 'SELECT name FROM queue WHERE id = 1';
	
# ask database
$Self->{DBObject}->Prepare(
    SQL => $SQL,
    );

Thanks for your help!

[crythias]

The weird thing is though, when I use the following query (without using a variable Queue ID), I still get the same error. How is that possible?

There isn't enough information to provide you with an answer. Possibly restart apache. There's likely other code pieces that aren't in there which makes my snippet not working in your code.

I apologize for contributing. However, if you're going to use "prepare", you shouldn't put variables in the sql statement. You put ? where you want to put the variables and Bind the variables in the order you want them filled. Of course, the variables should have values and it appears $Param{QueueID} isn't valid, so don't use it.

[bitos]

Hi crythias, thanks again for your reply.

I got it to work by defining my DB properties in the sub routine like:

Code: Select all

	# ask database
	my $host = "localhost";
	my $driver = "mysql"; 
	my $database = "mydbname";
	my $dsn = "DBI:$driver:database=$database:host=$host";
	my $userid = "myuserid";
	my $password = "mypassword";

	my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
	
	$queue_id = $_[0];
	my $sth = $dbh->prepare("SELECT name FROM queue WHERE id = ?");
	$sth->execute( $queue_id ) or die $DBI::errstr;
	#print "Number of rows found :" + $sth->rows;
	while (my @row = $sth->fetchrow_array()) {
		#my ($queue_name) = @row;
		$Result = $row[0];
	}
	$sth->finish();

The problem is that I don't fully understand the OTRS framework yet and would like to do so.
Declaring your database items in every sub isn't very lean, so I would prefer to use the already existing OTRS code.
If you are able, could you explain why my code wasn't working?

Am I passing QueueID incorrectly to the sub? Am I calling the sub not correctly?

Code: Select all

$PrijzenData{product_naam}  = $Self->{BitosPrijzenObject}->GetQueueNameByID(
	QueueID => $Row[0],
);

If so, why is $Param{QueueID} not valid?

Code: Select all

sub GetQueueNameByID {
	my ( $Self, %Param ) = @_;

	# ask database
	$Self->{DBObject}->Prepare(
        SQL => 'SELECT name FROM queue WHERE id = ?',
		Bind => [ \$Param{QueueID}],
    );
	
    my $Result = '';
	while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
		$Result = $Row[0];
	}
	
    return $Result;
}

Maybe not unimportant: this code is taken from my core module Kernel/System/BitosPrijzen.pm.
I am using BitosPrijzenObject which -as I understand- gets defined in the core module? Is that why my code may be problematic?
You said, I did not provide enough info, what other info do you require?

[crythias]

Declaring your database items in every sub isn't very lean, so I would prefer to use the already existing OTRS code.

True. Don't forget OTRS already has a database object.

Code: Select all

perldoc Kernel/System/DB.pm

The gist is ... use some Objects. Create some instances for them. Pass params.
Note that ConfigObject? Yeah, you can store variables in Config.pm so you don't have to put them locally.

Some examples: Interface with database
Tutorial on ticket events
Tutorial on Generic Agent

[bitos]

Ok, I'll have a look at your tutorials.
Thanks for writing them up and sending me those links!