Recent

Author Topic: Is there somebody who has a working gettablenames sample?  (Read 7337 times)

aducom

  • Full Member
  • ***
  • Posts: 122
    • http://www.aducom.com
Is there somebody who has a working gettablenames sample?
« 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?

tsr

  • New Member
  • *
  • Posts: 21
Re: Is there somebody who has a working gettablenames sample?
« Reply #1 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?
Ubuntu 64bit

aducom

  • Full Member
  • ***
  • Posts: 122
    • http://www.aducom.com
Re: Is there somebody who has a working gettablenames sample?
« Reply #2 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

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Is there somebody who has a working gettablenames sample?
« Reply #3 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)?

aducom

  • Full Member
  • ***
  • Posts: 122
    • http://www.aducom.com
Re: Is there somebody who has a working gettablenames sample?
« Reply #4 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.

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Is there somebody who has a working gettablenames sample?
« Reply #5 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.

aducom

  • Full Member
  • ***
  • Posts: 122
    • http://www.aducom.com
Re: Is there somebody who has a working gettablenames sample?
« Reply #6 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.

aducom

  • Full Member
  • ***
  • Posts: 122
    • http://www.aducom.com
Re: Is there somebody who has a working gettablenames sample?
« Reply #7 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?

aducom

  • Full Member
  • ***
  • Posts: 122
    • http://www.aducom.com
Re: Is there somebody who has a working gettablenames sample?
« Reply #8 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

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 635
Re: Is there somebody who has a working gettablenames sample?
« Reply #9 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.

aducom

  • Full Member
  • ***
  • Posts: 122
    • http://www.aducom.com
Re: Is there somebody who has a working gettablenames sample?
« Reply #10 on: March 11, 2016, 03:18:51 pm »
Thank you. Yes, I finally found a combination that works....


aducom

  • Full Member
  • ***
  • Posts: 122
    • http://www.aducom.com
Re: Is there somebody who has a working gettablenames sample?
« Reply #11 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...

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Is there somebody who has a working gettablenames sample?
« Reply #12 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.

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: Is there somebody who has a working gettablenames sample?
« Reply #13 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

aducom

  • Full Member
  • ***
  • Posts: 122
    • http://www.aducom.com
Re: Is there somebody who has a working gettablenames sample?
« Reply #14 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...
« Last Edit: March 11, 2016, 07:36:37 pm by aducom »