Hi Jan
Unfortunatly not, may be it has something to do that my query works on a table with a composite key, i. e. the primary key is built up of 3 attributes (columns). Whenever I add another non-primary key column, I get this error message (see appendix)
Any idea?
Best regards, Robert
jaleman:
Robert Huber:
Jan, should I make a request for the ability to be able to select NON Primary key attributes in a select statement (see my select above) as this is quite a severe restriction for an otherwise flexible way of selecting data or is this already on the way?
I think I am missing something, you can select anything in a sql statement as far as I know?
Thanks Jan, I had one ’ too much in the parameter list and that caused the true/false parameter to misbehave. Now it works in my solution as well so no bug in Servoy ![:-)]( "Smile")
The bug was here, may be the summer heat .-(
Best regards, Robert
jaleman:
Robert Huber:
I think I found an error with the 4th parameter true/false (displaying column names). When you set the parameter to false, the colum names are still displayed!?
var q = 'select companyname, city from customers'
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), q, null, 3);
application.output(dataset.getAsText(‘,’,‘\n’,‘"’,true))
application.output(dataset.getAsText(‘,’,‘\n’,‘"’,false))
results in:
"companyname","city"
"Alfreds Futterkiste","LA"
"Ana Trujillo Emparedados y helados","Mexico D.F."
"Antonio Moreno Taqueria","Mexico D.F."
"Alfreds Futterkiste","LA"
"Ana Trujillo Emparedados y helados","Mexico D.F."
"Antonio Moreno Taqueria","Mexico D.F."
I still have no idea what you are doing, how about you paste the actual code so we get an idea?
Here ist the code leading to the error message when I add an additional column (in bold9 to the select stmt:
When I change the SELECT … to
" SELECT pc.persons_role_person_id, pc.persons_role_role_id, pc.subject_code, pc.effective_from" +
Primary Keys for table persons_capabilities: persons_role_person_id, persons_role_role_id, subject_code
Primary Keys for table persons_roles: person_id, role_id
Primary Key for table persons: id
/*
Title: Loads list of experts who have the capability to act as experts for the selected subject
Author: Robert Huber
Created: 18.07.2007
Modified: -
Arguments: getExpertsForSubject()
Returns: -
Notes: Foundset sorted by persons name
*/
var arguments = new Array();
arguments[0] = forms.ScoSubjects.code;
arguments[0] = ‘B’;
var query =
" SELECT pc.persons_role_person_id, pc.persons_role_role_id, pc.subject_code" +
" FROM persons_capabilities pc INNER JOIN persons_roles pr" +
" ON (pc.persons_role_person_id = pr.person_id" +
" AND pc.persons_role_role_id = pr.role_id)" +
" AND pc.persons_role_role_id = 9" +
" AND pc.subject_code = ?" +
" INNER JOIN persons p" +
" ON pr.person_id = p.id" +
" ORDER BY p.name";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, arguments, 1000);
controller.loadRecords(dataset);
Thanks, Robert
jaleman:
I still have no idea what you are doing, how about you paste the actual code so we get an idea?
you can only select the pk’s and make sure that they are in the right order if you want to use loadrecords by a dataset.
If you don’t use loadrecords (eg to display them as text or html) it doesn’t matter.
Hi Johann
I am not sure what you are saying. Are you saying (as I assume) that you can only specify the pks if you want to use loadrecords? The order MUST BE alphabetically sorted, corrert?
We have the very need to get also other attributes (columns) than the pks. How to achieve the result of such a SQL statement (with other columns than the pks)? I found only very complicate ways with non DB relations as in FMP
and Calculations and … - usually not very stable solutions.
I thought (I read or heard) you are going to implement that possibility or should I make a feature request?
Thanks, Robert
jcompagner:
you can only select the pk’s and make sure that they are in the right order if you want to use loadrecords by a dataset.
Hi Jan
I have to display db table columns from more than one table. That’s what a join is baout, isn’t it? But it seems, that the current Servoy version is not supporting that?
How to do it without a miriad of (additional) relationships, calculated fields and the like? I don’t like to think about “programming methods” as in FileMaker, hopefully you point me to a better way ![:-)]( "Smile")
Thanks, Robert
jaleman:
If you don’t use loadrecords (eg to display them as text or html) it doesn’t matter.
What do you mean? In Servoy it’s extremely straighforward. If you want to map the data to a current form you loadrecords with just the pk dataset. After that you can map anything else to it, related data, calcs, etc, etc.
If you run your own SQL with more than PK’s you run your own show and display it in anyway you like.
That’s what I like about Servoy: You get the best of both worlds and depending on your needs you pick the right way to do it.
jaleman:
What do you mean? In Servoy it’s extremely straighforward. If you want to map the data to a current form you loadrecords with just the pk dataset. After that you can map anything else to it, related data, calcs, etc, etc.
May be I am missing something, but in the phrase … after that you can map anything else to, related data, calcls, … lays the problem. What if the related data is more than one relationship away? The . (dot) notation works only for calcs and that’s by far not always sufficient. What if you need a join (SQL statement) about more than 2 tables where you need to specify the non-pk column(s) to make the SQL statement work at all? For example left/right outer joins won’t (usually) work without all involved columns and they are not always pk columns.
jaleman:
If you run your own SQL with more than PK’s you run your own show and display it in anyway you like.
I don’t understand what you mean here. Can I find somewhere an example of a SQL statement joining 2 or more tables with more than the pk columns and loading this into a form?
jaleman:
That’s what I like about Servoy: You get the best of both worlds and depending on your needs you pick the right way to do it.
I seem to have sometime difficulties to pick the right way .-) Or my demands are different than yours ![:-)]( "Smile")
I hope we find some time to discuss that at Servoy World - you are invited to a Beer ![:-)]( "Smile")
Thanks and good night, Robert
ServoyWorld is THE opportunity to talk to us and many new features have been born at one of the conferences! Make sure to talk to Rob too, he enhanced the current SQL generator in 3.5.
Robert Huber:
May be I am missing something, but in the phrase … after that you can map anything else to, related data, calcls, … lays the problem. What if the related data is more than one relationship away? The . (dot) notation works only for calcs and that’s by far not always sufficient. What if you need a join (SQL statement) about more than 2 tables where you need to specify the non-pk column(s) to make the SQL statement work at all? For example left/right outer joins won’t (usually) work without all involved columns and they are not always pk columns.
Hi Robert,
When you need to, you can use databaseManager.getDataSetByQuery() to pretty much do anything you can imagine with your data – in fact anything that is allowed by your database.
We build most our dropdown lists, popup menus and reports this way – sending queries and arguments to a set of methods in a reusable module.
You can jump two or more hops away in your data and do a ‘fast’ local query on that data, and you can ‘SELECT DISTINCT’ on a foreign key etc.
This also removes the need to define a relationship only for the purpose of getting hold of some valuelist.
Hi Christian
So you can do a SQL join AND specify other than the primary keys of the underlying forms table and display that data on the form?
I can’t (I always get an error message of some sort) but would of course love to do!
If you have any working example I am very interested in it.
Best regards, Robert
swingman:
Robert Huber:
May be I am missing something, but in the phrase … after that you can map anything else to, related data, calcls, … lays the problem. What if the related data is more than one relationship away? The . (dot) notation works only for calcs and that’s by far not always sufficient. What if you need a join (SQL statement) about more than 2 tables where you need to specify the non-pk column(s) to make the SQL statement work at all? For example left/right outer joins won’t (usually) work without all involved columns and they are not always pk columns.
Hi Robert,
When you need to, you can use databaseManager.getDataSetByQuery() to pretty much do anything you can imagine with your data – in fact anything that is allowed by your database.
We build most our dropdown lists, popup menus and reports this way – sending queries and arguments to a set of methods in a reusable module.
You can jump two or more hops away in your data and do a ‘fast’ local query on that data, and you can ‘SELECT DISTINCT’ on a foreign key etc.
This also removes the need to define a relationship only for the purpose of getting hold of some valuelist.
you can only set the pk’s of a table where the form is build on.
So you can have a very big query with all kind of joins. But the end result will be the pk’s of the main table because those records is what you are loading with loadRecords.
You can’t also load directly in that form pk’s of a relation.. If you want to show all kinds of stuff very deeply you can do that but you have to use calcs or use tabpanels.
Hi Johann
Thanks for the clarifying, that’s what I found (is the behaviour of the version 3.5).
It would greatly improve the SQL based functionality in Servoy when we would be able to use non-pk columns with loadRecords. Now it’s limiting as some SQL statements need non-pk columns to work (get the desired result).
So I am going to make a feature request. May be in a first attempt for using the non-pk columns of the forms table. But it would also be very useful for non-pks of related join table columns.
Thanks, Robert
jcompagner:
you can only set the pk’s of a table where the form is build on.
So you can have a very big query with all kind of joins. But the end result will be the pk’s of the main table because those records is what you are loading with loadRecords.
You can’t also load directly in that form pk’s of a relation.. If you want to show all kinds of stuff very deeply you can do that but you have to use calcs or use tabpanels.
i think i can fix that yes. So that you can have all the extra columns that you want after the pk’s
Johann
That would be absolutly great! Even for non-pk columns of joined tables? I can’t believe it ![:-)]( "Smile")
) Beer at Servoy World for sure!
Many thanks, Robert
jcompagner:
i think i can fix that yes. So that you can have all the extra columns that you want after the pk’s
Hi Robert and Johan,
I guess I don’t understand or perhaps there is a misunderstanding in this post. What is the point of having non-PK values in a loadForm(dataset) on a regular form tied to a table? Presumably this is a ‘query’ that will be repeated frequently to show data from a variety of tables. And Robert wants to ‘show’ this data within a regular Servoy form rather than an HTML table or whatever. In that case wouldn’t it be much more efficient and faster to use a view created in the database and tie that to a form as we can now do?
Hi John
For some SQL statements to work (e.g. LEFT/RIGHT OUTER JOIN) you need to list the involved columns and that are quite often non-pk columns. If you can’t set the column in the SELECT col1, col2, … part, the SQL won’t give the desired result. It’s as you specify a SQL stmt in Interactive SQL, they do not consist only of pk columns, do they?
Regarding views, I think if you have to define a view only because the tool can’t handle full SQL statements (i. e. the same statments you can define in SQL Plus, Interactive SQL or any other tool) the software get’s very much bloated, complicated to support and much more error prone; beside the fact that the properties of views are not the same as that tables. And there are companies which avoid views as much as possible because of their “problems”. I think it’s a may be a bit similar as stored procedures, at first they were very welcome and looked at as step forward, but nowadays?
I think we only can create good, stable software when we can do things straight forward, otherwise it’s getting a workaraound programming software. BTW, Jan wrote me an email with this title when I was first interested in Servoy:
“Sind Sie work around-muede?”
I definitly was (with FMP) and that was beside other criteria the hope I would get with Servoy ![:-)]( "Smile")
Best regards, Robert
PS: John, are you coming to Servoy World this year? Would be great to met you again!
john.allen:
Hi Robert and Johan,
I guess I don’t understand or perhaps there is a misunderstanding in this post. What is the point of having non-PK values in a loadForm(dataset) on a regular form tied to a table? Presumably this is a ‘query’ that will be repeated frequently to show data from a variety of tables. And Robert wants to ‘show’ this data within a regular Servoy form rather than an HTML table or whatever. In that case wouldn’t it be much more efficient and faster to use a view created in the database and tie that to a form as we can now do?
Hi Robert,
Yes I’m planning on going to Servoy World and I’m looking forward to meeting up again!
First to this thread though!
I’m a little confused by some of your statements and/or I disagree with some of your conclusions. Let me start though by asking:
Do you intend to simply view this dataset or do you intend to edit this dataset? It is a pretty fundamental question, I think, in approaching anything in Servoy and it isn’t clear to me what you want to do with this data once you have it.
If I had to pick out the single biggest feature that puts Servoy in a completely different league than FM, it is the separation of database (with data) and the business logic/rules. I think the hardest thing for people coming from an FM background to Servoy is allowing their database to do the things that the SQL database does best and NOT trying to put everything down to Servoy and do everything through Servoy just because you can. THAT is what worries me in terms of bloatware and if Servoy tries to be everything to everyone. For example what does this mean?
I think it’s a may be a bit similar as stored procedures, at first they were very welcome and looked at as step forward, but nowadays?
Are you saying you don’t see the usefulness/utility/benefits of stored procedures? Or you don’t see those benefits in conjunction with Servoy? I don’t understand. ![:?]( "Confused")
Stored procedures have been developed over the last 20+ years in conjunction with all major SQL databases to optimize queries. Something that can take minutes to run as a standard query can run in seconds as a stored procedure, etc., with or without Servoy. What’s the downside?
Similar with views. You don’t want to directly EDIT data in views. Of course not. But what works GREAT with Servoy is to use a view as the basis for a form and then take column/row data from that view to relate back to the source table.column_row and edit with that. A view, after all, is really just an SQL SELECT statement. Throw in a ‘CREATE OR REPLACE VIEW AS’ in front of your query, make it dynamic to allow the user to define the ‘subject_code’ and you have your form. Using SQL hand-in-glove with Servoy is not workaround, FM-style programming. Far from it in my book. It is taking advantage of, working with and maximizing the qualities of your SQL backend. Otherwise might as well have simply a bunch of Excel sheets as your backend and use Servoy to ODBC your way around it! ![:D]( "Very Happy")
