Proper way to run SQL

(Nic Hubbard) #1

I am writing a script, and I need to query the Matrix DB for some data. What is the proper method for doing this?


So far, I was trying:


    require_once $MATRIX_ROOT.'/core/include/init.inc';
    require_once $MATRIX_ROOT.'/core/include/mysource.inc';
    $db_data = Array();
    $db = new MatrixDAL();
    $db->getDb();
    // Just a test statement
    $sql = 'SELECT type_code FROM sq_ast_typ';
    $rows = $db->executeSqlAssoc($sql);
    foreach ($rows as $row) {
    	$db_data[$row['type_code']] = $row;
    }
    print_r($db_data);


Error:
    [01-Jul-2010 09:16:58] PHP Fatal error: Cannot call private DAL::__construct() in /home/websites/mysource_matrix/core/lib/MatrixDAL/MatrixDAL.inc on line 55


Obviously I can't do that. So, what it the correct way?
(Luke Wright) #2

No need to construct and get the DB. For calling queries, the functions in the MatrixDAL class are called statically:

    $rows = MatrixDAL::executeSqlAssoc($sql);


In some circumstances, you may need to switch the database connection to the appropriate one (ie. ensure db2 is selected if doing anything that involves writing to the database):
    $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');   // before
    ...
    $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection();       // after

But that wouldn't be necessary in this case if you're just running SELECT code.

Hope this helps. :)
(Ashish Karelia) #3

[quote]
I am writing a script, and I need to query the Matrix DB for some data. What is the proper method for doing this?



So far, I was trying:


    require_once $MATRIX_ROOT.'/core/include/init.inc';
    require_once $MATRIX_ROOT.'/core/include/mysource.inc';
    $db_data = Array();
    $db = new MatrixDAL();
    $db->getDb();
    // Just a test statement
    $sql = 'SELECT type_code FROM sq_ast_typ';
    $rows = $db->executeSqlAssoc($sql);
    foreach ($rows as $row) {
    	$db_data[$row['type_code']] = $row;
    }
    print_r($db_data);


Error:
    [01-Jul-2010 09:16:58] PHP Fatal error: Cannot call private DAL::__construct() in /home/websites/mysource_matrix/core/lib/MatrixDAL/MatrixDAL.inc on line 55


Obviously I can't do that. So, what it the correct way?
[/quote]

Hi Nic,
You would need to initialize the MatrixDal like you are trying to do. Also you wouldnt need to include mysource.inc for your script.
You can do something like below

    

require_once $SYSTEM_ROOT.'/core/include/init.inc';

$sql = "SELECT assetid, type_code from sq_ast";
$result = MatrixDAL::executeSqlAssoc($root_node_treeid_sql);

$delete_sql = "DELETE FROM sq_ast where assetid= '999'";
$query = MatrixDAL::preparePdoQuery($delete_sql);
$result = MatrixDAL::execPdoQuery($query);




Also depending you what you would be trying to do in the script , there might be need to change the run level too.

Hope this helps :slight_smile:





Regards,



Ash
(Dw Andrew) #4

This is generic code i've taken from other matrix scripts, and use it for all different kinds of things.
You can take your pick from core/lib/DAL/DAL.inc funtions as to what function you need to run the query


    
    <?php
    error_reporting(E_ALL);
    if (php_sapi_name() != 'cli') {
        trigger_error("You can only run this script from the command line\n", E_USER_ERROR);
    }
    
    $SYSTEM_ROOT = (isset($_SERVER['argv'][1])) ? $_SERVER['argv'][1] : '';
    if (empty($SYSTEM_ROOT) || !is_dir($SYSTEM_ROOT)) {
        trigger_error("You need to supply the path to the System Root as the first argument\n", E_USER_ERROR);
    }
    
    require_once $SYSTEM_ROOT.'/core/include/init.inc';
    
    error_reporting(E_ALL); 
    ini_set('memory_limit', '-1');
    
    
    $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');
    $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN');
                 
    $db = NULL;
    // PHP 5
    if (version_compare(phpversion(), 5) >= 0) {
    	$db = MatrixDAL::getDb();
    } else {
    	$db =& $GLOBALS['SQ_SYSTEM']->db;
    }
    
    
    $select = "select blah etc";
    $query = MatrixDAL::preparePdoQuery($select);
    $results = MatrixDAL::executePdoAssoc($query);
    
    //manipulation and printing//
    
    //$GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT');
    $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection();
    
    ?>
(Nic Hubbard) #5

[quote]

    
    
    require_once $SYSTEM_ROOT.'/core/include/init.inc';
    
    $sql = "SELECT assetid, type_code from sq_ast";
    $result = MatrixDAL::executeSqlAssoc($root_node_treeid_sql);
    
    $delete_sql = "DELETE FROM sq_ast where assetid= '999'";
    $query = MatrixDAL::preparePdoQuery($delete_sql);
    $result = MatrixDAL::execPdoQuery($query);


[/quote]

Thanks Ash! This is exactly what I was looking for.