SQL Query Question

[rochard]

So I've created an SQL Query using the Query Analyzer and it works and gives the results I want but now I want to include that in a method. The query is:

SELECT M.CompanyName,
R.ReservationID,
R.LocationID,
R.InDate,
R.OutDate,
R.RoomTotal
FROM tblReservationRoom R,
tblMembers M,
tblReservation O
WHERE R.ReservationID = O.ReservationID
AND M.MemberID = O.MemberID

so, if I am correct in this, I would use

databaseManager.getDataSetByQuery

but I don't really want to display the results as html so can I simply create a report form with sub-summaries and the columns listed in the SQL query and then have the results displayed accordingly?

TIA

[ebrandt]

I as well am approaching this functionality and need to better to understand it in order to complete my initial design spec. I want to utilize the power of sql to run reports containing data from several tables.

In filemaker I had to create several calculations in a table that contained information that exsisted already in other tables. This was very repetitious but I was limited by the architechture of the development enviorment. I ended up with multiptle imports from many tables loaded into a report with multiple summary fields and parts.

I understand SQL queries but was a bit unsure myself as to how to capture the dataset and display it in a report format. As far as I understand I would use the databaseManager.getDataSetByQuery. Then I am guessing I would use an array (have not yet used one).

How can the data be returned so that the rows and colums can be grouped and summarized, are the standard form parts used as in filemaker? Can the data be edited or drilled down on in the report result without complex methods.

I realize that there are many ways to do things, plus I am not asking a specific question. I am hoping to better unserstand a few new concepts and how to best optimize the performance of my solution.

Is there a simple example of a few tables with a couple fields where a query is run and the data is formated in a servoy form?

If anyone gets bored, please feel free to help a brother out.

Thanks,
Erich

[ebrandt]

Looks Like I found the answer in the forum, I am going to have to learn HTML, looks like my development time will take a bit longer. On the upside Its another skill for the set. I am still curious as to the performance issues regarding form part reports V.S. SQL/ HTML. If I use summaries and forms one relation deep, will performance suffer as with filemaker?

[david]

I haven't done this yet but it seems like you need the fourth example given with the function: forms.actions.controller.loadRecords(Object[]).

Code: Select all

//4) to load records in to the form based on a query (also known as 'Form by query')

forms.actions.controller.loadRecords(sqlstring,parameters);

//limitations/requirements for sqlstring are:
//-must start with 'select'
//-must contain 'from' and 'order by' keywords
//-must at least select from the table used in Servoy Form
//-cannot contain 'group by' or 'having'
//-all columns must be fully qualified like 'orders.order_id'
//-the selected columns must be the (Servoy Form) table primarykey columns (alphabetically ordered like 'select a_id, b_id,c_id ...')
//-can contain '?' which are replaced with values from the array supplied to parameters function argument

Add a showPrintPreview step after loading up the form and I think that would do it.

- David

[ebrandt]

David,

I hope all is going well with the magazine and violin project. I want to thank you again for the seminar and the extra time. I have alot of Ideas on how to improve the seminar format (from a newbie perspective).

The only problem I see with this method is that you must exclude 'Group By' & 'Having' from the select statment, this is critical. This method is getting very close to really increasing the concept or servoys RAD enviorment.

If a SQL statement result could be incorporated into the Form Part Report concept, this would be a very powerful tool.

Thanks again I always appreciate a push in the right direction.

Erich

[david]

I hope all is going well with the magazine and violin project. I want to thank you again for the seminar and the extra time. I have alot of Ideas on how to improve the seminar format (from a newbie perspective).

All I can say is that I'm glad I like working with Servoy because it has taken over my life. I would also absolutely LOVE to hear your ideas on the Seminar format -- would help with the next one coming up. Drop me an email if you can in your spare time.

The only problem I see with this method is that you must exclude 'Group By' & 'Having' from the select statment, this is critical. This method is getting very close to really increasing the concept or servoys RAD enviorment.

If a SQL statement result could be incorporated into the Form Part Report concept, this would be a very powerful tool.

I suspect that you can get around "group by" because you can dump your returned data set into a form that has sub-summary parts. Make sure you have an "order by" clause.

Not being able to use the "having" clause in this case is a bummer.

I haven't done a test on all the various options yet to see what is the best way and what the various limitations are. Would love to hear what you come up with. For myself, I've been using the html method with embedded method triggers as Jaleman and/or Mattman have outline previously. Like them, I use GoLive to help with html production. Servoy's ability to display html and CSS seems to be where the future in reporting is at -- so much flexibility.

Check out this brilliant thread for more ideas:

http://forum.servoy.com/viewtopic.php?t=1936

- David

[ebrandt]

Muchas Gracious,
I'll be intouch

[cjemichael]

I strongly advise avoiding list views where possible and using SQL queries and looping through to load records into an html table. You can make the html look however you want using style sheets within the method which creates the html.

The data is retrieved much faster and also your data can be normalised (i.e. with SQL you can create joins which find data based on tables several layers deep rather than using calculation fields).

It also means that you can use a single global field for all reports - makes the solution simpler and easier to de-bug/upgrade etc.

[maarten]

ok, let's take an example of how to create a contactlist with company names
table companies ; columns company_id, company_name
table contacts ; columns contact_id, contact_name, company_id

Code: Select all

var myQuery = " SELECT contacts.contact_name, companies.company_name"+
" FROM contacts, companies"+
" WHERE contacts.company_id = companies.company_id";

var maxRows = 100 //this limits amount of rows returned

capture the query result in a dataset
(check out the database manager node in the method editor)

Code: Select all

var myDataset = databaseManager.getDataSetByQuery(controller.getServerName(), myQuery, null, maxRows)

myDataset is now an object that in fact looks like a matrix.
col1 col2
John Sony
James Philips
Henry Technics

Now you can use all methods inside the Datamanager>JSDataset to extract data from the Dataset.
e.g. myDataset.getValue(2,2) will return "Philips"

In our case we are going to use
myDataset.rowIndex to set the row of the dataset as we loop through it.
roomDataset[2] to retrieve a column, where [2] reflects the column nr. (company_name)

If you're not familiar with HTML have a look at this basic structure.

Code: Select all

<html>  //always start the html code with "html" lowercase , in order to trigger the html renderer in Servoy
<TABLE> // start creating table
   <TR> <TD></TD> <TD></TD> </TR>  // "<TR>" stands for a row, "<TD>" for a cell
   <TR> <TD></TD> <TD></TD> </TR>  // note that each opening tag"<TD>" is closed again by a "slash" tag  </TD>
</TABLE>
// you can add all kinds of design elements to tags.(alignment, colors, borders etc.)
// search on  google for html info about html editors, lessons etc.

So here is where the data and html come together...

Code: Select all

var HTML = "<html>"+  // all the HTML code is captured inside this var.
"<TABLE>";
for( var i = 1 ; i <= myDataset.getMaxRowIndex() ; i++ )
{
myDataset.rowIndex = i;  //set the row
HTML += '<TR bgcolor="#ffff33">'+
'<TD align=right> '+myDataset[1]+'</TD>'+ //refers to contact_name
'<TD align=right> '+myDataset[2]+'</TD>'+//refers tocompany_name
'</TR>'
}

Now that you have all HTML code lined up do following:
1)create a global named myHTMLReport
2) place it in any form you want
3) set the display type property to "HTMLarea", and make it non editable

add this line in your script:

Code: Select all

globals.myHTMLReport = HTML  //fill the global with the html created in this script.

Attach this script to the onShow event of the form. (or perhaps other events)

[ebrandt]

Thank you very much, this is very helpful.