Strange #42SS Error

ACS2000 · Post by **ACS2000** » Thu 03 Apr 2008 15:33

Hi

I'm using a MySQL 5.0 with BDS2006 and MyDAC 5.20.1.14

I have a TQuery with the following SQL

SELECT
a.*
FROM
account a
WHERE
a.Acc_Type = :Acc_Type AND
IF(:Active '', a.Active = :Active, TRUE) AND
IF(:Acc_Code '', a.Acc_Code LIKE CONCAT_WS('', '%', :Acc_Code, '%'), TRUE) AND
IF(:Acc_Name '', a.Acc_Name LIKE CONCAT_WS('', '%', :Acc_Name, '%'), TRUE)
ORDER BY
a.Acc_Code

I have created many Queries in the past without any trouble using MyISAM tables. I have just started a new project using InnoDB so that I can use Foreign Keys and Transactions.

When I open the form I have a routine that supplies the relevant variables and executes the query. All normal stuff. However, after 60 secs I get the following error message (twice, the first from EMySQLException the second from EMyError)

#42S22Unknown column 'a.Acc_Type' in 'where clause'

The error doesn't affect the workings of the form. I can create, edit and delete records from the table as normal.

It only happens once for the form.

Why? How do I fix it?

Dimon · Post by **Dimon** » Mon 07 Apr 2008 07:06

We can not reproduce the problem.
Please send a complete small sample to dmitryg*crlab*com to demonstrate it, including script to create and fill table.

ACS2000 · Post by **ACS2000** » Tue 08 Apr 2008 09:22

Have solved this

I had the TMyQuery option AutoRefresh enabled for 60 secs. Have disabled this and now don't get the problem.

Why doesn't the auto-refresh work? Do I need to specify an Update SQL for that to work?

Dimon · Post by **Dimon** » Tue 08 Apr 2008 13:13

Auto-refresh must work even if you don't specify Update SQL.

We still can not reproduce the problem. We have received your information by e-mail, but unfortunately the information you provided is not enough to investigate the problem.
Please send a complete small sample to dmitryg*crlab*com to demonstrate the problem, including a script to create and fill table.