MySQL and VFP  
Author Message
Misiacik7





PostPosted: Visual FoxPro General, MySQL and VFP Top

Hi experts.

I have a MySQL database. I operate with them using syntax like SQL(sqlhandle, "select * from...") I copy them into cursor and than operate with a cursor. Is there a simplier way how to work with them

Thanks in an advice.



Visual FoxPro1  
 
 
CetinBasoz





PostPosted: Visual FoxPro General, MySQL and VFP Top

The result of an SQLExec(m.nHandle, "select ....", "myCursor") is already a cursor. You could directly work with that cursor and make that cursor updatable to send back the results. ie: With an MSSQL server database:

csql = "SELECT EmployeeID, LastName, FirstName," + ;
"Title, TitleOfCourtesy, BirthDate," + ;
"Hiredate, Address, City, Region," + ;
"PostalCode, Country, HomePhone," + ;
"Extension, Photo, Notes," + ;
"ReportsTo, PhotoPath FROM dbo.Employees"

lnHandle=Sqlstringconnect('DRIVER=SQL Server;'+;
'SERVER=(local);DATABASE=Northwind;Trusted_Connection=Yes')

SQLExec(lnHandle,csql,'v_emp') && v_emp has data now but not updatable
* You can browse and change data but it wouldn't update
* at backend unless you prepare it to be updatable

* Make v_emp updatable
CursorSetProp('KeyFieldList','Employeeid','v_emp')
CursorSetProp('WhereType',1,'v_emp')
CursorSetProp('Tables','employees','v_emp')

* Specify fields' mapping
* Mapping is in pairs
* On left there is local field name and on right
* tablename.Fieldname on backend.
* employeeID employees.employeeid
* employeeID is local field name mapping to
* employees.employeeid on backend
* In other words if set to be updatable
* v_emp.employeeID would update employees.employeeID at backend
* Note: Include key field in this list even though you might not
* want it to be updatable
TEXT to m.lcUpdateNameList noshow
employeeID employees.employeeid,
Lastname employees.Lastname,
Firstname employees.FirstName,
Title employees.Title,
TitleOfCourtesy employees.TitleOfCourtesy,
BirthDate employees.BirthDate,
Hiredate employees.Hiredate,
Address employees.Address,
City employees.City,
Region employees.Region,
PostalCode employees.PostalCode,
Extension employees.Extension,
Notes employees.Notes,
ReportsTo employees.ReportsTo,
PhotoPath employees.PhotoPath
ENDTEXT
CursorSetProp("UpdateNameList", ;
Chrtran(m.lcUpdateNameList,Chr(13)+Chr(10),''),'V_emp')

* Specify fields that could be updated
CursorSetProp('UpdatableFieldList',;
"LastName, FirstName," + ;
"Title, TitleOfCourtesy, BirthDate," + ;
"Hiredate, Address, City, Region," + ;
"PostalCode, Country, HomePhone," + ;
"Extension, Notes," + ;
"ReportsTo, PhotoPath",'v_emp')
CursorSetProp('SendUpdates',.T.,'v_emp')
* We want all updates to occur in a batch
* so setting to table level buffering
CursorSetProp('Buffering',5,'v_emp')

Browse Title 'Editable fields are all except Id and Photo'
* Update backend with changes
If !Tableupdate(2,.T.,'v_emp')
Aerror(arrWhy)
Display Memo Like arrWhy
EndIf
* Requery data from backend to see if it has been really updated
SQLExec(lnHandle,'select * from dbo.employees','afterupdate')
SQLDisconnect(lnHandle)
Select afterupdate
Browse

In VFP9 you could use cursoradapters instead along with OLEDB or ODBC.


 
 
Misiacik7





PostPosted: Visual FoxPro General, MySQL and VFP Top

Is this code useful for MySQL database Which one is faster The code written above or everytime use SQLEXEC() I want it for my Chat, and I want to actuate the table records every 5 seconds, and the way with SQLEXEC(sqlhandle,"select * from chat",tempchat) is slow. And than work with cursor named "tempchat" is a little bit difficult. Is there a way how to actuate my cursor or something like that without any computer slowdown and update the records without writing that SQLEXEC But the main task is to accelerate the cursor updating.(I mean to stay the records up to date).

