Adodb - Cannot connect to Excel 1

[MakeItSo]

Hi friends,

I am trying to connect to an Excel file using ADODB / Jet.
I have no idea why.
Here's the relevant code part:

$strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . $dbPath . ";Excel 8.0;HDR=Yes;";
//...
$conn = new COM("ADODB.Connection") or die("Cannot connect to Excel");
echo $strConnection;
$conn->Open($strConnection)or die("Cannot connect to Excel");

I see the connection string, hence the Connection object itself works. I've already added the "extension=php_com_dotnet.dll" part to php.ini.
On a previous php page, I already connect to an access database using COM, hence I know it works.
The path to the Excel file is correct, too. It is the absolute path to the Excel, i.e.:

$dbPath = realpath("../../../[mydirectory]/[myfile].xls");

resulting in this output:
Data Source=C:\inetpub\wwwroot\[mydirectory]\[myfile].xls;
I've also tried

$dbPath=str_replace("\\","/",$dbPath);

just in case the backslash cause any problems although I don't know why it should, it doesn't with the access db.

[mydirectory], [myfile] only masked here, they're normal, explicit folder/file names.

I've given IIS_IUSRS and IIS_WPG sufficient rights to the folder.
Any idea what is wrong here?

Using: PHP 5 under Windows 7, IIS7. No WAMPP/XAMPP whatever.

Thanks for any input!

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

Argh! Solved this part...
It's not $conn->Open but $conn->Connect...


That's what happens when you translate a classic ASP page into php...



“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

Ermmm... Wait: actually, in this case it is $conn->Open...


Back to square one...

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

Update:
my OS is Windows 7 Pro 64 Bit.
I have both Office 2003 and Office 2010 installed.
Could that be an issue?
If so: why does it work with Access?

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

Update:
I added

include('../../adodb5/adodb.inc.php');

At the top of the file (that relative path is correct), modified the connection string by putting the database path in quotes and correcting the Extended Properties section:

$strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" . $dbPath . "\";  [b]Extended Properties=[/b]\"Excel 8.0;HDR=Yes\";";

The connection string echo is as follows:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\inetpub\wwwroot\[mydir]\[myfile].xls"; Extended Properties="Excel 8.0;HDR=Yes";

Looks good to me.

Still, this

 $conn->Open($strConnection) or die ("Cannot connect to Excel");

dies.

Replacing the backslashes in the final $dbPath with forward slashes does not help either.

Any of you PHP jedis know what is cooking here?


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[vacunita]

Total shot in the dark here, but all I can find about it uses the direct excel driver:

$conn->Open("Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=$xlsFile;ExtendedProperties=Excel 10.0;");

Have you tried it with that?


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[MakeItSo]

*sigh*
Yet another update:

If I set each of the connection settings explicitely, like this:

$conn->Provider="Microsoft.Jet.OLEDB.4.0";
$conn->DataSource=$dbPath;
$conn->ExtendedProperties="Excel 8.0;HDR=Yes";
$conn->Mode=16;
$conn->Open or die ("Cannot connect to Excel");

echo "got through!";

... then I get neither the "die" message nor my "got through" echo.
I thought: hey, maybe "DataSource" and "Data Source" ain't the same, eh?

This however produces the same non-result:

$conn->Properties['Provider']="Microsoft.Jet.OLEDB.4.0";
$conn->Properties['Data Source']=$dbPath;
$conn->Properties['Extended Properties']="Excel 8.0";
$conn->Properties['Mode']=16;
$conn->Open or die ("Cannot connect to Excel");

What the frigg is going on here?

Starting to hate php...

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[jpadie]

I don't use windows any more, but I will have a go.

first off, are you sure the jet driver works on xlsx files? or are you using an old style xls file?
second: are you wedded to using COM? e.g. might it be ok for you to use a standard odbc connection and configure the DSN in systems administrator? then you can use PDO to connect directly to the DSDN.
third: assuming we are staying with this method, let's just take a step at a time and debug. make sure that you are using a 32bit system as there is no 64bit jet driver that i am aware of.

ini_set('display_errors', true);
error_reporting(E_ALL);

