Reading MS Access db

Printable View

02/17/2011, 09:00
Ruud

Reading MS Access db

For a project at work I am building an intranet site using Contao. I need to get data from an existing MS Access database which is important for me to display inside the website. Right now I am just in a planning/orientation stage and I'd like to figure out how to best approach the problem. Just to be clear; the Contao db is MySQL, I need to get external data from the Access db.

I see in the drivers folder that there is no existing driver. Should I write one, or are they only intended for use by the Contao website itself?

Has anyone done this already?

ps, Would adding an Access driver not allow Contao to be run on servers without a database? It might be an extra feature to think about? Scratch that, I did some research. Still wanting an answer to the actual question though...
02/17/2011, 15:36
Tru

Re: Reading MS Access db

It would be great if you could write a new driver (I think that in early TYPOlight releases there was more drivers). I just wanted you to share a piece of code that I used in my recent project, to swap between 2 different mysql databases.

[code=php:2h2v5r16]
/**
***External*database*details
***@var*array
**/
protected*$arrDatabase*=*array
(
****'host'*****=>*'',
****'user'*****=>*'',
****'password'*=>*'',
****'database'*=>*'',
****'port'*****=>*''
);

/**
***Change*database*to*external
***@param*boolean
**/
public*function*changeDatabase($blnReverse*=*false)
{
****if*($blnReverse)
****{****
********//*Load*default*configuration
********include(TL_ROOT*.*'/system/config/localconfig.php');
****
********$this->Database->setDatabase($GLOBALS['TL_CONFIG']['dbDatabase']);
********
********return;
****}
****
****$GLOBALS['TL_CONFIG']['dbHost']*=*$this->arrDatabase['host'];****
****$GLOBALS['TL_CONFIG']['dbUser']*=*$this->arrDatabase['user'];
****$GLOBALS['TL_CONFIG']['dbPass']*=*$this->arrDatabase['password'];
****$GLOBALS['TL_CONFIG']['dbPort']*=*$this->arrDatabase['port'];

****$this->Database->setDatabase($this->arrDatabase['database']);
}
*[/code:2h2v5r16]

And in code you use it like this
[code=php:2h2v5r16]
//*Change*database*to*external
$this->changeDatabase();

//*Do*some*stuff
$objAsd*=*$this->Database->execute("...");

//*Change*database*back*to*Contao
$this->changeDatabase(true);
*[/code:2h2v5r16]
You could also improve this function e.g. by recognizing current database like
[code=php:2h2v5r16]
if*($this->Database->tableExists('tl_page'))
{
***//*swap...
}
*[/code:2h2v5r16]
and then no parameter is needed.
02/17/2011, 15:48
thyon

Re: Reading MS Access db

A great way is to write a XML generator (or JSON) on the remote server to query your database and return it as XML/JSON over http. Then there is a function in PHP which simply converts that into a PHP array, allowing you to parse it easily. This is particularly helpful in your case, when there is multiple types of technologies, and if the SQL doesn't work either. You can even make the query have a parameter, block it from using only your IP or domainname, etc.
02/17/2011, 17:19
Ruud

Re: Reading MS Access db

Thyon, the file is on the same physical server, I can access it directly if needed. I would prefer that rather then calling another process, then converting the results.

Tru, using that code the two databases should be using the same driver, right? Could I not add a second db object and get an instance of that instead?
02/17/2011, 20:51
Tru

Re: Reading MS Access db

In addition to my code, here is corrected version:
[code=php:2zkexirl]
/**
***Change*database*to*external
***@param*boolean
**/
public*function*changeData base($blnReverse*=*false)
{
****if*($blnReverse)
****{****
********//*Load*default*configuration
********include(TL_ROOT*.*'/system/config/localconfig.php');

********$this->Database->execute("SET*NAMES*'utf8'");****
********$this->Database->setDatabase($GLOBALS['TL_CONFIG']['dbDatabase']);
********
********return;
****}
****
****$GLOBALS['TL_CONFIG']['dbHost']*=*$this->arrDatabase['host'];****
****$GLOBALS['TL_CONFIG']['dbUser']*=*$this->arrDatabase['user'];
****$GLOBALS['TL_CONFIG']['dbPass']*=*$this->arrDatabase['password'];
****$GLOBALS['TL_CONFIG']['dbPort']*=*$this->arrDatabase['port'];

****$this->Database->setDatabase($this->arrDatabase['database']);
****$this->Database->execute("SET*NAMES*'utf8'");
}
*[/code:2zkexirl]
That awful hack is because set_database() creates a new mysql/mysqli object but does not set the charset... Either Leo missed this or it is another "necessary feature"...

