What is the actual name of the table occurence on the relationships graph that you're referring to as "table2"? If the name is a reserved word or if it contains spaces, you'll need to enclose it in double quotes in your SELECT query.
Another approach you could take would be to use scalar subqueries in an UPDATE via the Execute SQL script step.
Since it sounds like you already have your MySQL tables on the relationships graph using ESS, you could also use a more traditional FileMaker approach without writing any SQL. You'd need to script a find in each table and then use a group of Set Variable and Set Field script steps to copy the values from one record to the other. Or, you could script the finds and do an import with the "Update matching records" option.
One advantage of not using the Execute SQL script step is that you could run the script on a workstation that doesn't have the DSN configured. Only the host computer requires the DSN when using ESS.
thanks a lot for your solutions,
actually the name table in my relationship Graph is
and the name of EES ODBC commection is
tha is connected by DSN system on a mysql server 5.1
i was reading somewhere that my table should not have relation in my relationship graph but i'm gonna check this teory.
in the mean time i have also made your second solution that use the script step to chek the oring and destinetion record setting up 2 flag variables. after this first part of the script i use the flags to manage
the Execute SQL script step that works perfectly.
this stuff work but is not what i want looking for, becaus i was thinking to perform all the initial-task in back.ground. in this way i can see when the form change state and perform the find etc etc...
in this case i have not that advantage because all my tables are on a my sql server and all my clients use the file solution db.fm12 with the local DSN ( same for all clients ).
there are no data stored in the db.fm12, just 1 local table with global variable.
in this way the client can work without problem when i update the solution.
i was thinking if the proble is the name of the table that i have to specify in the query, because instead than in the Execute SQL step , in the function i can't specify the ODBC connection name.
i have tried also something sputid like this as table name :
but of course doesn't work.
i have tried also some external plug-in... same issues.
thanks a lot
i'm running some test and it seems that there is a bug regarding the name table
if i rename the table in pratiche_ditte it works!
if i put the numers at the end works either like pratiche_ditte_3_1
the function doesn't worl if i start the table name with a number
you was talking about encolse the name table in double quote '' in the query but ''3_1_pratiche_ditte'' doesn't work either.
have you some idea??
i would like leave the number_text notation for my tables is very useful
the problem was the name table, when it start with a number you have to add the double quote, like Greg said.
my problem was in the editor because i have to use \ in the string before the double quote:
runSQL ( "SELECT * FROM \"3_1_pratiche_ditte\"";",";";" )
work perfectly, very fast in this way i can cut my first script of over 10 steps
In the Help files, they talk about not naming fields to start with a number. I don't seen anything specific about doing the same with tables, but it kind of makes sense that this would carry through to tables.
Glad to see that you resolved the issue by quoting your table names.