Lazarus

Programming => Databases => Topic started by: aducom on March 08, 2016, 08:27:58 pm

Title: Is there somebody who has a working gettablenames sample?
Post by: aducom on March 08, 2016, 08:27:58 pm
Whenever i call this method (from the standard db components, not zeos) I get a method not supported error or field 'table-name' not found error. I'm using MySQL. Does anybody have a working sample?
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: tsr on March 08, 2016, 10:00:30 pm
I have a working example. Put memo on form and:
Code: Pascal  [Select]
  1. MySQL55Connection1.GetTableNames(Memo1.Lines);
It shows me a list of tables in memo 8-)

If it doesn't work for you then show your code. What FPC & Lazarus Version you use?
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: aducom on March 11, 2016, 11:05:13 am
original working code, but unwanted...
Code: [Select]
   T := TSQLTransaction.Create(DM);
   DB := TSQLConnector.Create(DM);
   Q := TSQLQuery.Create(DM);
   DBC := DM.phSpeedProject.GetDBConnection(ConnectionId);
   if DBC <> nil then begin
     DB.ConnectorType := DBC.DBType;
     DB.DatabaseName := DBC.DBDatabase;
     DB.Password := DBC.DBPwd;
     DB.UserName := DBC.DBUid;
     DB.Hostname := DBC.DBHost;
     if DBC.DBPort <> '' then
        DB.HostName := DB.HostName+':'+DBC.DBPort;
//     DB.DBFile := DBC.DBFile;
   end;

   DB.Transaction := T;
   try
     DB.Open;
     Q.DataBase := DB;
     if pos('MYSQL', uppercase(DBC.DBType)) > 0 then begin
       Q.SQL.Text := 'show tables';
       Q.Open;
       while not Q.EOF do begin
         sl.Add(Q.Fields[0].AsString);
         Q.Next;
       end;
    end;
   except
//     SB.SimpleText := 'connection FAILED';
   end;
   Q.Free;
   DB.Free;
   T.Free;   

Would like to replace with:

Code: [Select]
....
   DB.Transaction := T;
   try
     DB.Open;
     DB.GetTableNames(sl, true);   
...
but generates an error : the metadata is not available for this type of database
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: rvk on March 11, 2016, 11:56:35 am
Quote
Code: [Select]
DB := TSQLConnector.Create(DM);
What did you put in ConnectorType for the TSQLConnector??

You used the more generic TSQLConnector instead of the available TMySQL55Connection... etc.
So you need to provide the correct ConnectorType otherwise TSQLConnector doesn't know what database exactly it's dealing with.

Or could you try using TSQL55Connection (or TSQL56Connection etc)?
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: aducom on March 11, 2016, 12:03:41 pm
Ah, I need to be able to use different databases like in Zeos, but wanted to stay with standard lazarus functionality. I think that I have some other issue, because if I use the mysqlib.dll from my current database (setup by wamp) I doesn't work because the lib cannot be loaded. I'll do some more tests. I'm currently using an old dll that does work. That might be the issue.
In general I have a datamodule containing all the database components which are used when a user request a connection to a certain database. The application I'm building should not be database dependent and configurable.
btw, I can access the database without any issues, only the metadata things don't work.
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: rvk on March 11, 2016, 12:07:21 pm
Ah, I need to be able to use different databases like in Zeos, but wanted to stay with standard lazarus functionality. I think that I have some other issue, because if I use the mysqlib.dll from my current database (setup by wamp) I doesn't work because the lib cannot be loaded. I'll do some more tests. I'm currently using an old dll that does work. That might be the issue.
Make sure your bitness of Lazarus and dll match.
So if you use Lazarus 1.6 64bit you also need a 64bit version of mysqlib.dll.
If you use Lazarus 1.4.4 32bit you need a 32bit version of mysqlib.dll.
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: aducom on March 11, 2016, 12:17:44 pm
Ouch, that would be stupid of me, but you have a valid point there. I use 32 b lazarus, but afaik a 64 bit wamp. I'll look into that..
Sooo obvious, I hope that's the case.
tnx.
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: aducom on March 11, 2016, 02:28:04 pm
Well, I have uninstalled wamp and re-installed the 32 bits version. I also have 32 bits lazarus and win32 as target. Copied the libmysql.dll to my application root. Still no luck, the errormessage remains 'cannot load default default MySQL library. I also tried to put the file in windows system32 with no luck. the old libmysql does work, but then I cannot use the gettablenames for sure.

