List of related values

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

Re: List of related values

Postby ROCLASI » Wed Sep 17, 2008 12:03 am

Hi Ben,
LOGIsoft wrote:Something like this launched from the Clients form (based on a recent post from Robert Ivens - thanks!):

You're welcome ;)

I believe iAnywhere doesn't support LIMIT n but uses TOP n. Also all column names should be prefixed with the table alias. I know it might work without but lets say it's the proper way to do it when you mix and match columns in subqueries.
So your code will look something like this:

Code: Select all
var vForm = application.getMethodTriggerFormName();
var vTable = controller.getTableName();
var vServer = controller.getServerName();
var sQuery = "UPDATE TABLE clients a SET a.id_client_rep = (SELECT TOP 1 b.id_client_rep FROM invoices b WHERE b.id_client = a.id_client ORDER BY b.invoice_date desc)";
var sArg   = "";
if ( !plugins.rawSQL.executeSQL( vServer,  vTable,  sQuery,  sArg) ) {
   // something went wrong
   application.output( "Error: " + plugins.rawSQL.getException() );
} else {
   // need to flush any cached data of this table or else your and other clients won't see it.
   plugins.rawSQL.flushAllClientsCache(vServer,  vTable);
}


I suggest you try the SELECT query in iSQL first to see it if it gives the required result.

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: List of related values

Postby david » Wed Sep 17, 2008 12:24 am

Yea, don't do it on live data. Get a copy to test with until you have it down correctly.

Don't use Servoy -- use Sybase Central, Navicat or something of that nature to run the update.

Not sure about the exact syntax but Sybase has really good docs for its SQL. Once you get this figured out you will find that you will be resorting to this kind of approach for a lot of situations.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: List of related values

Postby LOGIsoft » Wed Sep 17, 2008 2:22 pm

Sorry David - couldn't resist running the method from Servoy, just to see what the result would be :? - but I did it when no user was logged in, and it only took 39.16 seconds to update more than 3000 client records with more than 5000 related invoices - not bad! :) Running this directly in iSql would probably have taken about 7 seconds, though... :!:

Turns out that Sybase iSQL didn't like the 'TABLE' keyword; this is my working method:

Code: Select all
var vStart = new Date();
var vForm = application.getMethodTriggerFormName();
var vTable = forms[vForm].controller.getTableName();
var vServer = forms[vForm].controller.getServerName();
var sQuery = "UPDATE companies a SET a.i_fk_rep_id = (SELECT TOP 1 b.i_fk_rep_id FROM orders b WHERE b.company_id = a.company_id ORDER BY b.order_date desc)";
var sArg   = "";
if ( !plugins.rawSQL.executeSQL( vServer,  vTable,  sQuery) ) {
   // something went wrong
   application.output( "Error: " + plugins.rawSQL.getException() );
   return;
   } else {
      // need to flush any cached data of this table or else your and other clients won't see it.
      plugins.rawSQL.flushAllClientsCache(vServer,  vTable);
   }
var vStop = new Date();
var vDuration = ( vStop - vStart ) / 1000;
var msg = "Completed in " + vDuration + " seconds.";
plugins.dialogs.showInfoDialog( 'Info',  msg,  'OK');

You'll notice I added a little timer to see how much time the method takes to run.

Again, thanks a lot, guys! I really appreciate your help with this.

Hope to 'see' you later at the VUG,

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Re: List of related values

Postby david » Wed Sep 17, 2008 7:30 pm

looks good!

this approach is also good for when you add stored calculations to a solution that already has data. it is easier/faster to use one SQL update statement to update the stored calc field than to loop through records to force servoy to trigger the calculations for each record.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: List of related values

Postby LOGIsoft » Wed Sep 17, 2008 10:24 pm

Roger that!

Again, much obliged.

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Previous

Return to How To

Who is online

Users browsing this forum: No registered users and 8 guests

cron