Updating from a dataset

Questions, tips and tricks and techniques for scripting in Servoy

Updating from a dataset

Postby TonyK » Wed Jun 29, 2011 9:31 am

Hi,

I have a global method in which I extract some records to an dataset using databaseManager.getDataSetByQuery(). I examine those records using various conditions because I'm really only testing to see if one of them meets certain criteria. However, if I find a record meeting the criteria I need to update it with a new value and this where I'm stuck. What do I need to do, to then locate and update the actual database records, either from the dataset or directly?

Kind regards,

Tony
TonyK
 
Posts: 42
Joined: Mon Sep 27, 2010 6:07 pm

Re: Updating from a dataset

Postby jcompagner » Wed Jun 29, 2011 10:44 am

why are you extracting some records by converting it to a dataset?
why not directly use the records and update when needed?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Updating from a dataset

Postby TonyK » Wed Jun 29, 2011 10:58 am

Hmmm....lack of knowledge...I've only gotten this far by playing around with samples. I couldn't see how to access the data directly.

How would I do this? Can you point me in the right direction?
TonyK
 
Posts: 42
Joined: Mon Sep 27, 2010 6:07 pm

Re: Updating from a dataset

Postby jcompagner » Wed Jun 29, 2011 11:21 am

Code: Select all
var record = foundset.getRecord(index)
var record2 = foundset.getRecord(index+1)
if (record.column != record2.column) record.column = "changed";
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Updating from a dataset

Postby TonyK » Wed Jun 29, 2011 1:13 pm

Your example would be great if I knew the indexes of the records I needed but I don't.

Is there any documentation covering this topic in detail? because if I'm honest I do not fully understand foundsets and datasets.

When I looked at using .loadrecords() with a SQL query to filter the records I wanted to examine, it didn't work and the sample info seemed to suggest that you could only do that when working with a form and I'm not, I'm writing a batch process in global method.

Kind regards,

Tony
TonyK
 
Posts: 42
Joined: Mon Sep 27, 2010 6:07 pm

Re: Updating from a dataset

Postby jasantana » Wed Jun 29, 2011 1:28 pm

Hi Tony.

The sample Johan gave you is just the code inside the loop.

After loading the foundset with the SQL of data that you need, just do a loop to read all the records. The var index is the one incrementing the loop.

If you are not using a foundset of a form, just create it.

This code is version 6

Code: Select all
/** @type {JSFoundset<db:/myserver/mytable>}*/
var fs=databaseManager.getFoundSet('myserver','mytable');
var cSQL="SELECT * FROM mytable WHERE status= ?";
var aArgs=new Array();
aArgs[0]=1;
fs.loadRecords(cSQL,aArgs);
for(var idx=1;idx<=fs.getSize(); idx++){
    fs.setSelectedIndex(idx);
    // Do your stuff here
}


I hope this helps.

If you still have problems with Servoy concepts I can advice you the Andy McGillys book http://www.mcgilly.com/
Best regards,
Juan Antonio Santana Medina
jasantana@nephos-solutions.co.uk
Servoy MVP 2015
Servoy 6.x - Servoy 7.x - Servoy 8.x - MySQL - PostgreSQL - Visual Foxpro 9
User avatar
jasantana
 
Posts: 555
Joined: Tue Aug 10, 2010 11:40 am
Location: Leeds - West Yorkshire - United Kingdom

Re: Updating from a dataset

Postby TonyK » Wed Jun 29, 2011 2:01 pm

Thanks for the expanded example and book suggestion, I might well get it.

I've now created a test solution in which I have the following (the SQL argument would normally come from a global variable);

Code: Select all
var lvSQLQuery = 'Select * From incoming where callid=?';
var lvSQLArgs = new Array();
lvSQLArgs[0]='1049674';
var lvIncFS = databaseManager.getFoundSet('logger','incoming');
lvIncFS.loadRecords(lvSQLQuery,lvSQLArgs);
var lvRecords = lvIncFS.getSize();


