Variable for Table

Scriptcase 8 Bugs
#1

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

#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.”
";
echo str_replace(’<<00>>’,‘XXX’,$s);
?>

#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 :slight_smile:

#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.

#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