SQL Queries

Release notes for Servoy betas

SQL Queries

Postby Nicolas Daum » Mon Oct 27, 2003 3:22 am

Hello List

I am currently evaluating Servoy. I am a 4D developer. I used to work with FMPro too. I am shopping for an alternate tool with standard languages.

I just read the 'Servoy Developer.pdf' doc. and I have a couple of questions to start with.

* I saw nothing concerning SQL Queries. It is a must-have for me. I managed with lots of work to mimick queries in 4D but it's not the real thing. Is there anything to edit queries spanning more than one table and its related child tables? If so how to view them? If not is there a plan to implement queries in the near future? I read somewhere about temporary tables but it's not clear.

* The portal feature looks pretty much like in FMPro. In case of joins (or so-called Many to Many relationships) how is it possible to display fields from the other Many table? For example in an Order form, how to display the product name, reference, etc. in the portal lines. From page 71 I have the impression it's Calculations that make it possible. If so it's very FMPro like. And it's quite a no-no for me.

TIA
Cheers
Nicolas Daum
 

Postby maarten » Mon Oct 27, 2003 9:48 pm

Hi Nicolas,

Welcome to Servoy talk!

Is there anything to edit queries spanning more than one table and its related child tables? If so how to view them?

Go into the method editor and check out the Databasemanager tree.
You can do lot's of SQL stuff here, including locking and transaction control.
rightclicking on a method gives you some sample code to figure them out.
Here's an example:
//get a dataset based on query
var maxReturedRows = 10;//usefull to limit number of rows
var query = 'select c1,c2,c3 from test_table';//do not use '.' or special chars in names or aliases if you want to access data by name
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);

//place in label: elements.myLabel.text = '<html>'+dataset.getAsHTML()+'</html>';

//example to calc a strange total
global_total = 0;
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i;
global_total = global_total + dataset.c1 + dataset[3];
}
//example to assign to dataprovider
//employee_salary = dataset.getValue(row,column)

In case of joins (or so-called Many to Many relationships) how is it possible to display fields from the other Many table? For example in an Order form, how to display the product name, reference, etc. in the portal lines. From page 71 I have the impression it's Calculations that make it possible.

when using portals you can indeed only go 1 level deep. (unless you use calcs of course)
But you also have tabpanels in Servoy, where you can go down multiple levels, with complete scripting freedom within each level.
In your example I would:
build a tabpanel in the orderform that displays an other form (line items list) based on orders_to_orderitems relation.
In the line_items list you can show product stuff by putting in related fields to products. You can nest as many forms as you wish.
In scripting, you can also go down multiple levels by "chaining" relations to each other in order to get some value:
var x = relationA_to_B.relationB_to_C.myColumnInTableC

Valuelists can also depend dynamically on SQL.
Check out the Application tree/samplecode>>
application.setValueListItems( name, display_val_array/dataset, [real_values_array])

Just let me know if you need more help here.
Maarten Berkenbosch
User avatar
maarten
 
Posts: 797
Joined: Wed Apr 23, 2003 10:52 pm
Location: Amersfoort, Netherlands

SQL Queries

Postby Nicolas Daum » Tue Oct 28, 2003 11:48 am

Hi Maarten

Thanks for the quick answer. I found the features you mentionned.
I'll continue my evaluation.

Cheers

Nick
Nicolas Daum
 

Postby JDW » Sat Feb 14, 2004 8:35 pm

I too was curious about Servoy methods for accessing related data several tables "away", and also not interested in continuing to 'shunt' data through calculating fields as is commonly done in Filemaker 6 and below.

In additon to Maarten's notes above, it's interesting to note that non-stored calculations in Servoy do not appear to create a column in the database (and are stored only in the repository and evaluated when needed, I assume). To my mind, this is an advantage over Filemaker as your schema should stay more "pure".

Cheers.
JDW
 
Posts: 71
Joined: Sun Feb 01, 2004 1:50 am


Return to Latest Releases

Who is online

Users browsing this forum: No registered users and 2 guests

cron