CODEWITHSUNDEEP
mysqldelphidbexpress
Anderson
Anderson

Reputation: 143

Delphi EDataBaseError - Operation Not Supported. How can I solve it?

I'm trying to query data from MySQL5 db, but when I use some other features from SQL code, I have the following error returned: [0x0005]: Operation Not Supported.

My SQL code query:

Select 
  s.nome, s.id_sistema, s.st_sis 
from 
  perm_usuar as p 
inner join 
  sistemas as s 
on 
  s.id_sistema = p.id_sistema 
where 
  p.id_usuario = "' + idusuario + '"'

When I don't use those features, it works just as well:

Select 
  sistemas.nome, sistemas.id_sistema, sistemas.st_sis 
from 
  perm_usuar 
inner join 
  sistemas 
on 
  sistemas.id_sistema = perm_usuar.id_sistema 
where 
  perm_usuar.id_usuario = "' + idusuario + '"'

Also, if I try to use WHERE of a joined table, I get the same error... I'm using DBExpress on Delphi XE8, with the following components: SQLConnection, SQLDataSet and SQLQuery.

When I use the code directly on MySQL, it works fine.

Why is it being returned and what's the solution?

Upvotes: 2

Views: 2280

Answers (2)

Anderson
Anderson

Reputation: 143

I found the solution! The problem were on SQLQuery1.RecordCount. By what I read, the dbExpress is unidirectional, so the RecordCount bring it resource, however have its limitations (you can see here: http://edn.embarcadero.com/ru/article/28494)

Before (returning error):

 SQL1.SQL.Clear;
 SQL1.SQL.Add(CodigoMYSQL);
 SQL1.Open;
 SQL1.First;
 cont := SQL1.RecordCount; //have limitations
 if cont > 0 then // check
  begin
   for i := 1 to cont do //loop in
    begin
     for ii := 0 to NValue do
      result[ii].Add(SQL1.Fields[ii].AsString);
     SQL1.Next;
    end;
  end;
 SQL1.Close;

** SQL1 = SQLQuery1

After (solved):

 SQL1.SQL.Clear;
 SQL1.SQL.Add(CodigoMYSQL);
 SQL1.Open;
 SQL1.First;
 if not SQL1.IsEmpty then //check
  begin
    ii := 0;
    while not SQL1.Eof do //till the end
    begin
     for ii := 0 to NValue do
      result[ii].Add(SQL1.Fields[ii].AsString);
     SQL1.Next;
     inc(ii);
    end;
  end;
 SQL1.Close;

Now I can use even more complex one SQL codes and functions.

Upvotes: 3

spencer7593
spencer7593

Reputation: 108380

Thanks for the status report. I'm assuming that you intended to ask a question, e.g.

Q: Why is this error being returned?

As far as why you are seeing that behavior, my suspicion is that the issue is with the AS keyword. (That's just a guess.)

Some databases, such as Oracle, disallow the AS keyword in the assignment of a table alias, following the ANSI SQL standards. Other databases don't follow the standard as closely and (as an extension) allow (but don't require) the AS keyword.

The database interface/driver components you're using may be designed to support "universal" syntax. The interface library may be rejecting the SQL statement because of the non-standard AS keyword.

Q: How do I fix this?

You could try removing the unnecessary AS keywords.

If that doesn't change the behavior, then maybe the database interface library doesn't support table aliases. (That would be bizarre.)

If that doesn't resolve the issue, you may consider switching to a different database interface/driver, one that is specific to MySQL.

Upvotes: 0

Related Questions