2 Replies Latest reply on May 22, 2009 8:41 AM by TSDiva

    Execute SQL

    SDS

      Title

      Execute SQL

      Post

      Looking for some help re: Execute SQL script step FM10.

       

      I have used this script step to write data to another application in previous versions of FM 7,8 &9. It has worked well before and I was able to construct an SQL calculation up to 30000 characters in length. I have recently upgraded to FM10 and now find I can only construct the calculation up to 3000 characters otherwise I get an ODBC error message which says (ODBC Error). I checked the Get(LastODBCError) but nothing was returned. Get(LastError) returns Error:4209 which seems to be a generic error message.

       

      To wrap up if I open up the solution with FM9 and run the script with the Execute SQL script step all is good. When I open and run the same script in FM10 the write fails and the error message returned is (ODBC Error).

       

      Q. Does anyone know what has changed with this script step in FM10?

       

      Thanks

        • 1. Re: Execute SQL
          Solaris
            

          You are not the only one.  I reported this a couple months ago.  Hoping they get a fix for it. 

           Others have stated this exact problem:

               http://fm.lithium.com/fm/board/message?board.id=aut&message.id=1632&query.id=27202#M1632

               http://fm.lithium.com/fm/board/message?board.id=aut&message.id=2396

           

                

          • 2. Re: Execute SQL
            TSDiva
              

            In FileMaker Pro 10 for select, delete, and other DBMS specific statement, it doesn't support statement lengths greater than 4096 characters. 

             

            There seems to be 2 different issues:

             

            First, the total query length is now limited to 4096. If you run a query which is not an INSERT or UPDATE query, then this is the limitation.

             

            The second case is for INSERT or UPDATE queries. If the total query length is less than 4096, then there is no problem. However, for INSERT or UPDATE queries, it is very likely that the query length will be longer, as long text data could be sent as parameters. In this specific case, we are extracting the text parameters and send them separately from the rest of query. The query without parameters is still limited to the 4096 characters lengths, parameters themselves can be as long as they want. However, we have encountered some limitations coming from the drivers, and they concern the data this time, not the query.

             

            I hope this information helps.

             

            -TSDiva

            FileMaker, Inc.