Who has some more idea's?
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: aducom on March 11, 2016, 03:05:51 pm
I tried the library connector, selected the dll, applied the mysql5.5 connector and then enabled. Gave me the same errormessage
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: SymbolicFrank on March 11, 2016, 03:09:56 pm
In my experience, MySQL only works if both the bitness and version number of all the different parts is the exact same.

So, if you use the TMySQL56Connection, make sure that both the MySQL server as the libmysql.dll have that same version number.

I often have to try different builds of the dll to find one that actually works. I have a whole library by now.
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: aducom on March 11, 2016, 03:18:51 pm
Thank you. Yes, I finally found a combination that works....

Title: Re: Is there somebody who has a working gettablenames sample?
Post by: aducom on March 11, 2016, 04:45:15 pm
Well... actually the problem is something else: I use a SQL connector. If you use the gettablenames of that component you get the 'the metadata is not avaliable for this type of database'. If you use the 'bare' database components it works well. I'll try to dive deeper...
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: rvk on March 11, 2016, 05:05:27 pm
It's probably because the TSQLConnector does this:
Code: Pascal  [Select]
  1. function TSQLConnection.GetSchemaInfoSQL( SchemaType : TSchemaType; SchemaObjectName, SchemaPattern : string) : string;
  2. begin
  3.   case SchemaType of
  4.     stProcedures: Result := 'SELECT *, ROUTINE_NAME AS PROCEDURE_NAME FROM INFORMATION_SCHEMA.ROUTINES';
  5.     stSchemata  : Result := 'SELECT * FROM INFORMATION_SCHEMA.SCHEMATA';
  6.     stSequences : Result := 'SELECT * FROM INFORMATION_SCHEMA.SEQUENCES';
  7.     else DatabaseError(SMetadataUnavailable);       // <---------------- THIS IS YOUR ERROR
  8.   end;
  9. end;
  10.  
While the "deeper" TConnectionName (which is the base for TMySQL50Connection etc) does this:
Code: Pascal  [Select]
  1. function TConnectionName.GetSchemaInfoSQL(SchemaType: TSchemaType; SchemaObjectName, SchemaPattern: string): string;
  2. begin
  3.   case SchemaType of
  4.     stTables     : result := 'show tables';       // <---------------- YOU WANT THIS ONE
  5.     stColumns    : result := 'show columns from ' + EscapeString(SchemaObjectName);
  6.   else
  7.     DatabaseError(SMetadataUnavailable)
  8.   end; {case}
  9. end;

I thought the TSQLConnection automatically created a TMySQL50Connection according to the ConnectorType but apparently it doesn't work like that.
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: LacaK on March 11, 2016, 06:46:56 pm
Well... actually the problem is something else: I use a SQL connector. If you use the gettablenames of that component you get the 'the metadata is not avaliable for this type of database'. If you use the 'bare' database components it works well. I'll try to dive deeper...

Strange, because TSQLConnector overrides GetSchemaInfoSQL and forwards call to "underlaying" connection ... so it should pass to GetSchemaInfoSQL
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: aducom on March 11, 2016, 07:31:00 pm
@rvk: no it doesn't create that. But the odd thing is that you will need the appropiate db component on the form to get access to it's prototype. Otherwise the connector doesn't work. Didn't try to 'just apply the include'.

But it looks like the method is not complete.

@LacaK: Appearantly it doesn't. Not sure...
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: rvk on March 11, 2016, 07:44:15 pm
@rvk: no it doesn't create that. But the odd thing is that you will need the appropiate db component on the form to get access to it's prototype. Otherwise the connector doesn't work.
It does. It create a "Proxy"-connection internally (you can see that in the source). FProxy in TSQLConnector is filled with the correct TMySQLXXConnection. It is used in TSQLConnector for lots of things.

