Delphi - ZEOS MySQL - access denied

Question

I am trying to connect to my mysql database server (version 5.6) on the web, from Delphi 7 application...

With Mysql Workbench, it works OK, i can run select queries with my test user (the same user that not working in Delphi 7)...

In Delphi, where using TZEosConn 7.1.4-stable , typed correct hostname for DB server, correct username and password... (parameter loginprompt = false) When I change "connected" to true, everything working OK...

But when i am trying to execute a simple query:

zq.SQL.Text:='SELECT field FROM database.TABLE where id='+inttostr(x)+';'; zq.ExecSQL;

where zq is TZQuery, and x is integer,

I got this error: **EZSQLException .. SELECT Command denied for @my_internet_hostname **

When i am logged in MySQL Workbench, i see the same user with wildcard session, like mydbuser@% , the same select works fine

So i think, the problem is in ZEOS component, it adding automatically my internet provider hostname...

On the mysql server i have wildcard hostnames allowed ( simply - '%' - so all hosts can access my MYSql DB )

Can someone suggest me, what is the problem with my ZEOS component???

PS: Sorry for my english and formatting

Answer 1

Does your MySQL server is from internet ?

• first of all : does your internet provider allows you to direct access to MySQL Database with a pipe/socket connexion ? Usually internet provider don't allow this because of security matters. (They can open a special IP/Port for this if you ask them).

• Second : did you have the grant option ? this can cause problem with ZEOS component.

  GRANT USAGE ON . TO 'root'@'%'

  GRANT ALL PRIVILEGES ON mydb.* TO 'root'@'%' WITH GRANT OPTION

If i remember well, After i change privilege like this works. it was with Local network mysql Server (with EasyPHP and WAMP tested).

I would also advise you to connect with a DSN string : check below the way to connect :

http://synopse.info/forum/viewtopic.php?id=1547

var
  dsn : string;
begin
  dsn := 'zdbc:mysql://192.168.2.60:3306/world?username=root;password=dev';

U can use it for your ZEOSConnexion initialize, and also be careful what is the Charset used : iso-8859-1, iso-8859-15, utf-8, ansi. Because Delphi 7 using ANSI, and internet servers use often others like UTF-8.

anyway i see an error in your code :

zq.SQL.Text:='SELECT field FROM database.TABLE where id='+inttostr(x)+';';
zq.ExecSQL;

ExecSQL is used to UPDATE, INSERT, DELETE commands, usually for a select command that return records it's more convenient to use open command :

zq.Close;
zq.SQL.Text:='SELECT field FROM database.TABLE where id='+inttostr(x)+';';
zq.Open;

I give many path to investigate, i hope it helps.

Answer 2

i think that you must add some line in a properties of your TZConnection component:

before opening connection with your TZConnection component try this:

function openConnection(BD, User, Host, Pass: string; DBConnection: TZConnection): boolean;
begin
    Result:= false;
    if DBConnection = nil then exit;

    DBConnection.HostName := Host; // Host name to connect
    DBConnection.Protocol := 'mysql-5';
    DBConnection.Port     := 3306; //default port
    DBConnection.Database := DB; // your database name
    DBConnection.User     := User;
    DBConnection.Password := Pass;
    DBConnection.Properties.clear;
    DBConnection.Properties.Add('CLIENT_MULTI_STATEMENTS=1');
    DBConnection.Properties.Add('controls_cp=GET_ACP');

    try
        DBConnection.Connect;
        Result:= DBConnection.Connected;    
    except
        On e:Exception do
        begin
            Result:= false;
            ShowMessage(e.Message);
        end;
    end;
end;