Viewing the SQL query results in horizontal format.

Classic Servoy
1

Hi,

I have the following SQL query that I like to view it in Servoy form in the horizontal format:
maxRows = 20;
var query4 = “select household_id from household where household_id = " + persons_in_household_to_household.household_id + " )”;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query4, null, maxRows);

globals.Filed36_text = ‘’+dataset.getAsHTML() + ‘’

What I get as an out put is:

household_id

2
2
4
5

I like to out put it as:

household_id : 2 2 4 5

Is this possible?

Thanks in advance,

Abrahim

2

If you want to go with html one of the possibilities is to build an html table.

For an example have a look at: http://www.servoymagazine.com/home/2005 … ing_h.html

Hope it helps

3

Enrico,

can we do it is SQL?

Thanks,

Abrahim

4

Yes, probably something like:

var query = "select field_one, field_two  from your_table where your_condition"
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 999999999);
var col_count = 0;
var temp_html = '<html><table><tr>' // starts the html string
for ( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
    dataset.rowIndex = i;
    temp_html += '<td>' + dataset[1] + '
'+dataset[2]+'</td>'; // displays field_one and field_two
    if (++col_count == 4)// max 4 values for each row of data
    {
        temp_html += '</tr><tr>';
        col_count = 0;// new row of data
    }

}
temp_html += '</tr></table></html>' // closes the html string
5

Enrico,

Here is what I tried, but I get the blank output in Servoy form

var query4 = "select subj_num , id_birthdate from ID where subj_num = " + persons_in_household_to_id.id_biological_mother + " ";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query4, null, 999999999);
var col_count = 0;
var Filed1_text = ‘

’ // starts the html string
for ( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i;
Filed1_text += ‘'; // displays field_one and field_two
if (++col_count == 4)// max 4 values for each row of data
{
Filed1_text += ‘’;
col_count = 0;// new row of data
}

}
Filed1_text += ‘

’ + dataset[1] + ’
‘+dataset[2]+’
’ // closes the html string

I assumed temp_html is where the output is supposed to be. Therefore, I changed it to Filed1_text which I set it up as a Global filed that is set in the Servoy form to HTML format.

Thanks,

Abrahim

6

akalehzan:
var query4 = "select subj_num , id_birthdate from ID where subj_num = " + persons_in_household_to_id.id_biological_mother + " ";

First: try putting an ’ around your where variable:

var query4 = "select subj_num , id_birthdate from ID where subj_num = '" + persons_in_household_to_id.id_biological_mother + "' ";

the query string generated at runtime should be something like … where field_1 = ‘123’ …

Second: try running your method in the debugger… so you can easily check what is going on… the query is ok? the dataset is the one expected? the loop is ok? the generated html string is ok? etc etc.

7

Enrico,

After I changed the SQL script, we are getting SQL output as the following:

3459 1961-08-16 00:00:00.0

Then, if I clear the above out put and run the script again, then nothing show up.

var query3 = "select subj_num, id_birthdate from ID where subj_num = ‘1814’ ";

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query3, null, 999999999);
var col_count = 0;
var Field12_text = ‘

’ // starts the html string
for ( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i;
globals.Field12_text += ‘'; // displays field_one and field_two
if (++col_count == 4)// max 4 values for each row of data
{
globals.Field12_text += ‘’;
col_count = 0;// new row of data
}

}
globals.Field12_text += ‘

’ + dataset[1] + ’
‘+dataset[2]+’
’ // closes the html string

Abrahim

8

akalehzan:
After I changed the SQL script, we are getting SQL output as the following:
3459 1961-08-16 00:00:00.0
Then, if I clear the above out put and run the script again, then nothing show up.
var query3 = "select subj_num, id_birthdate from ID where subj_num = ‘1814’ ";

Sorry, I do not understand what do you mean?
subj_num = 1814 exists in table ID?
did you try running your method with the debugger?
How many rows of data do you get with subj_num = 3459, and with subj_num = 1814?

9

There should be one rows of data, which I get, but not in the format that I expected.
Yes, I ran the method in debugger, but I didn’t see any error.

