this combo: php on a windows2003 + ms access on a samba share

sugar2 · 05-20-2006, 01:48 PM

hi, i didnt know where exactly to post this question:
This is the scneario, i have a unix samba share in my network, there are all the office stuff, including a share to a Ms access database wich its accessed from many windows clients.
Now i need to access that ms access database from http, so i installed apache+php in a windows server in the same workgroup, i did succesfully test when the ms access database was on the local windows server, but once i tried with the database in the actual server it didnt worked, i found few scripts in google about odbc, ole, and dsn-less connections, all of them worked when database resides in the local machine, but don work when database is in the actual unix samba share.

This is the script used with local machine test:

//$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("\\\\POS-SERVER\C$\om\xxafta.mdb") ." ;DefaultDir=". realpath("\\\\POS-SERVER\C$\om");

this is the script used for samba share, it didnt work:
//$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("\\\\nas1\workware\OrderManager\xxafta.mdb") ." ;DefaultDir=". realpath("\\\\nas1\workware\OrderManager");

What can I do? any adcvices will be apreciated/.

robbbert · 05-21-2006, 09:32 AM

Unfortunately, I don't have any experiences with Samba shares but - before we start guessing in the wild:

What's the literal error message and number?

Some guesses though:
The error occurs on "connection.open()" or an equivalent I guess?
Does the user your web is running under have write permissions to the directory the MDB is in?

sugar2 · 05-22-2006, 02:31 PM

Quote:

Originally Posted by robbbert

Unfortunately, I don't have any experiences with Samba shares but - before we start guessing in the wild:

What's the literal error message and number?

Some guesses though:
The error occurs on "connection.open()" or an equivalent I guess?
Does the user your web is running under have write permissions to the directory the MDB is in?

well am getting no error codes, the php page is just displaying a blank page, it takes a bit to load the blank page, so i think it loads something but it isnt rendering, weirdo.

about the write permissions for the web user... you mean the computer wich is running the web as a client or as a server?

robbbert · 05-22-2006, 03:55 PM

Quote:

well am getting no error codes, the php page is just displaying a blank page

So there's "On Error Resume Next" error handling? - Remove that! - You'll definitely need to get the literal error message and number; in programming there is no other way of debugging.

Quote:

about the write permissions for the web user... you mean the computer wich is running the web as a client or as a server?

That's just a quick guess. Guessing is not debugging, so we need to catch the actual error, at any rate. - Nevertheless, an explanation of my thoughts: Using IIS, each website or Virtual Directory runs either in the identiy of (= is impersonificated by) the default "anonymous user" (IUSR_COMPUTERNAME) or of a Windows identity that has specifically been set. Moreover, any Windows process, like Apache itself, or one of Apache's sub-processes (like a web) runs under / is impersonified by a Windows identity. I'm not sure how this is set up for Apache though.

HTH

sugar2 · 05-22-2006, 05:27 PM

This is the full code i am using... there are no "On Error Resume Next"... how can I turn on the error handling?

PHP Code:



<?php

$db_connection = new COM("ADODB.Connection");

//$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("\\\\10.10.10.10\\om\xxafta.mdb") ." ;DefaultDir=". realpath(".");

$db_connection->open($db_connstr);
$rs = $db_connection->execute("SELECT LocalSKU, ItemName, QOH, Price FROM Inventory WHERE Price < 1");
$rs_fld0 = $rs->Fields(0);
$rs_fld1 = $rs->Fields(1);
while (!$rs->EOF) {

/* do some stuff with the results to make it look nice on screen */
/* but as an example... */

  print "$rs_fld0->value $rs_fld1->value\n";

  $rs->MoveNext(); /* updates fields! */

  print "<br>";

}
$rs->Close();
$db_connection->Close(); 

?>

sugar2 · 05-22-2006, 06:22 PM

The thing was solved. I debbuged using the realpath php function...

I changed the user that apache uses to logon, to a samba authorized user acconunt, not the system account, and with this string:

PHP Code:



$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("\\\\nas1\\workware\\OrderManager\\xxafta.mdb") ." ;DefaultDir=". realpath("\\\\nas1\\workware\\OrderManager");

thanks to all!!!

robbbert · 05-23-2006, 11:32 AM

Glad it's solved.

Strange though that Samba didn't throw an error...

sugar2 · 05-23-2006, 11:41 AM

now, i think i can do any conventional type of databaser connection... what should i use for faster response and performance?
ODBC? OLE? DSN-LESS?... or may be linking the access databse to the SQL Server i have installed on the web server?, what I really wish its to have a faster querying of the MS access database without compromising the performance of Our SQL Server database application.

There are 2 machines involved:

Mahcine1:
Dual Core Unix Server
Samba Server
MS Access Database

Machine1:
Dual Core Windows Server
Sql Server
Apache and PHP Server

Thanks in advance.

ALDO

robbbert · 05-23-2006, 01:24 PM

Does your database connection feel slow?

In my experience, a db connection to Access does not necessarily need to be significantly slower than SQL Server. (No, I'm not an Access fan.) Nevertheless, Access doesn't scale well (1. simultanious queries, 2. file size). Of course, there are no query optimizations as with SQL Server.
What's most important is setting the indexes right, and Access is suitable for that.

OLEDB and ADODB both are additional layers over ODBC. You'll need such a layer as it is your db connection object for programming. I'm not sure how much your way of using ADODB with PHP costs itself. You could do a research for a more native PHP layer to ODBC.

As for using a system DSN vs. a DSN-less connection (yours is DSN-less), I think I remember DSN-less benchmarked better in several aspects.

AFAIK, linked servers from within SQL Server are not faster than their native datasources (the "link", in case of Access, is done via OLEDB), i.e., there are no SQL Server query optimizations. There's a performance gain if you JOIN SQL Server and the linked server's tables as well as when using a T-SQL cursor to loop the linked server's tables - in opposite to using an ADODB.Recordset, like you're doing.

Remember, an ADODB.Recordset contains meta, and more, information than just the recordset's values. I.e., it contains information about the field size, type, etc. Anyways (I'm talking too much), this only becomes significant when you're doing bulk operations on ten or hundred thousands of records. For outputting a few dozends or hundreds of records on some client application, ADODB is just great.

As for the PHP/ADODB interaction, I tend to think, COM interoperability has become a mature feature within PHP in the meantime. So I wouldn't expect significant performance decreases from there.

What I don't know about is how Samba integrates. You could check this by manually opening the original Access database, and fire a harder query on it, and doing the same with a local copy of the database.

After all, I really need to ask: Are there performance issues, actually? If so, how many queries are running simultaniously (on the same tables)? Are there Updates simultaniously with Selects? And, how much is the size of the DB?