//instantiate a COM object
$com = new COM('ADODB.Connection');
//test to ensure that the COM object exists
if($com == false) die('problem starting ADODB Connection');
echo 'successfully instantiated a connection to the adodb service' . "\n";

//open path to excel file
$connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\"%s\";  Extended Properties=\"Excel 8.0;HDR=Yes\";"; $fileName = 'C:/absolute path/to/excel/file.xls';
$connectionString = sprintf($connectionString, $fileName);
echo "DEBUG: " . $connectionString . "\n\n";
$com->open($connectionString) or die('problem connecting to excel document');
echo "DEBUG: successfully attached to excel document";

 

[MakeItSo]

Hi Phil,

thanks for the feedback!
Using that dsn like connection, at least I get my "Cannot connect to Excel" back.
Same with "Provider=Microsoft.ACE.OLEDB.12.0" and with "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=..."
*snif*
*shrug*

It cannot be THAT hard, can it?

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

@jpadie:
1.) this hsall work with an old style .xls file.
2.) this is currently running on localhost but shall in future (once it works) run on a hosted site. So far, I've never made good experience with file based dsn of any sort. Grew to hate 'em.
3.) I am currently running this on a 64 bit windows. As mentioned, I can connect to an old style access .mdb for verification from another .php before accessing this very page here.
I have configured the IIS application pool to activate 32 bit applications.
4.) I have added your COM test lines and get "successfully instantiated a connection to the adodb service COM created"

Echos of connection string and file path I've done till I was blue in the face: they're good.

TA,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

OK, I think I got something:
Indeed it seems to be a 64-bit related problem here.
If I open the System's ODBC manager, I get a driver error for Access & Excel drivers.
Downloaded & installed the latest database engine from Microsoft, still the same error.
Opening odbcad.exe from the SysWOW64 directory however gives me the correct driver entries.

I'll give it another try tomorrow, will install php on a 32 bit system and retry.

This makes you feel like 64 bit just appeared yesterday, out of thin air, and nobody expected it, least of all Microsoft!



“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[jpadie]

I think it is more that ado based access via jet is no longer supported by ms. There is a third party driver layer that exists however.

