Desperately need help with FoxPro db

cnisvcs

Perch
Has anyone had any success with making ODBC DSN-based or DSN-less connection to FoxPro database file?

I tried on 3 different servers (win2, win23, web9) with no success whatsoever. Different errors each time. Anyone could share working settings/configuration? Please!
 
We do have a few people using foxpro DB and we do have hte foxpro drivers loaded.

What errors are you getting?
 
First I used web9 and tried dsnless connection. The error I was getting was:

Warning: odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] Driver does not support this function, SQL state IM001 in SQLConnect in D:\hshome\cnisvcs-\cni-test.com\dbtest.php on line 23

Please note that this particular error msg was produced on web2, as I already closed account on web9 and cannot reproduce it there. The message was persistent regardless what path to the db file I specified.

As a second attempt I was using my own account on web2. Each time I tried to setup DSN connection in CP I was getting the following:

Failed to add DSN
Unable to create ODBC data source COM error: Failed to set security info API error : The system cannot find the file specified. Time:Wed May 16 13:24:59 EDT 2007 Server IP: 204.10.107.232 Server name: JH-OUN7AM8B1DCF Parameters: SourceType=DBF&DSN=cnisvcs_aaa&Collate=&Description=&driver-name=Microsoft+Visual+FoxPro+Driver&Exclusive=&BackgroundFetch=&SourceDB=cni-test.com%5CAAA_esrpt_final.dbf&user-name=cnisvcs- H-Sphere Winbox version : 02.43 H-Sphere Winbox build : 779
Command: odbc-createdatasrc.asp

Part of the message refers to missing file. I tried all possible paths, relative, absolute, put the db file in acct root, to no avail.

DSN-less attempts on web2 were causing same error as listed above on web9.

Getting desperate I started looking for alternative ways to get the data out of the dbf file. I will be dealing with 2 or 3 files, each containing no more than 50 records, only reading. PHP dbase commands seemed an option. I managed to enable the dbase PHP extension on web2 and had positive results.

The client's site will be hosted on win23, so whatever I do ultimately has to work there. At first I tried to setup DSN connection in CP. Seemed to go thorough, no errors. However, I intentionally entered wrong path and there was no error either. This indicates that the CP does not check for the presence of the file in indicated location. Then I tried to access the file with the following command:

$dsn_name = "princet_a";
$connect = odbc_connect($dsn_name,"","");

Script executes for about two minutes and then sort of dies. It does not display any error, warning or any message that was generated by the script prior to the odbc_connect line.

I am afraid that this script causes some kind of major overhaul on the server. In the process of testing (last ninght between 1-3 am CT) there were times when my site stopped responding for minutes.

Next I tried with DSN-less connection string like this one:

$dsn_string = "{Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=D:\home\princeto\leoknows.com\AAA_esrpt_final.dbf;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO;";
$connect = odbc_connect($dsn_string,"","");

I tried various combinations: without brackes around driver designation, without less relevant pieces like: null=no, collate, background fetch, tried adding UID=;PWD=;. Nothing worked, same behavior - script processed for quite awhile then timing with no error.

Finally I requested enabling dbase commands for this account. Ticket# LNT-98368-966. No response yet.

Please help.
 
web2/web9 or win2/win9?

I am also seeing a blend of PHP and ASP in there. (your post)

I will tell you, php likely won't work in any shape or form, ASP I tihnk I can help with and it is likely just a permissions matter.
 
First I started on Linux web9. Then I used my own Windows account on win2. Finally I worked on Windows win23. Sorry I mistyped server's name in the middle of my post.

I never used the ASP. Had no intention to. All I do is in php. The "Command: odbc-createdatasrc.asp" is part of the CP red error message displayed atop of the ODBC page after trying to add DSN connection on win2.

The databse administrators, who produce the FoxPro db file, had created a prototype on Windows 2000 machine running Apache and php scripts worked for them. I am not dead set on using ODBC. Dbase functions would work if you only allow me to use them.
 
Sorry but i don't think you will be getting foxpro to work with PHP. Even if you can get it to work PHP and ODBC are not a good combo and you will be fighting off more problems than anything.
 
Okay then. How about enabling dbase commands? All I did on win2 was uncommenting

extension=php_dbase.dll

in generic php.ini and had the commands working. They seem to be perfectly sufficient to do the job. Can you enable them on win23?
 
Win23 is using php5, I can try doing that in the next 12 hours, as it requires and IIS reset to take effect.
 
Would it be easier to transfer entire hosting to another Windows machine with php 4.x on it? Just a thought...
 
We can do php4 on win23 as well :) ou need to request it then you can use the custom php.ini
 
Wonderful! I request it then.

I was typing email to you when saw your post coming. I submitted two responses to my original ticket: one via ticket system and second via email.Had not received the email confirmation in both cases. Is something going on?

At this point I prefer to work with php4 on win23 if it can be done. Please advise the timeframe.

You're the best!
 
AWESOME! I am on php4.4.4 and the dbase commands work. Thanks so much.

Actually. My other script using ODBC commands worked as well. That's a progress. Thanks again. You guys are THE BEST!
 
Just as a follow-up: The dbase commands work just fine. After few more tests I managed to get the DSN-less connection to work properly. The winning code lines were:


$dsn_string = "Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceType=DBF;SourceDB=D:\hshome\8_characters_account_name\domain_name\subfolder;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO;";
$connect = odbc_connect($dsn_string,"","");
$query = "SELECT * FROM database_file_name";
$result = odbc_exec($connect, $query);
//data processing commands
odbc_close($connect);

Important:
- SourceDB parameter in the connection string must contain the absolute path only (not including file name!!!). All examples I found included the file name. First big one.
- Parameters: null, collate, backgroundfetch, delete are optional. So are UID and PWD.
- The query must contain the database file name after "FROM" - this is how the driver knows which file to pull the data from. Second big one. Also, it appears that the database file name does not need extension .dbf, as the driver uses parameter SourceType to attach the extension on its own.

Hope that helps other FoxPro users.
 
Back
Top