@Ruud
Well I think it is possible to have two drivers, since Databse library is a Factory pattern. I did not investigate the code enough to be sure, though.
And yes, that code is used for the same drivers.

## EDIT
Here is improved version of above function http://pstbn.com/felQzb
If you leave empty values in array, then default (localconfig.php) will be used.
02/18/2011, 10:33
Vera

Re: Reading MS Access db

I tried looking up older versions of Contao and the oldest I could find was 2.6. It does not include drivers for Access. So I probably need to figure out how to make a proper connection, then build the driver. But again, why would I use a switching function? What would be the advantage?
02/18/2011, 10:45
taca

Re: Reading MS Access db

Quote:

Originally Posted by Vera

I tried looking up older versions of Contao and the oldest I could find was 2.6. It does not include drivers for Access.

2.9.3 also dosen't include drivers for Access but MS SQL.
02/18/2011, 11:23
Vera

Re: Reading MS Access db

MS Sql is a completely different database altogether, isn't it?

ps, I forgot to mention I work together on this one with Ruud... :P
02/18/2011, 15:06
Tru

Re: Reading MS Access db

Quote:

Originally Posted by Vera

But again, why would I use a switching function? What would be the advantage?

How would you change the database other way?

Quote:

Originally Posted by Ruud

Just to be clear; the Contao db is MySQL, I need to get external data from the Access db.
02/18/2011, 20:47
Ruud

Re: Reading MS Access db

Am I right when I assume you just change the database in such a way that if you wouldn't change it back the entire system would be using the wrong db? In that case that solution is not what I want, it seems like a hack. Can I not add a database object to whatever global place Contao uses for it's normal database object instance? Then I can just call that new instance whenever I need it. I guess that's the way to go, right?
02/18/2011, 21:18
Tru

Re: Reading MS Access db

Hi Ruud

Please check the link from my previous post http://pstbn.com/felQzb. There is also a usage example at the bottom of it.

I don't think that creating another database instance is possible and simple, but if you manage to do that - share with me :) I used that code and worked for me very well. What you gonna do - your choice of course.
02/19/2011, 09:36
ga.n

Re: Reading MS Access db

Quote:

Originally Posted by Vera

I tried looking up older versions of Contao and the oldest I could find was 2.6. It does not include drivers for Access. So I probably need to figure out how to make a proper connection, then build the driver. But again, why would I use a switching function? What would be the advantage?

AFAIK the only way to connect to a MS Access database from php is using ODBC

You must setup a ODBC connection on your windows installation (define it as system and not as user)

Then you can write a ODBC driver that uses the odbc_* functions http://it.php.net/odbc
02/19/2011, 16:46
Ruud

Re: Reading MS Access db

The access db is on a linux/apache server. So I'd need to see if an equivalent is available...
02/19/2011, 17:09
ga.n

Re: Reading MS Access db

Quote:

Originally Posted by Ruud

The access db is on a linux/apache server. So I'd need to see if an equivalent is available...

If this is a legacy database (that will not be updated any more) I suggest you to migrate the data to mysql

I really do not like the idea of a ms access database on a linux server :o
02/19/2011, 17:37
Ruud

Re: Reading MS Access db

I am, in fact, able to change the db to a MSSQL db. But we made that switch ones before and that gave us more trouble then it was worth. The software is kind of lame... Changing to MSSQL is something I'd have to discuss and will take longer then using an Access method if it exists for Linux/Apache.