Thanks


 
 
CetinBasoz





PostPosted: Visual FoxPro General, MySQL and VFP Top

"Is this code useful for MySQL database "

Yes, why not. Only change sql and connection string.

"Which one is faster "

Comparing what MSSQL and MySQL I don't know. Never used mySQL.

"The code written above or everytime use SQLEXEC() "

WHat do you mean Yes I wrote it there. No I don't use SQLExec() everytime. I choose depending on my needs between RV,SPT,ADO,CA...

"I want it for my Chat, and I want to actuate the table records every 5 seconds, and the way with SQLEXEC(sqlhandle,"select * from chat",tempchat) is slow. And than work with cursor named "tempchat" is a little bit difficult. Is there a way how to actuate my cursor or something like that without any computer slowdown and update the records without writing that SQLEXEC But the main task is to accelerate the cursor updating.(I mean to stay the records up to date)."

It has never been slow for me. Even from within millions of records table joins in MSSQL I get results in subsecond times. Of course I also do things on MSSQL side to speed up things like stored procedures, functions, partioning etc.

Of course there are other ways to select/insert/update/delete from/to a backend. If that were SQL2005 you could even have notification implementations. Probably you'd use parameters with some datetime and rowidentifier (formerly timestamp in MSSQL) columns to speed up things for yourself. That sample just pulls down all rows.


 
 
Misiacik7





PostPosted: Visual FoxPro General, MySQL and VFP Top

Thanks for your help, but I use free no paid MySQL database. Do you think that is the reason why every "select * from..." slows me down for a second even the table has only five records By the way, do you know provider that gives free MSSQL database for my testing purposes for free of charge. I am disapointed of such a slow comunication. The best will be if someone who have more experiences will try to subscribe a free MySQL database download ODBC driver for MySQL and try to make faster connection.

The best will be if someone knows that provider cause MySQL has a little bit different syntax and therefor the communication and programming is more difficult.

Please help me someone. I want to make an remote table, fast one and free of charge.

Thanks for an advice


 
 
CetinBasoz





PostPosted: Visual FoxPro General, MySQL and VFP Top

MSSQL server 2005 express version (SSE) is available for free from Microsoft. Check msdn.microsoft.com/express. You can even download other express versions from there for free (C#.Net, Visual web developer etc).

You can also find some free hosting providers with SQL server and .Net support.


 
 
Misiacik7





PostPosted: Visual FoxPro General, MySQL and VFP Top

I cannot accept this as an awnser. I don't want to download any MSSQL Server. I just want free SQL database which is fast. Don't know if the speed problem is due it is a MySQL database or casue it is free of charge. I don't believe the gives such as slow database for free. Noone will subscribe it and noone will buy their products.

No matter which SQL database will I use, vhether MSSQL or MySQL, but it has to by faster than this one. I have changed some providers with no success. The same speed problem. I don't know wheter it is caused by bad code or by provider or by MySQL restriction, but I think table with only five records must be fast.

Please help me someone. But I don't want to download MSSQL Server.


 
 
SuperFox





PostPosted: Visual FoxPro General, MySQL and VFP Top

It is difficult to understand what it is you are trying to do here. Are you using VFP as a front end to MySQL What exactly is slow that you are want to speed up

Greg Gum


 
 
CetinBasoz





PostPosted: Visual FoxPro General, MySQL and VFP Top

Then don't accept and don't download. You don't need to express it explicitly. Good luck.
 
 
AndyKr





PostPosted: Visual FoxPro General, MySQL and VFP Top

>> I cannot accept this as an awnser. I don't want to download any MSSQL Server. I just want free SQL database which is fast. Don't know if the speed problem is due it is a MySQL database or casue it is free of charge. I don't believe the gives such as slow database for free. Noone will subscribe it and noone will buy their products.

 

 

The most likely explanation, since (whatever you may believe) a LOT of people do use MySQL for all sorts of reasons without problems, is that it is either your hardware, your environment or your code.

>> Please help me someone. But I don't want to download MSSQL Server

Seems to me that you have had a LOT of help and all you have done is to spurn it. As Cetin said you can do whatever you like but remember that just because you want a particular solution does not automatically mean that there is one!

 



 
 
Misiacik7





PostPosted: Visual FoxPro General, MySQL and VFP Top

Once Again... I am using VFP and I want to connect a via ODBC to a SQL database which is faster than the one I have. I want it free without paying. No mather whether MySQL database or MSSQL database. Just a SQL database which won't slow down for a second after typing SQLEXEC(sqlhandle,"select * from table01",cursor1). You believe me or not this simple code no mather There are 5 fields and 5records takes a second to realize.

And guys, don't be so angy. Please, I need your help.


 
 
CetinBasoz





PostPosted: Visual FoxPro General, MySQL and VFP Top

Why don't you think it might be your own code or settings somewhere that makes it slow. From within millions of records I do it in a second or two and you do it in a second for 5 records. As I said I use MSSQL and never tried mySQL (well tried once years ago and I don't remember that as slow as you say).

Also did you ever think you might not have a need for mySQL or MSSQL backend at all and use native VFP tables.


 
 
AndyKr





PostPosted: Visual FoxPro General, MySQL and VFP Top

>> And guys, don't be so angy. Please, I need your help.

No-one is angry! But I repeat, just because you want an answer does not mean that there is one!

The only FREE SQL databases are MySQL and SQL Server Express (or the older MSDE). You are using one and don't want the other. What other answer do you want Simply repeating the question doesn't get you any further.

The key issue for you is the apparent speed of MySQL. But there are thousands of applications out there using MySQL with databases much bigger than yours. They don't have the problem that you say you are having,  so the obvious conclusion is that it is not the database but either your code, the hardware or your environment.

>> There are 5 fields and 5records takes a second to realize

The following code runs in less than 3 seconds against a SQL Server table and returns 103,207 records:

lnSt = SECONDS()
SQLEXEC( 1, "SELECT * FROM rpt_hash" )
lnEn =
SECONDS()
STR( lnEn- lnSt, 8, 4 )

While if I run the same code against a table that has only 5 records, then the time is 0.02 seconds. I am sorry to repeat myself, but it all leads me to the same conclusion - something is wrong with your setup, or your connection, or your code! One thing is certain, it is NOT MySQL that is the problem here.

 

 

 



 
 
Misiacik7





PostPosted: Visual FoxPro General, MySQL and VFP Top

I understand but I have asked whether the problem may be that I am using free MySQL database, whether this speed isue can be one of the database limits.

the code below takes somethimes 0,61seconds and sometimes up to few seconds(1-3)

start=SECONDS()
OPEN DATABASE data
mySQLhandle=SQLCONNECT("connection")
end=SECONDS()
WAIT WINDOW end-start

the code below takes 1325.436seconds to complete (10 000 records)

start=SECONDS()
FOR i=1 TO 10000
SQLEXEC(mySQLhandle,"INSERT INTO `p2pchat` VALUES ('', '"+ALLTRIM(STR(i))+"', '"+ALLTRIM(STR(10001-i))+"', '"+"test"+ALLTRIM(STR(i))+"', '')")
ENDFOR
end=SECONDS()
WAIT WINDOW end-start

the code below takes 19.111seconds to complete (10 000 records)

start=seconds()
SQLEXEC(mySQLhandle,"SELECT * FROM `p2pchat`")
end=SECONDS()
WAIT WINDOW end-start



Thanks

 
 
AndyKr





PostPosted: Visual FoxPro General, MySQL and VFP Top

>> I understand but I have asked whether the problem may be that I am using free MySQL database, whether this speed isue can be one of the database limits.

And the answer, as I have told you at least twice, is that My SQL is used by thousands of people all over the world and speed does NOT seem to be an issue for any of them.

I am sorry but I do not NOT use MySQL (I have enough problems dealing with SQL Server 2000, SQL Server 2005 and Oracle in addition to VFP to worry about MySQL too) and so cannot be prescriptive but in SQL Server the default set-up for transactions is 'implicit', and in VFP the setting for a connection is to use automatic transactions (which effectively means you will be using a separate transaction for each insert)  and running code like you show would be slow there too. 

As we have told you already the most likely explanation is your environment and your code. I can't tell about your environment, but is your connection being set to use MANUAL transactions Are you managing the SQL Transaction properly in your code I don't see this anywhere.

I suggest your find out HOW to do this sort of job properly before you start blaming the tool!