modify SQL String

Help
sissi pol
2003-10-29
2003-12-04
  • sissi pol

    sissi pol - 2003-10-29

    Hello all,

    is it possible to modify the SQL-String? I can't tell the report to select with paramters, because these parameters aren' t always the same. And so the WHERE-Clause differs every time.

    I tried it with one Parameter (String) who should be the WHERE-Clause but the following SQL-String:

    select T_log1.Tll_id, T_log1.kat, T_log1.Zusdat, T_log1.Pos_id, T_log1.Emp_id, T_log1.Zeit
    from T_log1
    where (  {?Select})
    order by T_log1.Tll_id asc, T_log1.Pos_id asc, T_log1.Emp_id asc, T_log1.kat asc, T_log1.Zeit asc

    doesn't work!

    Can anyone help me?

     
    • Jim Menard

      Jim Menard - 2003-10-29

      Why doesn't it work? What did you enter as the value of the Select parameter?

       
    • sissi pol

      sissi pol - 2003-10-30

      Sorry i forgot it!

      {?Select} :
      T_log.Pos_id > 0 AND Tlog.Emp_id = 23 AND T_log1.kat = 0

      Does it only works with { }?

       
    • sissi pol

      sissi pol - 2003-10-30

      okay, i also tried with { }, but no effect!!

      To simplifie i tried it with

      {T_log1.Pos_id} > 0

      Can anyone help me?

       
      • Jim Menard

        Jim Menard - 2003-11-26

        Column names can't appear in parameters used in WHERE clauses. That's because the entire parameter is turned into a string by the Java JDBC code. Your query becomes

        SELECT ... FROM ...
        WHERE '{T_log1.Pos_id} > 0'

        I would like to figure out a way to allow this in DataVision.

         
    • Anonymous - 2003-12-03

      to dynamically generate whereCluase query , my solution
      is to split .xml file into 3 parts:
      1. first  part: header.xml file it includes the static select part like this:
      <?xml version="1.0" encoding="UTF-8"?>
      <!-- Generated by DataVision version 0.7.13 -->
      <!-- http://datavision.sourceforge.net -->
      <report dtd-version="0.8" name="Unnamed" title="Untitled" author="">
          <description><![CDATA[]]></description>
          <paper name="US-Letter" orientation="portrait"/>
          <source>
          <database driverClassName="oracle.jdbc.driver.OracleDriver" connInfo="jdbc:oracle:thin:@... " name="" username=""/>
          <query>
              <join from="....." relation="=" to="...."/>
              <join from="..." relation="=" to="...."/>
              <where><![CDATA[
      ends here.
      2. the  2nd part comes from my own whereClause string which is dynamically generated in java code.
      // here insert the complete whereclause as a string
      String whereClauseStr=" tablea.year=" +thisYear
      +" and tablea.week="+thisWeek+.....;

      3. the last part is tail.xml
      like this:
      ]]></where>
          </query>
          </source>
          <parameters>
          <parameter id="11" type="string" name="..." question="...=" arity="single">
              <default></default>
          </parameter>
          </parameters>
          <formulas>
          <headers>
          <section height="35.0">
              <field id="1" type="text">
              <text><![CDATA[ Project Report]]></text>
              <bounds x="76.0" y="17.0" width="152.0" height="16.0"/>
              <format size="12.0" bold="true" align="center" color="102;0;255;255"/>
              </field>
          </section>
          </headers>
          <footers>
          <section height="18.0"/>
          </footers>
          <page>
          <headers>
              <section height="44.0">
              <field id="3" type="text">
                  <text><![CDATA[User Name]]></text>
                  <bounds x="417.0" y="19.0" width="58.0" height="17.0"/>
              </field>
              </section>
          </headers>
          <page>
          <details>
          <section height="46.0">
      </section>
          </details>
      </report>

      so when you read from xml file, actually you first read from header.xml and put it into a stringbuffer, then your whereclause string to the stringbuffer , then the last part tail.xml ...
      after all, you may convert the whole complete string into
      byte array and use:
      ByteArrayInputStream streamArray =
                      new ByteArrayInputStream(byteArray);

                  InputStreamReader inputReader =
                      new InputStreamReader(streamArray);
                  report.read(inputReader);
      then you may generate your report as usual.

      hope it will help ..

       
    • sissi pol

      sissi pol - 2003-12-04

      Thank you very much, but it works in also in a simpler way:

      report.getDataSource().getQuery().setEditableWhereClause(String whereclause);

       

Log in to post a comment.