MySQL Forums - DBF, dBASE, FoxPro, Clipper, xBASE

Can not connect mysql to foxpro (7 replies)

gopal joshi — Tue, 21 May 2013 13:42:08 +0000

Hiii

I want to connect foxpro to MySql and also succeed in it. But, it returns error "CONNECTION HANDLE IS INVALID" after formating my computer , i have already install mysql odbc connecter 5.2.5 and connectionstring is as below

SQLstringCONNECT("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=dashboard;User=root;")

but it cant work..
have you any option to connect it..

MySQL Connector/ODBC 5.2 crashes with Foxpro 9 Remote Views and CursorAdapters (1 reply)

Gerold Lübben — Thu, 23 May 2013 11:39:38 +0000

Hi Community,

I tried out the MySQL ODBC client Version 5.2 with a Visual Foxpro 9 based application. Using a CursorAdapter (or remote view) crashes the whole application (no specific error message, just a GPF) on the "cursorfill" method of the Cursor Adapter. The same application works fine and stable with MySQL ODBC 5.1 (any version). Also SQL-passthrough commands (SQLEXEC) seem to work without problems. I tried out several options including "no_ssps" without effect.
There must be a behaviour change in the ODBC 5.2 Client that leads to the crash.

Thank you very much for any suggestions!

PS: The Crash occurs with both client Versions 5.2a and 5.2w

Environment:
* MySQL Community Edition 5.6 (tried 5.1,5.5 also) 64Bit
* Windows 7 64Bit
* MySQL ODBC 5.2.4 32Bit
* VFP 9.0 SP1 Build 7423

Kind regards
Gerold

Connection Timing out (no replies)

Claus Mygind — Fri, 07 Dec 2012 13:36:50 +0000

I am a newbie to this forum, so sorry if this is posted in the wrong group.

I have an existing dBase desktop app that connects remotely to my MySQL server.

Client Computer:
MS Xp 32 bit
MySQL ODBC connector 3.51.29
dBase 2.61.3
BDE 5.01

MySQL Server:
MS Xp 32 bit
MySQL 5.0.27-comunity-nt via TCP/IP

Recently we upgraded our connection lines between the two buildings housing the client machines (3 clients) and the MySQL server. Our previous connection was a 384K (sharing both data and phone traffic), the new connection is 3Mb (data only).

The app has run for about 3 years with no problems. Once the upgraded phone line was installed the app (on all three workstations) started to drop the connection between the client and server. Error messages like these come up "Lost connection to server" or "Server has gone away".

Initially I had no error trapping. So when the error occured the user was kicked out of the program. The data is stored in a local dbf table before transmission to the MySQL server. When the user reloads the app and resend the data, it is done instantly.

I added an try/catch error trap and looped 100 times before timing out with minimal success.

I am not sure if this is a dBase, BDE or ODBC problem. I can capture the error and I may even get that to work eventhough I don't understand the problem. But the timing out process is too long 30 to 45 seconds.

Can the MySQL ODBC driver be set to shorten the time out problem?

I do not want to make adjustments to the MySQL server if possible. I have both dBase and php Web apps that connect with no problem to the server.

Any suggestions will be appreciated.

Foxpro Remote MySQL Connectivity (1 reply)

adeel iqbal — Wed, 06 Jun 2012 21:23:51 +0000

hi,
i hav written following code in foxpro to connect with remote mysql database.

lcStringConn="Driver={MySQL ODBC 3.51 Driver};Port=3306;Server=192.168.0.13;Database=test;Uid=root;"
SQLSETPROP(0,"DispLogin",3)
lnHandle=SQLSTRINGCONNECT(lcStringConn)
MyQry="select * from table1"
SQLEXEC(lnHandle, MyQry ,"MyView")
SELECT MyView
BROWSE

secondly, i have disabled the firewall as well.
but still unable to connect with remote mysql database. can someone please guide? thanks in advance

End of Life FoxPRO/DBF Question (3 replies)

Christopher Cook — Mon, 26 Dec 2011 12:22:25 +0000

Recently had a "new" transition project was volun-told into my lap and I can't find the answer to the last question...

My Client is and has been running on a FoxPRO /DBF database since the early '90s and they have almost 98,000 records in a single FoxPRO/DBF data table. The client's question was, and I quote: "Back in the 90's we paid [Name Redacted] to build this *Explitive Deleted* thing and they said it would never run out of room... Why should we convert it over to something new?"

