ExecuteSQL count of external sql table not working (count with relationship works)

[bac mac]

I need some info from many tables from an SQL system and I have ODBC working and I can use the data through relationships with filemaker commands and almost everything is fine.

 

I'd like to count the records in various SQL tables without setting up relationships and I've tried a large number of variations and searched the posts and web with no luck.

 

For example In a calculation:

ExecuteSQL ("Select Count () from Dflt.epCLC_Step3_RAN50";"";"")

yields a ?

 

This is a simple table of one field of scrambled IDs, just 50 records.

 

Eventually, I plan to have the table specified by a filemaker field to present record counts for different tables that might be of interest.

 

What do I need to do to get a record count without establishing a relationship?

 

[eos]

ExecuteSQL ("Select Count () from Dflt.epCLC_Step3_RAN50";"";"")

yields a ?

 

That's because your syntax isn't correct; you need to count something, even if it is anything (note the asterisk):

ExecuteSQL (" SELECT COUNT ( * ) FROM "Dflt.epCLC_Step3_RAN50" " ; "" ; "" )

assuming that Dflt.epCLC_Step3_RAN50 is the name of an existing TO.

 

You need to escape the TO name since a dot is a delimiter in SQL syntax; best to get hold of some Custom Functions (or write them yourself) that not only protect your FileMaker element references against schema changes, but also preemptively escape those names for use in a SELECT statement.

[bac mac]

That solved it instantly!

I had initially used the * and I also had tried escaping the table name, as well as too many things to mention, but never hit the correct combination.

 

Now, I just tried playing with getting the TO name entered into the calculation from another field, and I tried escaping that as well, but can't get that to work.

I also tried using the ?:

ExecuteSQL ("Select Count (*) From ?";"";"";Table Name)

Table Name is a field in the current table.  I suspect I have to add a calculation to generate the appropriately escaped TO name.

 

Do you have a suggestion for that syntax, with the TO name is in a field called Table Name?

 

Thank you very much,

-Bruce

[eos]

Do you have a suggestion for that syntax, with the TO name is in a field called Table Name?

 

I think you cannot use the optional ExecuteSQL parameters for table or field references.

 

But the SELECT statement you pass is simply a text string, so write:

ExecuteSQL ( " SELECT Count (*) FROM " & Quote ( Table Name ) ; "" ; "" )

btw, you don't need to store the table name:

Let ( [
to = GetValue ( Substitute ( GetFieldName ( anyFieldFromYourTable ) ; "::" ; ¶ ) ; 1 ) ;
~sql = ExecuteSQL ( " SELECT Count (*) FROM " & Quote ( to ) ; "" ; "" )
] ;
ExecuteSQL ( ~sql ; "" ; "" )
)

which has the added benefit that you work with a TO name, which it was ExecuteSQL actually uses as reference.

 

As you may imagine, it would make sense to put the logic for querying the TO name (and wrapping it in quotes, while you're at it) into a Custom Function; change the GetValue argument to 2 to get the field name.

Also, since you're referencing a name from the internal FileMaker schema, changing the field or TO name won't break any existing SQL statements.

[bac mac]

That totally does it.  And I greatly appreciate your speedy replies.

I never would have come up with that syntax.  I don't think I've ever used the Quote function.

 

In my current incarnation, I have Table Name fed by a drop down list populated by a field calculation:  TableNames("") and now, when I select the desired table, I instantly see the total record count in the SQL table desired.

 

I'll investigate the other points that you mentioned.

 

Thank you!!!

Bruce