SQL Select? Where does the data go?!

[Skeeve Stevens]

OK

I am new to SQL... and I think I am doing pretty well. I have a mySQL server running on Linux with a database setup and the myODBC working happily.

I wrote a script which can do a insert of data from Filemaker over to the mySQL server... and it works great.

Now... I can execute a SQL query - such as a SELECT blah from blah; and the SQL statement is working fine on the server... and the server is giving back to Filemaker the result... but.. what is Filemaker DOING with that result?

How do I do a select and get that data into a field of a record? It is driving me nuts!

If anyone can help... PLEASE PLEASE do... (email copy and post here please)

...Skeeve

[email protected]

[Veselko]

Now... I can execute a SQL query - such as a SELECT blah from blah; and the SQL statement is working fine on the server... and the server is giving back to Filemaker the result... but.. what is Filemaker DOING with that result?

How can You tell that server is giving back to FM result?

[Skeeve Stevens]

Because I am watching the SQL log files on the mySQL server and I see it giving the correct responses... and the other queries (importing) work fine.

I just don't know on a normal ExecuteSQL SELECT query, where the actual result goes?

...Skeeve

[-Queue-]

I could be wrong, but I do not think you can perform a query using Execute SQL and return the data to FileMaker. Execute SQL will operate on SQL tables, but that is it. You have to use an Import Records step to pull SQL data into FileMaker.

[Skeeve Stevens]

You cant return the data to filemaker? you have to be kidding! that would be the dumbest thing FM ever did.

I want to have a field... think of something simple like a surname... then hit a button, and then populate a bunch of fields - global or otherwise.. with the data extracted from a mySQL database... That just has to be possible.

I could do it via clicking the button, going to another table, importing all records matching, then bringing the data back across.. but that seems so long as slow to do it that way. There has to be an easier way using the ExecuteSQL function and getting access to the data that is returned... there just has to be.

...Skeeve

[-Queue-]

If you read the online help regarding SQL, you'll see that Execute SQL is purported to

'Execute any SQL statement. You can use this script step to manage data in a foreign data source through an ODBC SQL interface. This provides more control over the FileMaker Pro interaction with ODBC data sources.'

There is no automatic interaction with FM implied here. If you want to import the results of a query into FileMaker:

'If you're importing into an existing FileMaker Pro file, choose File menu > Import Records > ODBC Data Source, and then choose your data source. The SQL Query builder dialog box appears (you might see a password and user name dialog box first).

If you're creating a new FileMaker Pro file from the data source records, choose File menu > Open. In the Open dialog box, choose ODBC Data Source for Files of type (Windows) or Show (Mac OS), and then choose your data source. The SQL Query builder dialog box appears (you might see a password and user name dialog box first).'

So, it seems clear that you use either an Import Records or Open to import the results of an SQL query into FileMaker. Execute SQL is used only to execute a query on an SQL table.

I do not think this is necessarily dumb or ridiculous. I think it makes sense because FileMaker is not an SQL-based database and makes no claims to be wholly compatible with one. It merely has a way to interact via ODBC with SQL tables to perform certain actions. I think we are lucky that it even possesses the minimal capabilities that it does for this.

There are some plug-ins, I believe, that expand FM's inherent SQL capabilities, but I have not personally used any for version 7 and am not aware if there even are any currently developed for version 7.

[Veselko]

PDM plug in can help you. It contain lot of functions (like SQL-execQuery ), and with combining few of them, you will probably find a solution for your problem. I have something like that (customer push the one button, and data from several tables from MySQL bring to same tables in FM app ).