Beanshell : How put the result of a sql query into a beanshell variable ?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Beanshell : How put the result of a sql query into a beanshell variable ?

Anne
Hello,

I am using Version 4.0 - La Rochelle of OrbisGIS.
My question is how put the result of a sql query into a beanshell variable ?
I would like for example my h_max variable takes the value of max height field in the table bati.
Here is basically what I would do :

h_max = sql("select max(hauteur) from bati;");
print(h_max);

Obviously this is not as simple as that.
The sql query works but h_max does not take the result of the query and output is :

2

Lancer linstruction {0}/{1} :
select max(hauteur) from bati;
Max	
104	

I heard about a function result or resultset but I don't find how to use in beanshell consol.

Thank in advance !

Anne
Reply | Threaded
Open this post in threaded view
|

Re: Beanshell : How put the result of a sql query into a beanshell variable ?

ebocher
Administrator
Dear Anne,

It's not possible to return a sql result in a variable because a result can have many columns . But as sql is a beanshell script you can write your own function sqlto that returns the result into a (gdms) datasource (http://javadoc.orbisgis.org/latest/). After you will be able to get the max value from the datasource using datasource.getDouble(long row, String fieldName).

Below an example of script:


/*
* This script permits to execute a select statement  in beanshell and returns
* the result as a datasource.
* @author = Erwan BOCHER
*/

import org.orbisgis.core.Services;
import org.orbisgis.core.DataManager;
import org.gdms.data.DataSourceFactory;
import org.gdms.data.DataSource;


bsh.help.cd = "usage: sqlto( \"SELECT * FROM mytable;\" )";


DataSource sqlto(instruction){

    return dsf.getDataSourceFromSQL(instruction);
}

Save this script as sqlto.bsh, put it in your OrbisGIS workspace folder and reload OrbisGIS.

Now execute

h_max = sqlto("select max(hauteur) from bati;");
h_max.open();
print(h_max.getdouble(0,0));
h_max.close();

Hope this helps.

Erwan


--
Institut de recherche en sciences et techniques de la ville
École Centrale de Nantes
BP 92101
1 rue de la Noë, 44321 NANTES Cedex 3
France
Tél : 02 40 37 68 67
http://www.irstv.fr/
Reply | Threaded
Open this post in threaded view
|

Re: Beanshell : How put the result of a sql query into a beanshell variable ?

Anne
Hi,

Thank you for your help, I am going to try !

Anne


Reply | Threaded
Open this post in threaded view
|

Re: Beanshell : How put the result of a sql query into a beanshell variable ?

Anne
In reply to this post by ebocher
Hello,

I don't succeed with using sqlto.bsh. I don't understand why it doesn't work.


Another question for you :
I use ST_Extrude to extrude buildings. I use this kind in sql query :

create table bati_2d as select ST_Force_2D(the_geom) as the_geom, hauteur, id from bati;
create table extrude as select * from ST_Extrude(bati_2d, 'id', 'hauteur');
create table extrude_solene as select * from extrude where "type"!='floor';


I have problems with the field "type" in beanshell query.
type is used to identify format of field in sql (integer,float,...) .
So I can't write just type without quote and it doesn't work with ' ' .
And in  beanshell query,  symbols  " "  are used to define string.


I would like to do something like this:

sql("Create table "+ extrude_solene_i +" as select * from " + bati_extrude_i + " where ""type""!='floor';");

 
Have you an idea for this ?

Thank you for your help,

Anne