MyODBC not updating tables

[Graham Brown]

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
---

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/05c301d4870f$01b31600$05194200$@compsys.co.uk
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Ted Roche]

Post by Graham Brown
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?

Post by Graham Brown
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
---

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CACW6n4tmWYBPJ6HDmJNO9V2RhYA9OopzYvHMYvSkTcDV+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Graham Brown]

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-***@leafe.com] On Behalf Of Ted Roche
Sent: 28 November 2018 14:01
To: ***@leafe.com
Subject: Re: MyODBC not updating tables

Post by Graham Brown
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?

Post by Graham Brown
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/062901d48768$6d6cea70$4846bf50$@compsys.co.uk
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Ted Roche]

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.

Post by Graham Brown
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-----
Sent: 28 November 2018 14:01
Subject: Re: MyODBC not updating tables

Post by Graham Brown
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?

Post by Graham Brown
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CACW6n4smYRoiu_kk3RGdhtoqPn+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Stephen Russell]

First guess is the port # is blocked at the new location.

Post by Graham Brown
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.