How to Connect Foxpro(with Remote System using system DSN) in php

Hi Experts,

I want to connect foxpro database with php. I have a code to connect locally.

The code is below.  (Using ODBC - with Local system)
-----------------------------------------------------------------------
$conn = odbc_connect("Bestlocal", "", "") or die ("Error: could not connect to database");;

if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM glbals212.dbf";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<table border='1'><tr>";
echo "<th>Companyname</th>";
echo "<th>Contactname</th></tr>";
while(odbc_fetch_array($rs))
  {
  $compname=odbc_result($rs,"Glacnt");
  $conname=odbc_result($rs,"Yearprd");
  echo "<tr><td>$compname</td>";
  echo "<td>$conname</td></tr>";
  }
odbc_close($conn);
echo "</table>";
?>
-----------------------------------------------------------------------
In the abouve code "Bestlocal" is my System DSN for my local foxpro database

And i have tried with OLEDB (Locally) Its also working properly.  The OLEDB code is follows

-----------------------------------------------------------------------
<?php

$conn = new COM("ADODB.Connection");
$conn->Open("Provider=VFPOLEDB.1;Data Source=e:\DATA212;Collating Sequence=Machine;");
// SQL statement to build recordset.
$rs = $conn->Execute("SELECT * FROM glbals212.dbf");
echo "<p>List</p><hr>";
// Display all the values in the records set
while (!$rs->EOF) {
    $fv = $rs->Fields("Glacnt");
    echo $fv->value."<br>\n";
    $rs->MoveNext();
}
$rs->Close();
?>

-----------------------------------------------------------------------


These two snippets are working fine in local. But i want to connect foxpro database which is in my network.

when i am trying to access remote db using system DSN am getting the following error

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Visual FoxPro Driver]File 'glbals212.dbf' does not exist., SQL state S0002 in SQLExecDirect in C:\xampp\htdocs\odbc.php on line 13
Error in SQL

Thanks in Advance...

Cheers...

LVL 7
techsathishAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
The system DSN must be configured on the server where PHP code is executed and the remote FoxPro database must be on some mapped drive. You could also try UNC path in DSN definition. Disadvantages of this topology are clear - VFP ODBC driver must read all necessary DBF data over the network before executing SQL command. It can be optimized by properly defined indexes but the connection should not be slow.
0
techsathishAuthor Commented:
Hi pcelba,

Thank u for ur quick reply.

I have configured the DSN in the same server.  Please see the attached image.

What is UNC path how and where can i configure.

I have mapped the drive
System-DSN-Config.JPG
0
pcelbaCommented:
UNC (Uniform Namig Convention) path specifies a common syntax to describe the location of a network resource. It consists of \\ComputerName\SharedFolderName  and you may see it e.g. in your mapped drive definition. More info is e.g. here: http://en.wikipedia.org/wiki/Path_(computing)

The only advantage is you don't need to map the remote drive. I am just not sure if VFP ODBC driver supports it (my guess is Yes).
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

pcelbaCommented:
Simple try to enter ODBC driver Path as: \\Sakthivel\DATA\... etc. (I don't see full UNC path on above picture) and you'll see.
0
techsathishAuthor Commented:
Hi,

This is my connection string

$conn=odbc_connect('Provider=VFPOLEDB.1;Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=\\sakthivel\DATA212;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;','','');

And also tried with \\\\sakthivel\DATA212. But it wont works.
0
pcelbaCommented:
Does it work with mapped drive?

If backslashes must be duplicated then you should try \\\\sakthivel\\DATA212

And, of course, appropriate access rights for user executing PHP code must exist on mapped/remote drive.
0
techsathishAuthor Commented:
Hi,

Its not working. i tried with \\\\sakthivel\\DATA212 this also.

Mapped drive having full access. Still not working.


See the attached image.
Access-Permission-for-Map-Drive.JPG
0
pcelbaCommented:
Your connection string contains unsupported keywords, try following:

$conn=odbc_connect('Provider=VFPOLEDB.1;Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;Data Source=\\sakthivel\DATA212;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted Records=Yes;','','');

and then try to duplicate backslashes.
0
pcelbaCommented:
BTW, are you trying odbc_connect() via VFP OLE DB provider...  You should use above string via ADODB connection.
0
pcelbaCommented:
Hmm, tested both VFP OLE DB and ODBC connection using UNC path and it works here...

The Provider=VFPOLEDB.1  used in ODBC connection does not affect it...

Are you able to test the connection from some other application? Access, FoxPro, ...
0
Olaf DoschkeSoftware DeveloperCommented:
techsathish,

people mostly forget, that it's not the admin user or themseleves needing access rights to the database files, but the system account of the webserver. And typically both with IIS or apache and other webservers, there access is limited to local drives only and it's best to have data on the webserver or you need to run the webserver with a domain account, which has file access rights to a remote mapped or unmapped file share.

Bye, Olaf.
0
techsathishAuthor Commented:
Hi pcelba,

Am getting the following error message again

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC Visual FoxPro Driver]Provider, SQL state 01S00 in SQLConnect in C:\xampp\htdocs\odbc.php on line 7
Connection Failed:
0
techsathishAuthor Commented:
Hi Olaf,

Thanks for ur reply.

