Use columnames with dataset

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Use columnames with dataset

Postby nick1461658753 » Fri Sep 09, 2016 1:47 pm

I have a query that I execute and it returns a dataset. I can get the data out with dataset.getvalue(row,col) (for example dataset.getValue(1,1) ), but I don't want to use column numbers but the column names instead. I can see the names are present in the dataset because a dataset.getColumnName(index) gives me correct names back. So is there a good reliable way to acces the data in a dataset with columnames? Something like dataset[rownumber][columnname]?
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Use columnames with dataset

Postby mboegem » Sat Sep 10, 2016 1:41 am

Hi,

within your loop you can set the dataset rowindex with something like:
Code: Select all
myDs.rowindex = i;


You can start using columnnames right away, but you probably want code completion on this as well.
This can be achieved using JSDocs as:
Code: Select all
var myQry = "SELECT client_id, client_name, NULL AS myExtraInfo FROM clients"
/** @type {JSDataSet<client_id:Number, client_name:String, myextrainfo:String>} */
var myDs = databaseManager.getDatasetByQuery(etc. etc. etc.)

for(var i = 1; i <= myDs.getMaxRowIndex(); i++) {
   myDs.rowindex = i;
   application.output(myDs.client_name);
   myDs.myextrainfo = 'some additional text'
}


Although the JSDoc needs some typing, your code is gonna look a whole lot better and you never have to think about shifting column index again whenever you insert an additional field in your query.
Also setting the value works quite convenient.

Only thing to bear in mind: Servoy handles all columnnames in lowercase.
That's why I converted the 'myExtraInfo' to lowercase already in the JSDoc

You could work on a bit of code which outputs the columnnames in JSDoc format already.
This will save you some work as your queries get bigger and return more columns.
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1740
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Use columnames with dataset

Postby mboegem » Sat Sep 10, 2016 1:47 am

As I could use the convenience of generating JSDoc as well, I thought of doing the job in between
Just put it in some 'tool' or 'utils' scope and pass a dataset to the function...

Code: Select all
/**
* @param {JSDataSet} _ds
*/
function generateDsJSDoc(_ds) {
   var _sName,
      _nType,
      _sType,
      _aColumns = [];
   for (var i = 1; i <= _ds.getMaxColumnIndex(); i++) {
      _sName = _ds.getColumnName(i).toLowerCase();
      _nType = _ds.getColumnType(i);
      switch(_nType) {
         case JSColumn.TEXT:
            _sType = ':String';
            break;
         case JSColumn.INTEGER:
         case JSColumn.NUMBER:
            _sType = ':Number';
            break;
         default:
            _sType = ''
      }
      
      _aColumns.push(_sName + _sType);
   }
   
   var _sDoc = '/** @type {JSDataSet<' + _aColumns.join(',') + '>} */';
   application.setClipboardContent(_sDoc);
   
   application.output('JSDoc has been placed in your clipboard:');
   application.output(_sDoc);
}
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1740
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Use columnames with dataset

Postby nick1461658753 » Sat Sep 10, 2016 5:29 pm

THX! this is exactely what I needed. works like a charm :)
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Use columnames with dataset

Postby nick1461658753 » Sat Sep 10, 2016 6:48 pm

An additional question. I'm getting an error, that I don't understand.

I have a Postgresql database, but there were some changes, so I deleted the database, created a new one and imported data into it. I have made a select query to get some data out of it.
I'm working with two databases, and doing the same on both databases. One gives me a dataset without any problems, the other gives the error.

Code: Select all
query = "";
   query += "SELECT ";
   query += " * ";
   query += "FROM ";
   query += "collection ";
   query += "WHERE cmc_thid = ? ";

   Dataset = databaseManager.getDataSetByQuery( DB, query, ["45:80006501:AAEC7704"], 1 );


ERROR: relation "collection" does not exist
Position: 16
Wrapped java.lang.RuntimeException: com.servoy.j2db.dataprocessing.DataException: ERROR: relation "collection" does not exist
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Use columnames with dataset

Postby mboegem » Sat Sep 10, 2016 9:44 pm

Your problem is in the PG db.
Quick search on Google returned this:
http://stackoverflow.com/questions/14489105/postgresql-relation-table-does-not-exist
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1740
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Use columnames with dataset

Postby Bernd.N » Sun Sep 11, 2016 2:28 am

You also could use the \ to write SQL strings over several lines, like

Code: Select all
sSQL = "SELECT * FROM collection WHERE \
           column1 = ? AND \
           column2 = ?";

One needs to put a blank before the \ and no blank behind.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Use columnames with dataset

Postby nick1461658753 » Sun Sep 11, 2016 5:30 pm

I'm not really familiar with Postgresql. But why do i need to make a relation? "collection" is just a table in the database, i can make a form with this table as a datasource, so I don't really understand why I get the error...
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Use columnames with dataset

Postby Bernd.N » Sun Sep 11, 2016 6:50 pm

That is the standard error message in that case.
You will get the same message when you try a "select * from x", when x is not existent.

Codd also did not talk about tables but about relations, so a relation is represented by a table, see https://en.wikipedia.org/wiki/Relation_(database)
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Use columnames with dataset

Postby nick1461658753 » Wed Sep 14, 2016 10:13 am

We changed the columnnames to all lowercase, and now it works. So I guess it was a conflict between PG and Servoy in the columnnames.
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Use columnames with dataset

Postby ROCLASI » Thu Sep 15, 2016 8:32 am

nick1461658753 wrote:We changed the columnnames to all lowercase, and now it works. So I guess it was a conflict between PG and Servoy in the columnnames.


Be aware that database objects are case sensitive.
PostgreSQL defaults to lowercase (Oracle for instance defaults to uppercase) so when you use SQL like you did then it assumes you mean "collection".
Lets say your table name is "Collection" then you need to put the object name between double quotes.
So you SQL would look like this:
Code: Select all
var query = "SELECT \
   * \
   FROM \"Collection\" \
   WHERE cmc_thid = ? ";

Dataset = databaseManager.getDataSetByQuery( DB, query, ["45:80006501:AAEC7704"], 1 );


Hope this makes things more clear.
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


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 3 guests