Only problem is that TSQLConnector doesn't implement gettablenames and as a consequence the gettablenames of TSQLConnection is used. That in turn uses GetDBInfo() in which there is no Proxy so the actual TMySQLXXConnection is never used for the gettablenames.

Only TMySQLXXConnection has a direct override for gettablenames which handles this correctly.

B.T.W. did you include mysql55conn, mysql51conn etc in your uses clause? I think it is necessary for the registering of those TMySQLXXConnections (but I could be wrong).
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: LacaK on March 12, 2016, 08:54:53 am
I thought the TSQLConnection automatically created a TMySQL50Connection according to the ConnectorType but apparently it doesn't work like that.

IMO TSQLConnector creates TSQLConnection based on ConnectorType.
Try TSQLConnector.Proxy ...

@aducom: can you try TSQLConnector.Proxy.GetTableNames ?
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: rvk on March 12, 2016, 02:18:54 pm
Yes, I already mentioned the Proxy-connection in TSQLConnector.
The problem is that FProxy is a private property.

The TSQLConnector.GetTableNames works for SQLite because the default version of GetTableNames in TSQLConnection is apparently written for SQLite. I'm not even sure why TSQLConnection even has a GetTableNames because it should be an abstract class from which the others are made.

So TSQLConnector.GetTableNames should probably be overridden and use the one from FProxy.

I can't override TSQLConnector to get to the FProxy because it's private.

Fastest way I can think of is creating your own GetTableNames and calling GetDBInfo() directly but in that case it will fail again if you use this with anything else but MySQL.
Code: Pascal  [Select]
  1. type
  2.   TMySQLConnector = class(TSQLConnector)
  3.   public
  4.     procedure myGetTableNames(List : TStrings; SystemTables : Boolean = false);
  5.   end;
  6.  
  7. procedure TMySQLConnector.myGetTableNames(List : TStrings; SystemTables : Boolean = false);
  8. begin
  9.   GetDBInfo(stTables,'','tables_in_'+DatabaseName,List)
  10. end;

Because this is not a real solution I submitted this issue (with solution) to the bugtracker:
http://bugs.freepascal.org/view.php?id=29819
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: LacaK on March 12, 2016, 03:07:44 pm
if you call TSQLConnector.GetTableNames then TSQLConnector.GetDBInfo is called.
GetDBInfo sets required schemaInfo and opens "metadata dataset"
While preparing this "metadata dataset" GetSchemaInfoSQL is called.
There is overriden GetSchemaInfoSQL for TSQLConnector, which calls FProxy.GetSchemaInfoSQL
It seems me okay ... but may be that here is any problem I will check next week ...
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: rvk on March 12, 2016, 04:23:08 pm
Then my first question would be... why is there a separate GetTableNames in the TMySQLXXConnections? If the GetSchemaInfoSQL takes care of it all it shouldn't be necessary.

I've looked a bit further and it seems like the GetSchemaInfoSQL() of the TMySQL56Connection does indeed gets called. Also MySQL doesn't support getting system-tables so calling GetTableNames should give the mentioned error (not available).

But... calling GetTableNames with false (for usertables) still gives an error.
That's because the GetTabeNames of TSQLConnection fills in a "Returnfield" parameter with "table_name". Like this:
Code: Pascal  [Select]
  1.   GetDBInfo(stTables,'','table_name',List)
For the TMySQLXXConnections however the returnfield is "tables_in_databasename". You can see that because the GetTableNames in TMySQLXXConnection does this:
Code: Pascal  [Select]
  1.   GetDBInfo(stTables,'','tables_in_'+DatabaseName,List)

Oh, AND ADDITIONAL:
Same as above also goes for TSQLConnector.GetFieldNames(TableName, TStrings)
 : Field not found : "column_name".

(I added it to the bug-entry as comment.)
Title: Re: Is there somebody who has a working gettablenames sample?
Post by: rvk on March 14, 2016, 05:19:05 pm
@aducom, The issue with GetTableNames() and GetFieldNames() with TSQLConnector i.c.w. MySQL5+ is now fixed in trunk (FPC 3.1.1) by LacaK.
http://bugs.freepascal.org/view.php?id=29819
(it could take a while before it is in a stable release version)