ODBC Question
lazarus / 16-Databases

----------------------------------------------------------------------
xinyiman · 2010-06-15 06:56

How do I see what I odbc active on the machine? Let me explain, how do
I know the names of ODBC installed on the machine? Thanks

----------------------------------------------------------------------
theo · 2010-06-15 07:06

What do you mean?
Drivers list, DSN, ... ?

----------------------------------------------------------------------
xinyiman · 2010-06-15 07:14

Dsn is the list that the driver list

----------------------------------------------------------------------
theo · 2010-06-15 13:12

?

----------------------------------------------------------------------
xinyiman · 2010-06-15 22:50

the other is the unche

----------------------------------------------------------------------
xinyiman · 2010-06-21 03:46

Quote from: theo on June 15, 2010, 09:06:25 amWhat do you mean?
Drivers list, DSN, ... ?

Driver List and DSN List!

----------------------------------------------------------------------
spitofland · 2010-06-21 04:21

If I understand your question correctly, then I had the same question.
http://www.lazarus.freepascal.org/index.php/topic,9563.0.html

The answer I received was very helpful. It led me to this website:
http://www.delphi3000.com/articles/article_3584.asp

----------------------------------------------------------------------
xinyiman · 2010-06-21 04:27

Quote from: spitofland on June 21, 2010, 06:21:57 amIf I understand
your question correctly, then I had the same question.
http://www.lazarus.freepascal.org/index.php/topic,9563.0.html

The answer I received was very helpful. It led me to this website:
http://www.delphi3000.com/articles/article_3584.asp

I had already tried to connect to that link, but will not let me open.
You open it and do a copy paste here the content? Thanks

----------------------------------------------------------------------
spitofland · 2010-06-21 07:23

I'm sorry that link does appear to be broken now. Here is the function
that I created based on the information from that link. It retrieves
the information from the registry. I hope this helps you.

This function fills a ComboBox with a list of ODBC DSNs availiable on
the system. It should get the User DSNs and the System DSNs. You need
to include the 'Registry' Unit in the Uses section.

Code: [Select]procedure TLoginPrompt.GetDSNList;
var
  aStringList : TStringlist;
  aStrings1   : TStrings;
  aStrings2   : Tstrings;

  aRegistry   : TRegistry;
  aInt        : Integer;
  tempInt     : Integer;
Begin
  aStringlist:=TStringlist.Create;
  aStrings1:=TStringlist.Create;
  aStrings2:=TStringlist.Create;

  aRegistry:= Tregistry.Create;
  With aRegistry do Begin
    RootKey:=HKEY_CURRENT_USER;
    OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',False);
    GetValueNames(aStrings1);
  End;
  aRegistry.Free;

  aRegistry:= Tregistry.Create;
  With aRegistry do Begin
    RootKey:=HKEY_LOCAL_MACHINE;
    OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',False);
    GetValueNames(aStrings2);
  End;
  aRegistry.Free;

  aStringlist.AddStrings(aStrings1);
  aStrings1.Free;
  aStringlist.AddStrings(aStrings2);
  aStrings2.Free;
  aStringlist.Sort;
  ComboBox1.Clear;
  tempInt := 0;
  for aInt:=0 to aStringList.Count-1 do begin
    if Trim(aStringList[aInt]) <> '' then begin
      ComboBox1.Items.Add(Trim(aStringList[aInt]));
      INC(tempInt);
    end;
  end;
End;

----------------------------------------------------------------------
Lacak2 · 2010-06-21 19:27

Another approach is using ODBC32 API:
SQLDataSources()
SQLDrivers()

See here: http://www.delphipages.com/forum/showthread.php?t=107542

----------------------------------------------------------------------
xinyiman · 2010-06-21 19:38

Quote from: spitofland on June 21, 2010, 09:23:30 amI'm sorry that
link does appear to be broken now. Here is the function that I created
based on the information from that link. It retrieves the information
from the registry. I hope this helps you.

This function fills a ComboBox with a list of ODBC DSNs availiable on
the system. It should get the User DSNs and the System DSNs. You need
to include the 'Registry' Unit in the Uses section.

Code: [Select]procedure TLoginPrompt.GetDSNList;
var
  aStringList : TStringlist;
  aStrings1   : TStrings;
  aStrings2   : Tstrings;

  aRegistry   : TRegistry;
  aInt        : Integer;
  tempInt     : Integer;
