[Chameleon] SQL Query and Table Widgets - Please Advise

[Julien-Samuel Lacroix]

Hi,

Your widget definitions seem right. My advice would be to make sure your 
query return something. You can do this by either trying your query 
directly in PostGreSQL (recommended) or by adding adding the following 
events in your SQLQuery widget definition (you already have the first one):
    <ONEVENT Event="ConnectFailed" Text="Connection failed."/>
    <ONEVENT Event="QueryFailed" Text="Query failed."/>
    <ONEVENT Event="NoRowReturned" Text="No row returned."/>


1) The SharedResourceName in the SQLQuery and Table widgets is the name 
of a, well, shared resource. A shared resource in Chameleon is a data 
container. The SQLQuery put the results of its query in a Shared 
Resource and the Table widget reads from this shared resource to build 
its table. It's important that both values are the same.

2) Your widget definition seems ok. By default the SQLQuery widget will 
use MySQL, but you can make it use PostGreSQL by specifying 
dbtype="PGSQL" like you did.

Best Regards,
Julien

Jessica Fendos wrote:
> Hi list:
> 
> I am using sqlquery to build a report on employment change in my
> mapping application. Having little experience in PostgreSQL and
> Chameleon, I managed to upload a shapefile (empchgfinal.shp) to
> PostgreSQL database, created a spatial index (gist type, name "emp")
> for the shapefile and wrote the following SQL query in the html
> file. However, when I draw the select the area of interest (using
> ROIManager widget), I can see the area is highlighted, but when I
> click on generate report, I got an empty result.  1) What is
> sharedresourcename in the SQL Query and Table widgets for and how
> should it be specified in the table widget? "emp" is a report type
> out of 4 link options in the report.html and it is also the name for
> the spatial index in the shapefile.  2) Is there some thing I should
> do extra in PostgresSQL so that this will work? Could someone please
> give me some guidance as to how I can fix this?  Any suggestions
> will be highly appreciated. 
> 
> Sincerely,
> Jessica Fendos 
> 
> <!-- Query for Employment Report  -->
> <cwc2 type="SQLQuery" server="XXXXX" database="XXX"
> username="postgres" password="mapsXXX"  dbtype="PGSQL"
>    sqlquery="select sum(e.aest_00) as avgest00,sum(e.aest_05) as
> avgest05,sum(e.e_est) as  
>    estchange,round(sum(e.e_est)/sum(e.aest_00)*100, 1) as p_estchg,
> sum(e.aemp_00) as
>    avgemp00,sum(e.aemp_05) as avgemp05,sum(e.e_change) as   
>    empchange,round(sum(e.e_change)/sum(e.aemp_00)*100, 1) 
>    as p_empchange,count(*) as n_blockgroups from empchg_final e
> where e.the_geom && 
>    SetSRID('BOX3D([$_MinX_$] [$_MinY_$] , [$_MaxX_$] 
>    [$_MaxY_$])'::box3d,-1) AND within (e.the_geom, SetSRID
> ('BOX3D([$_MinX_$] [$_MinY_$],[$_MaxX_$] 
>    [$_MaxY_$])'::box3d,-1));"
>    sharedresourcename="EMP">
>    <ONEVENT Event="ConnectFailed" Text="Connection failed."/>
> </cwc2>
> 
> <!-- Table for Employment Report  -->
> <cwc2 type="Table" sharedresourcename="EMP" >
>   <template name="header"><![CDATA[
> <TABLE BORDER=1 CELLSPACING=1 CELLPADDING=1 WIDTH=600>
>   ]]></template>
>   <template name="body"><![CDATA[
> <TR HEIGHT=16 >
> <TD WIDTH=52%  ALIGN=LEFT > <BR></TD>
> <TD WIDTH=12%  ALIGN=RIGHT ><FONT style=FONT-SIZE:10pt FACE="Arial"
> COLOR=#000000>2000#</P></TD>
> <TD WIDTH=12%  ALIGN=RIGHT ><FONT style=FONT-SIZE:10pt FACE="Arial"
> COLOR=#000000>2005#</P></TD>
> <TD WIDTH=12%  ALIGN=RIGHT BGCOLOR="#dddddd"><I><FONT
> style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>Change</P></I></TD>
> <TD WIDTH=12%  ALIGN=RIGHT BGCOLOR="#dddddd"><I><FONT
> style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>Change
> %</P></I></TD>
> </TR>
> <TR HEIGHT=18 >
> <TD ALIGN=CENTER COLSPAN=5><BR><P CLASS="sub">Employment
> Statistics</P></TD>
> </TR>
> <TR HEIGHT=15>
> <TD ALIGN=LEFT ><P CLASS="detail">Average Establishment</P></TD>
> <TD ALIGN=RIGHT ><P CLASS="detail">%avgest00%</P></TD>
> <TD ALIGN=RIGHT ><P CLASS="detail">%avgest05%</P></TD>
> <TD ALIGN=RIGHT BGCOLOR="#dddddd"><P
> CLASS="detail"><I>%estchange%</I></P></TD>
> <TD ALIGN=RIGHT BGCOLOR="#dddddd"><P
> CLASS="detail"><I>%p_estchg%</I></P></TD>
> </TR>
> <TR HEIGHT=15 >
> <TD ALIGN=LEFT ><P CLASS="detail">Average Employment</P></TD>
> <TD ALIGN=RIGHT ><P CLASS="detail">%avgemp00%</P></TD>
> <TD ALIGN=RIGHT ><P CLASS="detail">%avgemp05%</P></TD>
> <TD ALIGN=RIGHT BGCOLOR="#dddddd"><P
> CLASS="detail"><I>%empchange%</I></P></TD>
> <TD ALIGN=RIGHT BGCOLOR="#dddddd"><P
> CLASS="detail"><I>%p_empchange%</I></P></TD>
> </TR>
> ....
> 
> 
> Jessica M. L. Fendos
> Research Analysis Specialist Sr./GIS Application Developer
> Labor Market Information (LMI) Office
> MN Department of Employment and Economic Development
> Tel: 651-296-3739
> jessica.fendos at state.mn.us
> Homepage: jessicafendos.com
> 
> _______________________________________________
> Chameleon mailing list
> Chameleon at lists.maptools.org
> http://lists.maptools.org/mailman/listinfo/chameleon

-- 
Julien-Samuel Lacroix
Mapgears
http://www.mapgears.com/