1 Reply Latest reply on Oct 21, 2013 8:35 AM by bvondeylen

    ExecuteSQL Issues



      ExecuteSQL Issues


           I have a FileMaker Database 'connected' to a MS SQL database (our student information system Infinite Campus). I have an ODBC connection created, and I have tables from the MS SQL database displaying in my FileMaker database.

           I am now attempting to use ExecuteSQL commands to retrieve records that I cannot get easily using database joins within FileMaker.

           So, using FMP Advanced, and the Monitor Tool, I am trying to use some SQL Commands to see what I can get.

           since all the tables in Infinite Campus begin with dbo.<table> (eg dbo.student, dbo.teacher, etc), I left things as they were and tried the following:

           ExecuteSQL("SELECT * FROM dbo.student";"";"")

           Which gave a result of ?

           Frustrated, I renamed the table occurance to student then tried

           ExecuteSQL("SELECT * FROM student";"";"")

           Which gave me an hour glass, a spnning wheel of death, and nothing for over 30 minutes. FIleMaker either 'locked up' or something bad happened.

           So 2 questions.

           Does FileMaker or SQL not like periods in table names (thus the ? when using dbo.student)?

           Why would FileMaker lock up with the 2nd command after changing the table occurance to plain student?

           When I look at the table in my layout, all the fields have data in them. Just trying to get SQL to work.

        • 1. Re: ExecuteSQL Issues

               So, after about 30 minutes, the results finally showed up (wow that takes a long time). So I tried displaying just 2 columns (lastName, firstName) and this time it went much quicker.

               So I did the next step and tried this SQL command

               ExecuteSQL("SELECT DISTINCT s.lastName, s.firstName FROM student s WHERE studentNumber = '37972' ";"";"")

               Now I am waiting again (it has been 10 minutes and counting).

               What is going on?