* Get and initialise a DAS with the metadata
*************************************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/*************************************************************************************
* Find the company or companies and delete
*************************************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
try {
$root = $das->executeQuery($dbh, "select name, id from company where name='Acme'", array('company.name', 'company.id', 'company.employee_of_the_month'));
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to retrieve data from the database.";
echo "Probably something wrong with the SQL query.";
echo "\n" . $e->getMessage();
exit;
}
foreach ($root['company'] as $acme) {
echo "Looked for Acme and found company with name = " . $acme->name . " and id " . $acme->id . "\n";
}
unset($root['company']);
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
try {
$das->applyChanges($dbh, $root);
} catch (SDO_DAS_Relational_Exception $e) {
echo "\nSDO_DAS_Relational_Exception raised when trying to apply changes.";
$reference_metadata = array($parent_reference);
// Create the Relational Data Access Service telling it the database
// schema, that table should be considered the root of the graph,
// and finally the additional information for the object model.
$das = new SDO_DAS_Relational($table_metadata, 'alltypeparent', $reference_metadata);
// no security on my locate database so access control strings are empty
$user = "";
$password = "";
try {
// connect to the mysql database. This connection will be released when the
// $dbh variable is cleaned up.
$dbh = new PDO("mysql:host=localhost;dbname=interop", $user, $password);
// construct the SQL query for contact retrieval
$stmt = "select p.parentid, p.description, a.abit, a.atinyint, a.aboolean, a.asmallint, a.amediumint, a.ainteger, a.abigint, a.afloat, a.adouble, a.adoubleprecision, a.areal, a.adecimal, a.adate, a.adatetime, a.atimestamp, a.atime, a.ayear, a.achar, a.avarchar, a.parentid from alltypeparent p, alltype a where p.parentid = a.parentid";
// execute the query to retrieve the departments
$root = $das->executeQuery($dbh, $stmt, array('alltypeparent.parentid', 'alltypeparent.description', 'alltype.abit', 'alltype.atinyint', 'alltype.aboolean', 'alltype.asmallint', 'alltype.amediumint', 'alltype.ainteger', 'alltype.abigint', 'alltype.afloat', 'alltype.adouble', 'alltype.adoubleprecision', 'alltype.areal', 'alltype.adecimal', 'alltype.adate', 'alltype.adatetime', 'alltype.atimestamp', 'alltype.atime', 'alltype.ayear', 'alltype.achar', 'alltype.avarchar'));
echo "\nprint_r root \n";
print_r($root);
echo "\n";
// get each alltype object and print location
echo "print out data for each alltype \n";
$alltypeparent = $root['alltypeparent'];
$alltype = $alltypeparent[0]['alltype'];
$count = 1;
foreach ($alltype as $row) {
echo "Alltype obtained from the database has id = " . $row['asmallint'] . "\n";
$count = $count + 1;
}
//create a new row in the table
$newrow = $alltypeparent[0]->createDataObject('alltype');
// set the properties from the first row (the one that was loaded when the table was created)
*
* See companydb_mysql.sql and companydb_db2.sql for examples of defining the database
*/
/**************************************************************
* Get and initialise a DAS with the metadata
***************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$root = $das->executeQuery($dbh, "select name, id from company where name='Acme'", array('company.name', 'company.id'));
$company = $root['company'][0];
$name = $company->name;
$id = $company->id;
unset($root['company']);
$new_company = $root->createDataObject('company');
$new_company->name = $name;
$new_company->id = $id;
/**************************************************************
* Get a PDO database connection
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
/**************************************************************
* Write the changes out
***************************************************************/
try {
* Retrieve multiple company rows from the company table.
*/
/**************************************************************
* Get and initialise a DAS with the metadata
***************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/**************************************************************
* Get a PDO database connection
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
/**************************************************************
* Issue a query to obtain the company data objects
***************************************************************/
try {
$root = $das->executeQuery($dbh, 'select name, id from company', array('company.name', 'company.id'));
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to retrieve data from the database.";
echo "Probably something wrong with the SQL query.";
echo "\n" . $e->getMessage();
exit;
}
foreach ($root['company'] as $company) {
echo "Company obtained from the database has name = " . $company['name'] . " and id " . $company['id'] . "\n";
}
$das->applyChanges($dbh, $root);
echo "\nCompany Acme has been written to the database\n";
/**************************************************************
* Retrieve the company and Shoe department, then delete Shoe and add IT
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$root = $das->executeQuery($dbh, 'select c.id, c.name, d.id, d.name from company c, department d where d.co_id = c.id', array('company.id', 'company.name', 'department.id', 'department.name'));
$acme = $root['company'][0];
echo "Looked for Acme and found company with name = " . $acme->name . " and id " . $acme->id . "\n";
$shoe = $acme['department'][0];
echo "Looked for Shoe department and found department with name = " . $shoe->name . " and id " . $shoe->id . "\n";
unset($acme['department'][0]);
$it = $acme->createDataObject('department');
$it->name = 'IT';
$das->applyChanges($dbh, $root);
echo "Deleted a department and added one\n";
/**************************************************************
* Retrieve the company and IT department, then delete the whole company
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$root = $das->executeQuery($dbh, 'select c.id, c.name, d.id, d.name from company c, department d where d.co_id = c.id', array('company.id', 'company.name', 'department.id', 'department.name'));
$acme = $root['company'][0];
$it = $acme['department'][0];
echo "Looked for IT department and found department with name = " . $it->name . " and id " . $it->id . "\n";
unset($root['company'][0]);
$das->applyChanges($dbh, $root);
echo "Deleted Company and IT department\n";
public function testExecuteQuery_ColumnSpecifersMustBeValidTableAndColumnNames()
{
$company_table = array('name' => 'company', 'columns' => array('id', 'name', 'employee_of_the_month'), 'PK' => 'id');
$database_metadata = array($company_table);
$exception_thrown = false;
$das = new SDO_DAS_Relational($database_metadata);
try {
$company = $das->executeQuery('dummy PDO handle', 'SELECT * from company', array('not-a-table.not-a-column'));
} catch (SDO_DAS_Relational_Exception $e) {
$exception_thrown = true;
$msg = $e->getMessage();
}
$this->assertTrue($exception_thrown, 'Exception was never thrown');
$this->assertTrue(strpos($msg, 'table.column') != 0, 'Wrong message issued: ' . $msg);
}
// keep sue but we no longer have an employee of the month
$acme->employee_of_the_month = null;
$das->applyChanges($dbh, $root);
echo "Wrote back company with null for employee of the month\n";
/**************************************************************
* Retrieve the company and check the employee of the month is null; then set it back to Sue again
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$root = $das->executeQuery($dbh, 'select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name' . ' from company c, department d, employee e ' . ' where e.dept_id = d.id and d.co_id = c.id', array('company.id', 'company.name', 'company.employee_of_the_month', 'department.id', 'department.name', 'employee.id', 'employee.name'));
$acme = $root['company'][0];
echo "Looked for Acme and found company with name = " . $acme->name . " and id " . $acme->id . "\n";
$shoe = $acme['department'][0];
echo "Looked for Shoe department and found department with name = " . $shoe->name . " and id " . $shoe->id . "\n";
$sue = $shoe['employee'][0];
echo "Looked for Employee Sue and found employee with name = " . $sue->name . " and id " . $sue->id . "\n";
assert($acme->employee_of_the_month === null);
$acme->employee_of_the_month = $sue;
$das->applyChanges($dbh, $root);
/**************************************************************
* Retrieve the company and check the eotm is sue
* We are not injecting anything into the SQL statement so safe to use the simple executeQuery
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
$root = $das->executeQuery($dbh, 'select c.id, c.name, c.employee_of_the_month, d.id, d.name, e.id, e.name' . ' from company c, department d, employee e ' . ' where e.dept_id = d.id and d.co_id = c.id', array('company.id', 'company.name', 'company.employee_of_the_month', 'department.id', 'department.name', 'employee.id', 'employee.name'));
$acme = $root['company'][0];
echo "Looked for Acme and found company with name = " . $acme->name . " and id " . $acme->id . "\n";
$sue = $acme->employee_of_the_month;
assert($sue->name == 'Sue');
/**************************************************************
* Get and initialise a DAS with the metadata
***************************************************************/
try {
$das = new SDO_DAS_Relational($database_metadata, 'company', $SDO_reference_metadata);
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to create the DAS.";
echo "Probably something wrong with the metadata.";
echo "\n" . $e->getMessage();
exit;
}
/**************************************************************
* Get a database connection
***************************************************************/
$dbh = new PDO(PDO_DSN, DATABASE_USER, DATABASE_PASSWORD);
/**************************************************************
* Issue a query to obtain a company data object
***************************************************************/
try {
$root = $das->executeQuery($dbh, 'select name, id from company where name="Acme" or name="emcA"', array('company.name', 'company.id'));
} catch (SDO_DAS_Relational_Exception $e) {
echo "SDO_DAS_Relational_Exception raised when trying to retrieve data from the database.";
echo "Probably something wrong with the SQL query.";
echo "\n" . $e->getMessage();
exit;
}
$company = $root['company'][0];
assert($company->name == 'Acme' || $company->name == 'emcA');
echo "obtained a company with name of " . $company->name . "\n";
$company->name = 'Acme';
$das->applyChanges($dbh, $root);
