Results 1 to 5 of 5

Thread: ODBC Insert

  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Arrow Unanswered: ODBC Insert

    I am trying to insert a record into a FM Pro table through ODBC. There are a lot of summary fields defined on this file. As soon as an insert command is fired FM Pro starts "Summarizing" all the summary fields in the file. There is a huge volume of records in this table and it takes forever for the "Summarizing" to run. When a new record is created in FM pro itself it looks like the summarizing happens so quick that I don't even notice it.

    My question is, Why does it take so much time (>1 hour) for the "summarizing" to run when inserting a record thru ODBC? Is there another way to do this?

  2. #2
    Join Date
    Mar 2004
    Posts
    3
    I am wondering if a work around is possible for the problem I stated in the previous thread. I don't know File Maker, but I have seen a simillar solution to a totally different problem. Please let me know if my idea will work or not.

    <B>Idea:</B>
    How about inserting this record into another (surrogate) file in the File Maker Pro database and have a computed column in the file fire a filemaker script that copies the data to the original table. Is this possible? If so could somebody help me with the script to copy the record to the original table from the surrogate table?

  3. #3
    Join Date
    Feb 2004
    Posts
    84
    Originally posted by Arvind7
    I am wondering if a work around is possible for the problem I stated in the previous thread. I don't know File Maker, but I have seen a simillar solution to a totally different problem. Please let me know if my idea will work or not.

    <B>Idea:</B>
    How about inserting this record into another (surrogate) file in the File Maker Pro database and have a computed column in the file fire a filemaker script that copies the data to the original table. Is this possible? If so could somebody help me with the script to copy the record to the original table from the surrogate table?
    Yes and Yes.

    The first question I have is what are your summary fields doing? In many cases their are multilpe ways to approach the problem, but I rarely use summary fields. In many cases I can use a self-join realtionships and creative portal keys to get the same data without a summary field.

    On your other question I is possible more data from one record to anohter in another table. A) you can constrain you found set to the single record and then import that data into another record, or you can script the data migration with set fields and/or replaces.

    There are certain issues with the ODBC drivers which are written by Microsoft. There are also certain issues with summary fields, the is particaluarly true when your summary invovled related of unindexable data. If you give me more details, then I would be happy to give you my opinions....


    thanks,

    Joe

  4. #4
    Join Date
    Mar 2004
    Posts
    3
    Thanks for your reply, Joe. Actually I have found a work around for the problem. I am explaining that below but before that answer to your questions.

    Answer to your questions:
    There are so many summary fields, I don't even know all of them. I know that some of them compute group totals. But this is irrelevant because I am only building a web portal for an existing Filemaker Pro sytem and I am not authorized to make any major changes to the existing system.

    Problem:

    When I insert a record thru ODBC, through I insert values only for 10 out of ~500 fields in the file, ODBC automatically triggers off the "summarization". Filemaker pro has the concept of layouts which is like "views" in other relational DBMS. If I can insert into the layout instad of the actuall file I can bypass the summary fields. However, Filemaker pro ODBC driver doesn't understand the laytous. I think, filemake pro JDBC driver understands layouts but we aren't using it.

    Our Workaround.

    We are now using the "Filemaker Web Companion" for the data access. This enables us to insert into a layout which doesn't have the summary fields so the damn "summarization" doesn't happen. But it is a lot of work to use this web companion.

    Arvind.

  5. #5
    Join Date
    Feb 2004
    Posts
    84
    Originally posted by Arvind7
    Thanks for your reply, Joe. Actually I have found a work around for the problem. I am explaining that below but before that answer to your questions.

    Answer to your questions:
    There are so many summary fields, I don't even know all of them. I know that some of them compute group totals. But this is irrelevant because I am only building a web portal for an existing Filemaker Pro sytem and I am not authorized to make any major changes to the existing system.

    Problem:

    When I insert a record thru ODBC, through I insert values only for 10 out of ~500 fields in the file, ODBC automatically triggers off the "summarization". Filemaker pro has the concept of layouts which is like "views" in other relational DBMS. If I can insert into the layout instad of the actuall file I can bypass the summary fields. However, Filemaker pro ODBC driver doesn't understand the laytous. I think, filemake pro JDBC driver understands layouts but we aren't using it.

    Our Workaround.

    We are now using the "Filemaker Web Companion" for the data access. This enables us to insert into a layout which doesn't have the summary fields so the damn "summarization" doesn't happen. But it is a lot of work to use this web companion.

    Arvind.

    I understand and I realize that there are pros and cons to every approach. Most of the work I have done over the past five years has begun with legacy databases, so I realize what it is like to be trapped in a solution that may not have been written in the best methods.

    Another thought is to use the ODBC drivers into to a 'clone' database and then do an import. My guess is that this method will act the Web Companion approach but without using CDML.


    thanks,


    Joe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •