Using a FileMaker 11 Table in a Servoy Form

Questions and answers regarding general SQL and backend databases

Using a FileMaker 11 Table in a Servoy Form

Postby gdurniak » Tue Sep 24, 2013 3:14 pm

I have a Servoy solution that gets some data from FileMaker 11 Server, by running SQL queries

I would now like to show the FileMaker Table in a Form. It does work, but is a bit slow

it seems that Servoy gets all the PK's, then loads all the fields. Since there are over 1 million records, this takes awhile ( not sure if this is limited to 200 records )

I did set the "onShow" property to start with an empty Found Set, but it did not help:

var fs = databaseManager.getFoundSet('filemaker','media');
forms.media_filemaker.controller.showRecords(fs);

and if I do a "Find" that fails, Servoy tries to load all records again !

1. is there any way to stop Servoy from asking for all the PK's ?
2. can I prevent a failed "Find" from loading all records ?
3. The form shows 5 columns, yet the Performance Log shows the query asks for ALL columns ? any way to control this ? ( this is a wide table )

greg
File Shoppe
gdurniak
 
Posts: 232
Joined: Thu Jul 08, 2004 7:25 pm
Location: Bayside, NY, USA

Re: Using a FileMaker 11 Table in a Servoy Form

Postby lwjwillemsen » Tue Sep 24, 2013 3:45 pm

Looks a bit the same as our https://support.servoy.com/browse/SVY-5099 ...
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 680
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: Using a FileMaker 11 Table in a Servoy Form

Postby gdurniak » Tue Sep 24, 2013 4:09 pm

That was my next question ... How does Servoy impose the 200 limit ?

From the sample query you posted, it appears they use LIMIT and OFFSET

However, it seems that FileMaker 11 and 12 SQL does not support these

greg

PS
it seems that Sybase SQL Anywhere does not support LIMIT and OFFSET either


> The sql is not limited to 200 records! Working with large tables this will give big performance problems.

If after this the foundset is expanded, it works correct again:
_fs.setSelectedIndex(200)

will result in the following line in the performance data:
Load foundset select crm_contact_id from crm_contact where crm_contact_id in (select crm_contact_id from crm_contact) order by crm_contact_id asc limit ? offset ?
File Shoppe
gdurniak
 
Posts: 232
Joined: Thu Jul 08, 2004 7:25 pm
Location: Bayside, NY, USA

Re: Using a FileMaker 11 Table in a Servoy Form

Postby gdurniak » Tue Nov 05, 2013 5:58 pm

It seems the answer is using databaseManager.setCreateEmptyFormFoundsets() in my "opener" method, to start all foundsets as empty foundsets, and not search for Primary Keys

It appears to work

greg
File Shoppe
gdurniak
 
Posts: 232
Joined: Thu Jul 08, 2004 7:25 pm
Location: Bayside, NY, USA


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 6 guests