asked on

ODBC and Access with a PHP script not working

We have tried to use ODBC to get data from an ACCESS database in a PHP page. This works fine on my local system (XP PRO, IIS5, PHP 5.3.6 installed). When I try and run the same page on a web server (PHP 5.2.14) nothing happens ... not even any error messages, which have been turned on and tested :) The php page returns echos and other bits of PHP code into HTML but not the stuff from the database! we have tried it with a DSNless connection and a DNS ... same result with both ... nothing.

We are perplexed. Could this possibly be a version difference problem OR not? Any ideas.

Here is the ODBC code to get the data. This works a treat locally.

<?php
function dbquery($sql) {
//optional SQL_CUR_USE_ODBC param in connection string allows ODBC to read Memo Fields.
$conn = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=" . realpath('customer.mdb'), "","", SQL_CUR_USE_ODBC);
// $conn=odbc_connect('	myDatabaseName','','');
$rs = odbc_exec($conn,$sql);
return $rs;
}

$query = "SELECT * FROM PageContent WHERE page_ID = 3";

$rs = dbquery($query);

//format return html
while(odbc_fetch_row($rs)) {
	//read fields into variables
	$showsection = odbc_result($rs, 'show_section');
	$header = odbc_result($rs, 'header');
	$showheader = odbc_result($rs, 'show_header');
	$subheader = odbc_result($rs, 'subheader');
	$showsubheader = odbc_result($rs, 'show_subheader');
	$content = odbc_result($rs, 'content');
	$showcontent = odbc_result($rs, 'show_content');
	
	//format return listview
	echo "<ul data-role=\"listview\">";
	if($showsection == TRUE) {
		echo "<li style=\"white-space: normal\">";
		if($showheader == TRUE){
			echo "<h1>" . $header . "</h1>";
		}
		if($showsubheader == TRUE){
			echo "<h3>" . $subheader . "</h3>";
		}
		if($showcontent == TRUE) {
			echo $content;
		}
		echo "</li>";
	}
	echo "</ul>";
}
?>

Julian Hansen

Is the webserver windows based - does it have the PHP ODBC driver installed?

Laride

ASKER

Yes. It is a windows 2003 server with ODBC installed. It's actually version 5.2.17

odbc
ODBC Support      enabled
Active Persistent Links       0
Active Links       0
ODBC library       Win32

Directive      Local Value      Master Value
odbc.allow_persistent      On      On
odbc.check_persistent      On      On
odbc.default_cursortype      Static cursor      Static cursor
odbc.default_db      no value      no value
odbc.default_pw      no value      no value
odbc.default_user      no value      no value
odbc.defaultbinmode      return as is      return as is
odbc.defaultlrl      return up to 4096 bytes      return up to 4096 bytes
odbc.max_links      Unlimited      Unlimited
odbc.max_persistent      Unlimited      Unlimited

Julian Hansen

Are you sure the db connect is working - is it finding and connecting to the database?

Dave Baldwin

Where is the database located when it fails? Many hosting servers have error reporting turned off so you may need to turn it back on for your code. Put this at the top of your code.

// Same as error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);

Open in new window

Dave Baldwin

For what it's worth, I have never been able to get the Access or Jet driver to connect remotely. The problem seems to be that unlike SQL server, an Access *.mdb is just a file and not a server.

Julian Hansen

With an access database the file has to be visible from the file system of the server the site is running on.

Laride

ASKER

Thanks for the comments so far. the ACCESS database is sitting in the same folder as the web site. Error reporting is on ( I have the override code installed and have tested it .. it does report errors). We don't know whether the connect is working or not as no errors show up. I put an echo command before and after the database stuff and these both work fine ... but no errors.

Julian Hansen

Have you tried connecting to the database through another application using the ODBC datasource - like Excel for example?

Laride

ASKER

No. It works 100% fine on my local XP box under IIS5. Do you think it could be anything to do with the version of PHP being an older version?

Julian Hansen

Well the thing is to try and break it down - eliminate what you know works which is why I asked if you can connect to the db through the ODBC connection using another program - if that does not work the problem is not PHP - you need to look elsewhere - if it does work it says the ODBC connection and database are working correctly.

Dave Baldwin

Have set the permissions of the 'mdb' file to read and write? The Access driver won't work unless it is.

Laride

ASKER

Look. It all works perfectly well on my box in my office .... it just won't work on the web.

Julian Hansen

@Laride - but that is exactly what we are trying to assist you with in finding out. It works on your box but not the web so something is different - the question is what. To find that we have to go through it step by step and see what works and what does not.

As things stand we still don't know if ODBC connection on that box is functioning correctly. That is the first step.

Dave Baldwin

I'll ask again, are the permissions on the 'mdb' set to allow reading And Writing? The Access driver tries to open the file in read/write mode and fails if it can't do it. Every time I add an Access 'mdb' to the IIS server on this computer, I have to change permissions to allow writing or it doesn't work.

Laride

ASKER

Yes they are. I have written a replacement ASP script and this does what the php script should do.

Dave Baldwin

What is the web server and operating system? What hosting company?

Laride

ASKER