In that two systems having admin privilege.

If we move files to web server means how can we mapping the drive. Can u explain clear.

cheers.
0
pcelbaCommented:
You should simplify the problem.

If the following code works on local computer:

$conn = new COM("ADODB.Connection");
$conn->Open("Provider=VFPOLEDB.1;Data Source=e:\DATA212;Collating Sequence=Machine;");

then simple change for mapped remote drive must work also...

The only problems could be 1) Access rights, 2) Firewall.

Did you try to connect data from some other application?
0
techsathishAuthor Commented:
Hi pcelba,

I tried wit that also. i thing it may be access rights problem. But i fully checked the access control. i gave full control for everyone. But still the problem...
0
Olaf DoschkeSoftware DeveloperCommented:
How do you setup full control for everyone? The webserver process typically runs as a sytem account, and even if you allow access to dbf files for the system account 'EVERYONE', this excludes system accounts like 'SYSTEM' or an 'IUSR...' IIS account. Also, if dbfs are on another server than the webserver, you need to run the webserver with an account, that can access the network, which is also not allowed by default for system accounts, you need a domain account then.

That's why moving the dbfs to the server serving the web application is a simpler solution, because then you don't need to map any drive, then the data is local from the point of view of the webservice, then you're having a local path to the data and the windows account running the webservice will have access because no LAN limits prohibt access, if you specifically grant rights to the right account. Look into services at the apache or iis service to see which account needs access rights and grnt rights to this account, not to ALL or EVERYONE, that insufficient.

Bye, Olaf.
0
techsathishAuthor Commented:
Hi Olaf,

Thanks for your detail description.

The main thing is that the foxpro database is on the another system. We cant copy that(For Some security reason).
If we moved into web server means we cant access the db.

0
Olaf DoschkeSoftware DeveloperCommented:
Well, now you've got the sme problem the other way around. So if not being allowed to move the database to the webserver is your problem, because users will then need but shouldn't get access to a drive or folder in the webserver, then you need to let the webserver run as a domain account having access to the fileserver with the foxpro data, which I would consider the worse security issue than allowing users on a webserver share. Or you need to replicate the data.

Bye, Olaf.
0
manomaniCommented:
Hi.,

First Please create System dsn with remote system db path

after that.,

use this connectionstring

Provider=VFPOLEDB.1;DSN=*********(your DSN);Mode=ReadWrite;Password="";Collating Sequence=MACHINE;

Hope may solve your problem.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
techsathishAuthor Commented:
Hi manomani,

Is this for ODBC or OLEDB
0
manomaniCommented:
Hi techsathish.,

This will Work 100% in oledb ,  some slighter modification results positive in odbc also.....
0
manomaniCommented:
Hi.,

Also Dont want to create a mapped drive....... just use the dsn name ......
0
techsathishAuthor Commented:
Hurrayyyyy.....

Finally i got it to working... Thanks manomani...

Its working fine with OLEDB but its not working with the ODBC.

I tried with the Remote system to remote system. But its not working locally.

But i need a solution for remote system only... Finally i got success...

Thanks again manomani and who r all participated with discussion....
 
See the attached working code with OLEDB.



<?php

ini_set("display_errors","on");
$conn = new COM("ADODB.Connection");

   try {
      $conn->Open('Provider=VFPOLEDB.1;DSN=ArrowWeb;Mode=ReadWrite;Password="";Collating Sequence=MACHINE;');
         if (! $conn) {
            throw new Exception("Could not connect!");
        }
   }
   catch (Exception $e) {
      echo "Error (File:): ".$e->getMessage()."<br>";
   }

if (!$conn)
  {exit("Connection Failed: " . $conn);}
echo "Connection Sucessfull";


// SQL statement to build recordset.
$rs = $conn->Execute("SELECT invno,invdte,custno,salesmn FROM ARMAST101 WHERE custno = 'DIAM02'");
echo "<hr>";

// Display all the values in the records set
echo "<table border='1' cellpadding='10' cellspacing='0'><tr><th>Invoice Number</th><th>Invoice Date</th><th>Customer No</th><th>Sales Man</th></tr>";
while (!$rs->EOF) {
	
      $invno= $rs->Fields("invno");
      $invdte= $rs->Fields("invdte");
      $custno= $rs->Fields("custno");
      $salesmn = $rs->Fields("salesmn");
	echo "<tr>";
  	echo "<td>".$invno->value."</td>";
  	echo "<td>".$invdte->value."</td>";
  	echo "<td>".$custno->value."</td>";
  	echo "<td>".$salesmn ->value."</td>";
    	echo "</tr>";
    	$rs->MoveNext();
}
echo "</table>";
$rs->Close();
?>

Open in new window

0
techsathishAuthor Commented:
The solution what he gave is perfectly working with my code....
0
manomaniCommented:
Hi techsathish.,

Happy to hear this........ :)

If you got worked with odbc . post the changes to be made. being i am not familiar with odbc...........


which may be usefull for others toooo...............

Regards
manomani.
(joomla developer).
0
techsathishAuthor Commented:
Yeah Sure manomani... If so i ll post the working code with ODBC..

Thanks again...

May be my code(OLEDB) will help for further seekers....

Thank u for all....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.