However, the query produces the followiong error. My table has 12 columns, is that too many?

ERROR: subquery has too many columns
Position: 35
Wrapped com.servoy.j2db.dataprocessing.DataException: ERROR: subquery has too many columns
Position: 35 (C:\Users\Tony\servoy6_workspace\incTest\globals.js#13)
at C:\Users\Tony\servoy6_workspace\incTest\globals.js:13 (onSolutionOpen)
TonyK
 
Posts: 42
Joined: Mon Sep 27, 2010 6:07 pm

Re: Updating from a dataset

Postby patrick » Wed Jun 29, 2011 2:12 pm

when loading foundsets, query for the primary key:

Code: Select all
var lvSQLQuery = 'Select incoming_id From incoming where callid=?';
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Updating from a dataset

Postby TonyK » Wed Jun 29, 2011 2:28 pm

But the primary key is just a unique number and I don't know what range of id's I would want. I'm getting really quite confused now.

All I want to do is query a database table, examine the records and if the right conditions are met I want to update one record.
It seems that with a foundset I can't query on anything other than the primary key and with databaseManager.getDataSetByQuery(), I can't update the actual records because all I'm doing is extracting them into an array/dataset.

Is there not a straight-forward method for this process?
TonyK
 
Posts: 42
Joined: Mon Sep 27, 2010 6:07 pm

Re: Updating from a dataset

Postby jasantana » Wed Jun 29, 2011 3:29 pm

Tony what Patrick means is that I made a mistake in the SQL sentence and he is right. The SQL sentence to load a foundset must select only the pk field of the table even the fiundset will contain all the fields. In fact you just need to use the SQL he sent you, that is the same that you had but selectin just the pk field.
Best regards,
Juan Antonio Santana Medina
jasantana@nephos-solutions.co.uk
Servoy MVP 2015
Servoy 6.x - Servoy 7.x - Servoy 8.x - MySQL - PostgreSQL - Visual Foxpro 9
User avatar
jasantana
 
Posts: 555
Joined: Tue Aug 10, 2010 11:40 am
Location: Leeds - West Yorkshire - United Kingdom

Re: Updating from a dataset

Postby TonyK » Wed Jun 29, 2011 5:13 pm

Yes but querying only on the database pk is no good for what I'm trying to do.

Kind regards,

Tony
TonyK
 
Posts: 42
Joined: Mon Sep 27, 2010 6:07 pm

Re: Updating from a dataset

Postby jcompagner » Wed Jun 29, 2011 5:20 pm

with a foundset you can query on anything you want from that record, just look at the documentation/sample of Foundset.find()/Foundset.search()
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Updating from a dataset

Postby jasantana » Wed Jun 29, 2011 5:27 pm

Ok Tony, then you can create a dataset with the SQL you need and load the foundset based on that dataset.

Code: Select all
var _dataSet=databaseManager.getataSetByQuery('myserver','SELECT .......',Arguments,-1);
fs.loadRecords(_dataSet);


fs is the foundset you initialized before, like I did in my code. After that you can loop the foundset.
Best regards,
Juan Antonio Santana Medina
jasantana@nephos-solutions.co.uk
Servoy MVP 2015
Servoy 6.x - Servoy 7.x - Servoy 8.x - MySQL - PostgreSQL - Visual Foxpro 9
User avatar
jasantana
 
Posts: 555
Joined: Tue Aug 10, 2010 11:40 am
Location: Leeds - West Yorkshire - United Kingdom

Re: Updating from a dataset

Postby TonyK » Thu Jun 30, 2011 10:44 am

Thanks everyone, this has been an invaluable lesson and I have finally acheived what I was trying to do.

Kind regards,

Tony
TonyK
 
Posts: 42
Joined: Mon Sep 27, 2010 6:07 pm


Return to Methods

Who is online

Users browsing this forum: No registered users and 8 guests

cron