Here's the catch, the key index value (the only unique data value in the table) is limted to 6 digits in length... So what happens when record number 100,000 is entered? Does the world come crashing down around them? Will FoxPRO/DBF refuse to accept the data entry? Will the key value rest to 000001? After many hours of Googling, I have found exactly nothing... Any help would be greatly appreciated.

-Chris

Date Problems (no replies)

Jack Brown — Wed, 16 Nov 2011 01:18:01 +0000

Hello,

I have an old foxpro program however this year it is being funny with dates saying incorrect date.

I think the person who created this program wanted to dates to stop the program working this year.

Is there anyway of getting this to work?

Is there a separate file for the calendar that could be edited / replaced maybe?

Thanks,

Jack.

JOIN & Query Problem - unique indexes & PKs, converting FoxPro to MySQL (2 replies)

Grind Stone — Thu, 03 Nov 2011 18:04:20 +0000

Our company has live data in a FoxPro 9 source database whose structure and functions cannot be edited - it is part of an application we purchased 15 years ago that will soon no longer be supported.

I am using DB Sync to synchronize tables from the FoxPro DBCs (Windows XP) to MySQL 5.0.92 (Mac Snow Leopard Server) with PHP 5.3.6, to allow more robust reporting with our other systems, including shipping, until we can move everything from the old application to a new PHP/MySQL ERP system.

The FoxPro 9 database does not have Primary Keys (I believe PK indexing is stored in FoxPro CDX files). DB Sync allows me to create Unique Indexes to compensate.

The data sync seems to work just fine.
The problem is trying to create a JOIN for a query. I can JOIN with Unique Indexes (UI), but the UIs that should match, don't match, because one UI has a length of 8 with 3 spaces before the data, and the other UI has a length of 12 with 7 spaces before the data.

I confess that I am at a loss for figuring out which direction to go to make this work.

Any ideas?

Vfpro Application connects to mysql db online (no replies)

Mars Val — Thu, 11 Aug 2011 02:27:10 +0000

My programmer created a business application for me. It's great but it sits on my server which is not a publicly available server (no public IP).

I have a webserver that I "rent" a space on where I can create mysql db under a domain.

What I would like is my programmer to create "some sort of app-connection" that will take certain data from the vfpro database on my private server and post it to the web server db where I created a table for the data I'm interested in.

I'm guessing he would make a procedure to be called perhaps every 30 days (I don't need it daily) that will query the vfp db and write to mysql db online.

My questions are:

1. Is it doable?
2. What program or extra windows/vfp app/module will he need to make the web connection from my win2003 server to the web mysql db?

Re: Synchronization MySql to Foxpro (-1 replies)

David Ickringill — Wed, 09 Feb 2011 18:55:45 +0000

Not Found
The requested URL /pricing.htm was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

HARDLY A RINGING ENDORSEMENT FOR YOUR COMPANY WHEN THE FIRST BUTTON I HAPPEN TO CLICK CRASHES THE WEB SITE!

foxpro translation (1 reply)

Gleason Pace — Thu, 21 Oct 2010 14:15:10 +0000

I have a query that works right in Foxpro

Select Sum(invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share) From invoiceitems, invoice Where
invoiceitems.siteid = invoice.siteid And invoiceitems.invoiceid =
invoice.id And (invoice.pending = false And invoice.islay = false)

In MySql it gets a huge negative number.

sprice*qantity gets 1209.88. share is a percentage. So the result of
the above query should be something less than 1209, but positive.

Can somebody give me an idea of how this should be done in mysql?

odbc driver problem (no replies)

Prem Jain — Fri, 26 Mar 2010 06:19:39 +0000

hi,

i have foxpro database , and i am connecting it through jdbc-odbc connenctivity in java programme.

i just want to know the foreign key of each table into that database , i am trying to retrive the foreign key information by using getExportedKeys() function .
But it throwing an exception as follow :

xception in thread "main" java.sql.SQLException: [Microsoft][ODBC Driver Manager] Driver does not support this function

please let me know , what is exact problem .. Thanks

mapping (no replies)

Prem Jain — Tue, 23 Mar 2010 07:05:35 +0000

with which data type of mysql could i map general and memo data type of foxpro

How to use FoxPro database for MySQL ? (1 reply)

SMO ZWT — Tue, 23 Mar 2010 00:22:05 +0000

Hi,

I am used to foxpro database and have taken backup now i want to use MySQL so how to get it work for me.

Designing Website India | Web Development India

