8 Replies Latest reply on Aug 23, 2016 4:22 AM by beverly

    ODBC and NULL Auto Enter

    bigtom

      According to this documentation FM returns empty strings as NULL into an ODBC SQL table, but when using an auto enter calc of "" for new records in the MySQL ODBC table it does not actually work. Any ideas on the best way to do this?

       

      ODBC limitations for FileMaker Pro | FileMaker

        • 1. Re: ODBC and NULL Auto Enter
          beverly

          MySQL will insert NULL if you do not put anything into the field (as FM sees it). Just don't INSERT into the field. "" is not necessarily NULL.

           

          Sent from my miPhone

          • 2. Re: ODBC and NULL Auto Enter
            bigtom

            That is not working as NULL is not set as the default in the column. Changing the column default would be easy but not a viable option of this project. The column has a default as None.

             

            As it is now on new record creation I get the following error:

             

            ODBC Error: [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.32-78.0-log]Field 'name' doesn't have a default value

             

            I also tried Char(0000) as well as "" (empty string) and still get the error. NULL and "" (empty string) are both valid in MySQL so I wonder why I am getting this error. Can I suppress it somehow?

            • 3. Re: ODBC and NULL Auto Enter
              beverly

              What does the column look like in Define Fields dialog? Maybe it is required to be NOT NULL?

               

              Sent from my miPhone

              2 of 2 people found this helpful
              • 4. Re: ODBC and NULL Auto Enter
                bigtom

                MySQL is in NO_ENGINE_SUBSTITUTION mode and not STRICT mode. Maybe the answer is in changing the configuration of the mode. I just hoped I could insert NULL but it is not happening.

                • 5. Re: ODBC and NULL Auto Enter
                  bigtom

                  beverly wrote:

                   

                  What does the column look like in Define Fields dialog? Maybe it is required to be NOT NULL?

                   

                  Sent from my miPhone

                  The NULL checkbox is empty so that would be NOT NULL. I guess natively MySQL just logs an error and moves along, but with ODBC the FM user is forced to deal with the error.

                  • 6. Re: ODBC and NULL Auto Enter
                    bigtom

                    When set to As Defined and empty string no error. Someone besides me will need to make the decision on allowing NULL or "". NULL would be preferred but not my decision to make. beverly Thank for the pointer. There used to be an actual NOT NULL selector, but now there is a little checkbox that I overlooked.

                    • 7. Re: ODBC and NULL Auto Enter
                      beverly

                      NOT NULL = needs something!  If you have control of MySQL then check that box.

                       

                      Sent from my miPhone

                      • 8. Re: ODBC and NULL Auto Enter
                        beverly

                        bt, it depends on the app/interface you use to manage the MySQL db. There are several apps (various OS). A common interface is the web-based PHPmyAdmin. It works in every browser & platform I've tested. Even that interface has changed over the years!

                         

                        If you don't have the access to make such a change (NOT NULL), then you've discovered one of the warnings of using ESS. It's best to have the ability to work with the external source or have good cooperation with those who do.

                         

                        Or be forced to enter "something", of course.

                         

                        Sent from my miPhone