4 Replies Latest reply on Jun 11, 2009 7:54 AM by TSGal

    using FM PRO as a SQL FE and authentication

    terraformer

      Title

      using FM PRO as a SQL FE and authentication

      Post

      I have a SQL db that I want to use fm as a FE for to allow reasonably knowledgeable users, although fallible, access to the raw tables for data corrections. I want to have them browse under a R/O account and then if they want to make a change, login for that change only. Is this possible?

      TIA,

      Tom 

        • 1. Re: using FM PRO as a SQL FE and authentication
          TSGal

          terraformer:

           

          Thank you for your post.

           

          Yes, this is possible, and I'll assume your next question is "How?".  There is a technical brief available from our web site about External SQL Sources, and I think it would be easier if you read this information first, as I think it will answer most, if not all, of your questions.

           

          http://www.filemaker.com/downloads/pdf/public_techbrief_ess_en.pdf

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: using FM PRO as a SQL FE and authentication
            terraformer
              

            I have been using FM pro for the FE for a while, so all of my DSNs are set up, etc. My problem is this. I use a DSN with a R/O user from the SQL database. If any changes are made, it throws an error. As a side note, I would love to trap that error and suppress it. But back to the task at hand, I would love to be able to dynamically make the form r/o or w/r. This way they can browse and not get any error since they can't inadvertantly change data, but then hit an edit button, auth as a R/W user, get a W/R form to edit with, then force them to save that record. After that, I would like to force them back to R/O automatically and lock the browse form from a script controlling this. It is easy for a user to alter data without much effort (hence why I want to have them browse R/O). I want the user to have to push a button to authenticate for that record only, then allow them to make a change, save it and then go back to R/O. 

             

            That edit button also allows me to deal with the inherent concurrency issues this setup brings.

             

            How do I, from a script, allow them to reauth to the database for that brief time? That and the form locking (which is admittedly less of a big deal) the part that I am struggling with.

            TIA,

            Tom 

            • 3. Re: using FM PRO as a SQL FE and authentication
              terraformer
                 BTW: Thanks for the link to the paper. 
              • 4. Re: using FM PRO as a SQL FE and authentication
                TSGal

                terraformer:

                 

                Thanks for the additional information.

                 

                The function "Get (LastODBCError)" returns a string that shows the error state published by "ODBC standards".  Also, "Get (LastError) will return 1408 if there is an error while performing a SQL query, and 1409 if there is no information about the error.  You can trap for these errors if you have "Set Error Capture [On]" in your scripts.

                 

                You may also want to look at Chapter 8 of the "ODBC and JDBC Guide" - "ODBC and JDBC Error Messages":

                 

                http://www.filemaker.com/downloads/pdf/fm10_odbc_jdbc_guide_en.pdf

                 

                Let me know if you need additional information.

                 

                TSGal

                FileMaker, Inc.