The reason I guess is that there is very little reason why uoubwoukd want to do what you are trying to do. Excel is a terrible medium for data storage. I don't know the long term goal you have but if you care to share I'm happy to suggest alternatives. Off the top of my head I reckon using sqlite as a storage engine and medium and dumping to an excel format on an export request might satisfy your intended use (but I'm guessing).

Enabling 32 bit apps won't make any difference. The driver is not working at app level but system level. You need either a 32bit system or a 64bit driver.

Every time I've played with things like this in the past I've preferred system level dsns and odbc. But you are still left with a driver issue if you want to use jet. There are direct excel drivers that may work better however. These can be used without dsns through a com object. But that would not be my preference as I've never been Comfortable about using com through php due to the debug headaches.

 

[MakeItSo]

Xls is not the format of my choice but that of the customer.
Xls is the source format and the customer wishes to be able to download the latest data in xls format. I tried to use the xls as data source in the still current classic asp site, no problem.
In a few weeks, we'll have to switch to php and a hosted site as compared to a dedicated windows server as until now. I know that a db solution will be preferrable. But I need to buy time. We need to be able to switch without the customer noticing or needing to adapt.
The latest data source is xls.
If I can use this as basis and have a true db to work with instead AND have the possibility to provide a download of that latest status in xls format, I'll be the last to refuse to switch. ;-)

Making the old version work and then testing new ways was my approach.
I'm open for other solutions though.
Whatever works will be good!

TA,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[jpadie]

If I can use this as basis and have a true db to work with instead AND have the possibility to provide a download of that latest status in xls format, I'll be the last to refuse to switch

this is the path of least resistance and the work of moments to achieve the import and export. for the export you can either use a spreadsheet builder (my preference) or keep things really simple and spit out a csv with an excel application header and file name. the browser will interpret the file as an excel file and open it. excel will automatically convert either a csv or an html table to its own format (or at least open it in such a way that the process is transparent to the user).

to do the import - just dump to csv and write a quick bit of code to parse the file and put it in a database.

literally - writing the export code is less than 10 mins for a csv solution and less than a half hour for the excel native variant. writing the import code - budget on 5 mins per column and you'll be fine. take the time to expand the table structure and normalise the database at the point of import, of course. don't just replace flat file with flat file...

for a database - if excel were chosen for its backup-ability and portability, sqlite is your friend. otherwise any old thing is fine (mysql?).

use PDO (not adodb) as your db abstraction layer. that gives you more (native) options to change data layers later.

 

[jpadie]

if you're willing to share the data-structure of the excel spreadsheet I can see whether i have routines already built that will be close to what you need.

 

[MakeItSo]

Good morning!

Just so this is 100% cear: I won't have Excel installed on the future web server.
Is there a way to create XLS without Excel installed?

Else I would rather export to an Excel-compatible XML.
Why?
Believe it or not: this is a very simplified custom terminology management site. [cough]
The data structure is simple enough:
Recid | German | English | Chinese | ... | Definition | Context | Annotation

RecID is a simple, continuously numbered integer, the other columns hold a term in the respective language, so the export will need to be in UTF-8 or UTF-16 and some of the fields may contain commas, semicola, linebreaks, single quotes or double quotes.
So far, all fields are restricted to 255 characters (and I'd like to keep it that way).
XML should be the safest option here, plus it is not dependent on OS language for delimiters.

Thanks!
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[jpadie]

Not a problem to output to xls without excel. Or xlsx which may be better if you need things in utf16. Does office support this natively in version 8?

Equally not difficult to export to XML but that's is somewhat different to office XML of course. Personally I never use XML for data transport as I find the overhead in parsing the structure unworthy of the structure itself. I'd rather use json for structured data and CSV for flat.

I will check my code stores and post back when I'm back at my desk in a couple of hours.

As it happens I think a flat file is not the best structure for this type of data. FYI.

 

[MakeItSo]

Okay, before switching over to using different technology, I wanted to know what exactly is going on here - and it is getting more and more mysterious!

I've created a new Access database and simply linked the excel table in it.
Since connecting to Access mdb worked in the login verification page, it MUST work on this page too - but it doesn't!
The path to the mdb is correct; here's my connection string:

echo $strConnection;

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\inetpub\wwwroot\[dir]\[file].mdb; Persist Security Info=False;

Adodb COM object is being created,

$conn = new COM("ADODB.Connection") or die ("Cannot establish COM");

...but this fails:

$conn->Open($strConnection) or die ("Cannot connect to Excel");

This doesn't make sense!
There must be some simple thing fundamentally wrong!

For comparison, here's the code part from my verification php, one dir level higher up:

$conn = new COM("ADODB.Connection") or die("Cannot start ADO");
    $recordSet = New COM("ADODB.Recordset");
    $conn->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . realpath("../../[dir]/[file].mdb") . ";Persist Security Info=False;");
	$recordSet = $conn->Execute($SQL);

which works just fine!
The only difference I can see is that in the verfication page, the code is with the html <body> whereas in the non-working page it is outside the body element.
But that cannot possibly make a difference.
I don't get it!



“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[jpadie]

Since connecting to Access mdb worked in the login verification page, it MUST work on this page too - but it doesn't!

I don't see that this is necessarily true. you are assuming a lot of the underlying programs.

beyond the above, as I don't use this kind of technology I can't help you further without creating a windows system to test on. People have reported success with the ACE driver, if you are willing to purchase it.

i have written some quick code for you, should you wish to try it. it will follow in the next posts.

 

[jpadie]

you need to install pear spreadsheet write. typically this is done by the following command

pear install Spreadsheet_Excel_Writer

or if you are using the more up to date installer

php pyrus.phar install pear/Spreadsheet_Excel_Writer

all code should be inserted in to the same file in the order I post it. This may require several posts due to the length
the short cut functions are documented, and the import function has some samples for you.

major warning: i have not tested this code as I do not have sample data to hand. feel free to post back with queries, if you do use it.

<?php
ob_start();
//start the motor

$pdo = new PDO("sqlite:termsDatabase.sqlite");
setup();  //comment out this line when you're sure that the database is properly populated

/** helper functions **/
define("BASE_LANGUAGE", 'english'); //change if English is not the base language.  NB must be lower case


/**
 * function to create a new term.  typically you will not need to call this directly
 *
 * @param $term
 * @param $recID
 * @param $definition
 * @param $context
 * @param $annotation
 * @return id of newly created or existing term
 */
function addTerm($term, $recID=NULL, $definition='', $context='', $annotation=''){
	$term = new term;
	$term->loadfromterm($term);
	if($term->getPrimary()):
		return $term->getPrimary();
	endif;
	$term->term = $term;
	$term->save();
	addTranslation($term, BASE_LANGUAGE, $term);
	return $term->getPrimary();
}


/**
 * function to add a new translation of a term.
 *
 * @param $term - either the term in the base language OR the termID
 * @param $language - either the language in plain text or the languageID of the TARGET language
 * @param $translation - the translated term
 * @return the id of the latest entry, or false if there was an error
 */
function addTranslation($term, $language, $translation){
	if(!is_numeric($term)):
		$termID = addTerm($term);
	else:
		//double check that the termID is valid
		$term = new term;
		$term->loadfromID($term);
		if(empty($term->term)):
			die('invalid termID');
		endif;
	endif;
	return $term->addTranslation($language, $translation);
}

/**
 * function to translate a given term
 * @param $term - the term to translate (the termID or the term in the base language)
 * @param $language - the language to translate TO (either the languageID or the language)
 * @param $bypass - set to true to bypass the ID/text checks and pull the data straight from the translations table
 * @return translation string or false if no translation was found.
 */
function translate($term, $language, $bypass = FALSE){
	if($bypass):
		global $pdo;
		$sql = <<<SQL
SELECT 	translation
FROM	translations
WHERE	termID = ?
AND		languageID = ?
SQL;
		$s = $pdo->prepare($sql);
		$s->execute(array($term, $language));
		$row = $s->fetchObject();
		return $row->translation;
	endif;
	$term = new term;
	$term->loadfromID($term);
	if(empty($term->term)):
		die('invalid termID');
	endif;
	
	$translation = new translation;
	return $translation->get($term, $language);
}

/**
 * function to import a csv into the database
 * ensure it is in proper csv format and the top row has the column names in it
 */

function import($fileName){
	$fH = fopen($fileName, 'r') or die('cannot open file for processing');
	//get headers
	$headers = fgetcsv($fh);
	$mainFields = array('recID', 'definition','context','annotation');
	while(FALSE!== ($row = fgetcsv($fh))):
		//first create the term
		$language = array();
		$termID = $recID = $definition = $context = $annotation = NULL;
		foreach($headers as $colNum=>$field):
			if(in_array(strtolower($field), $mainFields)):
				$$field = $row[$colNum];
			elseif(strtolower($field) == BASE_LANGUAGE):
				$term = $row[$colNum];
			else:
				$language[$field] = $row[$colNum];
			endif;
		endforeach;
		$termID = addTerm($term, $recID, $definition, $context, $annotation);
		foreach($language as $key=>$val):
			$result = addTranslation($termID, $key, $val);
			if(!$result):
				logError($termID, $key, $val);
			endif;
		endforeach;
	endwhile;
	fclose($fH);
}

/**
 * small helper function to log errors to a file for later inspection
 * @param $termID
 * @param $key
 * @param $val
 * @return unknown_type
 */
function logError($termID, $key, $val){
	file_put_contents('logfile.txt', "$termID\t$key\t$val\n", FILE_APPEND);
}

/**
 * this creates the database tables
 * @return unknown_type
 */

function setup(){
	global $pdo;
	$sql = array(
	
<<<SQL
PRAGMA encoding = "UTF-16le";
SQL
,
<<<SQL
CREATE TABLE IF NOT EXISTS languages
(
	languageID INTEGER PRIMARY KEY AUTOINCREMENT,
	language TEXT NOT NULL UNIQUE
)
SQL
,
<<<SQL
CREATE TABLE IF NOT EXISTS terms
(
	termID INTEGER PRIMARY KEY AUTOINCREMENT,
	term TEXT NOT NULL UNIQUE,
	recID INT UNIQUE,
	definition TEXT,
	context TEXT,
	annotation TEXT
)
SQL
,
<<<SQL
CREATE TABLE IF NOT EXISTS translations
(
	termID INT ,
	languageID INT,
	translation TEXT
)
SQL
,
<<<SQL
CREATE UNIQUE INDEX IF NOT EXISTS tx ON translations
(
	termID,
	languageID
)
SQL
); //end array
	foreach($sql as $query):
		$r = $pdo->exec($query);
		if($r === false):
			print_r($pdo->errorInfo());
		endif;
	endforeach;
}

/**
 * function to export the sqlite file to a flat file
 * @param $format - either xls, csv, table, xml or sqlite
 * @return unknown_type
 */
function export($format){
	//get languages
	global $pdo;
	$s = $pdo->prepare("Select * from languages");
	$s->execute();
	$languages = array();
	$_languages = array();
	while($row = $s->fetchAssoc()):
		if(strtolower($row['language']) == BASE_LANGUAGE):
			array_unshift($languages, $row);
			array_unshift($_languages, $row['language']);
		else:
			$languages[] = $row;
			$_languages[] = $row['language'];
		endif;
	endwhile;
	
	//now get all the terms
	$s = $pdo->prepare("Select * from terms");
	$s->execute();
	$data = array();
	while($row = $s->fetchObject()):
		$term = array(
			$row->termID,
			$row->recID,
			$row->term
		);
		foreach($languages as $language):
			$tx = translate($row->termID, $language['languageID'], true);
			array_push($term, $tx === false  ? '' : $tx);
		endforeach;
		foreach(array('definition','context','annotation') as $item):
			array_push($term, $row->$item);
		endforeach;
		$data[] = $term;
	endwhile;
	
	$header = array_merge(array(
	'Term ID',
	'Record ID',
	'Term'),
	$_languages,
	array(
	'definition',
	'context',
	'annotation'
	));
	
	ob_end_clean();
	switch ($format):
	case 'xls':
	case 'excel':
		require_once 'Spreadsheet/Excel/Writer.php';
		$workbook = new Spreadsheet_Excel_Writer();
		$rowNumber = 0;
		// Creating a worksheet
		$worksheet =& $workbook->addWorksheet("Translation Export");
		$worksheet->setInputEncoding('UTF-16le');
		foreach($header as $key=>$field	):
		  $worksheet->write($rowNumber, $key, $field );
		endforeach;
		foreach($data as $row):
			$rowNumber++;
			foreach($row as $key=>$val):
				$worksheet->write($rowNumber, $key, $value);
			endforeach;
		endforeach;
		$workbook->send('Terms Export.xls');
		$workbook->close();
		exit;
		break;
	case 'csv':
		//nb may need a BOM here
		$fH = new fopen('temp.txt','wb');
		fwrite($fH,"\xFE\xFE"); //BOM
		fputcsv($fH, $header);
		foreach($data as $line):
			fputcsv($fH, $line);
		endforeach;
		fclose($fH);
		header('Content-Description: File Transfer');
		header('Content-Type: application/octet-stream');
		header('Content-Disposition: attachment; filename=file.csv');
		header('Content-Transfer-Encoding: binary');
		header('Expires: 0');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Pragma: public');
		readfile('temp.txt');
		unlink('temp.txt');
		exit();
		break;
	case 'table':
		header('Content-Type: text/html; charset=UTF-16');
		echo "\xFE\xFE";
		echo <<<HTML
<!DOCTYPE HTML>
	<html>
	<head>
		<meta http-equiv="Content-type" content="text/html;charset=UTF-16">
		<meta charset="UTF-16">
		<title>Terms</title>
	</head>
	<body>
HTML;
		echo '<table>';
		echo '<thead><tr>';
		echo '<th>'. implode('</th><th>', $header) . '</th>';
		echo '</tr></thead>';
		echo '<tbody>';
		foreach($data as $line):
			echo '<tr><td>' . implode('</td><td>', array_map('htmlspecialchars', $line)) . '</td></td>';
		endforeach;
		echo '</tbody></table></body></html>';
		exit;
	case 'xml':
		$fH = new fopen('temp.txt','wb');
		fwrite($fH, '<?xml version="1.0" encoding="UTF-16le"?>');
		fwrite($fH, "\n<terms>\n");
		foreach($data as $row):
			fwrite($fH, "\t" . '<term>'."\n");
			foreach($row as $key=>$val):
				fwrite($fH, "\t\t".'<' . $header[$key] . '>' . htmlspecialchars($val) . '</' . $header[$key] . '>' . "\n");
			endforeach;
			fwrite($fH, "\t" . '<\term>'."\n");
		endforeach;
		fwrite($fH,  '<\terms>');
		fclose($fH);
		header('Content-Description: File Transfer');
		header('Content-Type: application/octet-stream');
		header('Content-Disposition: attachment; filename=termsDatabase.xml');
		header('Content-Transfer-Encoding: binary');
		header('Expires: 0');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Pragma: public');
		readfile('temp.txt');
		unlink('temp.txt');
		exit();
		break;
	case 'sqlite':
		header('Content-Description: File Transfer');
		header('Content-Type: application/octet-stream');
		header('Content-Disposition: attachment; filename=termsDatabase.sqlite');
		header('Content-Transfer-Encoding: binary');
		header('Expires: 0');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Pragma: public');
		readfile('termsDatabase.sqlite');
		exit;
		break;
	endswitch;
}

class base{
	
	public function __construct(){
		foreach($this->fields as $field=>$val):
			$this->$field = $val;
		endforeach;
	}
	
	public function save(){
		$primary = $this->getPrimary();
		if(!empty($primary)):
			return $this->update();
		else:
			return $this->saveNew();
		endif;
	}
	
	public function saveNew(){
		global $pdo;
		$sql = "insert into {$this->table} ";
		$fields = $placeholders = $values = array();
		foreach($this->fields as $field=>$default){
			$fields[] = $field;
			$placeholders[] = '?';
			$values[] = $this->$field;
		}
		$sql .= '(' . implode(',',$fields).') VALUES (' . implode(',', $placeholders) . ')';
		try{
			$s = $pdo->prepare($sql);
			$s->execute($values);
			$id = $pdo->lastInsertId();
			$this->setPrimary($id);
			return true;
		} catch (PDOException $e){
			print_r($e);
			return false;
		}
	}
	
	public function update(){
		global $pdo;
		$sql = "update {$this->table} set ";
		$fields = $placeholders = $values = array();
		foreach($this->fields as $field=>$default):
			$fields[] = "$field = ?";
			$values[] = $this->$field;
		endforeach;
		$sql .= implode(',',$fields) . " where {$this->primary} = ?";
		$values[] = $this->getPrimary();
		try{
			$s = $pdo->prepare($sql);
			$s->execute($values);
			return true;
		} catch (PDOException $e){
			print_r($e);
			return false;
		}
	}

	public function load($data){
		if(is_object($data)) $data =get_object_vars($data);
		foreach($this->fields as $field=>$val):
			if(isset($data[$field])):
				$this->$field = $data[$field];
			endif;
		endforeach;
	}
	
	public function loadFromPrimary($id){
		global $pdo;
		$sql= "Select * from {$this->table} where {$this->primary}=?";
		try{
			$s = $pdo->prepare($sql);
			$s->execute(array($id));
			$row = $s->fetchObject();
			if($row) $this->load($row);
		} catch (PDOException $e){
			print_r($e);
		}
	}
	public function loadFromID($id){
		return $this->loadFromPrimary($id);
	}
	public function loadByID($id){
		return $this->loadFromPrimary($id);
	}
	
	public function getPrimary(){
		if(isset($this->{$this->primary})):
			return $this->{$this->primary};
		else:
			return null;
		endif;
	}
	
	public function setPrimary($val){
		$this->{$this->primary} = $val;
	}
	public function delete(){
		global $pdo;
		$sql = "delete from {$this->table} where {$this->primary}=?";
		$params = array($this->getPrimary());
		try{
			$s = $pdo->prepare($sql);
			$s->execute($params);
		} catch (PDOException $e) {
			print_r($e);
		}
	}
	
	public function bail($messages){
		echo '<pre>';
		print_r($messages);
		echo '</pre>';
		die;
	}
}


class language extends base {
	
	public $fields = array('languageID','language');
	public $primary = 'languageID';
	public $table = 'languages';
	
	public function __construct(){
		parent::__construct();
	}
	
	public function save(){
		$this->language = ucwords(strtolower($this->language));
		return parent::save();
	}
	
	public function loadFromLanguage($language){
		global $pdo;
		$sql = <<<SQL
SELECT		*
FROM		{$this->table}
WHERE		language LIKE ?
SQL;
		$s = $pdo->prepare($sql);
		if($s === false) $this->bail($pdo->errorInfo());
		$r = $s->execute (array(trim($language)));
		if($r === false) $this->bail($s->errorInfo());
		$this->load ($s->fetch(PDO::PDO_FETCH_ASSOC));
	}
}

class term extends base {
	
	public $fields = array('termID', 'term', 'recID','context','definition','annotation');
	public $primary = 'termID';
	public $table = 'terms';
	
	public function __construct(){
		parent::__construct();
	}
	
	public function addTranslation( $language, $translation){
		$language = new language;
		if(is_numeric($language)):
			$language->loadFromID($language);
			if(empty($language->language)):
				$language->language = $language;
				$language->save();
				$languageID = $language->getPrimary();
			else:
				$languageID = $language;
			endif;
		else:
			$language = new language;
			$language->loadFromLanguage($language);
			if($language->getPrimary()):
				$languageID =  $language->getPrimary();
			else:
				$language->language = $language;
				$language->save();
				$languageID = $language->getPrimary();
			endif;
		endif;
		$translation = new translation;
		return $translation->add($this->getPrimary(), $languageID, $translation);
	}
	
	public function translate ($term, $language){
		if(is_numeric($term)):
			$this->loadFromID($term);
		else:
			$this->loadFromTerm($term);
		endif;
		$translation = new translation;
		return $translation->get($this->getPrimary(), $language);
	}
	
	public function loadFromTerm($term){
		global $pdo;
		$sql = <<<SQL
SELECT 		*
FROM 		{$this->table}
WHERE	 	term LIKE ?
SQL;
		$s = $pdo->prepare($sql);
		if($s === false) $this->bail($pdo->errorInfo());
		$r = $s->execute (array(trim($term)));
		if($r === false) $this->bail($s->errorInfo());
		$this->load ($s->fetch(PDO::PDO_FETCH_ASSOC));
	}
	
}

class translation extends base {
	
	public $fields = array('termID', 'languageID', 'translation');
	public $table = 'translations';
	
	public function __construct(){
		parent::__construct();
	}
	
	public function save(){
		$sql = <<<SQL
REPLACE INTO	{$this->table}
(termID, langaugeID, translation)
VALUES
(?,?,?)
WHERE	termID = ?
AND 	languageID = ?
SQL;
		$params = array($this->termID, $this->languageID, $this->translation, $this->termID, $this->languageID);
		$s = $pdo->prepare($sql);
		if($s === false) $this->bail($pdo->errorInfo());
		$r = $s->execute ( $params );
		if($r === false) $this->bail($s->errorInfo());
		return $true;
	}
	public function add($termID, $languageID, $translation){
		$this->termID = $termID;
		$this->languageID = $languageID;
		$this->translation = $translation;
		return $this->save();
	}
	public function get($term, $language){
		if(is_numeric($language)):
			$sql = <<<SQL
SELECT 		*
FROM		{$this->table}
WHERE		termID = ?
			AND
			languageID = ?
SQL;
		else:
			$sql = <<<SQL
SELECT 		a.*
FROM		{$this->table} a
JOIN		languages b
ON			b.languageID = a.languageID
WHERE		a.termID = ?
			AND
			b.language LIKE ?
SQL;
		endif;
		$params = array($term, trim($language));
		$s = $pdo->prepare($sql);
		if($s === false) $this->bail($pdo->errorInfo());
		$r = $s->execute ( $params );
		if($r === false) $this->bail($s->errorInfo());
		$this->load($s->fetch(PDO::FETCH_ASSOC));
		return empty($this->translation) ? false : $this->translation;
	}
}
?>