Troubles with the XML API Web Publishing Engine...???

[angelleye]

I'm trying to get responses back from the Web Publishing Engine. I'm using examples from the documentation.

When I use http://filemaker.dealexpress.com/fmi/xml/fmresultset.xml?-dbnames

I get a response of the database names. So that's cool.

Then I try

https://filemaker.dealexpress.com/fmi/xml/fmresultset.xml?-db=Products&-lay=Item%20Overview&-findall

And I get Error Code 802. I look in the documentation and that code says "Unable to open file." But nothing about why or how to fix the problem.

Any information would be greatly appreciated. Thanks!

[Mikhail Edoshin]

Could you check everything is installed correctly? It seems the names of opened dbs can be listed even if the FM WPE isn't actually connected to the FM Server.

[angelleye]

I've got the extended privaleges setup for the files I need access to. They don't show up with -dbnames until I do that. Then (following along with a FM book I bought) I go into the Web Publishing Admin Console but when I click on Publishing Engine section all of the sub-categories are greyed out and I can't do anything in this page.

Any ideas?

[angelleye]

Actually, I see this message in there:

localhost

The web server and Administration Console server must be restarted before continuing to configure the Publishing Engine.

And there's a button that says restart, but when I click that I get an "unexpected error" message.

[angelleye]

Ok I got that part figured out. Rebooted the sever and everything started working like it should in the web publishing console.

I'm still having trouble, though. I have the extended privelages setup for the files I'm trying to access but I'm getting strange results.

On one of them I try a simple find and it sits there for a very long time and never does load anything to the page. On another that I try I'm getting error code -1 which says Unknown Error in the docs.

:??:??

[angelleye]

Ok, I'm getting a little further along with this but it's not really working how I expected.

For instance, I do this basic query:

https://sername.com/fmi/xml/fmresultset.xml?-db=Inventory&-lay=List%20View%20-%20Internal&Ref_Number=5939&-find

And it does indeed return that single record, however, most of the fields it returns with it are empty. In FileMaker, though, they're not empty. They clearly contain data.

You can get a look at the xml that I got back here: http://angelleye.sytes.net/dealexpress/temp/fmresultset1.xml

I know the Qty_Available field is a calculated field that comes from other tables. I'm not sure about the fields at the moment, I'll have to check.

Why are they all coming back empty, though? I know they're not empty. So, of course this doesn't work for grabbing the particular item data but then I also can't do searches like this:

https://servername.com/fmi/xml/fmresultset.xml?-db=Inventory&-lay=List%20View%20-%20Internal&Qty_Available=0&Qty_Available.op=gt&-find

Here I'm trying to return all products in Inventory that a Qty_Avaialble > 0. Well, since that field is coming back empty, I end up with error code 401 here, saying no records returned.

Here is a JPG of the Inventory file I'm connecting to as shown in FileMaker Pro: http://angelleye.sytes.net/dealexpress/temp/inventory.jpg

As you can see all of those fields are filled in, so I'm confused as to why this isn't returning anything.

Another thing. If I try and see what happens when I just do a -findall, it sits there forever loading the page until I give up 10 min later. There are only 4000 records in this Inventory file so I'm confused about that as well.

Any information I can get on any of this would be greatly appreciated. Thanks!!!!

[angelleye]

Another question now, while playing around with this. Isn't the XML API suppose to be faster than ODBC?

We have a file called Auctions that holds all of our eBay auctions. There's a field in there called Auction_Status that stores the values Current, Winner, or Loser.

I've got 2 scripts setup that grab all current auction records and displays a list of the auction numbers. One uses ODBC and the other uses Custom Web Publishing with XML.

The ODBC script returns resuls in a couple of seconds while the XML way takes a full minute or more to return results. There are about 3500 records that get returned, and of course the same amount is returned both ways.

[Martin Brändle]

Ok, I'm getting a little further along with this but it's not really working how I expected.

For instance, I do this basic query:

https://sername.com/fmi/xml/fmresultset.xml?-db=Inventory&-lay=List%20View%20-%20Internal&Ref_Number=5939&-find

And it does indeed return that single record, however, most of the fields it returns with it are empty. In FileMaker, though, they're not empty. They clearly contain data.

Are these the related fields? Did you turn on the fmxml privilege set for those?

[angelleye]

Martin:

Thanks! Don't know why I didn't think of that. I only had the Inventory file setup for CWP and not the related files. So, now it is displaying all the fields and data correctly.

Any ideas on the speed issue? ODBC seems much faster, however, this one particular SQL SELECT I'm making takes a LONG time and leaves the server stuck at 60% cpu usage...even after the browser is closed down. But only with this 1 SQL. Most SQL commands work great in FM.

The problematic SQL does work better using CWP instead of ODBC (SQL) in that it doesn't leave the CPU usage, however, it takes a good full min. or more to get the results back.

I'm using the following CWP request: .../fmi/xml/fmresultset.xml?-db=Inventory&-lay=List%20View%20-%20Internal&Qty_Available=0&Qty_Available.op=gt&-find

which returns about 4,937 records but takes way longer than it should.

Edited October 8, 2006 by Guest

[Wim Decorte]

ODBC should normally be faster than XML, purely because the data returned through ODBC has less overhead than the equivalent XML document. XML by its nature has a large metadata section. ODBC data does not. The XML data therefore is always bigger than the ODBC data.

Having said that, there are advantages to using XMÖ

- its purely FM native unlike the FM ODBC drivers that are provided by DataDirect. So you'd tend to have less convoluted support issues.

