[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2010-01-04
26
Medium Priority
?
4,706 Views
Last Modified: 2013-12-13
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...

0
Comment
Question by:techsathish
  • 11
  • 8
  • 4
  • +1
26 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 26169975
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
 
LVL 7

Author Comment

by:techsathish
ID: 26170061
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
 
LVL 43

Expert Comment

by:pcelba
ID: 26170123
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:pcelba
ID: 26170134
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
 
LVL 7

Author Comment

by:techsathish
ID: 26170301
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
 
LVL 43

Expert Comment

by:pcelba
ID: 26170364
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
 
LVL 7

Author Comment

by:techsathish
ID: 26170396
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
 
LVL 43

Expert Comment

by:pcelba
ID: 26170716
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
 
LVL 43

Expert Comment

by:pcelba
ID: 26170729
BTW, are you trying odbc_connect() via VFP OLE DB provider...  You should use above string via ADODB connection.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26170816
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 26172856
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
 
LVL 7

Author Comment

by:techsathish
ID: 26177399
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
 
LVL 7

Author Comment

by:techsathish
ID: 26177444
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
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 200 total points
ID: 26179335
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
 
LVL 7

Author Comment

by:techsathish
ID: 26179419
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 26179480
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
 
LVL 7

Author Comment

by:techsathish
ID: 26188570
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 26189314
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
 
LVL 5

Accepted Solution

by:
manomani earned 1800 total points
ID: 26257606
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
 
LVL 7

Author Comment

by:techsathish
ID: 26257729
Hi manomani,

Is this for ODBC or OLEDB
0
 
LVL 5

Expert Comment

by:manomani
ID: 26257877
Hi techsathish.,

This will Work 100% in oledb ,  some slighter modification results positive in odbc also.....
0
 
LVL 5

Expert Comment

by:manomani
ID: 26258110
Hi.,

Also Dont want to create a mapped drive....... just use the dsn name ......
0
 
LVL 7

Author Comment

by:techsathish
ID: 26258573
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
 
LVL 7

Author Closing Comment

by:techsathish
ID: 31672313
The solution what he gave is perfectly working with my code....
0
 
LVL 5

Expert Comment

by:manomani
ID: 26260024
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
 
LVL 7

Author Comment

by:techsathish
ID: 26260399
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question