Thread: Problem Retrieving/Refresing Data From Database

Problem Retrieving/Refresing Data From Database

From

"Jeanna Geier"

Date:

15 January 2007, 15:36:53

Hello List!

I've been working on an issue and have it almost working -- going to need
some help to troubleshoot this one!  Our program connects to the database
and depending on the view/table that the user selects to view, pops up a
table with that information in it.

For example, click on a room in a blueprint, select 'Measurement' to attach
and the 'measurment' data from the database for that room is displayed.  The
box in which this is displayed has a drop-down box in three of the columns,
allowing the user to select whether to view the data in 'feet' (the
default), 'meters', 'yards', etc...

My problem is, that when the user tries to select a different unit of
measure than the default (feet), the box defauts back to feet and does not
allow the user to view the data in any other measurement.


Here's my CREATE VIEW for the 'measurement' table:

CREATE OR REPLACE VIEW elementdata.measurement AS
 SELECT e.elementid, da.projectname, da.square_unit AS square_unit_sq,
a.area * su.units_per_sqfoot AS area_sq, e.slope AS slope_inches,
sa.slopearea * cu.units_per_cufoot AS slopearea_sq, da.linear_unit AS
linear_unit_lin, p.perimeter * lu.units_per_foot::double precision AS
perimeter_lin, da.cubic_unit AS cubic_unit_cu, e.height * lu.units_per_foot
AS height_lin, e.height::double precision * a.area * cu.units_per_cufoot AS
volume_cu, da.drawingid
   FROM (( SELECT perimeter.elementid, perimeter.perimeter
           FROM elementdata.perimeter
UNION
         SELECT length.elementid, length.length AS perimeter
           FROM elementdata.length)
UNION
         SELECT circumference.elementid, circumference.circumference AS
perimeter
           FROM elementdata.circumference) p
   LEFT JOIN elementdata.area a USING (elementid)
   LEFT JOIN element e USING (elementid)
   LEFT JOIN elementdata.slopearea sa USING (elementid)
   JOIN layer la USING (layerid)
   JOIN drawing da USING (drawingid)
   JOIN globaldata.linear_units lu USING (linear_unit)
   JOIN globaldata.square_units su USING (square_unit)
   JOIN globaldata.cubic_units cu USING (cubic_unit);


UPDATE RULE:

CREATE RULE delete_measurement AS ON DELETE
    TO elementdata.measurement
    DO INSTEAD UPDATE element SET height = 0 WHERE (element.elementid =
old.elementid);



In my code, I'm checking to see if the check-box is checked to attach this
information, if so, attach it to the drawing it is displayed on; I also have
a method for chkAttachMsrmntActionPerformed:

private void addTables(String schema) throws SQLException{
        APTListModel listModel = (APTListModel)list.getModel();

        Vector usedTables = null;

        int type = -1;
        if(schema.equals("elementdata")){
            type = APTListModel.DATA_TABLE;
            usedTables = aptElement.getTableNames(schema,
                    main.getConnection());

            //**01/13/07 - JG Added statment to check if 'Measurement' is
added to Model
            //If checkbox checked, add it; if not, delete it
            if (chkAttachMsrmnt.isSelected() &&
!listModel.contains("Measurement")){
                listModel.addElement("Measurement",
APTListModel.DATA_TABLE);
            } else{
                listModel.removeElement("Measurement");
            }

        }
        else if(schema.equals("region")){
            type = APTListModel.REGION_TABLE;
            usedTables = aptElement.getTableNames(schema,
                main.getConnection());
        }
        else if(schema.equals("projectdata")){
            Project project = main.getProject();
            type = APTListModel.PROJECT_TABLE;
            usedTables = project.getTableNames(main.getConnection());
        }

        //add tables and capitialize first letter
        ListIterator li = usedTables.listIterator();
        while(li.hasNext()){
            String nextTable = (String)li.next();
            nextTable = nextTable.substring(0,1).toUpperCase() +
nextTable.substring(1);
            listModel.addElement(nextTable, type);
        }
}

I think the code is all right, but I'm just a bit confused as to why you
cannot select any other unit of measure from the box??

Thoughts and comments would be greatly appreciated!!  Thanks for your time
and help!
-Jeanna

Re: Problem Retrieving/Refresing Data From Database

From

"Jeanna Geier"

Date:

15 January 2007, 16:56:02

It's the following three fields that have the drop-downs related with them.

da.square_unit AS square_unit_sq
da.linear_unit AS linear_unit_lin
da.cubic_unit AS cubic_unit_cu


