Execute SQL

[mcotton]

I need to use data retrieved from an Execute SQL script step. My SQL query count the number of records according to a condition, the result will be 0 or 1. How can I tell to FM:

if (numberOfRecords From SQL Table = 0) then do something...

Thanks!

[Todd Geist]

Hello,

When you use the Execute SQL Script step any results are returned to FileMaker through an Import.

So if you set up your select statement to return some records and execute it you will get an import records mapping dialog. You can then map the results from the Select Statement into your FileMaker fields.

After the import, as in any filemaker import, you will be left the found set of imported records. If your Select Statement returns no records then you will have a found set equal to 0 so

Get(FoundCount)  0

will tell you if any records where found by the select statement.

There maybe another option as well. It is possible that Get(LastODBCError) will return an error if no records were returned, but I haven't tested. Even if this does work I doubt it would be much of advantage.

One more idea...

If you set up a SQL Statement to simply return the number of records found. Then this single row and column can be imported into a global field where you could test it. And if there were records found you could do the "real" SQL step next. Again, I can't see too much advantage to this but I thought I would throw it out anyway.

hope this helps

t

[mcotton]

That helps, Thanks!

The better way I've found is to create a global numeric field that will act has a counter, I import into that my SQL COUNT and then I can do my validation and the rest of the script depending on what the counter give me...

[Todd Geist]

Yeah that is basically what I was talking about in the third option...

But I think it would only really be better if the percentage of times you had no records was higher then when you have some. If you almost always have records, then adding a validation before the import doesn't buy you much except another SQL query.

:>)