Jump to content

Update a record FM <> ODBC<>MySQL(remote)


This topic is 3160 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello, I am Very Very New to this so please be gentle.

I have a MySQL Database on a hosted server. I can connect to it via PHPMyAdmin and I can run very basic SQL Select * From where etc. I have managed to update records in that Db via the PHPMyAdmin. I can create new records and run local scripts to make bulk changes all via the PHPMyAdmin.

I have managed to get a FM13Pro connection to the said Db using ACTUAL ODBC. I have created a copy of the table that I am interested in and all of the data is replicated on my MAC in FM.

The question now though is: How can I change data in the FM Db that will be passed to the remote MySQL database.

So if I update a record locally(FM) it then updates the record remotely(MySQL).

I hope that makes sense. And thanks in advance or any help.

Link to comment
Share on other sites

Well, you'll have to script that.  You have a couple of options:

- it sounds like you have the MySQL table available inside your FM solution through ESS.  With that you can switch to a layout based on that ESS TO, do a find and update the record with the values from your real FM record

- you can also use the ODBC connection to use the "execute SQL" script step (not the ExecuteSQL() function) to do an UPDATE call to your MySQL table.  The benefit of this approach is that you do not have to switch context in your FM solution (go to the ESS layout)

Link to comment
Share on other sites

Hello thanks for the help.

So far so good. I have updated to FMPro14.

I have re done my first Db and have a copy of 2800 records in FM from the MySQL Db.

I have A complete set of Fields on my first Table which I can view as Form List or Table. That all makes sense.

Following the clues you have left I am now trying to make a change in a single field of a single record and have that replicated back to the MySQL Db.

I'll let you know how I get on.

Have a great day.

Stephen

 

 

Link to comment
Share on other sites

OK, Well I thought I got it. but I'm missing something.

This script works when implimented in http://localhost/phpmyadmin/ ... selecting

INSERT INTO `defibcfr_backenddb`.`location_entry` (`ENTRY_ID`, `AUTHOR`, `CREATION_DATE`, `LOCATION_ID`, `NAME`, `TYPE`, `SITE_NAME`, `BUSINESS_NAME`, `ADDRESS_SUMMARY`, `BUILDING_NAME`, `STREET_ADDRESS`, `NEIGHBORHOOD`, `CITY`, `STATE`, `POSTAL_CODE`, `COUNTRY`, `CONTACT_NAME`, `CONTACT_PHONE`, `CONTACT_EMAIL`, `LATITUDE`, `LONGITUDE`, `WEB_ADDRESS`, `TRAINING_DATE`, `RETRAINING_DUE_DATE`, `TRUST_NAME`, `LOCALITY`, `FLAGALL`, `EmailCheckSentDate`, `ProbNotes`, `UpdateCheck`, `UpdatedDate`, `SpecialNotes`, `WhoUpdated`) VALUES ('29999', 'Stephen Thornber', CURRENT_TIMESTAMP, '999', '', NULL, 'Test Site 999', 'Test Business 999', NULL, NULL, NULL, NULL, NULL, NULL, 'LE9 9NN', NULL, NULL, NULL, '[email protected]', '52.6291468', '-1.343625', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Special 999 notes ', 'SKT’)

However when I try it via FM I get the error message shown here. https://www.dropbox.com/s/efbgms9bzgmc4jf/Screenshot 2015-08-03 09.57.26.png?dl=0

In FM I use

Execute SQL [SQL Text: INSERT INTO `defibcfr_backenddb`.`location_entry` (`ENTRY_ID`, `AUTHOR`, `CREATION_DATE`, `LOCATION_ID`, `NAME`, `TYPE`, `SITE_NAME`, `BUSINESS_NAME`, `ADDRESS_SUMMARY`, `BUILDING_NAME`, `STREET_ADDRESS`, `NEIGHBORHOOD`, `CITY`, `STATE`, `POSTAL_CODE`, `COUNTRY`, `CONTACT_NAME`, `CONTACT_PHONE`, `CONTACT_EMAIL`, `LATITUDE`, `LONGITUDE`, `WEB_ADDRESS`, `TRAINING_DATE`, `RETRAINING_DUE_DATE`, `TRUST_NAME`, `LOCALITY`, `FLAGALL`, `EmailCheckSentDate`, `ProbNotes`, `UpdateCheck`, `UpdatedDate`, `SpecialNotes`, `WhoUpdated`) VALUES ('29999', 'Stephen Thornber', CURRENT_TIMESTAMP, '999', '', NULL, 'Test Site 999', 'Test Business 999', NULL, NULL, NULL, NULL, NULL, NULL, 'LE9 9NN', NULL, NULL, NULL, '[email protected]', '52.6291468', '-1.343625', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Special 999 notes ', 'SKT’)]

I have set a button on the form. this actions the script resulting in two actions windows shown here https://www.dropbox.com/s/z25t58nr7oofn1c/Screenshot 2015-08-03 10.04.12.png?dl=0   These are then followed by the UID/PW screen and then a moment later the error screen.

Any ideas pleaase.

Stephen

 

 

Link to comment
Share on other sites

Not sure if that is the issue but you have a mix of "fancy" single quotes and straight ones.

Also not sure whether "CURRENT_TIMESTAMP" would work... try with a real timestamp to test.

Are Lat and Long fields defined as text or numbers on the MySQL side?

Link to comment
Share on other sites

Well, after many experiments with code to the Db the Insert Script works.  Your comment about timestamp and '  "  ` etc was bang on the money. I also noted that NULL values in the database seem only allowed as , '', this led to me checking the table types in more detail. Probably should have done that first just needed the clues.

So Text can be passed via "Text like this" numbers like '1234' or '-1.234' or '23.1234' and dates with time 'y-m-d 00:00:00' or y-m-d'

the final entry looks like this:

INSERT INTO `defibcfr_backenddb`.`location_entry` (`ENTRY_ID`, `AUTHOR`, `CREATION_DATE`, `LOCATION_ID`, `NAME`, `TYPE`, `SITE_NAME`, `BUSINESS_NAME`, `ADDRESS_SUMMARY`, `BUILDING_NAME`, `STREET_ADDRESS`, `NEIGHBORHOOD`, `CITY`, `STATE`, `POSTAL_CODE`, `COUNTRY`, `CONTACT_NAME`, `CONTACT_PHONE`, `CONTACT_EMAIL`, `LATITUDE`, `LONGITUDE`, `WEB_ADDRESS`, `TRAINING_DATE`, `RETRAINING_DUE_DATE`, `TRUST_NAME`, `LOCALITY`, `FLAGALL`, `EmailCheckSentDate`, `ProbNotes`, `UpdateCheck`, `UpdatedDate`, `SpecialNotes`, `WhoUpdated`)

VALUES (309995, 'Stephen Thornber', '2015-08-03 10:10:12', 5678999, 'errName', '', 'Test Site 999', 'Test Business 999', '', '', '', '', '', '', 'LE9 9NN', '', '', '', '[email protected]', 52.6291468, -1.343625, 'www.address.co.uk', '2015-08-03 10:10:12', '2015-08-03 10:10:12', '', '', '', '2015-08-03', '', '', '2016-08-04','Special 999 notes','SKT')

THANK YOU.

 

Link to comment
Share on other sites

This topic is 3160 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.