14 Replies Latest reply on Jun 15, 2009 10:31 PM by Ice72

    FM10 AdvSrv - JDBC questions



      FM10 AdvSrv - JDBC questions

      Your post



      I have a FM10-file which have an auto-inc PK field ("id").  

      With JDBC I could insert a record without specifying the "id" field (so FMSrv

      must generate it).


      How can I detect the generated ID, via SQL?  I don't want use MAX() function

      due to concurrencies problem ...


      Another question: is there any way to lock a table (like "LOCK <tbl>" SQL statement)?



        • 1. Re: FM10 AdvSrv - JDBC questions



          Thank you for your post.


          One of the features of FileMaker Pro is to have an auto-enter a serial number into a field when a new record is added.  The MAX function would be the correct function, but if you are concerned about concurrencies, you may want to invoke the WHERE clause for the record just added.  For example, assuming your table is named Contacts, if you had:


          INSERT INTO Contacts (last, first) VALUES ('Smith', 'John')


          The next statement could be:


          SELECT MAX (Contacts.id) AS maxid FROM Contacts WHERE Contacts.last = 'Smith' AND Contacts.first = 'John'


          This should minimize the possibility of concurrencies.


          I cannot find any reference to LOCK in the documentation.  What are you trying to accomplish?  Why do you want to LOCK the database?



          FileMaker, Inc. 

          • 2. Re: FM10 AdvSrv - JDBC questions



            I agree with  your "tips", but it slow down the search.  If I could lock the table, before INSERT, and execute a MAX,

            I coud bypass concurrency problem.


            In every case I have noted that MAX(id) is very slow on table with >100,000 records (some minutes to execute

            the query on a powerful Win2003 srv).  The table have only the fields: "id" (auto-inc) and "v" (integer).


            Could you confirm me this performance?



            • 3. Re: FM10 AdvSrv - JDBC questions
              When I need to do this, I generate an unique string on the java side and use it to select the created row.
              This should be much better in performance.

              1) Generate a unique string that can distinguish the new row from others.
              String uid = yourMethodToGenerateUniqueString();

              2) Insert new record along with the unique string.
              "INSERT INTO Contacts (last, first, uid) VALUES ('Smith', 'John', '" + uid + "')";

              3) Select the inserted row using the unique string.
              "SELECT id FROM Contacts WHERE uid = '" + uid + "'";

              Hope this helps
              • 4. Re: FM10 AdvSrv - JDBC questions



                >String uid = yourMethodToGenerateUniqueString();


                but ... "myMethod" doesn't exists!  I defined an auto-inc integer column, in FM, for this scope!

                • 5. Re: FM10 AdvSrv - JDBC questions
                  As the mehod name implies, you have to define it by yourself ;)
                  For most cases, a simple method like the following would be sufficient.

                  /** Returns an unique string */
                  public static String yourMethodToGenerateUniqueString() {
                    return System.currentTimeMillis() + "_" + new java.rmi.server.UID().hashCode();

                  And you will need to add the text type field 'uid' in your Contacts table.

                  You want to get the generated id, but you cannot guess the value before inserting the new row, right?
                  So, I proposed using an alternative temporary key just for retrieving the generated id.
                  Sorry if it does not meet your requirement.
                  • 6. Re: FM10 AdvSrv - JDBC questions
                       Ok, but I have a problem :robotsad: : I can't change the production DB structure, to add a "temp_PK", as you suggests ...
                    • 7. Re: FM10 AdvSrv - JDBC questions

                      What about selecting mutiple fields to create a unique enough search?


                      I am doing something very similar, what I do is insert stuffs, then pick about 5 fields where the combinations thereof create a unique enough query that it shouldn't be returning duplicate records. If you include every field you're inserting as the ultra long search then that should return the original inserted record with the auto increasing value.  It's just an annoyance to do that for every record you insert and wish to get it unique id back. 


                      Under my ODBC tests, the return time for such a query is small enough that it's acceptable.  Avg ODBC return is around 0.1 second ish from my tests.


                      BTW, I originally tried the MAX() function and a local copy of the DB on my computer for testing just about died.  It got about 8.7M records in it.  So any of those functions is a massive I/O drain that make it unrealistic for anything beyond small number of rows.

                      • 8. Re: FM10 AdvSrv - JDBC questions

                        Multiple fields match is a solution, but I can't use it because I can't risk a collision between two or more records.


                        About the MAX() function I'm surprise of performance (your and mine).  With a PostGreSQL server, on a low

                        level PC, a SELECT MAX() is executed in 0.05ms on a table of 1,230,000 of records ...  


                        Your 8.7MB data-set is completly fill into RAM (or, with the modern CPU, in the 2nd or 3rd level CPU cache!).

                        In my test I haven't noted any I/O, but only a hight CPU usage (my FP file was 14MB ... not GB!).

                        • 9. Re: FM10 AdvSrv - JDBC questions

                          If you do All Fields matching then you shouldn't be running into a collision.  And if you're inserting 2 records with like 20 fields that are all exact duplicates... well then ya got other problems...


                          My database is 8.7 million rows, about 3.8 GB.   I was hosting it locally on one of the newer iMac. The problem appear to be something with the way FileMaker indexes.  I can do the samething on MySQL and it wouldn't take very long to return an indexed unique numerical value with B-Tree indexing.  FM seem to build the index the first time, and do som other funky stuffs later.  I tried it once, and it was doing something with a progressbar for aobut 40 minutes...  Needless to say I quit the MAX() approach and went with fields check...


                          • 10. Re: FM10 AdvSrv - JDBC questions

                            Misreaded!  8.7M of rows not of bytes!   Sorry.


                            I noted that also searching a record on a 300,000 records ultra-simple table (id and txt fields), via PK,

                            I obtain a long wait  (2min).  


                            I'm thinking that if I have a long wait with a single field select ("... WHERE id=?") I suppose that I will obtain

                            an extra-long wait if I will match all fields ...

                            • 11. Re: FM10 AdvSrv - JDBC questions

                              On 300K records, you shouldn't be seeing 2M wait.


                              I have a few tables that are very large and my selection is relatively decent if I'm doing a small amount of record searches.  I can get around 1000 rows/seconds out if the table isn't linked to anything.  I use ODBC to connect, but the difference with JDBC should be very minimal.


                              What is the nature of the text you're searching and what is the table linking structure?  Getting a record from FM trigger all it auto calcs and links for that row.  If you do a SELECT * FROM X and a row is heavily calc on linked then that would explain your 2 minutes wait time to pick up a  1 row.  

                              • 12. Re: FM10 AdvSrv - JDBC questions



                                there isn't any calculated fields. The table is ultra-simple, with 3 fields: id,txtVal,intVal



                                • 13. Re: FM10 AdvSrv - JDBC questions

                                  If you're on a mac, try loading the odbc drivers and use iodbctest to go in and query and DB.  That will let you check to see if the error is localized to the table/database, or if it's a problem with the JDBC driver.


                                  On the ODBC driver on OSX, I can get a pretty decent 250+ records a second where the records have about 15 fields with no calculation on a large query fetch of like 10000+ rows.

                                  • 14. Re: FM10 AdvSrv - JDBC questions

                                    I'm trying to connect a client from a JDBC+WinXP-PC to a FM10+Win2003srv.


                                    My slowness (2min wait) is due to the execution of the "SELECT ... WHERE id=?" on the server side 

                                    (CPU at 100%).  I'm reading only the selected record.