Begin
  aStringlist:=TStringlist.Create;
  aStrings1:=TStringlist.Create;
  aStrings2:=TStringlist.Create;

  aRegistry:= Tregistry.Create;
  With aRegistry do Begin
    RootKey:=HKEY_CURRENT_USER;
    OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',False);
    GetValueNames(aStrings1);
  End;
  aRegistry.Free;

  aRegistry:= Tregistry.Create;
  With aRegistry do Begin
    RootKey:=HKEY_LOCAL_MACHINE;
    OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',False);
    GetValueNames(aStrings2);
  End;
  aRegistry.Free;

  aStringlist.AddStrings(aStrings1);
  aStrings1.Free;
  aStringlist.AddStrings(aStrings2);
  aStrings2.Free;
  aStringlist.Sort;
  ComboBox1.Clear;
  tempInt := 0;
  for aInt:=0 to aStringList.Count-1 do begin
    if Trim(aStringList[aInt]) <> '' then begin
      ComboBox1.Items.Add(Trim(aStringList[aInt]));
      INC(tempInt);
    end;
  end;
End;
Is a multi-platform method?

----------------------------------------------------------------------
xinyiman · 2010-06-21 21:06

And how do I get the list of tables after I connected to DB via ODBC?

TODBCConnection.GetTableNames();

 function always returns an error!

----------------------------------------------------------------------
Lacak2 · 2010-06-22 01:45

Quote from: xinyiman on June 21, 2010, 11:06:36
pmTODBCConnection.GetTableNames();

 function always returns an error!
Which error ? Which DB ?
(for me it works ok)

----------------------------------------------------------------------
xinyiman · 2010-06-22 02:53

Quote from: Lacak2 on June 22, 2010, 03:45:58 amQuote from: xinyiman
on June 21, 2010, 11:06:36 pmTODBCConnection.GetTableNames();

 function always returns an error!
Which error ? Which DB ?
(for me it works ok)

Error: The metadata is not available for this type of database.
DB: MySql 5.1.41-community via tcp/ip
OS: Windows Xp SP3

----------------------------------------------------------------------
Lacak2 · 2010-06-23 00:05

Do you call GetTableNames(..., true); with second argument
"SystemTables"=true ?
If yes, then this is missing feature in TODBCConnection ... see:
http://bugs.freepascal.org/view.php?id=13893#bugnotes

----------------------------------------------------------------------
xinyiman · 2010-06-23 20:52

Quote from: Lacak2 on June 23, 2010, 02:05:31 amDo you call
GetTableNames(..., true); with second argument "SystemTables"=true ?
If yes, then this is missing feature in TODBCConnection ... see:
http://bugs.freepascal.org/view.php?id=13893#bugnotes


Not working even with the second argument set to true, I pointed to
the fact that I've reported bugreport!

----------------------------------------------------------------------
xinyiman · 2010-06-23 22:30

Now that aspect of knowing how to see the names of the tables, I
assume that the table name that interests me, how do I know the name
and type of fields that comprise it?

----------------------------------------------------------------------
xinyiman · 2010-06-23 23:05

