Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
Clear All
new posts
Previous template Next
    #1

    Datasource query problems

    I'm working with the demo version and hit a problem with sql datasources.

    The database describes IT assets. Each asset has an owner and a user. I have 3 tables:

    1. People: primary key idPeople
    2. Assets: primary key idAssets, foreign key People.idPeople points to the owner
    3. Checkouts: primary key idCheckouts foreign key Assets.idAssets points to the asset and People.idPeople points to the user.

    Note that table 2 and 3 both have foreign keys that point to the People table, and that for any given asset the keys have different values since the owner and the user don't have to be the same person.

    I've set up my data sources with the proper fields and the foreign keys declared. Putting Checkouts or Assets into listgrids individually works fine. However when I try to include the user from Checkouts in Assets smartclient just duplicates the owner rather than retrieving the user.

    I can easily write the proper join in SQL, but smartclient seems to get lost. Perhaps too much optimization?

    Is there a way to tell smartclient to do less query optimization, or give it an explicit where clause to use?

    Russ Poyner
    Tags: None
    #2
    You need to post your actual DataSources and the server logs from the request that generates SQL you weren't expecting.

    Comment

      #3
      Seems to have been a cache problem

      This suddenly started working after I re-ran the DataSource generator on another table.

      After some fiddling around I discovered that the secret is to stop and restart the embedded server after every change in the ds.xml files.

      Is there a way to disable some of the server side caching during development?

      Comment

        #4
        Nope. still trouble. The following doesn't work

        What doesn't work:

        The client jsp:

        <%@ taglib uri="isomorphic" prefix="isomorphic" %>
        <HTML><HEAD>
        <isomorphic:loadISC />
        </HEAD><BODY>
        <SCRIPT>
        <isomorphic:loadDS ID="Assets" />
        <isomorphic:loadDS ID="Asset_types" />
        <isomorphic:loadDS ID="Assets_checked_out" />
        <isomorphic:loadDS ID="People" />
        <isomorphic:loadDS ID="User_names" />
        isc.ListGrid.create({
        ID: "AssetsList",
        left: 50, top: 50,
        width: 1000,
        dataSource: Assets
        });
        AssetsList.fetchData(
        {Asset_types_id_Asset_type:2}
        );
        </SCRIPT>
        </BODY></HTML>

        The DataSources:
        <DataSource
        dbName="cbeITAssets"
        tableName="Assets_checked_out"
        ID="Assets_checked_out"
        dataSourceVersion="1"
        serverType="sql"
        >
        <fields>
        <field name="idCheckouts" type="integer" primaryKey="true"></field>
        <field name="People_id_Person" type="integer" foreignKey="People.idPeople"></field>
        <field name="Assets_id_Asset" type="integer" foreignKey="Assets.idAssets"></field>
        <field name="Rooms_idRooms" type="integer" foreignKey="Rooms.idRooms"></field>
        <field includeFrom="People.fullName" name="userName"></field>
        <field includeFrom="Rooms.roomNumber"></field>
        <field includeFrom="Rooms.buildingName"></field>
        <field includeFrom="Rooms.type"></field>
        <field name="dateOut" type="date"></field>
        <field name="dateDue" type="date"></field>
        <field name="dateIn" type="date"></field>
        <field name="conditionOut" length="300" type="text"></field>
        <field name="conditionIn" length="300" type="text"></field>
        <field name="notes" length="300" type="text"></field>
        </fields>
        </DataSource>

        <DataSource
        dbName="cbeITAssets"
        tableName="Assets"
        ID="Assets"
        dataSourceVersion="1"
        serverType="sql"
        >
        <fields>
        <field sqlType="integer" primaryKey="true" sqlLength="10" name="idAssets" type="sequence" hidden="true"></field>
        <field sqlType="integer" sqlLength="10" name="Asset_types_id_Asset_type" type="integer" foreignKey="Asset_types.idAsset_types" hidden="true"></field>
        <field sqlType="integer" sqlLength="10" name="People_id_People_owner" type="integer" foreignKey="People.idPeople" hidden="true"></field>
        <field includeFrom="Asset_types.assetType" title="Type"></field>
        <field includeFrom="People.fullName" title="Owner"></field>
        <field includeFrom="Assets_checked_out.userName"></field>
        <field includeFrom="Assets_checked_out.roomNumber"></field>
        <field includeFrom="Assets_checked_out.buildingName"></field>
        <field includeFrom="Assets_checked_out.type"></field>
        <field sqlType="varchar" sqlLength="45" name="serial" length="45" type="text"></field>
        <field sqlType="varchar" sqlLength="45" name="manufacturer" length="45" type="text"></field>
        <field sqlType="varchar" sqlLength="45" name="model" length="45" type="text"></field>
        <field sqlType="integer" sqlLength="10" name="count" type="integer"></field>
        </fields>
        </DataSource>

        Generates the following query:

        SELECT
        Assets.Asset_types_id_Asset_type,
        Assets.People_id_People_owner,
        Assets.count,
        Assets.idAssets,
        Assets.manufacturer,
        Assets.model,
        Assets.serial,
        Asset_types.assetType,
        People.fullName,
        People.fullName AS userName,
        Rooms.roomNumber,
        Buildings.name AS buildingName,
        Room_types.type
        FROM
        Rooms,
        Assets_checked_out,
        Asset_types,
        Assets,
        Room_types,
        Buildings,
        People
        WHERE
        (Assets.Asset_types_id_Asset_type = 2)
        AND Assets.Asset_types_id_Asset_type = Asset_types.idAsset_types
        AND Assets.People_id_People_owner = People.idPeople
        AND Assets.idAssets = Assets_checked_out.Assets_id_Asset
        AND Assets_checked_out.Rooms_idRooms = Rooms.idRooms
        AND Rooms.Buildings_id_Building = Buildings.idBuildings
        AND Rooms.RoomTypes_id_RoomType = Room_types.idRoomTypes

        Comment

          #5
          Creating a view in MySQL tricks it into working

          I won't repeat all of the code.
          In MySQL I create a view that repeats the People table under a different name and point the Assets_checked_out datasource at the view.

          Changes:
          1. In MySQL "create view User_names as select * from People;"

          2. In the Assets_checked_out datasource change:
          foreignKey="People.idPeople" to foreignKey="User_names.idPeople"
          and
          includeFrom="People.fullName" to includeFrom="User_names.fullName"

          Is there something in SmartClient to make this work without the extra view?

          With the listed changes the generated query is:

          SELECT
          Assets.Asset_types_id_Asset_type,
          Assets.People_id_People_owner,
          Assets.count,
          Assets.idAssets,
          Assets.manufacturer,
          Assets.model,
          Assets.serial,
          Asset_types.assetType,
          People.fullName,
          User_names.fullName AS userName,
          Rooms.roomNumber,
          Buildings.name AS buildingName,
          Room_types.type
          FROM
          Rooms,
          Assets_checked_out,
          Asset_types,
          Assets,
          Room_types,
          Buildings,
          User_names,
          People
          WHERE
          (Assets.Asset_types_id_Asset_type = 2)
          AND Assets.Asset_types_id_Asset_type = Asset_types.idAsset_types
          AND Assets.People_id_People_owner = People.idPeople
          AND Assets.idAssets = Assets_checked_out.Assets_id_Asset
          AND Assets_checked_out.People_id_Person = User_names.idPeople
          AND Assets_checked_out.Rooms_idRooms = Rooms.idRooms
          AND Rooms.Buildings_id_Building = Buildings.idBuildings
          AND Rooms.RoomTypes_id_RoomType = Room_types.idRoomTypes

          Comment

            #6
            It sounds like you found a solution in another layer, however, if you still want help with this:

            1. we need the server logs so we can see where this query is coming from

            2. we need the rest of the DataSources, not just one field from them - details matter

            3. we need your explanation of what's wrong with the query - rejected as invalid? Not generating the join you expected?

            Comment

            Previous template Next
            Working...
            X