Hello
I've just had to revisit a foxpro app I wrote about 10 years ago.
This just reads an excel spreadsheet, generates an SQL statement and fires it at a MySQL database.
I'm using MyODBC 5.1 and Foxpro 8.
This is a sample of the SQL.
insert into aw_due (f_mem_rn,f_supplier_rn,f_mem_name,f_supp_name,f_date,f_value,f_mem_code,f_x ls_ref) values (544,0,
'AB ','An','1990/01/01',3735.49,'117 ','9999');
The code opens a connection with
nMySQL=Sqlstringconnect("Driver=MySQL ODBC 5.1 Driver;SERVER=mysql.abc.com;UID={aw};PWD={test};DATABASE={aw16};port=52905")
and sqlexec(nMySQL,cSQL)
It has worked for years but the client recently moved to a new hosting company. No errors at runtime, no data arrives in the table but I can run these commands in PHPMyAdmin so the SQL commands are fine.
Has anybody seen anything like this please?
Graham
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
On Wed, Nov 28, 2018 at 6:38 AM Graham Brown info@compsys.co.uk wrote:
nMySQL=Sqlstringconnect("Driver=MySQL ODBC 5.1 Driver;SERVER=mysql.abc.com ;UID={aw};PWD={test};DATABASE={aw16};port=52905")
and sqlexec(nMySQL,cSQL)
Do you capture the results of these functions and test that they are not returning an error?
It has worked for years but the client recently moved to a new hosting company. No errors at runtime, no data arrives in the table but I can run these commands in PHPMyAdmin so the SQL commands are fine.
PHPMyAdmin is running on the same machine as the MySQL, or a different one?
The VFP is surely running on a Windows box. The PHP and MySQL, well, you didn't say.
Offhand, I'd confirm that port 52905 is open in any firewall in place.
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Thanks for the pointers
The app runs local on Windows 7 and uploads a lot of Excel data to an external online portal on shared hosting.
The hosting has a remote SQL option to unlock the port for my IP. If the port is locked it won't even connect. Also I get a connection and I can fire off select and delete commands but not update and insert commands. I've checked the size of the MySQL DB and it isn't over quota.
I don't know anything about the set up as it is shared hosting with www.20i.co.uk. As the domain is managed by another company I can't even talk to 20i myself. I don't know where MySQL is in relation to PHPMyAdmin. They just say use the mysql.abc.com in the hostname and they resolve this to the correct box.
The domain was originally hosted by HeartInternet.co.uk and the update always worked.
One thing the 20i have said in the last hour is that there are a number of "discarded" commands in the event log. I haven't found out why they are discarded but there doesn't appear to be any problems with the SQL as I can run the same sql in PHPMyAdmin.
I don't get any error so don't know how to check what the ODBC or MySQL is doing or why commands are being discarded if they are mine.
Regards Graham
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Ted Roche Sent: 28 November 2018 14:01 To: profox@leafe.com Subject: Re: MyODBC not updating tables
On Wed, Nov 28, 2018 at 6:38 AM Graham Brown info@compsys.co.uk wrote:
nMySQL=Sqlstringconnect("Driver=MySQL ODBC 5.1 Driver;SERVER=mysql.abc.com ;UID={aw};PWD={test};DATABASE={aw16};port=52905")
and sqlexec(nMySQL,cSQL)
Do you capture the results of these functions and test that they are not returning an error?
It has worked for years but the client recently moved to a new hosting company. No errors at runtime, no data arrives in the table but I can run these commands in PHPMyAdmin so the SQL commands are fine.
PHPMyAdmin is running on the same machine as the MySQL, or a different one?
The VFP is surely running on a Windows box. The PHP and MySQL, well, you didn't say.
Offhand, I'd confirm that port 52905 is open in any firewall in place.
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
You can determine if it is a rights issue from phpMyAdmin with:
select * from information_schema.user_privileges;
ASSuMIng you have rights to that metadata. You should see information about the user you log in with and permissions for the database in question.
You might need to add the filter:
WHERE grantee LIKE '%aw%'
(where aw is the username you referred to in the original post)
Also, SELECT CURRENT_USER(); will let you know whom the phpMyAdmin is logged in as.
On Wed, Nov 28, 2018 at 5:18 PM Graham Brown info@compsys.co.uk wrote:
Thanks for the pointers
The app runs local on Windows 7 and uploads a lot of Excel data to an external online portal on shared hosting.
The hosting has a remote SQL option to unlock the port for my IP. If the port is locked it won't even connect. Also I get a connection and I can fire off select and delete commands but not update and insert commands. I've checked the size of the MySQL DB and it isn't over quota.
I don't know anything about the set up as it is shared hosting with www.20i.co.uk. As the domain is managed by another company I can't even talk to 20i myself. I don't know where MySQL is in relation to PHPMyAdmin. They just say use the mysql.abc.com in the hostname and they resolve this to the correct box.
The domain was originally hosted by HeartInternet.co.uk and the update always worked.
One thing the 20i have said in the last hour is that there are a number of "discarded" commands in the event log. I haven't found out why they are discarded but there doesn't appear to be any problems with the SQL as I can run the same sql in PHPMyAdmin.
I don't get any error so don't know how to check what the ODBC or MySQL is doing or why commands are being discarded if they are mine.
Regards Graham
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Ted Roche Sent: 28 November 2018 14:01 To: profox@leafe.com Subject: Re: MyODBC not updating tables
On Wed, Nov 28, 2018 at 6:38 AM Graham Brown info@compsys.co.uk wrote:
nMySQL=Sqlstringconnect("Driver=MySQL ODBC 5.1 Driver;SERVER=mysql.abc.com ;UID={aw};PWD={test};DATABASE={aw16};port=52905")
and sqlexec(nMySQL,cSQL)
Do you capture the results of these functions and test that they are not returning an error?
It has worked for years but the client recently moved to a new hosting company. No errors at runtime, no data arrives in the table but I can run these commands in PHPMyAdmin so the SQL commands are fine.
PHPMyAdmin is running on the same machine as the MySQL, or a different one?
The VFP is surely running on a Windows box. The PHP and MySQL, well, you didn't say.
Offhand, I'd confirm that port 52905 is open in any firewall in place.
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
First guess is the port # is blocked at the new location.
On Wed, Nov 28, 2018 at 5:39 AM Graham Brown info@compsys.co.uk wrote:
Hello
I've just had to revisit a foxpro app I wrote about 10 years ago.
This just reads an excel spreadsheet, generates an SQL statement and fires it at a MySQL database.
I'm using MyODBC 5.1 and Foxpro 8.
This is a sample of the SQL.
insert into aw_due
(f_mem_rn,f_supplier_rn,f_mem_name,f_supp_name,f_date,f_value,f_mem_code,f_x ls_ref) values (544,0,
'AB ','An','1990/01/01',3735.49,'117 ','9999');
The code opens a connection with
nMySQL=Sqlstringconnect("Driver=MySQL ODBC 5.1 Driver;SERVER=mysql.abc.com ;UID={aw};PWD={test};DATABASE={aw16};port=52905")
and sqlexec(nMySQL,cSQL)
It has worked for years but the client recently moved to a new hosting company. No errors at runtime, no data arrives in the table but I can run these commands in PHPMyAdmin so the SQL commands are fine.
Has anybody seen anything like this please?
Graham
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
-
Graham Brown -
Stephen Russell -
Ted Roche