Check out ESS in filemaker 9 and above which supports direct connection to MYSQL, MSSQL & Oracle.
There are several white papers and it is in the help file. Works good in many cases.
Tim is right on. Use External Sequel Sources to put the MySQL tables in the Relationship Graph and make relationships that way. FileMaker makes shadow tables of the MySQL tables and they look and feel like they are inside of FileMaker even though the data is in MySQL.
As to how you do relationships, that depends on your solution. The MySQL table occurrence will be like any other FileMaker table occurrence for relationships. The biggest limitation is that you can't change MySQL table schema from inside of FileMaker. So if you need a new field in MySQL or need one indexed, you have to go back to MySQL to do it.
I'm more of a Mac guy and I have RazorSQL on my Mac development machine so I can make ODBC connections to MySQL database as well as FileMaker databases and use it to develop SQL calls.
FileMaker's ESS makes MySQL integration very slick. However, there are performance limitations and native FileMaker data will always perform faster than external data (which is probably a true statement of just about any database).
One thing nice about Windows machines is that the ODBC drivers are mostly free. If you use a Mac to host FileMaker Server, you'll need the ODBC drivers from Actual Technologies which are not cheap for a server ($399.95) to connect to MySQL. The Actual Technologies drivers work great, but they are far from free.
Bought the drivers already. I did not think it was too expensive, but that is me.
I will check out RazorSQL. I am also a Mac guy.
Relating two ESS databases directly in the graph seems interesting. I will see what it does anyway. This ESS stuff is pretty handy. Mostly I like having a nice iOS GUI for MySQL data.
Great on being a Mac guy too. Unfortunately many of my customers are Windows users, but its all good. I try to convert as many as I can <grin>. I guess the $400 for the Actual ODBC driver wouldn't bother me so much if it wasn't that it often doesn't cost anything on the Windows side. But they do a good job and I've used Actual ODBC drivers for a long time and they keep them updated and working well.
RazorSQL is one of many different SQL tools for writing SQL reports. For playing around with SQL calls in FileMaker, we have the Data Viewer. Unfortunately, the FileMaker Dataviewer and Debugger are not on par with many other database development tools. Silly things like line number and coloring of text so you can see more easily what goes with what (e.g., looking for missing parenthesis, etc.). MBS plugin helps with FileMaker, but many SQL developers are used to tools like RazorSQL. So it is a good alternative to play around with SQL before plugging it into a script or calculation in FileMaker. These tools also work well if you're working in a multi-database environment to make reports combining all of them together. Unfortunately, many SQL tools don't have Mac support or if they do, it is not for FileMaker. My favorite Mac free SQL tool is SequelPro, but it does not work with FIleMaker. However, it is very useful for connecting to MySQL. RazorSQL is almost as good as Sequel Pro and it does connect to FileMaker through a JDBC connection. RazorSQL really connects to almost any database you can think of. The list is pretty amazing. The only drawback is that it costs $99. The only other SQL tool I know connecting to FileMaker is Squirrel, but I could not get it to work.
This is all getting really interesting dealing with multiple databases. Just getting to the right record in a ESS database is tough. Moving through a table with globals sounds like the answer except when it is not. Being directly connect to one works fine two is a chore.
From what I can see there is no way to set a field in an ESS database with ExecuteSQL because there is no way to find the correct record and field name with that function. Seems like I am stuck doing a find in a floating window. Not so cool. Maybe I can fix this with just putting hidded fields on the layout. Linking this all together has got me thinking too hard.
ExecuteSQL calcluation supports SELECT calls only and does not do INSERT, DELETE, UPDATE, etc. However, the ExecuteSQL Script Step supports all of the SQL functions and you can update records with that script step via the ODBC connection. You know which record to update by managing primary keys for records and relationships between primary and foreign keys. And FileMaker scripting does not require a field to be on a layout for it to be updated with a FileMaker Set Field script step. There is a lot to learn on how SQL works, but it is powerful and will do a lot more than you think. But keep in mind that using SQL is not necessarily faster than using traditional FileMaker functions and scripts. Sometimes it can even be slower. So you need to learn to combine the best techniques to accomplish what you want done.
You keep jumping to the conclusion that you can't do things when that is usually not true. Give us some specific examples of tables and relationships or a sample attached file and I'm sure we'll be able to give you ideas on how to make it work.
When I want to use the Execute SQL script step it needs the drivers installed on the FMPro client in order to select the correct database source. How does this work on FMGo? The documentation just says Web publishing and sceduled scripts are restricted from running it.
I will have to figure out the balance in the speed. With Filemaker I can offload some of the tasks to the server without wait. Execute SQL script cannot run server side.
I'm still not sure why you need to use the ExecuteSQL script step, but you are correct it is not compatible to run on the FileMaker Server as a scheduled script. However, there are plenty of other plugins that can do this for you. I personally use the Monkey Bread Software plugin on my FileMaker Server, but there are plenty of other plugins supporting all SQL calls (INSERT/DELETE/UPDATE/ALTER). Whatever you are trying to do can probably be done without SQL via normal FileMaker record/field modifications including on MySQL tables via ESS.
For testing have setup a filemaker solution that has one table. This is related to two MySQL ESS database.
The MySQL databases reflect two web deployments that get information from Filemaker.
If the "qty" field in filemaker is changed I need the corresponding "qty" field in the other databses updated. If the "qty" is updated in one of the MySQL databases Filemaker and the other ESS database need to be updated.
The "qty" field in the MySQL databse is in an attributes table of various keys and values that are related to the main record by a keyed field.
I am having trouble getting filemaker to reach out to the ESS database to the current related record and then across the keyed relationship in the MySQL database to the table with the "qty" value.
I can get this to work no problem if I script a new window that goes to the layout and performs a find for key value I that relates directly to the Filemaker record. Then the key for the attributes is stoerd as a variable and a find is run to find the record where the relationship key and the attribute key are both present. I can then set the field with the proper value and close the window.
This takes a long time though and seems like there should be a better way to do this. I can send it off to the server I guess so the clients dont get stuck waiting, but not really what I want to be doing as in practice we still have people running on 12 that need plugins that are not ready for 13 yet. So they are stuck waiting anyway.
If you're in a layout and need to go to a MySQL database to do a find to get the key, that sure sounds like something that would be done with a join where you store the MySQL key right into FileMaker. Of at a minimum something that can be accomplished with an ExecuteSQL select statement. If you have to make a change in the MySQL table and you're averse to using FM 13 features or plugins, then, yes, you have to go to the layout to do the update. FileMaker will go faster if you do not open a new window but instead just change layouts. And if this is something that needs done in the background where the user doesn't see anything, then create a layout for that table that has not fields in it and it will go a lot faster too. Then return to the orginal layout. Better yet, if you have a batch of them to do, don't jump between layouts... carry over an array of the records to be updated, update them and return to the original layout. There are all kinds of ways to optimize things.
I am not seeing how to make the relationship work to store that key in FM. The actual key I need is two steps away from FM in a table filled with all attributes from all records. The attribute record I need to change has a unique ID, but the only place that is stored is in the ESS attributes table. There may be up to 25 attribute records with the same main record key. So yes the main problem is the relationship for me. The attribute needs to be related by the id and the key in order to get to the correct entry.
I will keep trying things to get it to work. When using the Execute SQL script step this goes very fast, but my test file only has a few hundred entries in the attribute table.
"UPDATE attributes SET value=" & $qty & " WHERE id = " & $id & " AND key = 'qty' " is what is working for me. If I can manage to do this with a relaitnoiship that would be great, but I am not seeing it.
In a different example Filemaker has a database table of People including phone numbers, home, work, mobile, etc. The ESS database has a table of People with names and address only. The ESS has an additional table with phone number stored for all people using an "people_id" that is keyed to the People table, a unique "attribute_id" that acts as a record id in the phone number table, a "label" field, and a "value".
I can relate FM to ESS People with the people_id. I cannot figure out how to access a specific "value" in the ESS phone table with this.
If John Jones has a people_id of 12, how do I get to the ESS phone attribute table and change the phone number for people_id=12 and label="home" when all filemaker knows is the people_id?
The attributes show in a portal on the FM record so I know I have the basic relationship right. Maybe I need to store the attribute_id when the record is created.
I can set up another test file with the ESS databases connected and message you the login details if you are willing to look at it.
I set the attributes I need to have available in a filtered portal on my user layout. Making the changes via the relationship is pretty quick even when I used an attribute table with 10,000 entries. The first few data changes were slow and I got a Find in progress...Processing a Query message. Maybe it was just indexing.
I have given up on relating two ESS sources to eachother as the Attribute keys are different and only certain attributes need to match. The others need to remain independent.
I really needed to be working with a bigger table to get a sense of the speed.
Creating new records is still somehting I need to work out the process for. Maybe a plugin is the answer as INSERT and UPDATE would make things a lot easier to accomplish.
I do appreciate all the advice so far Taylor. You helped more than you might think.