Simple sql query problem

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

Hi,
I'm newbie and got stuck with something weird on sql query via Perl. As long as I don't use any specific field name in query statement I'm fine, but if not error appears - so Get_House_Security_Status method works, but Get_House_Security_Status1 method doesn't work. Why ?

Thanks in advance, regards, Rob.

sub Get_House_Security_Status
{
    my ($self)= @_;
    $self->{House_Security_Status} = $self->Sql_Query("select * from p
+luto_security.ModeChange order by psc_mod DESC limit 0,1",'EK_HouseMo
+de');
    return $self->{House_Security_Status};
}

sub Get_House_Security_Status1
{
    my ($self)= @_;

    $self->{House_Security_Status} = $self->Sql_Query_New("select EK_H
+ouseMode from pluto_security.ModeChange order by psc_mod DESC limit 0
+,1");

}



sub Sql_Query
{
    my ($self, $sql_statement, $sql_field)= @_;
    my $statement = $self->{SQL_Connector}->prepare($sql_statement);
    
    $statement->execute();
    return $statement->fetchrow_hashref()->{$sql_field};
}

sub Sql_Query_New
{
    my ($self, $sql_statement)= @_;
    my $statement = $self->{SQL_Connector}->prepare($sql_statement);
    
    $statement->execute();
    return $statement->fetchrow_hashref();
}


my $Security_Status = $LMCE->Get_House_Security_Status();
my $Security_Status1 = $LMCE->Get_House_Security_Status1();


DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near '(0x84195cc)' at line 1 at LMCE_Connector.p
+m line 218.
[download]

Comment on Simple sql query problem Download Code

Replies are listed 'Best First'.
Re: Simple sql query problem by graff (Chancellor) on Dec 01, 2007 at 17:23 UTC
Does the code that you posted include the 218th line of your ~~script~~ "LMCE_Connector.pm" file? My guess is "no". That particular kind of sql error string results from trying to "prepare()" an sql statement that includes a perl variable (e.g. `"select * from $table_name"`), and the value of the variable is wrong for that slot in the sql statement. From the content of the error report, it looks like you are passing a scalar that holds some sort of reference (hash or array ref), the sql statement is supposed to have some sort of string in that position, and perl is "stringifying" the value of the reference (a memory address). Since the code you've shown involves string constants with no variables in the two "prepare()" calls, your sql error was not caused by either of those calls. The problem is somewhere else (hint: look at what is being passed to the prepare() call at line 218 in your code).	[reply] [d/l]
Re: Simple sql query problem by jrsimmon (Hermit) on Dec 01, 2007 at 14:12 UTC
Have you tried running this directly against your database? From the DBD error you listed, it would appear that the error is coming back from MySQL rather than from perl.	[reply]
Re^2: Simple sql query problem by Anonymous Monk on Dec 01, 2007 at 15:48 UTC
I agree, but have no clue what is wrong. In first example I select first all fields and then particular one, in second example I select that field right away, but I wonder why is this wrong....	[reply]
Re^3: Simple sql query problem by jrsimmon (Hermit) on Dec 01, 2007 at 19:01 UTC
That's why I think that you'll have better luck by submitting your query directly to mysql rather than through perl. It's always a good idea to get as close to the error as you can when you run into something you don't understand.	[reply]
Re: Simple sql query problem by kyle (Abbot) on Dec 01, 2007 at 15:04 UTC
I think there's something you're not showing us. That error message looks to me as if you tried to interpolate a reference into your query string. For example: `my $stuff = [ 'things', 'items', 'etc.' ]; my $query = "SELECT $stuff FROM foo"; print $query, "\n"; __END__ SELECT ARRAY(0x504290) FROM foo` [download] Is this possible? It would probably be useful, from a debugging standpoint, to print out your query before you send it to the database.	[reply] [d/l]
Re^2: Simple sql query problem by Anonymous Monk on Dec 01, 2007 at 15:50 UTC
Thanks for response. I think I'm not hiding anything, cause SQL query is sent as string to subroutine.... So string is used as it is declared in code. I just wanted to check if there is any obvious error in this code.. Regards, Rob.	[reply]
Re: Simple sql query problem by snopal (Pilgrim) on Dec 01, 2007 at 15:52 UTC
In 'sub Sql_Query' you are returning a value. In 'sub Sql_Query_New' you are returning a hash ref object whose key/value pair are 'EK_HouseMode' and the result.	[reply]


The stupid question is the question not asked
	PerlMonks