column problem (no replies)

Prem Jain — Thu, 18 Mar 2010 11:44:14 +0000

Hi, i am using jdbc-odbc driver to connect to fox pro database.

i just want to know max width of value stored in particular column.
Let me explain you ..

suppose there is column first_name and width allocated for that column is 20 , now suppose if there is data of length 10 max in that column , i just want to find out that data with its width . So how can i find it out

.CAT file (no replies)

Luis Miguel Valenzuela — Mon, 08 Mar 2010 18:28:46 +0000

I need to open and edit a file with the .CAT extension. Searching over google I've found that this file extension is a dbase file (dbase catalaog file), but haven't been able to open it from anything esle than a notepad....
Thanks in advance.
Luis M. Valenzuela

data migration (1 reply)

Prem Jain — Thu, 04 Mar 2010 21:52:10 +0000

hi , i just want to know the schema from foxpro database(which contains .dbc file) i am using jdbc-odbc driver to connect to foxpro database but , i am able to find out table name, column name and primary key , but unable to find data type ,width, forign key constraint , please help me out

Thanks in advance

Trying to update two remote views (no replies)

Richard Stecenko — Sat, 27 Feb 2010 21:44:08 +0000

I have a VFP 9.0 application with ODBC to MySQL. There are is a table in MySQL. I have a remote view to that table. I added the view twice to the data environment of my form.

On the form, the user enters records into one of the remote views.

Before saving, I copy all the records into the other view and make some changes to the data in that view.

Then I try to update both views. I time out on the write.

I think this has something to do with persistent or non-persistent connections.

Can some one tell me which I should use with a VFP application, how to tell what kind of connection I have now, and how to change it if I have the other kind.

problem with database (5 replies)

Prem Jain — Mon, 15 Mar 2010 14:02:48 +0000

Hi , i am facing very serious problem , i have a dbc file and i want to convert it into mysql .

Please help me out

Thanks in advance

Database Migration: From Visual FoxPro to MySQL (no replies)

Edwin DeSouza — Fri, 12 Feb 2010 21:08:38 +0000

Outgrowing Visual Foxpro
http://pirringers.com/mysqlblog/?p=3

First Contact – VFP/MYSQL interaction
http://pirringers.com/mysqlblog/?p=11

Transferring VFP Table Structures to MYSQL
http://pirringers.com/mysqlblog/?p=17

Converting a Simple VFP Form to MySQL
http://pirringers.com/mysqlblog/?p=26

Converting and optimizing SQL Statements
http://pirringers.com/mysqlblog/?p=32

Optimizing differences
http://pirringers.com/mysqlblog/?p=45

Security Benefit By Switching to MYSQL
http://pirringers.com/mysqlblog/?p=52

Using MYSQL to maintain the VFP frontend
http://pirringers.com/mysqlblog/?p=59

VFP/MYSQL what works and what not discussion (no replies)

Martin Pirringer — Sun, 20 Dec 2009 15:49:44 +0000

Hi all,

I am converting a big VFP application to MYSQL and for now using VFP as a frontend. I converted the database (over 100 files) and the data (about 2 gig) and that went smoothly. If anyone is interested how to I will post that here. Overall it is workable and I tried now to improve performance an there is where the problems started.

Here is the basic overview
MYSQL Server 5.1.41 on a windows vista machine
MYODBC 5.1.6
VFP 9.0 sp2 on a toshiba satelite running XP

