3 Replies Latest reply on Oct 25, 2012 1:29 PM by Sorbsbuster

    Importing from ODBC

    catinthehat

      Title

      Importing from ODBC

      Post

            

           I would like to do the following in the Import records script step, with an ODBC data source.

           Basically, I only want to import the records that match the variable I set.I very much hope this can be done.

            

            


           SELECT "Grades"

           FROM "Students"

           WHERE "grade"."Students" > N'$MinGrade'

            

            

           ($MinGrade=65)

            

           Is this at all possible?

            

           Thanks

        • 1. Re: Importing from ODBC
          philmodjunk

               I would take a look at the entry in FileMaker help titled:

               Automating ODBC import using the Import Records script step
          • 2. Re: Importing from ODBC
            catinthehat

                 thanks, i actually missed that and it was very helpful.

                 However, I'm still not able to get the query to behave the way i want it to.

                 I tried storing the variable in the calc and I still get an SQL error.

                 Where I was setting the variable in the script now I set it in the calculation.

                 ::ODBC_Import =

                 "SELECT Age
                 FROM  Students
                 WHERE  Age > \"\"::MinAge\"\"
                 "

                 In the sql query builder I select the option "calculated sql text" and enter ::ODBC_Import

                 Thanks for the help

            • 3. Re: Importing from ODBC
              Sorbsbuster

                   You are trying to specify a 'variable' SQL.  Something like:

                   SELECT "MyField"
                   FROM "MyTable"

                   WHERE "MySearchField"."Students" > 'MyCriterion'

                   We do this by having a global SQL Field with maybe 10 repetitions.  We break it so the repetitions hold the parsed components of the SQL.  Then we build it up by calculation into the 10th repetition, using each repetition and the $Variables (say).

                    

                   It might look like:

                    

                   SGL [1]  = SELECT "

                   SQL [2] = "FROM "

                   SQL [3] = "WHERE "grade"."STUDENTS">'

                   SQL [4] = '

                    

                   $Variables:

                   $Grades

                   $Students
                   $MinGrade

                    

                   SQL [10] set to be  = SQL [1] & $Grades & SQL [2] & $Students & SQL [3] & $MinGrade & SQL [4]

                    

                   then we use SQL [10] as the result for the query.

                    

                   I'm sure I've built up the full SQL wrongly, but hopefully you'll get the idea.  (It's late in Ireland.  Sorry.)