Professional Web Applications Themes

FM Pro stops responding on search via ASP/ODBC - FileMaker

Hi, I'm not a filemaker developer so please forgive me if this is insultingly easy or stupid! I am having problems trying to run a SQL query on a large FM Pro 5.5 file (running on a Mac under OS X) from an ASP page and an ODBC driver (which I set using a System DSN on my W2K server, using the filemaker pro ODBC driver). If I query small files it is fine and comes back with results etc (albeit almost 50 times slower than SQL Server 2000!). However, if I query a large file it brings FM Pro ...

  1. #1

    Default FM Pro stops responding on search via ASP/ODBC

    Hi,

    I'm not a filemaker developer so please forgive me if this is
    insultingly easy or stupid!

    I am having problems trying to run a SQL query on a large FM Pro 5.5
    file (running on a Mac under OS X) from an ASP page and an ODBC driver
    (which I set using a System DSN on my W2K server, using the filemaker
    pro ODBC driver). If I query small files it is fine and comes back
    with results etc (albeit almost 50 times slower than SQL Server
    2000!). However, if I query a large file it brings FM Pro to it's
    knees and it then stops responding forcing a quit and restart. In fact
    I have just bought our production FM Pro server to it's knees by doing
    a simple SELECT query... not great when it's our busiest day of the
    week!

    So can anyone tell me why this happens or what might be going on? In
    normal use (ie: from FM Pro clients) everything runs fine and this
    sort of thing never happens. So what could be causing this shut
    down/non response to occur when querying via ODBC? (BTW, all the
    sharing options are turned on on the files in question - web sharing,
    LDAC and RDAC. I also know that I'm actually connecting to the FM Pro
    file - the problem occurs when the query actually runs.)

    Any help would be much appreciated as I've been asked to develop an
    ASP application which uses FM Pro as it's database - if I can't do
    ODBC queries with large files then what are my options?

    Mike.
    mike Guest

  2. #2

    Default Re: FM Pro stops responding on search via ASP/ODBC

    mike <mikeadenergy.com.au> wrote:
    >
    > I am having problems trying to run a SQL query on a large FM Pro 5.5
    > file (running on a Mac under OS X) from an ASP page and an ODBC driver
    > (which I set using a System DSN on my W2K server, using the filemaker
    > pro ODBC driver). If I query small files it is fine and comes back
    > with results etc (albeit almost 50 times slower than SQL Server
    > 2000!). However, if I query a large file it brings FM Pro to it's
    > knees and it then stops responding forcing a quit and restart. In fact
    > I have just bought our production FM Pro server to it's knees by doing
    > a simple SELECT query... not great when it's our busiest day of the
    > week!
    Although FMI will not admit this, Filemaker does not do well serving as
    an ODBC data source. A fellow developer tried to do what you're
    attempting, and after a long tale of frustration, finally got an
    engineer at FMI to admit that the ODBC capability was meant to pull
    single records, such as for a customer query, or at most a few records
    at a time. As you phrase it, small files are fine. Asking for large
    chunks of data is either glacially and unuseably slow, or causes total
    freeze.

    So here's an alternative to simply using FM as an ODBC data source.
    Start your search at

    [url]http://www.profdata.nl/pdm/generic.jsp?taxonomy_id=2[/url]

    and take a look at their plugins. (I'm not sure the SQL one is meant to
    provide 2-way connectivity, it might be simply from SQL > FM)

    Also look at

    [url]http://www.stravinsoft.com/[/url]

    particularly their FMtor product.

    While you're at the first link, take a look at Servoy. It may be more
    what you need...a javascript /web based interface with any SQL db as a
    backend. Look ma! Filemaker looks with an SQL heart!

    Don't tear out any more hair trying to get FM to do something it wasn't
    meant to do.
    --
    Lynn Allen Allen & Allen Semiotics
    FSA Associate Filemaker Consulting & Training
    [email]lynnsemiotics.com[/email] [url]http://www.semiotics.com[/url]
    Lynn allen Guest

  3. #3

    Default Re: FM Pro stops responding on search via ASP/ODBC

    Lynn,

    Thanks so much for the assistance - yes, I have been tearing my hair
    out over this and spending much time in a state of disbelief at how
    bad FM is at talking to the "real" world of enterprise data. I
    suspected the ODBC driver just wasn't up to it - to not be able to do
    a larger query is just unbelievable in a product that is as mature as
    FM Pro.

    Anyway, enough complaining... I'll check out the two plugins and see
    of they help. Other than that, what do you think of FMConnecter - do
    you think this is something we could use? Have you had any experience
    with this product or the FMConnector ASP product?

    One more question: if we stick to ASP and SQL Server what is the best
    way (via FM Pro 5.50) to get the updated/new rows in SQL Server into
    the corresponding FM Pro file? Can this be scheduled to run via the
    FM Pro SQL Server driver, say once a day? Any info or links etc you
    may have would be very helpful.

    Many thanks again for the reply.

    Mike




    [email]lynnNOT-semiotics.com[/email] (Lynn allen) wrote in message news:<1g2iyqr.f6s75is1fjb4N%lynnNOT-semiotics.com>...
    > mike <mikeadenergy.com.au> wrote:
    >
    > >
    > > I am having problems trying to run a SQL query on a large FM Pro 5.5
    > > file (running on a Mac under OS X) from an ASP page and an ODBC driver
    > > (which I set using a System DSN on my W2K server, using the filemaker
    > > pro ODBC driver). If I query small files it is fine and comes back
    > > with results etc (albeit almost 50 times slower than SQL Server
    > > 2000!). However, if I query a large file it brings FM Pro to it's
    > > knees and it then stops responding forcing a quit and restart. In fact
    > > I have just bought our production FM Pro server to it's knees by doing
    > > a simple SELECT query... not great when it's our busiest day of the
    > > week!
    >
    > Although FMI will not admit this, Filemaker does not do well serving as
    > an ODBC data source. A fellow developer tried to do what you're
    > attempting, and after a long tale of frustration, finally got an
    > engineer at FMI to admit that the ODBC capability was meant to pull
    > single records, such as for a customer query, or at most a few records
    > at a time. As you phrase it, small files are fine. Asking for large
    > chunks of data is either glacially and unuseably slow, or causes total
    > freeze.
    >
    > So here's an alternative to simply using FM as an ODBC data source.
    > Start your search at
    >
    > [url]http://www.profdata.nl/pdm/generic.jsp?taxonomy_id=2[/url]
    >
    > and take a look at their plugins. (I'm not sure the SQL one is meant to
    > provide 2-way connectivity, it might be simply from SQL > FM)
    >
    > Also look at
    >
    > [url]http://www.stravinsoft.com/[/url]
    >
    > particularly their FMtor product.
    >
    > While you're at the first link, take a look at Servoy. It may be more
    > what you need...a javascript /web based interface with any SQL db as a
    > backend. Look ma! Filemaker looks with an SQL heart!
    >
    > Don't tear out any more hair trying to get FM to do something it wasn't
    > meant to do.
    mike Guest

  4. #4

    Default Re: FM Pro stops responding on search via ASP/ODBC

    mike <mikeadenergy.com.au> wrote:
    > Thanks so much for the assistance - yes, I have been tearing my hair
    > out over this and spending much time in a state of disbelief at how
    > bad FM is at talking to the "real" world of enterprise data. I
    > suspected the ODBC driver just wasn't up to it - to not be able to do
    > a larger query is just unbelievable in a product that is as mature as
    > FM Pro.
    >
    > Anyway, enough complaining... I'll check out the two plugins and see
    > of they help. Other than that, what do you think of FMConnecter - do
    > you think this is something we could use? Have you had any experience
    > with this product or the FMConnector ASP product?
    I haven't had any experience with the FMConnector. If you use it, or any
    of the products, please report back to the group. ;) I suspect we're
    all going to be connecting to outside data sources much more often in
    the future.
    >
    > One more question: if we stick to ASP and SQL Server what is the best
    > way (via FM Pro 5.50) to get the updated/new rows in SQL Server into
    > the corresponding FM Pro file? Can this be scheduled to run via the
    > FM Pro SQL Server driver, say once a day? Any info or links etc you
    > may have would be very helpful.
    For a once a day update, I would seriously consider the creation of and
    import of a tab-delimited text file. Trigger the running of the import
    script in FM with a plugin for scheduling. Either that or use XML to
    transform the data from the SQL syntax to something FM can ingest.

    We're currently doing this very successfully taking accounting data from
    an SQL based system into FM. We use an intermediary processing file
    which checks all the data for compliance to the business rules, and
    kicks out error records into an Excel-friendly format for the accounting
    folks to go back and correct their errors before they re-export the
    records. There is some user-interaction here, but they wanted to have
    this error checking before the final import into the FM system.

    This process takes about 5 minutes per day. The hand-entry of this data
    used to take 1 full time person. :) Our users are quite happy.

    --
    Lynn Allen Allen & Allen Semiotics
    FSA Associate Filemaker Consulting & Training
    [email]lynnsemiotics.com[/email] [url]http://www.semiotics.com[/url]
    Lynn allen Guest

  5. #5

    Default Re: FM Pro stops responding on search via ASP/ODBC

    The best way to connect to FileMaker from ASP is not through ODBC; but
    rather by sending HTTP requests to FileMaker telling it what to do and
    getting XML results back.

    Enable the Web Companion plug-in on the FileMaker client, making the
    FileMaker client an HTTP responder. Web Companion accepts HTTP
    requests using FileMaker's cgi specs. A standard request might be:

    [url]http://server.com/FMPro?-db=mydb.fp5&-lay=my_web&-format=&-fmp_xml&my_field=my_value&-find[/url]

    Check out the "FileMaker Professional Training Book and CD" at the
    FileMaker.com web store for full listing and description (and set of
    examples) on these cgi commands. So - you send the web companion
    commands using HTTP and receive an XML stream in return. That's the
    basic idea of getting ASP and FileMaker to interact.

    To send HTTP requests and obtain the XML result in ASP you'll need to
    use the MSXML object provided in MDAC (microsoft data access
    components). I think MDAC is installed automatically with IIS, but
    I'm not sure. You can always download it for free at microsoft.com.
    Here's an example (in JScript) of how to send the http and create an
    XML DOM object from the result:

    // create and send xml http request to Filemaker
    var my_http_request = new ActiveXObject("Msxml2.XMLHTTP");
    my_http_request.open("GET", url, false);
    my_http_request.send();

    // create an xmlDOM object to store return data
    var my_xml_dom = Server.CreateObject("MSXML2.DOMDoent");
    my_xml_dom.loadXML(my_http_request.responseText);


    Then you can walk the DOM and handle the data appropriately:

    var root = my_xml_dom.doentElement;
    var nodeList = root.childNodes;

    // loop through the nodes in the DOM
    for (var i = 0; i < nodeList.length; i++) {
    // get the current node's nodeName
    var nodeName = nodeList.item(i).nodeName;

    // loop through this node's child nodes
    var childNodes = nodeList.item(i).childNodes;
    for (var j = 0; j < childNodes.length; j++) {

    // loop through this node's attributes
    var attributeList = childNodes.item(j).attributes;
    for (var k = 0; k < attributeList.length; k++) {
    // get the current attribute name
    var attribute = attributeList.item(k).nodeName;
    }
    }
    }

    Walk the DOM once and write the nodes and attributes to an object.
    Then, you can reference the object when you need data for input fields
    or populating lists, etc.

    This method is quite fast. (MUCH faster than ODBC).

    Alternatively, you can use PHP and Chris Hansen's FX class (free!) to
    handle all the HTTP and XML. His object class is quite easy to use
    and abstracts the whole XML DOM, so you just set and get properties
    from the object you instantiate from the FX class. You can download
    the FX class at [url]www.iviking.org[/url].

    The code above is more than you want to deal with on a web page. I
    highly recommend you encapsulate it in a function or class that
    creates a record object with properties representing the XML data. I
    also recommend you investigate the FX object as it will give you ideas
    on how to abstract the HTTP/XML communication into a much simpler
    process. I use PHP extensively with FileMaker using the technique
    above (but I use the encapsulated version in PHP) and have found it
    quite easy to work with.

    Hope this helps, and good luck!

    --One last thing: If the ASP server is a .Net server, you may have to
    turn on IIS 5 emulation for the XML to pass through.
    Mariano Peterson Guest

Similar Threads

  1. Contribute stops responding when publishing
    By dufnobles in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: February 22nd, 04:30 PM
  2. CFINDEX stops responding
    By Rich_Willmore in forum Coldfusion - Advanced Techniques
    Replies: 8
    Last Post: April 29th, 07:34 PM
  3. IIS 6.0 stops responding
    By Brad Gonzalez in forum ASP Components
    Replies: 2
    Last Post: October 9th, 05:32 PM
  4. Photoshop stops responding on startup
    By Alex Egg in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 1
    Last Post: July 29th, 09:46 AM
  5. Replies: 0
    Last Post: July 11th, 10:45 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139