Itamar
|
| Posted: 05/12/2002, 9:26 PM |
|
while building a grid I encounter an error trying the beta example using PHP MySQL on both Win 2000 and Linux 6.2
Inner join generated by CCS is:
SELECT emp_name, priority_name, project_name, status_name, task_id, task_name
FROM statuses RIGHT JOIN (projects RIGHT JOIN (priorities RIGHT JOIN (tasks LEFT JOIN employees ON tasks.user_id_assign_to = employees.emp_id) ON priorities.priority_id = tasks.priority_id) ON projects.project_id = tasks.project_id) ON statuses.status_id = tasks.status_id
Error generated is:
ERROR 1064: You have an error in your SQL syntax near '(projects RIGHT JOIN (priorities RIGHT JOIN (tasks LEFT JOIN employees ON tasks.' at line 2
Any Idea why?
Itamar
|
|
|
 |
Nicole
|
| Posted: 05/13/2002, 6:16 AM |
|
Itamar,
Please keep in mind that CC Studio is Beta and there're some problems with sql building for some databases, namely db used for design time affects on sql syntax. That's why the advice is to use db of the same type both for design time and server part (e.g. if live db is MySQL connect to MySQL db during design time).
|
|
|
|
Itamar
|
| Posted: 05/13/2002, 2:26 PM |
|
I am using same InnoDb Mysql on both linux with design and published projects and still get the same error
|
|
|
|
Nicole
|
| Posted: 05/14/2002, 3:40 AM |
|
Itamar,
as I understand you’re using one of CCS examples (BugTrack I suppose). It was built using Access db for design time connection and sql for Grid form was built for Access. The only thing that you have changed connection type cannot change built sql. You should do it.
What I’ve done to make default page of BugTrack example work. For Bugs form open Data Source window. Select Table as Data Source and click on select query. You’ll be presented with query builder similar to Access. Remove all joins between tables and select field you need to be displayed on the form (I think there’s no much sense to select all field from all tables). Save it and return to Data Source window.
The next step is to build where clause to join table for mysql. Enter custom where clause to textbox field located below ‘Where’ field:
bugs.project_id = projects.project_id and bugs.priority_id = priorities.priority_id and bugs.status_id = statuses.status_id and bugs.assigned_to=employees.employee_id
Well, it would be useful for future. Unfortunately in CCS Beta 3 there’s bug in PHP pattern and custom where isn’t added to Where variable. I believe it will be fixed in next release, so you do not need to edit something in project design after installing new version.
Here is temporary workaround for this problem. Open code tab and find Prepare() function of clsBugsDataSource class. Add following line at its end (right before close bracket):
$this->Where .= " bugs.project_id = projects.project_id and bugs.priority_id = priorities.priority_id and bugs.status_id = statuses.status_id and bugs.assigned_to=employees.employee_id";
Generate page and test it.
|
|
|
|
|
