8 Replies Latest reply on Jul 10, 2013 1:24 PM by disabled_JustinClose

    Simple ESQL not working


      Simple ESQL not working


           I am having troubles getting an apparently simple ESQL statement to work. 

           Here's my structure:

           Books  <bookID_Pk = bookID_FK >   BooksByAuthors   < AuthorID_FK = AuthorID_PK >   Authors

           Here's the statement:

           ExecuteSQL (
           "SELECT b.category, a.bookID_FK
           FROM BooksByAuthors as a, Books as b
           WHERE a.bookID_FK = b.bookID_Pk

              AND a.AuthorID = ? "
           ; "" ; "" ; 111 )

           Ultimately I am looking to get a list of IDs and categories for a single Author; Author ID should get me a list of books written by that author in BooksByAuthors, which should then be able to tie back into the Books table to get the category.

           I have tried many different forms (e.g. INNER JOIN, with/without table aliases, many many more) but keep getting a stupid question mark. 

           Been staring at this thing for far too long.  Time to rest the eyes.




        • 1. Re: Simple ESQL not working

               Maybe the seedcode SQLExplorer can help.   http://www.seedcode.com/cp-app/ste_cat/sqlxfree

          • 2. Re: Simple ESQL not working

                 Try putting quotes around table and field names such as: b.\"category\"

                 Without the quotes one of those names may turn out to be a SQL reserved word and this triggers the dreaded ? suntax error result.

            • 3. Re: Simple ESQL not working

                   Phil, Yeah, hadn't considered reserved words; double checked and there aren't any that I am using.  Darn.  That would have been simple.  :)

                   S Chamblee, I went and tried using SQL Explorer.  I added my table references and was able to do simple queries against a single table, but as soon as I tried to involve two tables I would get dreaded "?" response.  So no luck there yet.  I will keep trying.




              • 4. Re: Simple ESQL not working

                     Once you get two tables involved, you need a "join" clause specifying the relationship between them. And that is lacking in your original query.

                • 5. Re: Simple ESQL not working

                       I had tried manual inclusion of various forms of a JOIN.  One of those forms is in the WHERE clause I used above:  WHERE a.AB = b.BA.  At least from the examples noted in the Missing Manual at http://www.filemakerhacks.com/?p=6406.

                       Here's some output from SQL Explorer that I have tried (and it fails with a question mark):

                       SELECT a."aaaID", b."aaAuthorID", b."aaID"
                       FROM "Courses" a
                       INNER JOIN "BooksByAuthors" b ON a."aaaID" = b."aaID"

                       This is about as simple as it can be made.  I went ahead and renamed various fields to remove leading underscores, etc, but to no avail. 


                       --  J

                  • 6. Re: Simple ESQL not working

                         Hi J-

                         I am having the exact same problem.  I have tested everything in http://www.seedcode.com/cp-app/ste_cat/sqlxfree and it works fine.  The pasted formula in my database field simply returns the "?".  Is there something I/we are missing in the way filemaker needs to display the returned data?  Does it have to be in a portal, like in seedcode, or repetitions (although mine should return only one)?  I look forward to suggestions as do you i'm sure...




                    • 7. Re: Simple ESQL not working

                           YES!  I got it figured out.  

                           The problem was that the ID fields I was using in the two tables were of different TYPES:  one was a number, the other was text.  DOH!

                           Dealing with old architecture.  :)


                           --  J

                      • 8. Re: Simple ESQL not working


                             Sorry to hear you are having troubles.  Our problems sound slightly different as I wasn't able to get SQL Explorer to work either (which I guess is a good thing); having it work in Explorer but then NOT in the live system is really odd.

                             There is nothing special about how the returned data needs to be handled.  It is all in one data blob which you can assign to a field, a variable, whatever you can assign text-strings to.  What I often do is use the Data Viewer to test/create my ESQL statements; you can view the return results right there easily.  2emPower's Dev Toolbox (or something like that) also has a nice built in 'Evaluate' function that you can use to run ESQL statements through; one huge benefit to this tool is that you will often get an error code back and not just a simple "?" response.  The error code may not be helpful, but it is a little more helpful than "?".  :)

                             Can you post your query or screen shots of SQL Explorer?


                             --  J