Really basic I think but has me stumped for the moment:
var maxReturnedRows = -1;
var query = “SELECT * FROM abc”
dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
for( var i = 1; i <= dataset.getMaxRowIndex(); i++ )
{
dataset.rowIndex = i;
}
Q. How do I reference the columns in the dataset by name?
You can’t. You can iterate over the columns until you find the one with the name you are looking for. Then use that index to get to the column
patrick:
You can’t. You can iterate over the columns until you find the one with the name you are looking for. Then use that index to get to the column
Thanks for the feedback Patrick - that seems like a lot of work to reference the column!
Suppose I knew the column index Patrick (obviously I can grab it by hand from the dbase) - how would I reference it to update the data?
Would it be best to create a var for each index I’d like to access (10) and use those to reference them in code?
I think the cleanest approach is to have a select list instead of *. So something like
SELECT column_1, column_2, column_3 FROM table
Then you know what is where. A program like AquaDataStudio can create such a select statement for you, if your table has lots of columns.
Once you have your dataset, you can then say
dataset.setValue(row, column, value)
to “update” something in your dataset (you are aware of the fact that you are updating an in memory set and not something in the database?).
Patrick
This is a really cut down sample - but using dataset.setValue(1, 40, yearToSet) does not seem to work?
function execute_year_sqlNEW()
{
var sqlToRun = arguments[0]
var setZero = arguments[1]
var whereHigher = arguments[2]
var baseYear1 = arguments[3]
var yearToSet = arguments[4]
var multiRecordUpdate = arguments[5]
//Start SQL Work Here
//Get Dataset of SQL
var maxReturnedRows = -1;
var query = sqlToRun // Query MUST be well formed - Taken from Year Setting Form SQL field
var dataset = ''
dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
//Step through rows and do work
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i;
dataset.setValue(1,40,yearToSet)
databaseManager.saveData();
}
}
All of the arguments work and the dataset is as expected - seems like the updating of those columns is just not happening? Have I done something stupid?
to “update” something in your dataset (you are aware of the fact that you are updating an in memory set and not something in the database?).
I assume you mean did I do a saveData?
hmm. I don’t understand what you are trying to do. Imagine a dataset as an excel sheet with rows and columns. setValue(1, 40, someValue) will set the “cell” at row 1, column 40 to someValue. What I don’t get here is why you are doing this loop. Inside of your loop your are always assigning yearToSet to cell 1,40. So you are always updating the same cell!? I’d understand this: dataset.setValue(i,40,yearToSet) inside your loop… Also, setting the rowIndex is of no use, since you already pass the row you want to update to setValue(). So you can simply leave that out.
About this:
to “update” something in your dataset (you are aware of the fact that you are updating an in memory set and not something in the database?).
I assume you mean did I do a saveData?
getDataSetByQuery loads data from the database into a thing called JSDataSet. The dataset is held in memory and not linked in any way to the database, it just holds the result of your query. It is as if you had exported data to Excel. If you set some cell in a dataset, you do that in that dataset only. Nothing will ever happen in the database. Just like if you had exported something to Excel and now you change a cell in Excel. This will not do anything in the database where you have exported the data from…
patrick:
hmm. I don’t understand what you are trying to do. Imagine a dataset as an excel sheet with rows and columns. setValue(1, 40, someValue) will set the “cell” at row 1, column 40 to someValue. What I don’t get here is why you are doing this loop. Inside of your loop your are always assigning yearToSet to cell 1,40. So you are always updating the same cell!? I’d understand this: dataset.setValue(i,40,yearToSet) inside your loop… Also, setting the rowIndex is of no use, since you already pass the row you want to update to setValue(). So you can simply leave that out.
I got your points Patrick - I’ve obviously missed a concept here with Javascript looping.
My intention is this:
Get recordset based on query, Step through each record in the set and adjust column (Index is 40) to a specific value (as supplied in the arguments).
My understanding was that ‘for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )’ would step through that dataset - and I could adjust column 40 with each iteration of the loop. Again I thought I need to ‘dataset.rowIndex = i;’ to make that row selected before I do a setValue()???
Since the dataset is moving one row at a time - surely the row will always be setValue(1,40,value)??? Or should that move with variable (i)???
setValue() expects the row number. So “stepping” through the dataset does not make any sense with setValue().
Just change your code to
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.setValue(i,40,yearToSet);
}
and you are done.
And again: calling saveData() does not make any sense. saveData() writes outstanding changes to the database. When manipulating a dataset, you never make any changes to the database (only to your dataset).
patrick:
And again: calling saveData() does not make any sense. saveData() writes outstanding changes to the database. When manipulating a dataset, you never make any changes to the database (only to your dataset).
So how do I write the dataset changes back to the database? After all its the database data I’m intending to change!
After all its the database data I’m intending to change!
That was my impression the whole time and I really tried hard to explain that with a dataset you are on the wrong track. Why don’t you just get a foundset? You can something like this:
function execute_year_sqlNEW()
{
var sqlToRun = arguments[0]
var setZero = arguments[1]
var whereHigher = arguments[2]
var baseYear1 = arguments[3]
var yearToSet = arguments[4]
var multiRecordUpdate = arguments[5]
var tableName = ?? // you need to know which table to deal with
var columnName = ?? // the name of the column you want to update
//Start SQL Work Here
//Get Dataset of SQL
var query = sqlToRun // Query MUST be well formed - Taken from Year Setting Form SQL field
var myfoundset = databaseManager.getFoundSet(controller.getServerName(), tableName);
myfoundset.loadRecords(query);
//Step through rows and do work
for( var i = 1 ; i <= myfoundset.getSize() ; i++ )
{
var myrecord = myfoundset[i];
myrecord[columnName] = yearToSet;
}
databaseManager.saveData();
}
Kahuna:
Really basic I think but has me stumped for the moment:var maxReturnedRows = -1;
var query = “SELECT * FROM abc”
dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
for( var i = 1; i <= dataset.getMaxRowIndex(); i++ )
{
dataset.rowIndex = i;}
Q. How do I reference the columns in the dataset by name?
You could add a feature request/issue for this.
In Servoy Tano the following is possible:
var _ds = databaseManager.getDataSetByQuery('example_data','select * from customers',null,-1);
_ds.rowIndex = 1; //sets the active row
application.output(_ds.customerid); //outputs the value of the customerid column on the active row
Note: you will be able to access values in a certain row/column using the following notation as well:
_ds[4][5]; //to access the value in the 5th cell on the 4th row.
@Kahuna: if what you really want to do is update the database, use foundsets instead.
pbakker:
@Kahuna: if what you really want to do is update the database, use foundsets instead.
He already started a new thread about this using foundsets.
viewtopic.php?t=13007
@Patrick - excellent thanks Patrick, makes complete sense!
@Laurian - this was more down to my lack of understanding of how Servoy treats datasets created like this - foundsets are the way to go for this it seems!
@Paul - Thanks for the heads-up on Tano.
@Robert - many thanks for your feedback.
pbakker:
In Servoy Tano the following is possible:var _ds = databaseManager.getDataSetByQuery('example_data','select * from customers',null,-1);
_ds.rowIndex = 1; //sets the active row
application.output(_ds.customerid); //outputs the value of the customerid column on the active row
Note: you will be able to access values in a certain row/column using the following notation as well:_ds[4][5]; //to access the value in the 5th cell on the 4th row.
@Kahuna: if what you really want to do is update the database, use foundsets instead.
In tano you can now also do:
_ds[4].columname
You shouldnt set the rowIndex if you use the _ds[rowindex][columnindex] or _ds[rowindex].columnname notation
If you set the row index then you fall back into the old level of doing stuff it is then _ds[columnindex]