1 2 Previous Next 17 Replies Latest reply on Mar 3, 2017 8:09 AM by fmpdude

    SQL IDEs and FileMaker Compatibility

    rohrkemper

      Which SQL development tools are compatible with FileMaker 15?

       

      I've successfully connected with RazorSQL 7.2.3.

       

      I have some experience with Toad, SQLDeveloper, and SQL Workbench. Would any of these work, or are there other alternatives? I'm interested in compiling a list of alternatives, and at this point, don't need details about the advantages of each, though feel free to include that as well if you like.

        • 1. Re: SQL IDEs and FileMaker Compatibility
          fmpdude

          Razor is quite good and and, I agree, unfortunately, an external tool like this is necessary in my view for really working with data in FileMaker. The "Data Viewer"? Please. Forget it.

           

          I mainly use my Java IDE to connect to FileMaker (Intellij IDEA). That way, I have any JDBC code in a code window, a (Real) live SQL view, and the rest of my rich IDE.

           

          I have never compiled a list of JDBC-enabled database products since any product is only as fast as FMP's SQL performance, which, for some queries in relatively small data sets is not acceptable for enterprise applications I work with. For example, in only a 500,000-record table, a LIKE query (directly in FMP or via JDBC) can take up to 6 seconds! In MySQL, it's about 0.1 seconds. Or, in other words, FMP takes 59 times longer.

          ( (6-.1)/.1)) = 59.  Don't mean to sound negative, but that's my experience.

           

          In the cases where I do SQL in FMP, which are limited due to the query performance in FMP, I would use the external SQL tool (really really needed) to prototype the query against the live FMP database. Once the query works, I would then move the query into FMP (avoiding the silly, unhelpful "?" messages and instead enjoying the IDE's real-time SQL code assist, etc.) to add replaceable parameters or otherwise finalize the query.

           

          Below is a graphic I created to show the capabilities of a "real" database viewer as compared to the mostly brain-dead "viewer" that comes with FMP. This IDE's data "tab" is only one very small part of the IDE's capability.

           

          (Note: If I wasn't already paying for an IDE, I would buy Razor. It's the best I've seen.)

           

          HOPE THIS HELPS.

           

          1 of 1 people found this helpful
          • 2. Re: SQL IDEs and FileMaker Compatibility
            rohrkemper

            Thanks fmpdude, that helps a lot.

             

            Would it be possible to use Eclipse? I would prefer that if possible. Otherwise, I'll look more closely at Razor. Could you help me to get started with the setup or provide some details about your configuration?

            • 3. Re: SQL IDEs and FileMaker Compatibility
              fmpdude

              (If you're a Java developer...) Eclipse is free, and quite good, but in my view doesn't compare with Intellij's IDE. Intellij also has a free "community edition". You might want to check that out. If you're not a Java developer, Razor is probably a better option since you're not getting tools you don't need.

               

              -----

               

              To get any of these remote SQL tools to work, you just need to "tell them" where FMP's JDBC driver is -- usually in the install directory of FMP.

               

              Then, you need to allow JDBC/ODBC connections in the FMP application (all users or by privilege set or particular user).

              Then, you need to create a connection to your live FMP database (which must be open, and unfortunately AND ON THE SAME MACHINE (ridiculous FMI restriction), unless, of course, you have FMS).

               

              A JDBC connection string looks like the one below, but could use any IP, anywhere, but only FMS can be on a different machine):

               

              jdbc:filemaker://localhost/<NAME_OF_FMP_DB>

               

              I'll be glad to help if you have questions or run into problems. Just post back, OK?

               

               

              • 4. Re: SQL IDEs and FileMaker Compatibility
                rohrkemper

                I checked the feature comparison list and there it says I need the Ultimate edition for SQL. Is that also your understanding?   IntelliJ IDEA the Java IDE

                 

                Database Tools, SQL has a checkmark only under Ultimate and not Community.

                 

                It does look like a slick interface, though.

                • 5. Re: SQL IDEs and FileMaker Compatibility
                  fmpdude

                  If you're a Java developer, it's a simple choice. Get the best IDE with a data viewer that can connect to FMP (a multi-hundred dollar database tool) and work like FMP's data viewer "should" work. Intellij is extremely powerful, but at times, not that friendly.

                   

                  There's also "MyEclipse" which has a powerful data viewer, but it's not free. MyEclipse (MyEclipse :: The best Java EE IDE enhanced for the full stack developer ) is about $65/year for the pro version. It's very, very good and more friendly than Intellij (yet lacks some of the power).

                   

                  Check out NetBeans, which is free. I've never liked NB, but that's just my preference.

                   

                  IMHO, if you start using Intellij, you'll never go back...

                   

                  HOPE THIS HELPS.

                  1 of 1 people found this helpful
                  • 6. Re: SQL IDEs and FileMaker Compatibility
                    rohrkemper

                    I'm not a java developer, but I have done some java in the past. I won't need java now.

                     

                    But I am interested in getting Eclipse or MyEclipse set up. I did a few tests in both and I don't see how to add a new driver for FileMaker since FileMaker is not in the list of drivers. I have my driver stored at this location: /Library/Java/Extensions/fmjdbc.jar

                     

                    Could you take a look and walk me through the steps? I've been looking at two different locations: 1) Preferences > Data Management > Connectivity > Driver Definitions and 2) Window > Show perspective > Database debug (right click for new connector).

                    • 7. Re: SQL IDEs and FileMaker Compatibility
                      fmpdude

                      TONS of examples online. Just search for "Add external JARs to Eclipse" (or other IDE).

                       

                      Like this one:  java - Eclipse IDE- Add jar? Add External jar? Add Library? - Stack Overflow

                       

                      (MyEclipse would be the same process.)

                       

                       

                      • 8. Re: SQL IDEs and FileMaker Compatibility
                        rohrkemper

                        The link you sent says I should right click on the project. I think I also need one step before this, namely what kind of project to create. Right?

                         

                        I also found this link but didn't get very far: how to setup JDBC in Eclipse? - Stack Overflow

                        • 9. Re: SQL IDEs and FileMaker Compatibility
                          rohrkemper

                          Never-mind, I already have it working in Intellij IDEA. Thanks again for your help!

                          • 10. Re: SQL IDEs and FileMaker Compatibility
                            rohrkemper

                            After the success with Intellij IDEA, I decided to try Eclipse as well. Now, I have a message Status: connected, but I cannot see any data in the Data Source Explorer. I'm using the connection "Generic JDBC_1.x" but with the FileMaker JDBC driver in the JAR List. I have one specially defined property which is "Driver Class" and set to the value "com.filemaker.jdbc.Driver". Please see the screenshot attached. I don't see any data under Schemas or elsewhere.

                             

                            This guide was extremely helpful: Managing databases with Eclipse and the Database Tools - Tutorial

                            And this helped me resolve an error of "Required property in driver definition missing value: Driver Class": database - Connecting to oracle enterprise 11 from eclipse with Service Name - Stack Overflow

                            I've installed this plugin: DBViewer Plugin | Eclipse Plugins, Bundles and Products - Eclipse Marketplace

                             

                            Do you have any ideas about this?

                             

                            Screen Shot 2017-02-23 at 2.18.18 PM.png

                            • 11. Re: SQL IDEs and FileMaker Compatibility
                              fmpdude

                              Try this link: Managing databases with Eclipse and the Database Tools - Tutorial

                               

                              If you can't get it working still, post a message to StackOverflow or to the Eclipse forum itself.

                              • 12. Re: SQL IDEs and FileMaker Compatibility
                                rohrkemper

                                That's actually the same link I paste in above. I found that tutorial really useful. And it says connected successfully, but I don't see any data inside. The same settings do work in Razor and Intellij IDEA. I'd really like to be able to compare those with Eclipse as well. Thanks for your help. I'll consider writing a post at SO and I'll leave this open in case someone else has an idea.

                                • 13. Re: SQL IDEs and FileMaker Compatibility
                                  fmpdude

                                  I would suggest you try MyEclipse instead. They also have a free eval period.

                                   

                                  If you can't get the DB access in regular Eclipse working in the next day or two, let me know and I'll get it working.

                                   

                                  I know lots of devs who use regular Eclipse, but when you see XML elements get auto-smart populated in Intellij with its other features (like native support for SpringBoot, which even MyEclipse lacked the last time I checked), and if you're getting paid to write code, Intellij for my $ is the best investment.

                                   

                                  HOPE THIS HELPS.

                                  • 14. Re: SQL IDEs and FileMaker Compatibility
                                    rohrkemper

                                    Hi fmpdude, I haven't been able to get it working in regular Eclipse. Could you please give it a try with the FileMaker driver?

                                    1 2 Previous Next