[Solar-talk] Interesting SQL Error from Model

[Kilbride, James P.]

I've got a model which seems to be giving me this error from my
database:

'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near \'procedure WHERE
procedure.procedure_id = 101 ORDER BY procedure.procedure_id A\' at line
17'

The class name is procedure, the table name is documented_procedure,
though it looks like the database has it called procedure, not sure why
on that one. Here's the full stmt according to the PDO output:

'SELECT procedure_id, document_id, procedure_number, procedure_title,
approver, publisher, distributor, requested_support, nsi, uk, type,
documentation_type, other_developers, remarks, active FROM
documented_procedure AS procedure WHERE procedure.procedure_id = 101
ORDER BY procedure.procedure_id ASC LIMIT 1'
This is occurring because I"ve got a related table(matrix) which has a
procedure_id in it and is a child of the procedure table. I'm trying to
get the documented_procedure row for that matrix. 

would the class code help at all? If so it's below.

James Kilbride

class ProcedureMatrix_Models_VerificationMatrix  extends Solar_Sql_Model
{
    protected function _setup()
    {
    	$this->_table_name = 'verification_matrix';
    	
    	// Columns
    	
    	$this->_table_cols = array(
            'verification_matrix_id' => array (
            	'type'	=>'int',
            	'primary' => true,
            	'autoinc' => true,
            	'require' =>true,
            ),
   	           'procedure_id' => array(
                'type'    => 'int',
                'require' => true,
            ),
   	           'location_id' => array(
                'type'    => 'int',
                'require' => true,
            ),
   	           'technical_cog' => array(
                'type'    => 'varchar',
                'size'	  => 255,
                'valid'	  => 'word',
                'require' => true,
            ),
   	           'govt_witness' => array(
                'type'    => 'varchar',
                'size'	  => 255,
                'valid'	  => 'word',
            ),
   	           'estimated_hours' => array(
                'type'    => 'int',
            ),
   	           'estimated_people' => array(
                'type'    => 'int',
            ),
   	           'planned_verify_date' => array(
                'type'    => 'date',
            ),
   	           'results' => array(
                'type'    => 'varchar',
                'size'	  => 4,
            ),
   	           'result_date' => array(
                'type'    => 'date',
            ),
   	           'government_signature' => array(
                'type'    => 'varchar',
                'size'	  => 255,
            ),
   	           'contractor_signature' => array(
                'type'    => 'varchar',
                'size'	  => 255,
            ),
   	           'sitp_team' => array(
                'type'    => 'clob',
                'valid' => 'word'
            ),
   	           'conjunctive_test' => array(
                'type'    => 'clob',
                'valid' => 'word'
            ),
   	           'remarks' => array(
                'type'    => 'clob',
                'valid' => 'word'
            ), 
    	);
    	
    	$this->_belongsTo('procedure', array (
    		'foreign_class' => 'ProcedureMatrix_Models_Procedure',
    		'foreign_key' 	=> 'procedure_id',
    	));
    	$this->_belongsTo('location', array (
    		'foreign_class' => 'ProcedureMatrix_Models_Location',
    		'foreign_key' 	=> 'location_id',
    	));
    	$this->_hasMany('equipmentRelations', array(
    		'foreign_class' =>
'ProcedureMatrix_Models_VerificationEquipment',
    		'foreign_key'	=> 'verification_matrix_id',
    	));
    }
    /**
     * Set of result options for Verification
     *
     * @return array - Array of options for matrix_results field select
on the matrix.
     */
	public function resultOptions(){
		return array(
			''=>'',
			'PASS' =>'PASS',
			'FAIL' =>'FAIL',
		);
	}
}


class ProcedureMatrix_Models_Procedure extends Solar_Sql_Model
{
    protected function _setup()
    {
    	$this->_table_name = 'documented_procedure';
        // default order
        $this->_order = array(
            'procedure_number'
        );
        
        
        // -------------------------------------------------------------
        // 
        // COLUMNS
        // 
        $this->_table_cols = array(
            'procedure_id' => array(
                'type'    => 'int',
                'require' => true,
                'primary' => true,
                'autoinc' => true,
            ),
            'document_id' => array(
                'type'    => 'int',
                'require' => true,
            ),
            'procedure_number' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'procedure_title' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'approver' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'publisher' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'distributor' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'requested_support' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'nsi' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'uk' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'type' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'documentation_type' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'other_developers' => array (
            	'type'	=>'varchar',
            	'size' => 255,
            	'valid' => 'word',
            	'require' =>true,
            ),
            'remarks' => array (
            	'type'	=>'clob',
            	'valid' => 'word',
            ),
            'active' => array(
                'type'    => 'bool',
                'require' => false,
            ),
			
        );
        $this->_belongsTo('document', array(
            'foreign_class' => 'ProcedureMatrix_Models_Document',
            'foreign_key'   => 'document_id',
        ));
        $this->_hasMany('developerRelations', array(
        	'foreign_class' =>
'ProcedureMatrix_Models_ProcedureDeveloper',
        	'foreign_key' => 'procedure_id',
        ));
        $this->_hasMany('locationRelations', array(
        	'foreign_class' =>
'ProcedureMatrix_Models_VerificationMatrix',
        	'foreign_key' => 'procedure_id',
        ));
    }
    /**
     * Setup the UK Options as hardcoded in a single location based on
class.
     *
     * @return array - contains the selectable options for the UK
Information
     */
	public function UKOptions() {
		return array(
			'NO' => 'NO',
			'YES'=>'YES',
		);
	}
    /**
     * Setup the NSI Options as hardcoded in a single location based on
class.
     *
     * @return array - contains the selectable options for the NSI
Information
     */
	public function NSIOptions() {
		return array(
			'NO' => 'NO',
			'YES'=>'YES',
		);
	}
    /**
     * Setup the Type Options as hardcoded in a single location based on
class.
     *
     * @return array - contains the selectable options for the Type
Information
     */
	public function TypeOptions() {
		return array(
			''=>'',
			'New'=>'New',
			'Modified' => 'Modified',
			'Pull Through' => 'Pull Through',
		);
	}
    /**
     * Setup the Document Type Options as hardcoded in a single location
based on class.
     *
     * @return array - contains the selectable options for the Document
Type Information
     */
	public function DocTypeOptions() {
		return array(
			'Electronic' => 'Electronic',
			'Paper' => 'Paper',
		);
	}
}
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman-mail3.webfaction.com/pipermail/solar-talk/attachments/20080502/e2ec3bfe/attachment-0001.html