1 2 Previous Next 17 Replies Latest reply on Nov 25, 2014 5:46 AM by beverly

    XML to Start Script

    Padster

      Hi,

       

      I'm hoping that somebody might be able to help me with something, or at least tell me what I'm doing wrong.

       

      I have a setup that includes a a FMS & SQL DB, the end users are accessing the FMS through a FMP, and update the DB and records through this. On a nightly basis, a script is run that 'Copies' all of the Tables&Records over to the SQL DB where complex data analysis and reports are generated. This is run on the FMS as a scheduled script to pass the data out to the SQL DB. This is all working fine and without any issues. The data is always a day out on the reports, and up untiol now this has been acceptable.

       

      A request for 'Transactional' level reporting has come in, this would mean that I'd need to make more regular updates from FMS to SQL. This is only for a couple of reports though, so I don't want to have a scheduled script that is running every 5 minutes, when it could go a week without ever being needed, thus using resource when not required.

       

      What I was looking at doing, when the report is request from the reporting server, is have this send off an update request to the FMS to trigger the update script. Once done, the Reporting server then has access to all of the transactional information for the day, and can then produce the required report.

       

      I've got all the scripts set up, and the communication, and the flags to tell the reporting server that the data is ready etc. My problem comes that I can't seem to get the XML call right that executes the script.

       

      What I have so far is this as the URL syntax;

       

      http://192.168.100.222/fmi/xml/fmresultset.xml?-db=Live_to_SQL&-lay=ProgressInfo&-script=Run_All_TableGeneration&-view

       

       

      I'd expected this to set the script off, but it does not seem to be doing anything, apart from returning this XML data;

       

      <fmresultset xmlns="http://www.filemaker.com/xml/fmresultset" version="1.0">
       <error code="0"/>
       <product build="04/20/2014" name="FileMaker Web Publishing Engine" version="13.0.2.296"/>
       <datasource database="Live_to_SQL" date-format="MM/dd/yyyy" layout="ProgressInfo" table="ProgressInfo" time-format="HH:mm:ss" timestamp-format="MM/dd/yyyy HH:mm:ss" total-count="1"/>
       <metadata>
       <field-definition auto-enter="no" four-digit-year="no" global="no" max-repeat="1" name="progress" not-empty="no" numeric-only="no" result="text" time-of-day="no" type="normal"/>
       </metadata>
       <resultset count="0" fetch-size="0"/>
      </fmresultset>
      

       

       

      Is there anything that I'm missing, or doing wrong, as everythign I've been reading says that this should execute the script.

       

      Cheers,

      Pad

        • 1. Re: XML to Start Script
          wimdecorte

          Padster wrote:

           

          What I have so far is this as the URL syntax;

           

          http://192.168.100.222/fmi/xml/fmresultset.xml?-db=Live_to_SQL&-lay=ProgressInfo&-script=Run_All_TableGeneration&-view

           

           

           

          You can't use -view with the fmresultset.xml, it only works iwth the FMPXMLLAYOUT grammar.

           

          Instead of -view, use -findany

          1 of 1 people found this helpful
          • 2. Re: XML to Start Script
            wimdecorte

            oh, and specify a blank layout if you only want the script to run and are not interested in returning data at all.

            • 3. Re: XML to Start Script
              Padster

              Hi,

               

              I've made the changes as you suggest so that this is my new syntax;

               

              http://192.168.100.222/fmi/xml/fmresultset.xml?-db=Live_To_SQL&-lay=RunScript&-script=Run_All_TableGeneration&-findany

               

              But this still does not run the Script, and returns this;

              <fmresultset xmlns="http://www.filemaker.com/xml/fmresultset" version="1.0">

              <error code="0"/>

              <product build="04/20/2014" name="FileMaker Web Publishing Engine" version="13.0.2.296"/>

              <datasource database="Live_To_SQL" date-format="MM/dd/yyyy" layout="ProgressInfo" table="ProgressInfo" time-format="HH:mm:ss" timestamp-format="MM/dd/yyyy HH:mm:ss" total-count="1"/>

              <metadata>

                <field-definition auto-enter="no" four-digit-year="no" global="no" max-repeat="1" name="progress" not-empty="no" numeric-only="no" result="text" time-of-day="no" type="normal"/>

              </metadata>

              <resultset count="1" fetch-size="1">

                <record mod-id="0" record-id="2">

                 <field name="progress">

                  <data/>

                 </field>

                </record>

              </resultset>

              </fmresultset>

              I have as you suggested used a blank layout, but it still refers to the same table. Would that have an affect?

               

              Also to note, I'm trying to get this to run from a Web Browser, as that is how SQL would be making the call.

               

              Cheers,

              Pad

              • 4. Re: XML to Start Script
                Padster

                Hi,

                 

                The first script seems to be firing off correctly; Updating the progress marker;

                Screenshot_112014_011904_PM.jpg

                 

                It doesn't seem to be performing the subscripts correctly though;

                 

                Screenshot_112014_011943_PM.jpg

                 

                Is there something more that I need to do, as the scripts all have permissions etc.

                 

                Pad

                • 5. Re: XML to Start Script
                  wimdecorte

                  The XML returned is ok, it shows no error retrieving a random record.  How do you determine that the script does not run?  Any errors in the FMS log?

                   

                  Padster wrote:

                   

                   

                  I have as you suggested used a blank layout, but it still refers to the same table. Would that have an affect?

                   

                   

                  The layout you specify in the XML query will be the context in which the script starts, other than that, no effect.

                   

                   

                  Padster wrote:

                   

                  Also to note, I'm trying to get this to run from a Web Browser, as that is how SQL would be making the call.

                   

                   

                  I don't get the SQL part...

                  That you run this from the browser is irrelevant.

                  • 6. Re: XML to Start Script
                    wimdecorte

                    Can you be a little clearer on what you expect to see and what you actually see?

                    • 7. Re: XML to Start Script
                      beverly

                      Pad, are ALL script steps compatible for web (in script and subscripts)?

                       

                      Beverly

                      1 of 1 people found this helpful
                      • 8. Re: XML to Start Script
                        Padster

                        Hi Both,

                         

                        All the scripts are fully compatable for the required tasks.

                         

                        The end goal is to have Data (records) copied out of a FM Db, into the matching SQL Table. This is what the second script is showing. this would actually wirte the data assuming it SQL Table is blank. There is another script that handles simple updates.

                         

                        I think that I have just realised what is happening though, The DB that I am using to run this script on is not where the source data is stored, the data is in another DB, that requires permissions. The first step that is running is 'ReLogin' as a FullAccess account, but this step is not actioning, thus the account that is attempting to run the remainder or the scripts is still the Guest account, and does not have access to the Source DB file.

                         

                        The First Script step is kicking out a Error 9, Insufficient Privileges

                         

                        Following this, all the other script steps are throwing up Error 101, as the cannot find the record.

                         

                        I just need to resolve this, and it should all work as expected.

                         

                        Pad

                        • 9. Re: XML to Start Script
                          Padster

                          I have got the re-login working now, the full access account does not have the XML permission by default, so this was needed to be added so that the re-login would work.

                           

                          I've updated all of my error catching as well so that I can more identify what is happening, and where errors our occuring.

                           

                          There is a somewhat strange thing happening though, The 'Script' file is not seeing any of the records from the 'Data' file, though it is logging in with a full access account

                          Screenshot_112014_035422_PM.jpg

                          I can see the reocrds when I open the 'Script' file with the same account, so there must be something in the CWP that is causing this to go wrong, though I cannot think where this might be. The core of the 'TableGen_Status' script that is pictured above has not changed

                           

                          There are no errors that are generated at the 'Show All Records' step.

                           

                          Pad

                          • 10. Re: XML to Start Script
                            Padster

                            Upon further testing, it seems that CWP XML is not able to access an External Data Source that referes to a FileMaker database, though it can access an ESS source.

                             

                            I've tested this through completing a set of basic Queries and ExecuteSQL trying to count the number of records available when running an XML triggered script.

                             

                            Every time the FileMaker source give an error, but the SQL source returns a count.

                             

                            But when I open the Script file in a FM Client, this works perfectly as expected.

                             

                            I'm a little lost now...

                            • 11. Re: XML to Start Script
                              beverly

                              are you calling a subscript in another database?

                               

                              that may be what you are seeing.

                               

                              Beverly

                              • 12. Re: XML to Start Script
                                Padster

                                The scripts are all local, it is just the data that is not.

                                 

                                The scripts (as pictured above) refer to the fields like 'local_status::_id_Status' coming an FileMaker External source;

                                Screenshot_112014_063408_PM.jpg

                                The 'sql_status::_id_status' are relating to the ODBC External Source.

                                 

                                I've also tested triggering the script from a Schdeule on the FMS, and this works perfectly as well.

                                 

                                I'm not sure what else I can try

                                • 13. Re: XML to Start Script
                                  Padster

                                  For an Update,

                                   

                                  Having played with this more, and done a bucket load of testing, it seems that the problem stems from XML not having the ability to 'Open File'.

                                   

                                  If the 'remote' file that is trying to be accessed resides on the same machine that the file that is executing the XML script, then this is fine, and the script runs as expected, and accesses the 'remote' file.

                                   

                                  If the 'remote' file resides on another FMS, then the XML file is unable to locate/open this file, therefore causing the scripts to simply bottom out as they are unable to locate the corresponding data.

                                   

                                  This is somewhat of a limitation, but I can work around this problem, but I would have expected this to work..

                                   

                                  Pad

                                  • 14. Re: XML to Start Script
                                    Padster

                                    Side Note to all this, there is an error code that I have found that does not seem to be listed;

                                    <fmresultset xmlns="http://www.filemaker.com/xml/fmresultset" version="1.0">

                                              <error code="8003"/>

                                              <product build="04/08/2014" name="FileMaker Web Publishing Engine" version="13.0.2.290"/>

                                              <datasource database="" date-format="" layout="" table="" time-format="" timestamp-format="" total-count="0"/>

                                              <metadata/>

                                              <resultset count="0" fetch-size="0"/>

                                    </fmresultset>

                                     

                                     

                                    This code signifies that the user is already connected, an connect have 2 concurrent connects (as far as I can tell).

                                     

                                    You have to either 'Disconnect' the previous session, or wait for FMS to timeout the connection.

                                     

                                    Pad

                                    1 2 Previous Next