I understand how you retrieve the field names and their data types!
Assuming you use a TSQLQuey called Q_Query and TListBox List_Struttura
name where the code will print the results:

 List_Struttura.Clear;
     for i:=0 to (Q_Query.FieldCount-1) do
     begin
          Tipo:=Q_Query.FieldDefs.Items.DataType;
          List_Struttura.Items.Add(Q_Query.FieldDefs.Items.Name + '
--> ' + GetEnumName(TypeInfo(TFieldType), Integer(Tipo)));
     end;

add uses typinf to GetEnumName function


I now only remains to figure out how to retrieve the name of the
tables and if the way I was told to retrieve the name of the DSN is
cross-platform!

----------------------------------------------------------------------
xinyiman · 2010-06-23 23:52

Quote from: spitofland on June 21, 2010, 09:23:30 amI'm sorry that
link does appear to be broken now. Here is the function that I created
based on the information from that link. It retrieves the information
from the registry. I hope this helps you.

This function fills a ComboBox with a list of ODBC DSNs availiable on
the system. It should get the User DSNs and the System DSNs. You need
to include the 'Registry' Unit in the Uses section.

Code: [Select]procedure TLoginPrompt.GetDSNList;
var
  aStringList : TStringlist;
  aStrings1   : TStrings;
  aStrings2   : Tstrings;

  aRegistry   : TRegistry;
  aInt        : Integer;
  tempInt     : Integer;
Begin
  aStringlist:=TStringlist.Create;
  aStrings1:=TStringlist.Create;
  aStrings2:=TStringlist.Create;

  aRegistry:= Tregistry.Create;
  With aRegistry do Begin
    RootKey:=HKEY_CURRENT_USER;
    OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',False);
    GetValueNames(aStrings1);
  End;
  aRegistry.Free;

  aRegistry:= Tregistry.Create;
  With aRegistry do Begin
    RootKey:=HKEY_LOCAL_MACHINE;
    OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',False);
    GetValueNames(aStrings2);
  End;
  aRegistry.Free;

  aStringlist.AddStrings(aStrings1);
  aStrings1.Free;
  aStringlist.AddStrings(aStrings2);
  aStrings2.Free;
  aStringlist.Sort;
  ComboBox1.Clear;
  tempInt := 0;
  for aInt:=0 to aStringList.Count-1 do begin
    if Trim(aStringList[aInt]) <> '' then begin
      ComboBox1.Items.Add(Trim(aStringList[aInt]));
      INC(tempInt);
    end;
  end;
End;
Ok, I understand that is not a platform, because Linux does not work!
How do I find a list of dsn Linux / lazarus? It would be a better
platform, but if there's enough for me to know how to retrieve it with
Linux. Help me please!

----------------------------------------------------------------------
Lacak2 · 2010-06-24 01:09

Quote from: xinyiman on June 23, 2010, 10:52:53 pmQuote from: Lacak2
on June 23, 2010, 02:05:31 amDo you call GetTableNames(..., true);
with second argument "SystemTables"=true ?
If yes, then this is missing feature in TODBCConnection ... see:
http://bugs.freepascal.org/view.php?id=13893#bugnotes


Not working even with the second argument set to true, I pointed to
the fact that I've reported bugreport!
You must download Lazarus with FPC >= 2.3.1 (If you use 2.2.4 there is
not implemented GetTableNames for TODBCCOnnection at all)

----------------------------------------------------------------------
Lacak2 · 2010-06-24 01:16

Quote from: xinyiman on June 24, 2010, 01:52:10 amHow do I find a list
of dsn Linux / lazarus? It would be a better platform, but if there's
enough for me to know how to retrieve it with Linux. Help me please!

Have you tried ?:
SQLDataSources : http://msdn.microsoft.com/en-
us/library/ms711004(VS.85).aspx

It should work under Linux as far as it support standard ODBC API
(but I can not confirm, because I do not work with Linux)

----------------------------------------------------------------------
xinyiman · 2010-06-24 03:35

So I feel I wrote a C library that allows me to retrieve this data,
both on Linux and Windows, the list of dsn that the list of tables! I
just do not know how to create the file. Dll and filw. I do not know
how to see Lazarus the dll you could help me?

----------------------------------------------------------------------
theo · 2010-06-24 03:58

I wrote something in this area. This one is with Linux *.so included.
It will probably work on win too if you have the dll.

http://www.theo.ch/lazarus/mdbtools.zip

----------------------------------------------------------------------
xinyiman · 2010-06-24 04:37

Quote from: theo on June 24, 2010, 05:58:49 amI wrote something in
this area. This one is with Linux *.so included.
It will probably work on win too if you have the dll.

http://www.theo.ch/lazarus/mdbtools.zip
You could not fill. So on. Dll?

----------------------------------------------------------------------
theo · 2010-06-24 04:49

Quote from: xinyiman on June 24, 2010, 06:37:11 amYou could not fill.
So on. Dll?

I don't really understand you, but I guess you need the *.dll.
I don't know, I just neeeded the library on Linux.
It's this project, there you can get the source:
http://mdbtools.sourceforge.net/

EDIT: Sorry, I got that wrong. You don't need tools for mdb, you want
MySql over ODBC.

----------------------------------------------------------------------
xinyiman · 2010-06-24 05:07

Quote from: theo on June 24, 2010, 06:49:27 amQuote from: xinyiman on
June 24, 2010, 06:37:11 amYou could not fill. So on. Dll?

I don't really understand you, but I guess you need the *.dll.
I don't know, I just neeeded the library on Linux.
It's this project, there you can get the source:
http://mdbtools.sourceforge.net/

EDIT: Sorry, I got that wrong. You don't need tools for mdb, you want
MySql over ODBC.

Exact

----------------------------------------------------------------------
theo · 2010-06-24 05:10

Btw: SVK-ITA: 2:0 !!
Ahh...

EDIT:
Buon viaggio azzurri !

----------------------------------------------------------------------
xinyiman · 2010-06-24 19:37

Quote from: theo on June 24, 2010, 07:10:59 amBtw: SVK-ITA: 2:0 !!
Ahh...

EDIT:
Buon viaggio azzurri !

Really SVK-ITA: 3-2 !!
Grazie