My local system (XP PRO, IIS5, PHP 5.3.6 installed). When I try and run the same page on a web server (PHP 5.2.14) nothing happens ... not even any error messages, which have been turned on and tested :) The php page returns echos and other bits of PHP code into HTML but not the stuff from the database! we have tried it with a DSNless connection and a DNS ... same result with both ... nothing. I would rather not mention the host as it's not fair on them.

Dave Baldwin

A lot of hosts have been mentioned by name here because they have different policies and procedures and we share that with each other so we know whether we want to use their services. On many hosts that offer Windows servers and support Access, you have to put the 'mdb' in a restricted directory and use the DSN they set up to get to it. That is because they will not allow read/write access in the standard web directories. If you are using one of those hosts, it would not work in PHP or ASP.

Ray Paseur

This is very old code, but I think it is still in use and working correctly. HTH, ~Ray

//
// DATA BASE CONNECTION REQUIRED
//
$dbq = str_replace("/", "\\", $_SERVER["DOCUMENT_ROOT"]);
$dbq .= "\\Reports\\Data\\PM2KExport.mdb";

if (!file_exists($dbq)) 
{ 
    echo "<br />NO SUCH FILE EXISTS $dbq"; 
    die(); 
}

$db_connection = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbq", "ADODB.Connection", "PASSWORD", "SQL_CUR_USE_ODBC");
echo "<!-- db_connection:$db_connection -->\n";

//
// ESTABLISH LOWEST AND HIGHEST YEAR IN DB
//
$sql = "SELECT TOP 1 * FROM LineItems ORDER BY ItemDate ASC ";
if (!$qrs = query($sql)) 
{ 
    echo "<br />Zut! No ItemDate "; 
    die(); 
}


function query($sql) 
{
    global $db_connection;
    
    if (!$result = odbc_exec($db_connection, $sql)) 
    { 
        echo odbc_errormsg($db_connection); 
        die(" SQL FAILED"); 
    }

    $kount = 1;
    while (odbc_fetch_row($result)) 
    {
        $row = odbc_fetch_array( $result, $kount );
        $return_array[] = $row;
        $kount++;
    }

    if (!is_array($return_array)) return false; 

    return ($return_array);
}

Open in new window

Steve Bink

Just two cents...

The problem here is that you're trying to use Access as a database platform. Access is a wonderful little application for *small, local database-like files*. Access as an actual db server or platform has never gone over well, and is remarkably difficult and inconsistent in the best of circumstances. I have never trusted Access as a platform for web-facing databases.

You are better off porting the database over to a real platform, such as MSSQL or MySQL. As it happens, MySQL is free, works on Windows or Linux, and seamlessly integrates with PHP. Since it uses standard SQL (plus some extensions), taking tables from Access and re-creating them in MySQL will be a breeze. There is a small amount of work during the conversion, in exchange for truckloads of benefits once it is done.

Olaf Doschke

You repeat saying you have already tested anything and get no errors.
But your code does not even test, if the connection is successful. You do:

$conn = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=" . realpath('customer.mdb'), "","", SQL_CUR_USE_ODBC);

And in the next step you do:
$rs = odbc_exec($conn,$sql);

There are circumstances this will neither make a valid connection nor error.
In the first place odbc_connect returns 0 if no connection is made, and doesn't error. odbc_exec($conn,$sql) also triggers no php error, it simply returns FALSE.

And all that would just be a follow up error of not checking the $conn to be >0.
After odbc_connect() and odbc_exec() you have got to actively check for odbc_error() and odbc_errormessge() to see if that returns any error infos.

So do that, also echo $conn, echo realpath('cusomter.mdb'),find out if that web serer has the Microsoft Access Driver installed at all, etc.

Eg also take a look into phpinfo() about odbc. Also as others have said some hosters allow Access but don't allow an mdb to be within webroot, a mysql database or other databases also are not within that dir, as it would be insecure for data, the sheer databae files, to be in webroot and therefore accessible/downloadable from the web, it should just be accessable from within the web server.

And that also makes Access a viable database for your web pages. As you access the data from a web serer you do local access, and that's also fine with an mdb. It has other problems and disadvatages, though, eg it runs local, but still that is done in parallel in webserver threads from concurrent web users. It would b wise to follow the recommendation to move to mysql or another sql server.

Bye, Olaf.

Rob

Adding my 2c as well as I can see by configuring the code Ray has provided with your database path will tell you if something is not working. It sounds like your code doesn't have steps to catch potential errors. This is very important when trying to determine where an issue is happening and would help you step through the problem.

ASKER CERTIFIED SOLUTION

Laride

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Olaf Doschke

It reports some errors but not others!

Again, it's normal odbc_connect() or odbc_exec() raise no php errors. For the same reason you have to actively retreive mysql_error(), PDO::errorInfo you have to actively retrieve odbc_error(). It's not php erroring, it's the external component, mysql, pdo, odbc, which errors.

You just have the wrong concept, that all errors happening in php or the components it uses raise php exceptions and go into error logging. In case external components or extensions error, they report errors you can retreive, but you have to do that, there is no event triggered.

Bye, Olaf.

Rob

Glad you got it fixed

Laride

ASKER

Problem resolved by moving to a 2003 server where PHP was correctly set up.