Announcement

Collapse
No announcement yet.

Variable for Table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Variable for Table

    Hello, i'm using scriptcase version 8.00.0032.

    In grid application, if I use the following query:

    select cat , dog from animals00

    and I use variable "number" for table function, it works perfectly.
    This is the resault in the php code:
    $nm_comando = str_replace("<<00>>", $_SESSION['number'], "select ... FROM animals<<00>> ... ");

    If i use a more complex query such as:

    SELECT
    alias2.alias9,
    alias1.alias8,
    alias1.field3,
    (
    alias2.alias3 DIV 60
    ) AS alias3,
    (alias2.alias4 DIV 60) AS alias4,
    (alias2.alias5 DIV 60) AS alias5,
    (alias2.alias6 DIV 60) AS alias6,
    (
    alias2.alias7 DIV 60
    ) AS alias7
    FROM
    (
    SELECT
    tab1.field12 AS alias8,
    tab2.field3 AS field3
    FROM
    tab1
    JOIN tab2 ON tab1.field12 = tab2.alias8
    WHERE
    field11 = 'house'
    ) AS alias1
    LEFT JOIN (
    SELECT
    field9 AS alias9,
    field10 AS alias8,
    SUM(field4) AS alias3,
    SUM(field5) AS alias4,
    SUM(field6) AS alias5,
    SUM(field7) AS alias6,
    SUM(field8) AS alias7
    FROM
    tab00 <<<<<---------------------- here it is the '00' for Variable for Table fuction
    GROUP BY
    field9,
    field10
    ) AS alias2 ON alias1.alias8 = alias2.alias8
    WHERE
    alias9 IS NOT NULL
    ORDER BY
    alias2.alias9,
    alias1.field3

    Variable for Table function doesn't substs the table name, and it continues to query table 'tab00'.
    It seams to work only for simple queries, without brackets and without join.

    Thank you
    Fabrizio

  • #2
    Hi Fabrizio,

    I myself am not too much of a SQL expert but if your database allows you to create views (and if that works with variables) it's probably best to create a view. As far as I've heard/seen scriptcase can be a bit wonky at times with complex SQL queries so I usually just put the bit more complex queries in views.

    Greetings,

    Matt

    Comment


    • #3
      ?? I do this in standard php and it works:
      <?php
      $s="SELECT
      alias2.alias9,
      alias1.alias8,
      alias1.field3,
      (
      alias2.alias3 DIV 60
      ) AS alias3,
      (alias2.alias4 DIV 60) AS alias4,
      (alias2.alias5 DIV 60) AS alias5,
      (alias2.alias6 DIV 60) AS alias6,
      (
      alias2.alias7 DIV 60
      ) AS alias7
      FROM
      (
      SELECT
      tab1.field12 AS alias8,
      tab2.field3 AS field3
      FROM
      tab1
      JOIN tab2 ON tab1.field12 = tab2.alias8
      WHERE
      field11 = 'house'
      ) AS alias1
      LEFT JOIN (
      SELECT
      field9 AS alias9,
      field10 AS alias8,
      SUM(field4) AS alias3,
      SUM(field5) AS alias4,
      SUM(field6) AS alias5,
      SUM(field7) AS alias6,
      SUM(field8) AS alias7
      FROM
      tab<<00>>
      GROUP BY
      field9,
      field10
      ) AS alias2 ON alias1.alias8 = alias2.alias8
      WHERE
      alias9 IS NOT NULL
      ORDER BY
      alias2.alias9,
      alias1.field3
      ";
      echo $s."\n";
      echo str_replace('<<00>>','XXX',$s);
      ?>

      Comment


      • #4
        Hello,
        thank you for your replies.

        About views, i'm using mysql and it is not possibile (at least as I know) to subst the table name in a view. I should have to create a view for each table. Every month a new table is created automatically so I think vews aren't the right way.

        Abount what RR is saying, you are right. Your php code works. The problem seams to be another. Look at this example:

        select dog, age from dogtable

        I want to replace the substring "dog" in the table name, then the algorithm which searches it has to replace only the second dog, and not the field name.
        So scriptcase changes the string in:
        $s = 'select dog, age from <<dog>>table';

        and then the replace command is:
        str_replace('<<dog>>','XXX',$s);

        If I use a more complex query, with the table name hiden between brackers, the scriptcase algorithm is not more able to locate the substring.
        For example, I have table1 to table 9999. I want to subst '9999' with another number. This is the query:

        select field1, (field2 DIV 60) as field2, field3 from ( select tablealias1.field1 as field1, tableZ.field2 as field2, tableZ.field3 as field3 from (select field1 from table9999 group by field1) as tablealias1 join tableZ on tablealias1.field1 = tableZ.field1) as tablealias2 order by field3

        I thought the string should become:

        select field1, (field2 DIV 60) as field2, field3 from ( select tablealias1.field1 as field1, tableZ.field2 as field2, tableZ.field3 as field3 from (select field1 from table<<9999>> group by field1) as tablealias1 join tableZ on tablealias1.field1 = tableZ.field1) as tablealias2 order by field3

        and it doesn't happen.

        I hope I was able to explain :-)

        Comment


        • #5
          First of all nm_comando is an internal variable. It is used for several database queries. If you check the generated code from your application you will see a couple of times (in the appname.php and appname_apl.php and if using mobile in a few more) the assignment
          $nm_comando = ....... so you are doing something that can turn out differently then you want.

          I get your <<dog>> example, so that is fine.

          In order to change table1 to table9999 (or any other number) why dont you str_replace('table1','table9999','$s); or even if you want to be more safe use:
          str_replace('table1 ','table9999 ','$s);
          str_replace('table1.','table9999.','$s);
          This since any tablename is always followed by either a space or a .

          And to be even more safe use
          str_replace(' table1 ',' table9999 ','$s);
          str_replace(' table1.',' table9999.','$s);

          I hope I understood your problem tho.

          str_replace is just a simple standard php string replace, str replace is well able to replace strings. Of course if you want to replace '9' with '1' in the string 'table9999' you will get 'table1111'. So I'd opt for prepending the table name..
          Do check the generated code tho on where your code gets triggered. I guess you can only do this in OnApplicationInit properly. I havent checked it tho.

          Comment


          • #6
            Hello,
            thank you very much RR for your workaround.
            I would prefer not to have to change my code manually to get it to work, because all I described above is all code automatically generated by scriptcase.

            I was posting this case here because I think it is a bug and I suppose I should not have to change my code manually if the scriptcase "Variable for Table" function could work as described in web help.
            So, I think there is a "bug" in the scriptcase code or in scriptcase documentation which doesn't specify the limit of "Variable for Table" function.

            thank you
            Fabrizio

            Comment

            Working...
            X