Abrahim

10

What is the format you expected?

With only one row of data resulting… the resulting html table will only have one cell!

11

I expected to see something like:

subj_num 3459
id_birthdate 1961-08-16 00:00:00.0

Abrahim

12

I think you are mixing global and local variable Field12_text

try changing
var Field12_text = ‘

’ // starts the html string
with
globals.Field12_text = ‘
’ // starts the html string

Anyway… with the debugger you can have a look of the content of your variables and globals during a step by step method execution

Just in case I tested again the method I sent you and seems to be working fine on my machine.

13

Enrico,

That did it!!

Thank you very much for your help.

Best regards,

Abrahim

14

Ciao Enrico

I tried your idea - very nice! I would very much like to use it.

But it does not work for me because I need to access a NON primary key attribute. As far as I know this is still not possible (although I hope we get this feature soon :-)

My SQL statement looks like this:

SELECT pc.persons_role_person_id, pc.persons_role_role_id, pc.subject_code, pr.role_id, p.name, cl.number
FROM persons_capabilities pc INNER JOIN persons_roles pr
ON (pc.persons_role_person_id = pr.person_id
AND pc.persons_role_role_id = role_id)
AND pc.persons_role_role_id = 9
AND pc.subject_code = ‘B’
INNER JOIN persons p
ON pr.person_id = p.id
LEFT OUTER JOIN communication_lines cl
ON p.id = cl.person_id
ORDER BY p.name

where I would like to display as 3 horizontal columns the cl.number, which is something like Telefon Private, Telefon Business and eMail private.
Of course, I then would additionally have set a filter, i. e. enhance the SQL statement to just get these numbers (out of others).

But as said, it doesn’t work principally because I can’t add the attribute cl.number to the SELECT stmt.

Any other idea?

BTW; is this method still the only (best) way of showing values horizontally or are there now other possibilities?

Best regards, Robert

automazione:
Yes, probably something like:

var query = "select field_one, field_two  from your_table where your_condition"

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 999999999);
var col_count = 0;
var temp_html = ‘

’ // starts the html string
for ( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i;
temp_html += ‘'; // displays field_one and field_two
if (++col_count == 4)// max 4 values for each row of data
{
temp_html += ‘’;
col_count = 0;// new row of data
}

}
temp_html += ‘

’ + dataset[1] + ’
‘+dataset[2]+’
’ // closes the html string

15

Back to the original question:
Why not do it this way?

globals.Filed36_text = '<html>'+ dataset.getColumnAsArray(1).join(' ') + '</html>'

I’m assuming that the index of the first column of the dataset is 1. If this does not work, try 0.

16

Hello Christian

Your elegant solution code works - displays the whole foundset horizontally! Index of the first column seems to be 1.
It’s still a bit a different solution than that of Enrico, in his solution one can display for example pairs of column 1 and column2 as well as limit the number of columns. I wouldn’t know how to do that with your solution.
But I believe your solution is for many requirements really great and easy.

If I only could use non primary key attributes in the select statement to get the column I need to display!

Thanks and best regards, Robert

swingman:
Back to the original question:
Why not do it this way?

globals.Filed36_text = '<html>'+ dataset.getColumnAsArray(1).join(' ') + '</html>'

I’m assuming that the index of the first column of the dataset is 1. If this does not work, try 0.

17

swingman:
Why not do it this way?

globals.Filed36_text = '<html>'+ dataset.getColumnAsArray(1).join(' ') + '</html>'

You can also go straight to plaintext using the getAsText function on the dataset:

globals.Filed36_text = dataset.getAsText(‘,’,’ ‘,’"',true)

18

Hello Jan

Thanks for the tip, even more easy!
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!?

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?

Thanks and best regards, Robert

jaleman:

swingman:
Why not do it this way?

globals.Filed36_text = '<html>'+ dataset.getColumnAsArray(1).join(' ') + '</html>'

You can also go straight to plaintext using the getAsText function on the dataset:

globals.Filed36_text = dataset.getAsText(‘,’,’ ‘,’"',true)

19

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?

20

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.”