To access the server I use shared connections as setting up a non shared connection can take 0.5 seconds as compared to a shared one that is < 0.08 sec. This makes a difference if you have to retrieve data from 8 or 9 tables when opening a screen. Using seq. sync mode and nonshared connection the data access woud take 15 seconds a long time waiting for a screen to open. Opening up 10 shared connections simultaneously and running the queries asyncronously the data access part is done in 0.6 seconds (some of the data will fill in later but that is ok as the customer can start scrolling through whatever he wants and sees progress and does not sit in front of the screen waiting for 15 seconds with nothing happening.

Now to improve performance I started to change some settings in the odbc driver.

I set the compression setting on - did not do much on that screen but improved the data availability on another screen (accessing about 100,000 records out o a 2 Million record table with a complex where clause and a join involving 4 other tables using a view. Synchronously it takes 140 seconds (as all needs to be read) async data is available in 30 seconds with compression in 16.

Now I also tried to set "don't cache results on forward only cursors" this made dat available in 6 seconds - but then if the customer does not want to see the whole dataset and cancesls (sqlcancel(connection_id)) the system hangs. Also when running those 10 sql statements from the 1st example as soon as the 2nd simultaneous query executes one gets and error from the odbc driver something like "You cannot execute that command now - commands out of sync SELECT ......"

So there is a problem with that feature. Also forcing all sets to be forward only cursors or eabling/disabling large record sets in the driver does not work.

Also on the 100,000 record query when executing

Select * from WHERe ORDER by

I get erro 11 Cannot unlock table

Droping the ORDER BY will take care of the error - WHY ?

Also a workaround I found when rewriting the above query to

SELECT tmp.* FROM (Select * from WHERe ) tmp ORDER BY

Will take care of the error Also if you select less than 40,000 records then the error does not occur at all with the above query. This looks like a MYSQL problem to me as I can reproduce it in the MYSQL query editor.

One more problem :

Using an updatable cursor (by setting the properties with cursorsetproperty()) will fail with an error "Access denied for user ''@'') Turning the debug on on the ODBCDRIVER will show as the last statement

UPDATE SET WHERE

now taking that statement and executing it either in the Mysql Query browser or as code in th program

ln_conn = SQLCONNECT()
sqlexec(ln_conn, "UPDATE SET WHERE ")

Same statement foxpro tries to send.

As a note the user and PW are defined in the DSN so it should use that user and not some weird user ''@''

Any comments or solutions would be apreciated

Urgent HELP!!!! (1 reply)

Francis Flores — Sun, 20 Dec 2009 16:55:28 +0000

hi guys. can you please check my codes..

i am currently working on a migration of a foxpro to a mysql database
but i have an error when i am executing my insert query. help me out. :)

''''''''''''''''''''start of SUB
Dim myConnection As New MySqlConnection
Dim foxConnection As New OleDbConnection

Dim test1 As String = "z:\foxpro\" 'where resides the foxpro table

myConnection = New MySqlConnection("SERVER=" & My.Settings.DBSERVER & ";" _
& "DATABASE=testdata;" _
& "UID=user;" _
& "PWD=pass;")

Dim xFoxpro As String = "select strtran(dept_code,chr(39),'') as dept_code, strtran(desc,chr(39),'') as descr from dept"

Dim strSql As String = "insert into dept " & xFoxpro

Dim connCommand As New MySqlCommand
connCommand.CommandText = strSql & " Provider=VFPOLEDB;" + _
"Data Source= " & test1 & " ;" & _
"Collating Sequence=general;"

connCommand.Connection = myConnection
If connCommand.Connection.State <> ConnectionState.Open Then
connCommand.Connection.Open()
End If
connCommand.ExecuteNonQuery()

''''''''''''''''''''end of SUB

Need help please (no replies)

Gabby Takash — Thu, 26 Nov 2009 14:57:41 +0000

Dear Database Support Professional,

I am Database Administrator and I am writing to ask for your help in responding to a 10-12 minute survey for a degree requirement on relational database support professionals in the USA. Please note that the respondent is completely anonymous. If I don’t get this portion of my degree completed by the end of this semester, then I would have to wait for another year which entails additional costs and time on me. By completing the survey, you will be contributing to research in our field and also there is the satisfaction of having helped a fellow professional and a fellow human being attain his goals. I believe a good turn always bring another in some way in our lives. I therefore kindly beg for your help to a fellow database professional by completing this survey. The link to the survey is provided below. If possible please forward to other database professionals or groups:
http://www.surveymonkey.com/s.aspx?sm=a1JqU7GVt1g6rCkIPyL23w_3d_3d

Thank you earnestly for your kindness.

A very odd ODBC problem: Mysql connection busy (no replies)

gregory west — Fri, 21 Aug 2009 16:03:57 +0000

I have a Foxpro V9 sp2 program. It does 70,000+ updates to a MySQL table. Problem is the first update causes the cursor in MySQL to become busy for anywhere up to 5 minutes. I do a suspend in foxpro and wait, then do a resume and the last 70,000+ records are processed in the matter of seconds.

Any idea what would be causing this to happen?

Mail sending problem (1 reply)

Reshmi Babu — Thu, 13 Aug 2009 16:13:14 +0000

Hello,
I have a problem while sending email from a server the mail sending is not working because that server not supporting mai sending property but i have to send email in that web site.is it possible to send email using another server's server details in that site for sending email? pls give me an answer immedately..

LOCKS (no replies)

kumar raju — Tue, 28 Jul 2009 16:00:21 +0000

Hi,

I'm very new to mysql technology i need some information on how to see locks and blocks that are there in the database.....

Your help is highly appreciated....

Thanks,
Kumar

XBase : can't find .dbt file (2 replies)

john cowan — Thu, 16 Jul 2009 05:57:48 +0000

Greetings,
The title is correct; can't find a .dbt file (note the t in .dbt).

I'm moving a Perl script from my windows xp machine to a linux server. When finished, a cronjob to run this program.

The program runs fine on the windows machine, but I'm having what seems to be an XBase bug on the linux machine.

I run a command on the command line to copy a bunch of FoxPro tables from one server (a foxpro production db) to another server/directory to be worked on. This works and copies all the table files (.dbf, .cdx, and .fpt). Note: not all the .fpt files get copied.

Then I run a Perl script which loops through an array of the table names, creates a tab-delimited txt file to be used as a datafile for a sql-loader command that uploads the data into Oracle tables. Piece of cake!

Problem:
I have about 50 tables. This all works fine on about 44 tables. On the others, I'm getting an XBase error about '... can't find file tablename.dbt' note the 't' in the extension.

Would anyone have an idea on what is causing this error? I'm baffled.

Code - main parts related...

use DBI;
use XBase;

#connection to foxpro
eval { $foxDbh = DBI->connect("dbi:XBase: // blah blah blah } # partial syntax

# Tables that wont run in command line: '
@CsoTableNames = ('cm', 'jb','mp', 'nt', 'rp', 'su');

foreach my $table (@CsoTableNames) {
$sql = "select * from $table";
eval{
$foxSth = $foxDbh->prepare($sql);
$foxSth->execute;
};
if($@){
$errorString = " Problem Selecting from FoxPro $table table.".$@;
print "Error on table $table : ".$errorString."\n";
SendEmail($errorString);
}
}

Error:
DBD::XBase::st execute failed: Table cm not found: Error opening file /home/path/to/tables/cm.dbt: No such file or directory

Error on table cm : Problem Selecting from FoxPro cm table.DBD::XBase::st execute failed: Table cm not found: Error opening file /home/path/to/tables/cm.dbt: No such file or directory

I've checked and tested the tables in the directory they are copied to. I can open them in FoxPro and the data is good. Again, this only happens on six of the fifty tables. Strange!

Any help or ideas would be GREATLY appreciated.

how to read and write record in the mysql database of internet using Visual Foxpro? (1 reply)

Darren Ang — Thu, 07 May 2009 13:11:41 +0000

hi,

i have a website contains 2 databases in the mysql. one database called 'information_schema' which was created by mysql, and another database called 'infodata' which created by me.

i use visual foxpro to write a program to access the database, the database show ok when i use SQLSTRINGCONNECT command to access the 'information_schema' database, but the database show error when i use SQLSTRINGCONNECT to access 'infodata' database, may i know what is wrong with database ? how to ratify this problem ? pls help me. thanks !!!

ODBC connect user name problem (no replies)

Lee Bjerke — Sat, 21 Feb 2009 15:01:21 +0000

Hi
I have been using odbc connect to a remote mysql data base sucessfully.

Now I am on a different isp trying to connect.

my odbc system dsn can't connect and it looks like it is because the long isp name is added to my user name.

Help......

Lee

I;m new to this environment.. where to start (1 reply)

vijay kumar — Wed, 18 Feb 2009 15:53:30 +0000

Hi all, Please any one help me regarding how to start to learn about concept like " C++ access to mysql server in linux as well as in windows.. I know both C++ and basics of Mysql server..

Give some links where can I get the information regarding configuration and programming tips

Thanks and regards
vijay

Use an automatic generated ID (no replies)

Alberto Garcia Cano — Tue, 23 Dec 2008 14:31:05 +0000

Hi all:

I'm trying to register an invoice that has:

a) a Header (table A - autogenerated ID)
b) a Body (table B - Foreign Key from Table A)

The point is that I'm trying to encapsulate that in a TRANSACTION with the following statements:

start transaction;
insert into A (ID) values (0); (ID=Integer autoincremental PK)
select @IDCAB=LAST_INSERT_ID();
insert into B (ID,FKCAB) values (0,@IDCAB); (ID=Integer autoincremental PK)
commit;

Assuming that the value of ID in A was 34, when I see the B's new register, the field FKCAB is NULL instead of 34.
Could you help me please?