- the DataDirect ODBC drivers have traditionally not been very fast

- with XML you don't need to setup an DSN for every file you want to touch

The key to using XML properly is to realize that all requests go through a layout. So if you have fields on there that you don't need in your request FM will return their data *and* their metatdata with each request. It gets worse if you have portals on there: then FM returns *all* of the related data too.

Another key issues is this: are you sure you want to issue a query that returns 5000 records? Once you have them they are in a disconnected state and it's up to you to maintain them and check FMS for any changes to those. If you'd to this in VB/C#/whatever against SQL Server you'd have the same problem: only request the data that you really need to work with...

[angelleye]

So if you have fields on there that you don't need in your request FM will return their data *and* their metatdata with each request.

I don't really understand this. As long as I specify the fields that I want returned I'd only get those fields back. This particular example I showed here returns every field for that part number, but really, for this particular file I need all the fields anyway.

Now, I am returning ALL Inventory items with a Qty > 0 here, so maybe 5k records with that many fields here is a little much.

I have 2 other scripts, though, which simply pull out all auction records in our system marked as Current. I have one setup using ODBC another setup using CWP. They simply grab all the current auction records and print their auction number to the screen. So we're looking at:

CWP Way:

fmresultset.xml?-db=Auctions&-lay=Auction%20Detail&Auction_Status=Current&-find

SQL Way:

SELECT Auction_Number FROM Auctions WHERE Auction_Status = 'Current'

The Auctions database has 150k records in it, about 3k of which are marked as current. The CWP way chews up 25% of the CPU and takes a full min. or a little longer to return results. The SQL way chews up the same 25% of the server's CPU but takes just a second or two.

With all the hype I hear about FileMaker's XML stuff, though, it's hard for me to believe that returning a simple query of 3k records would take that long vs. the ODBC driver which as you say, isn't even native to FM and is supposedly the slow way.

The really don't think the meta data that's returned at the top of the XML should not have hardly any bearing on the time at all. Not noticeable anyway. I mean, if it did, that's a pretty harsh downfall of using it and nobody would. Just seems there's gotta be something about my configuration or something that's causing it to be slow...though I have no idea what it'd be. It's pretty straight forward, and it is working.

ODBC always worked fabulous for me until I got to this one particular SQL SELECT I need.

SELECT Inventory.Ref_Number, Purchase_Order_Items.Condition, Inventory.Qty_Available, Ref_Number.Description, Ref_Number.Manu_Part_Number, Ref_Number.Street, Ref_Number.Weight, Ref_Number.Class, Ref_Number.Hot

FROM Inventory, Purchase_Order_Items, Ref_Number

WHERE Inventory.Qty_Available > 0 AND Ref_Number.Item_Number = Inventory.Ref_Number AND Inventory.PO_Item_Number = Purchase_Order_Items.Control_Number AND Ref_Number.ClassID = 1

That SQL is pretty basic for most databases. There are some deep calculations on some of these fields, though, and I think that's the cause of an approximate 7 - 10 min wait anytime I try and run it. Also, it causes the CPU on the server to spike to 70% and won't come back down even after the query is complete and the browser has been closed. Stuck at 70% and of course everything runs crappy then until I close down those files and re-open them in FM Server Admin.

I've used ODBC to update 50k records in about 20 - 30 seconds. I use it everyday to update those 3k auction records I was talking about. Updates about 6 fields in each of those records. Grabs this data from the eBay/PayPal API's and that whole thing takes roughly 1-2 seconds per record. That's grabbing it from FileMaker via ODBC, using the data within API calls to eBay to grab data, and then updating the FM record again via ODBC. All within a couple seconds.

ther'es gotta be something going on with my CWP setup that's causing it to be so slow. I just have a hard time believing it's really that slow to return 5k Inventory records.

[Wim Decorte]

CWP Way:

fmresultset.xml?-db=Auctions&-lay =Auction%20Detail&Auc tion_Status=Current&- find

SQL Way:

SELECT Auction_Number FROM Auctions WHERE Auction_Status = 'Current'

The big difference between the two is that the SQL query returns only the Auction_Number field data.

The XML query returns all the fields and their data on the "Auction Detail" layout. Including all related records if you happen to have a portal on the layout.

So you need to create a layout with just the Auction_Number on it and use that one to have a fair comparison.

In general when you use the XML engine you do everything in FM since it's all layout driven. The equivalent of doing a SQL Join query would be to just create and specify a layout with a portal.

Some things that you can't do through SQL/ODBC but can with XML is to interrogate a layout and get the values of a value list for instance.

You can also run a script on FMSA through the XML engine, something that is not possible through ODBC.

Edited October 9, 2006 by Guest

[Wim Decorte]

That SQL is pretty basic for most databases. There are some deep calculations on some of these fields,

FileMaker is unique in that it has unstored calculated fields in the db schema. Most other databases don't and store only the result, the calc is performed when needed event driven in the front end, not the back end. That's more work for the developer but you end with stored (and fast) data in the back end, making SQL queries like this a snap.

If you want to do this with FileMaker you have to do away with your unstored calcs. Use an event plugin or other means to make your data stored.

[Martin Brändle]

There had been a recent discussion in another list (Wim and me included) on the performance of the WPE. The general consent was that performance must be improved heavily.

Feel free to submit a product problem on http://www.filemaker.com/company/problems.html

[Steven H. Blackwell]

And FMI does listen to those suggestions.

Steven