SQL Back to Basics

[kiwiora]

Hi,

 

I haven't had much to do with SQL, but with the inclusion of a new member of staff who's a SQL guru, I wanted to use Filemaker to be the front end to his tables.  Prove its worth as you will.

 

The issue is, the guides have been of no assistance.  I have created a connection to the SQL database.  I have then created a layout in filemaker where the underlying table is the SQL table.

 

I want to now be able to execute a search and return only those records that match.  I've simplified it down to no avail

Where $PersonID is the value of David

Execute SQL [DSN:ASPIRE; Calculated SQL Text:"select * from people where firstname = '" & $PersonID & "'"

I can run the query in SQL and return records where firstname = David, but for the life of me cannot do the same in Filemaker.  What am I doing wrong?  I have tried a Refresh Window after the excute.

 

Am I correct in how I've interfaced FM to SQL?  That is, create the DSN, then add it as an external source?

 

Any help would be much appreciated.

 

Thanks

[Wim Decorte]

The ExecuteSQL script step can not be used for SELECT, only for INSERT and UPDATE.

If you want to retrieve data from a SQL source you have to use the IMPORT script step and select the ODBC data source.

 

The other alternative is to use the ESS feature (External SQL Sources), that works with a few select SQL sources like MS SQL Server, MySQL, Oracle.  When you use that, you do not constructs SQL queries at all, the selected tables behave like regular FM tables so you use all the FM script steps and functions to interact with the data.

[Rick Whitelaw]

"The ExecuteSQL script step can not be used for SELECT, only for INSERT and UPDATE."

 

Wim, don't you mean just the opposite?

 

Rick.

[jbante]

"The ExecuteSQL script step can not be used for SELECT, only for INSERT and UPDATE."

 

Wim, don't you mean just the opposite?

 

Rick.

No, Wim got it right. The ExecuteSQL function can only be used for SELECT. The script step is good for everything but SELECT (everything supported by the target database, anyway).

[Rick Whitelaw]

Thanks Jeremy and Wim.

I obviously need to do some more studying.

Rick.

[Wim Decorte]

Yep

 

As an aside, if you already have set up the ESS part of it, then using the ExecuteSQL function to do selects on an ESS table is usually not a good idea from a performance point of view.

[beverly]

Also do not be confused by "Execute SQL" (script step) which does use a DSN and will INSERT, UPDATE, DELETE; and the "ExecuteSQL" (function) which will only SELECT and works with any table on the relationship graph. The space in the name is the clue to script step.