I can see all the units of measure from my table (i.e. inches, centimeters,
meters, yards, etc) in the drop-down, just cannot select them....if I try,
it goes back to the default (feet) and that's it.

If any additional info would be helpful, please let me know -- I've been
looking at this all morning and need to have it done for a release in an
hour...

Thanks much,
-Jeanna


-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Jeanna Geier
Sent: Monday, January 15, 2007 10:36 AM
To: Pgsql-Jdbc@Postgresql.Org
Subject: [JDBC] Problem Retrieving/Refresing Data From Database


Hello List!

I've been working on an issue and have it almost working -- going to need
some help to troubleshoot this one!  Our program connects to the database
and depending on the view/table that the user selects to view, pops up a
table with that information in it.

For example, click on a room in a blueprint, select 'Measurement' to attach
and the 'measurment' data from the database for that room is displayed.  The
box in which this is displayed has a drop-down box in three of the columns,
allowing the user to select whether to view the data in 'feet' (the
default), 'meters', 'yards', etc...

My problem is, that when the user tries to select a different unit of
measure than the default (feet), the box defauts back to feet and does not
allow the user to view the data in any other measurement.


Here's my CREATE VIEW for the 'measurement' table:

CREATE OR REPLACE VIEW elementdata.measurement AS
 SELECT e.elementid, da.projectname, da.square_unit AS square_unit_sq,
a.area * su.units_per_sqfoot AS area_sq, e.slope AS slope_inches,
sa.slopearea * cu.units_per_cufoot AS slopearea_sq, da.linear_unit AS
linear_unit_lin, p.perimeter * lu.units_per_foot::double precision AS
perimeter_lin, da.cubic_unit AS cubic_unit_cu, e.height * lu.units_per_foot
AS height_lin, e.height::double precision * a.area * cu.units_per_cufoot AS
volume_cu, da.drawingid
   FROM (( SELECT perimeter.elementid, perimeter.perimeter
           FROM elementdata.perimeter
UNION
         SELECT length.elementid, length.length AS perimeter
           FROM elementdata.length)
UNION
         SELECT circumference.elementid, circumference.circumference AS
perimeter
           FROM elementdata.circumference) p
   LEFT JOIN elementdata.area a USING (elementid)
   LEFT JOIN element e USING (elementid)
   LEFT JOIN elementdata.slopearea sa USING (elementid)
   JOIN layer la USING (layerid)
   JOIN drawing da USING (drawingid)
   JOIN globaldata.linear_units lu USING (linear_unit)
   JOIN globaldata.square_units su USING (square_unit)
   JOIN globaldata.cubic_units cu USING (cubic_unit);


UPDATE RULE:

CREATE RULE delete_measurement AS ON DELETE
    TO elementdata.measurement
    DO INSTEAD UPDATE element SET height = 0 WHERE (element.elementid =
old.elementid);



In my code, I'm checking to see if the check-box is checked to attach this
information, if so, attach it to the drawing it is displayed on; I also have
a method for chkAttachMsrmntActionPerformed:

private void addTables(String schema) throws SQLException{
        APTListModel listModel = (APTListModel)list.getModel();

        Vector usedTables = null;

        int type = -1;
        if(schema.equals("elementdata")){
            type = APTListModel.DATA_TABLE;
            usedTables = aptElement.getTableNames(schema,
                    main.getConnection());

            //**01/13/07 - JG Added statment to check if 'Measurement' is
added to Model
            //If checkbox checked, add it; if not, delete it
            if (chkAttachMsrmnt.isSelected() &&
!listModel.contains("Measurement")){
                listModel.addElement("Measurement",
APTListModel.DATA_TABLE);
            } else{
                listModel.removeElement("Measurement");
            }

        }
        else if(schema.equals("region")){
            type = APTListModel.REGION_TABLE;
            usedTables = aptElement.getTableNames(schema,
                main.getConnection());
        }
        else if(schema.equals("projectdata")){
            Project project = main.getProject();
            type = APTListModel.PROJECT_TABLE;
            usedTables = project.getTableNames(main.getConnection());
        }

        //add tables and capitialize first letter
        ListIterator li = usedTables.listIterator();
        while(li.hasNext()){
            String nextTable = (String)li.next();
            nextTable = nextTable.substring(0,1).toUpperCase() +
nextTable.substring(1);
            listModel.addElement(nextTable, type);
        }
}

I think the code is all right, but I'm just a bit confused as to why you
cannot select any other unit of measure from the box??

Thoughts and comments would be greatly appreciated!!  Thanks for your time
and help!
-Jeanna



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate