2 Replies Latest reply on Jun 25, 2014 2:57 AM by tomasd

    Using SQL Update

    mark_b

      Hi All,

       

      I am trying to implement a Drag and Drop from a listing (not a portal) and I have my global variables working correctly, marking the start ($$Drag) and end ($$Drop) records. All I need to do is place the $$Drop variable back in the $$Drag record "order" field. I'm using the OnObjectEnter script trigger to get the id into the $$Drag variable and the Auto Calculation to place the $$Drag value into the destination "order" field, then use OnObjectModify script trigger to get the record number for that field ($$Drop) so I can put that back in the starting record. I want to use filemaker SQL because I don't wan't to clutter up my Relationship graph.

       

      I'm having trouble with the SQL syntax for an update statement in the script step ExecuteSQL (as a script trigger). Here's what I have and it seems to run except the system wants an ODBC source. Execute SQL [UPDATE Course_Weeks SET order = $$Drop, WHERE WeekNumber = $$Drag]

       

      I have been using the ExecuteSQL function elsewhere successfully with something like this:

       

      Let ([

      ~TableName = GTN ( Get ( LayoutTableName ) );

      ~FieldOrder = GFN ( Course_Weeks::order );

      ~FieldWeek = GFN ( Course_Weeks::WeekNumber );

      ~SQL = "Update " & ~TableName & " SET " & ~FieldOrder & " = " & $$Drop & " WHERE " & ~FieldWeek & " = ?"];

       

      ExecuteSQL ( ~SQL ; ""; ""; $$Drag)

      )

       

      But this doesn't work at all (get the ? result). Perhaps because the Let statement works with the ExecuteSQL function and not the script step. So, where have I gone astray?

       

      Thanks in advance;

      Mark

        • 1. Re: Using SQL Update
          ch0c0halic

          I think line 3 of the help description addresses your question.

           

          Description

          ExecuteSQL enables you to execute SQL SELECT statements containing dynamic parameters to safely query FileMaker Pro databases in order to avoid security vulnerabilities through injection attacks.

          ExecuteSQL does not recognize relationships created in FileMaker Pro, which gives you flexibility to define relationships in SQL statements and retrieve data from any table, independent of the layout context.

          ExecuteSQL cannot be used with SQL statements that modify data or the database schema (such as the Insert Into or Delete Table commands).

          If an error occurs during query parsing or execution, FileMaker Pro returns ?.

          • 2. Re: Using SQL Update
            tomasd

            Execute SQL script step is for querying external ODBC data source.

            ExecuteSQL is function for querying Filemaker DB and does not support UPDATE, DELETE.

             

            If you want to use UPDATE SQL command on Filemaker data, you can install SQL plugin:

            http://www.dracoventions.com/products/2empowerFM/family/sql.php