Loading ...
Sorry, an error occurred while loading the content.

2831Re: [xml-dbms] Result Set problem

Expand Messages
  • Ronald Bourret
    Apr 1, 2003
      Sorry I haven't answered sooner. I have been very busy.

      In answer to your first two comments, you can use the first option (even
      though you have data from multiple tables) and you only need one
      classmap for a select statement.

      There are three ways to retrieve data from multiple tables in XML-DBMS
      v1.x. (Version 2.0 provides roughly similar capabilities.) I will
      illustrate with the following two tables:

      A: a_pk, a_data
      B: b_pk, a_fk, b_data

      1) Create a result set from both tables and use a class map for this
      result set that uses the special table name "Result Set". For example,
      use the following SELECT statement to retrieve the data for the rows
      corresponding to a primary key of 1 in table A:

      SELECT "a_data", "b_data" FROM A, B
      WHERE "a_pk"=1 AND "b_fk"="a_pk"

      and use the following class map for this result set:

      <ClassMap>
      <ElementType Name="ResultSet" />
      <ToClassTable>
      <Table Name="Result Set" />
      </ToClassTable>
      <PropertyMap>
      <ElementType Name="a_pk" />
      <ToColumn>
      <Column Name="a_pk" />
      </ToColumn>
      </PropertyMap>
      <PropertyMap>
      <ElementType Name="a_data" />
      <ToColumn>
      <Column Name="a_data" />
      </ToColumn>
      </PropertyMap>
      <PropertyMap>
      <ElementType Name="b_data" />
      <ToColumn>
      <Column Name="b_data" />
      </ToColumn>
      </PropertyMap>
      </ClassMap>

      A couple of things to note here:

      a) It is not always clear what element names to use. For example, in the
      following SELECT statement, I am not sure what names will be returned by
      JDBC's ResultSetMetaData:

      SELECT TABLE1.COLUMN1, TABLE2.COLUMN1
      FROM TABLE1, TABLE2
      WHERE TABLE2.COLUMN2 = TABLE1.COLUMN2

      If you have problems where data isn't being transferred, then use column
      aliases in the select statement and as column names in your map. For
      example, you could use the following select statement instead of the
      above select statement:

      SELECT TABLE1.COLUMN1 AS T1C1, TABLE2.COLUMN1 AS T2C2
      FROM TABLE1, TABLE2
      WHERE TABLE2.COLUMN2 = TABLE1.COLUMN2

      and use the column names T1C1 and T2C2 in your map file.

      b) XML-DBMS is fairly liberal with respect to ignoring extra columns or
      properties. For example, if a column is in the result set but is not
      mapped, it is ignored. Similarly, if a property (element type, attribute
      or PCDATA) is in the map file but does not have a corresponding column
      in the result set, it is ignored. This means that a single class map
      element can be used to map a variety of result sets.

      c) If the result set has more than one row in it, there must be an
      element type mapped as an ignored root. Otherwise, the code will try to
      add more than one (row) element to the document node and the DOM code
      will throw an exception.

      The major drawback to this method is that data from child tables is not
      nested inside data from parent tables. For example, if there are
      multiple rows in B for each row in A, the data for A will be repeated
      for each row in B. For example, if there are three rows in B (data
      values b1, b2, and b3) corresponding to the row in A with primary key 1,
      the following XML will be generated. (This assumes an ignored root
      element type of Result.)

      <Result>
      <ResultSet>
      <a_pk>1</a_pk>
      <a_data>a1</a_data>
      <b_data>b1</b_data>
      </ResultSet>
      <ResultSet>
      <a_pk>1</a_pk>
      <a_data>a1</a_data>
      <b_data>b2</b_data>
      </ResultSet>
      <ResultSet>
      <a_pk>1</a_pk>
      <a_data>a1</a_data>
      <b_data>b3</b_data>
      </ResultSet>
      </Result>

      2) The second way to return data from two related tables is to map one
      table as a related class of the other. For example:

      <ClassMap>
      <ElementType Name="A" />
      <ToClassTable>
      <Table Name="A" />
      </ToClassTable>
      <PropertyMap>
      <ElementType Name="a_pk" />
      <ToColumn>
      <Column Name="a_pk" />
      </ToColumn>
      </PropertyMap>
      <PropertyMap>
      <ElementType Name="a_data" />
      <ToColumn>
      <Column Name="a_data" />
      </ToColumn>
      </PropertyMap>
      <RelatedClass KeyInParentTable="Candidate">
      <ElementType Name="B"/>
      <CandidateKey Generate="No">
      <Column Name="a_pk"/>
      </CandidateKey>
      <ForeignKey>
      <Column Name="b_fk"/>
      </ForeignKey>
      </RelatedClass>
      </ClassMap>
      <ClassMap>
      <ElementType Name="B" />
      <ToClassTable>
      <Table Name="B" />
      </ToClassTable>
      <PropertyMap>
      <ElementType Name="b_data" />
      <ToColumn>
      <Column Name="b_data" />
      </ToColumn>
      </PropertyMap>
      </ClassMap>

      The main advantage of this method is that the data for the child table
      is nested inside the data for the parent table. This is the most common
      way to return data from two tables. For example, the above data would
      be:

      <A>
      <a_pk>1</a_pk>
      <a_data>a1</a_data>
      <B>
      <b_data>b1</b_data>
      </B>
      <B>
      <b_data>b2</b_data>
      </B>
      <B>
      <b_data>b3</b_data>
      </B>
      </A>

      3) The third way to retrieve data from multiple tables is to pass
      multiple table names and keys to DOMToDBMS.retrieveData. In this case,
      no related class elements are needed, but tables must be mapped as root
      tables or pseudo-root tables:

      <IgnoreRoot>
      <ElementType Name="A_B_Data"/>
      <PseudoRoot>
      <ElementType Name="A"/>
      <CandidateKey Generate="No">
      <Column Name="a_pk"/>
      </CandidateKey>
      </PseudoRoot>
      <PseudoRoot>
      <ElementType Name="B"/>
      <CandidateKey Generate="No">
      <Column Name="b_pk"/>
      </CandidateKey>
      </PseudoRoot>
      </IgnoreRoot>
      <ClassMap>
      <ElementType Name="A" />
      <ToClassTable>
      <Table Name="A" />
      </ToClassTable>
      <PropertyMap>
      <ElementType Name="a_pk" />
      <ToColumn>
      <Column Name="a_pk" />
      </ToColumn>
      </PropertyMap>
      <PropertyMap>
      <ElementType Name="a_data" />
      <ToColumn>
      <Column Name="a_data" />
      </ToColumn>
      </PropertyMap>
      </ClassMap>
      <ClassMap>
      <ElementType Name="B" />
      <ToClassTable>
      <Table Name="B" />
      </ToClassTable>
      <PropertyMap>
      <ElementType Name="b_pk" />
      <ToColumn>
      <Column Name="b_pk" />
      </ToColumn>
      </PropertyMap>
      <PropertyMap>
      <ElementType Name="b_data" />
      <ToColumn>
      <Column Name="b_data" />
      </ToColumn>
      </PropertyMap>
      </ClassMap>

      For example, the following code returns data for the row in A with
      primary key 1 and the rows in B with primary keys 2, 3, and 4.
      (Hopefully I've done the array initialization correctly.)

      DBMSToDOM dbmsToDOM = new DBMSToDOM(map, new DF_Oracle2());
      String[] tableNames = {"A", "B", "B", "B"};
      String[][] keys = {{1}, {2}, {3}, {4}};
      Document doc = dbmsToDOM.retrieveDocument(tableNames, keys);

      Using the above map, this returns the following XML:
      assuming that the rows in B from the previous example have key values,
      then the

      <A_B_Data>
      <A>
      <a_pk>1</a_pk>
      <a_data>a1</a_data>
      </A>
      <B>
      <b_pk>2</b_pk>
      <b_data>b1</b_data>
      </B>
      <B>
      <b_pk>3</b_pk>
      <b_data>b2</b_data>
      </B>
      <B>
      <b_pk>4</b_pk>
      <b_data>b3</b_data>
      </B>
      </A_B_Data>

      Again, if more than one row is retrieved, there must be an element type
      mapped as an ignored root. Because this method requires the application
      to know the key values, it is not as good a choice if the data in the
      second table is related to the data in the first table. This is because
      such data is related with foreign keys, but the code requires you to use
      primary keys. (Of course, you could lie in your map and tell the code
      that the foreign key columns are the primary key columns :) It is better
      for bulk transfer of data from multiple tables.

      -- Ron

      rene adam wrote:
      >
      > I have more than one tables in my db. So i think the
      > first solution does not work. I show you only a part
      > of my map file, not all.
      >
      > I use a select statement on many tables and i don't
      > know if i can do this by using only one classmap in my
      > map file. I don't know how to do to make it work. As
      > this work has been done by someone else, passing to v2
      > will be more work for me to understand how it works.
      > Can you help me? I send you my map file:
      > Here is my select statement:
      > SELECT "version", "IDMEF-Message"."AdditionalDataFK",
      > "IDMEF-MessageOrder",
      > "IDMEF-Message"."IDMEF-MessagePK", "Alert"."AlertPK",
      > "Alert"."i
      > mpact", "Alert"."IDMEF-MessageFK", "Alert"."ident",
      > "Alert"."AlertOrder", "Analyzer"."AnalyzerPK",
      > "Analyzer"."AnalyzerOrder", "Analyzer"."Ale
      > rtFK", "Analyzer"."HeartbeatFK",
      > "Analyzer"."analyzerid", "CreateTimePCDATAOrder",
      > "CreateTime"."CreateTimeOrder",
      > "CreateTime"."CreateTimePCD
      > ATA", "CreateTime"."AlertFK", "CreateTime"."ntpstamp",
      > "CreateTime"."HeartbeatFK",
      > "CreateTime"."CreateTimePK",
      > "DetectTime"."DetectTimeOrder"
      > , "DetectTime"."DetectTimePK",
      > "DetectTime"."DetectTimePCDATA",
      > "DetectTime"."AlertFK", "DetectTime"."ntpstamp",
      > "DetectTimePCDATAOrder", "Sou
      > rce"."SourcePK", "Source"."interface",
      > "Source"."ident", "Source"."AlertFK",
      > "Source"."SourceOrder", "Source"."spoofed",
      > "Target"."TargetOrder
      > ", "Target"."TargetPK", "Target"."interface",
      > "Target"."ident", "Target"."AlertFK",
      > "Target"."decoy", "Classification"."AlertFK",
      > "Classificat
      > ion"."ClassificationPK", "Classification"."origin",
      > "ClassificationOrder", "Process"."ident",
      > "Process"."SourceFK", "Process"."TargetFK", "Pro
      > cess"."AnalyzerFK", "Process"."ProcessOrder",
      > "Process"."ProcessPK", "Node"."NodePK",
      > "Node"."category", "Node"."NodeOrder", "Node"."ident",
      > "
      > Node"."SourceFK", "Node"."TargetFK",
      > "Node"."AnalyzerFK", "name1"."CorrelationAlertFK",
      > "name1"."ServiceFK", "name1"."UserIdFK", "name1"."Clas
      > sificationFK", "name1"."nameOrder",
      > "name1"."ProcessFK", "name1"."namePCDATA",
      > "name1"."NodeFK", "name1"."namePCDATAOrder",
      > "name1"."namePK",
      > "name1"."ToolAlertFK" FROM "IDMEF-Message", "Alert",
      > "Analyzer", "CreateTime", "DetectTime", "Source",
      > "Target", "Classification", "Process",
      > "Node", "name1" WHERE
      > "IDMEF-MessagePK"="Alert"."IDMEF-MessageFK" AND
      > "AlertPK"="Analyzer"."AlertFK" AND
      > "AlertPK"="DetectTime"."AlertFK" AND
      > "AlertPK"="CreateTime"."AlertFK" AND
      > "AlertPK"="Source"."AlertFK" AND
      > "AlertPK"="Target"."AlertFK" AND
      > "AlertPK"="Classification"."AlertFK" AN
      > D "AnalyzerPK"="Process"."AnalyzerFK" AND
      > "TargetPK"="Node"."TargetFK" AND
      > "ClassificationPK"="name1"."ClassificationFK"
    • Show all 8 messages in this topic