Jose Maldonado
|
| Posted: 06/19/2005, 6:19 PM |
|
I tryn to use this sql stament on a Grid
select product_name,price from store_products where product_id=(select
max(product_id) from store_products)
On MySQL console works ok, and works on the query test of CSS, when I upload
the page shows this
Database error: Invalid SQL: SELECT COUNT(*) FROM store_products)
MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near ')' at line 1)
Session halted.
I do not use any SELECT COUNT(*), but I find on code tab:
function Open()
{
$this->CCSEventResult = CCGetEvent($this->CCSEvents,
"BeforeBuildSelect");
$this->CountSQL = "SELECT COUNT(*) FROM store_products)";
$this->SQL = "select product_name,price from store_products where
product_id=(select max(product_id) from store_products)";
$this->CCSEventResult = CCGetEvent($this->CCSEvents,
"BeforeExecuteSelect");
$this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL,
$this->Where, ""), $this);
$this->query($this->OptimizeSQL(CCBuildSQL($this->SQL, $this->Where,
$this->Order)));
$this->CCSEventResult = CCGetEvent($this->CCSEvents,
"AfterExecuteSelect");
}
This is a bug?
|
|
|
 |
Jose Maldonado
|
| Posted: 06/19/2005, 6:49 PM |
|
Same error tryn on another table:
Database error: Invalid SQL: SELECT COUNT(*) FROM users)
MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near ')' at line 1)
Session halted.
"Jose Maldonado" <ze@lastserv.com> wrote in message
news:d955ia$8s4$1@news.codecharge.com...
>I tryn to use this sql stament on a Grid
>
> select product_name,price from store_products where product_id=(select
> max(product_id) from store_products)
>
> On MySQL console works ok, and works on the query test of CSS, when I
> upload the page shows this
>
> Database error: Invalid SQL: SELECT COUNT(*) FROM store_products)
> MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near ')' at line 1)
> Session halted.
>
> I do not use any SELECT COUNT(*), but I find on code tab:
>
> function Open()
> {
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "BeforeBuildSelect");
> $this->CountSQL = "SELECT COUNT(*) FROM store_products)";
> $this->SQL = "select product_name,price from store_products where
> product_id=(select max(product_id) from store_products)";
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "BeforeExecuteSelect");
> $this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL,
> $this->Where, ""), $this);
> $this->query($this->OptimizeSQL(CCBuildSQL($this->SQL,
> $this->Where, $this->Order)));
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "AfterExecuteSelect");
> }
>
> This is a bug?
>
|
|
|
|
mrachow
Posts: 509
|
| Posted: 06/19/2005, 11:16 PM |
|
After a short look.
It seems to me that CC Studio don't remove the last closing bracket from your original SQL statement when building the Count(*) statement from it!?
_________________
Best regards,
Michael |
 |
|
wkempees
Posts: 1679
|
| Posted: 06/20/2005, 10:44 AM |
|
Or, even quicker look responding just as dinner being served:
Use aliasing as in select ...... from store_products as A where .(...... from store_product as B)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Carsten =?iso-8859-1?Q?Th=F6nges?=
|
| Posted: 06/20/2005, 3:15 PM |
|
* "Jose Maldonado" <ze@lastserv.com> writes:
> I tryn to use this sql stament on a Grid
> select product_name,price from store_products where product_id=(select
> max(product_id) from store_products)
> On MySQL console works ok, and works on the query test of CSS, when I upload
> the page shows this
> Database error: Invalid SQL: SELECT COUNT(*) FROM store_products)
> MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near ')' at line 1)
> Session halted.
CCS tries to modify your query to get the number of expected rows.
Seems like it just looks for the last FROM keyword even if it's in a
subquery – which obviosly leads to something we don't want ...
> I do not use any SELECT COUNT(*), but I find on code tab:
> function Open()
> {
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "BeforeBuildSelect");
> $this->CountSQL = "SELECT COUNT(*) FROM store_products)";
> $this->SQL = "select product_name,price from store_products where
> product_id=(select max(product_id) from store_products)";
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "BeforeExecuteSelect");
> $this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL,
> $this->Where, ""), $this);
> $this->query($this->OptimizeSQL(CCBuildSQL($this->SQL, $this->Where,
> $this->Order)));
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "AfterExecuteSelect");
> }
You could ...
a. Create a sort of dummy parameter (which an empty string as its
value) and write your statement like
SELECT product_name,price
FROM store_products
WHERE product_id = (SELECT Max(product_id)
FR{dummy}OM store_products)
so the subquery's FROM won't be recognised
or
b. Change the CountSQL to a correct statement using the
BeforeExecuteSelect.
> This is a bug?
IMHO yes.
Carsten
|
|
|
|
dsafar
|
| Posted: 06/26/2005, 9:43 AM |
|
The countsql is used for navigation. It is well documented in the forums
that this does not work with anything other than standard sql, (no group by,
sub selects etc). As the prior post suggest modify the countsql in
BeforeExecuteSelect event.
Do a search on countsql in the forum for addtional information on how to do
this.
"Jose Maldonado" <ze@lastserv.com> wrote in message
news:d955ia$8s4$1@news.codecharge.com...
>I tryn to use this sql stament on a Grid
>
> select product_name,price from store_products where product_id=(select
> max(product_id) from store_products)
>
> On MySQL console works ok, and works on the query test of CSS, when I
> upload the page shows this
>
> Database error: Invalid SQL: SELECT COUNT(*) FROM store_products)
> MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near ')' at line 1)
> Session halted.
>
> I do not use any SELECT COUNT(*), but I find on code tab:
>
> function Open()
> {
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "BeforeBuildSelect");
> $this->CountSQL = "SELECT COUNT(*) FROM store_products)";
> $this->SQL = "select product_name,price from store_products where
> product_id=(select max(product_id) from store_products)";
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "BeforeExecuteSelect");
> $this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL,
> $this->Where, ""), $this);
> $this->query($this->OptimizeSQL(CCBuildSQL($this->SQL,
> $this->Where, $this->Order)));
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "AfterExecuteSelect");
> }
>
> This is a bug?
>
|
|
|
|
|
