[Openinteract-help] SELECT problem...

[Chris M. <Chr...@te...>]

Hello,

Making my own package and having some issues with a DB query... Hope someone
can help.

The code from the handler:
 $params->{host_list} = $R->host->fetch_iterator({
                        DEBUG => 5,
                        where => "customer_id =
$R->{auth}{user}{customer_id}",
                        order => 'monitored_hosts.monitored_host_id' });

the SPOPS def for this object:
  'host' => {
    'object_name' => 'Host',
    'no_update' => [
      'monitored_host_id'
    ],
    'isa' => [
      'OpenInteract::SPOPS',
      'SPOPS::Secure',
      'SPOPS::DBI::MySQL',
      'SPOPS::DBI'
    ],
    'has_a' => {
      'testsite::User' => [
        'user_id'
      ]
    },
    'class' => 'testsite::Monitoring',
    'no_insert' => [
      'monitored_host_id'
    ],
    'id_field' => 'monitored_host_id',
    'base_table' => 'monitored_hosts',
    'field' => [
      'monitored_host_id',
      'customer_id',
      'host',
      'ping_type',
      'is_alive',
      'email',
      'pager_number',
      'paging_configuration_id'
    ],
    'links_to' => {
      'testsite::PagingConfig' => 'paging_configurations',
      'testsite::PingLog' => 'ping_log'
    }
  },
.... (there are other objects)

the error log:
SPOPS::SQLInterface::db_select (78) >> Entering db_select with  $VAR1 = {
          'from' => [
                      'monitored_hosts'
                    ],
          'fields' => [
                        'monitored_host_id',
                        'customer_id',
                        'host',
                        'ping_type',
                        'is_alive',
                        'email',
                        'pager_number',
                        'paging_configuration_id'
                      ],
          'select' => [
                        'monitored_hosts.monitored_host_id',
                        'monitored_hosts.customer_id',
                        'monitored_hosts.host',
                        'monitored_hosts.ping_type',
                        'monitored_hosts.is_alive',
                        'monitored_hosts.email',
                        'monitored_hosts.pager_number',
                        'monitored_hosts.paging_configuration_id'
                      ],
          'DEBUG' => 5,
          'return' => 'sth',
          'where' => 'customer_id = 1',
          'order' => 'monitored_hosts.monitored_host_id',
          'class' => 'testsite::Monitoring',
          'offset' => 0,
          'max' => 0
        };

SPOPS::SQLInterface::db_select (86) >> No SQL passed in to execute directly;
building.
SPOPS::SQLInterface::db_select (107) >> SQL for select:
           SELECT  monitored_hosts.monitored_host_id,
monitored_hosts.customer_id, monitored_hosts.host, monitored_hosts.ping_
type, monitored_hosts.is_alive, monitored_hosts.email,
monitored_hosts.pager_number, monitored_hosts.paging_configuration_id
             FROM monitored_hosts
            WHERE customer_id = 1

            ORDER BY monitored_hosts.monitored_host_id

OpenInteract::UI::Main::handler (42) >> Action died. Here is what it left:
SELECT failed; cannot retrieve records
           SELECT  monitored_hosts.monitored_host_id,
monitored_hosts.customer_id, monitored_hosts.host, monitored_hosts.ping_
type, monitored_hosts.is_alive, monitored_hosts.email,
monitored_hosts.pager_number, monitored_hosts.paging_configuration_id
             FROM monitored_hosts
            WHERE customer_id = 1

            ORDER BY monitored_hosts.monitored_host_id
         at /usr/local/lib/perl5/site_perl/5.6.1/SPOPS/SQLInterface.pm line
119.

Anyway, any assistance would be much appreciated.
Note that the above query (from the error log) works if I paste it into the
mysql console (and add a semicolon)

Thanks!

Chris McDaniel