Is there somebody who has a working gettablenames sample? lazarus / 16-Databases ---------------------------------------------------------------------- aducom · 2016-03-08 19:27 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? ---------------------------------------------------------------------- tsr · 2016-03-08 21:00 I have a working example. Put memo on form and: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i MySQL55Connection1.GetTableNames(Memo1.Lines); It shows me a list of tables in memo If it doesn't work for you then show your code. What FPC & Lazarus Version you use? ---------------------------------------------------------------------- aducom · 2016-03-11 10:05 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 ---------------------------------------------------------------------- rvk · 2016-03-11 10:56 QuoteCode: [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)? ---------------------------------------------------------------------- aducom · 2016-03-11 11:03 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. ---------------------------------------------------------------------- rvk · 2016-03-11 11:07 Quote from: aducom on March 11, 2016, 12:03:41 pmAh, 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. ---------------------------------------------------------------------- aducom · 2016-03-11 11:17 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. ---------------------------------------------------------------------- aducom · 2016-03-11 13:28 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? ---------------------------------------------------------------------- aducom · 2016-03-11 14:05 I tried the library connector, selected the dll, applied the mysql5.5 connector and then enabled. Gave me the same errormessage ---------------------------------------------------------------------- SymbolicFrank · 2016-03-11 14:09 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. ---------------------------------------------------------------------- aducom · 2016-03-11 14:18 Thank you. Yes, I finally found a combination that works.... ---------------------------------------------------------------------- aducom · 2016-03-11 15:45 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... ---------------------------------------------------------------------- rvk · 2016-03-11 16:05 It's probably because the TSQLConnector does this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function TSQLConnection.GetSchemaInfoSQL( SchemaType : TSchemaType; SchemaObjectName, SchemaPattern : string) : string;begin  case SchemaType of    stProcedures: Result := 'SELECT *, ROUTINE_NAME AS PROCEDURE_NAME FROM INFORMATION_SCHEMA.ROUTINES';    stSchemata  : Result := 'SELECT * FROM INFORMATION_SCHEMA.SCHEMATA';    stSequences : Result := 'SELECT * FROM INFORMATION_SCHEMA.SEQUENCES';    else DatabaseError(SMetadataUnavailable);       // <---------------- THIS IS YOUR ERROR  end;end; While the "deeper" TConnectionName (which is the base for TMySQL50Connection etc) does this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function TConnectionName.GetSchemaInfoSQL(SchemaType: TSchemaType; SchemaObjectName, SchemaPattern: string): string;begin  case SchemaType of    stTables     : result := 'show tables';       // <---------------- YOU WANT THIS ONE    stColumns    : result := 'show columns from ' + EscapeString(SchemaObjectName);  else    DatabaseError(SMetadataUnavailable)  end; {case}end; I thought the TSQLConnection automatically created a TMySQL50Connection according to the ConnectorType but apparently it doesn't work like that. ---------------------------------------------------------------------- LacaK · 2016-03-11 17:46 Quote from: aducom on March 11, 2016, 04:45:15 pmWell... 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 ---------------------------------------------------------------------- aducom · 2016-03-11 18:31 @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... ---------------------------------------------------------------------- rvk · 2016-03-11 18:44 Quote from: 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. 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). ---------------------------------------------------------------------- LacaK · 2016-03-12 07:54 Quote from: rvk on March 11, 2016, 05:05:27 pmI 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 ? ---------------------------------------------------------------------- rvk · 2016-03-12 13:18 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i type  TMySQLConnector = class(TSQLConnector)  public    procedure myGetTableNames(List : TStrings; SystemTables : Boolean = false);  end; procedure TMySQLConnector.myGetTableNames(List : TStrings; SystemTables : Boolean = false);begin  GetDBInfo(stTables,'','tables_in_'+DatabaseName,List)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 ---------------------------------------------------------------------- LacaK · 2016-03-12 14:07 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 ... ---------------------------------------------------------------------- rvk · 2016-03-12 15:23 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   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.) ---------------------------------------------------------------------- rvk · 2016-03-14 16:19 @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)