Recent

Author Topic: ODBC, Excel, GetTableNames  (Read 5061 times)

dimer

  • Newbie
  • Posts: 4
ODBC, Excel, GetTableNames
« on: August 01, 2021, 02:10:50 pm »
I need get a list of all tables (sheets) in Excel workbook, using ODBC connection.  This is a code:

Code: Pascal  [Select][+][-]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, Forms, Controls, StdCtrls, ExtCtrls,
  9.   ODBCconn, SQLDB, DB, DBGrids, DBCtrls;
  10.  
  11. type
  12.  
  13.   { TForm1 }
  14.  
  15.   TForm1 = class(TForm)
  16.     Connection: TODBCConnection;
  17.     Transaction: TSQLTransaction;
  18.     Query: TSQLQuery;
  19.     DataSource: TDatasource;
  20.  
  21.     DBGrid2: TDBGrid;
  22.     DBNavigator2: TDBNavigator;
  23.     ListBox1: TListBox;
  24.     Splitter2: TSplitter;
  25.  
  26.     procedure FormCreate(Sender: TObject);
  27.     procedure ListBox1Click(Sender: TObject);
  28.   private
  29.   public
  30.   end;
  31.  
  32. var
  33.   Form1: TForm1;
  34.  
  35. implementation
  36.  
  37. {$R *.lfm}
  38.  
  39. { TForm1 }
  40.  
  41. procedure TForm1.ListBox1Click(Sender: TObject);
  42. var
  43.   tblName: string;
  44.   SQL: String;
  45. begin
  46.   if Listbox1.ItemIndex = -1 then
  47.     Exit;
  48.   tblName := Listbox1.Items[Listbox1.ItemIndex];
  49.   SQL := 'SELECT * FROM ' + tblName;
  50.   Query.Close;
  51.   Query.SQL.Text := SQL;
  52.   Query.Open;
  53. end;
  54.  
  55. procedure TForm1.FormCreate(Sender: TObject);
  56. var
  57.   dbName: String;
  58. begin
  59.   dbName := Application.Location + 'data.xls';
  60.  
  61.   // Connection
  62.   Connection.Driver:= 'Microsoft Excel Driver (*.xls)';
  63.   Connection.Params.Text := 'DBQ=' + dbName;
  64.   Connection.Params.Add('DriverID=790');
  65.   Connection.Params.Add('ReadOnly=1');
  66.   Connection.Params.Add('AutoCommit=1');
  67.   Connection.Params.Add('FirstRowHasNames=1');
  68.   Connection.KeepConnection := True;
  69.   Connection.Open;
  70.  
  71.   // Transaction
  72.   Transaction.DataBase := Connection;
  73.   Transaction.Action := caCommit;
  74.   Transaction.Active := False;
  75.   Transaction.StartTransaction;
  76.  
  77.   // Get table names
  78.   Connection.GetTableNames(Listbox1.Items);
  79.  
  80.   // Important settings:
  81.   Query.Database  :=  Connection;
  82.   Query.Transaction  :=  Transaction;
  83.   Query.UsePrimaryKeyAsKey  :=  False;
  84. end;
  85.  
  86. end.

I'm receiving an empty ListBox1. The file data.xls itself contains one sheet named NATURE. But when I'm changing a lot of properties in Lazarus IDE (Connection, Transaction, and so on) - with the SQL query like 'SELECT * FROM [NATURE$]' I'm receiving the proper contents of this sheet. So, ODBC connection with all these properties and options is working!

Wold anybody explain me if any errors are just known, or show me a really working example? Thanks in advance...
« Last Edit: August 01, 2021, 02:18:01 pm by dimer »

sstvmaster

  • Sr. Member
  • ****
  • Posts: 259
Re: ODBC, Excel, GetTableNames
« Reply #1 on: August 01, 2021, 10:51:14 pm »
I think you have to use FPSpreadsheet: https://wiki.lazarus.freepascal.org/FPSpreadsheet

You can install it via OPM.
« Last Edit: August 01, 2021, 10:56:13 pm by sstvmaster »
Windows 10 (64 bit)
Lazarus: 2.0.12 / Trunk x32

wp

  • Hero Member
  • *****
  • Posts: 8747
Re: ODBC, Excel, GetTableNames
« Reply #2 on: August 01, 2021, 11:44:40 pm »
Use the method GetTableNames of the ODBCConnection. See attached project.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

sstvmaster

  • Sr. Member
  • ****
  • Posts: 259
Re: ODBC, Excel, GetTableNames
« Reply #3 on: August 01, 2021, 11:52:33 pm »
@wp, i thought he would read tables/sheets from an Excel file?
... I need get a list of all tables (sheets) in Excel workbook ...

EDIT: Example with FPSpreadsheet in the attachment.
« Last Edit: August 02, 2021, 01:51:39 am by sstvmaster »
Windows 10 (64 bit)
Lazarus: 2.0.12 / Trunk x32

wp

  • Hero Member
  • *****
  • Posts: 8747
Re: ODBC, Excel, GetTableNames
« Reply #4 on: August 02, 2021, 01:03:37 am »
Ah, how can I confuse Excel and Access?

Tried the same with an Excel file, but it returns an empty string list:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   dbName: String;
  4. begin
  5.   dbName := Application.Location + 'datafile.xls';
  6.  
  7.   // Connection
  8.   ODBCConnection1.Driver := 'Microsoft Excel Driver (*.xls)';
  9.   ODBCConnection1.Params.Add('DBQ=' + dbName);
  10.   ODBCConnection1.Params.Add('AUTOCOMMIT=1');
  11.   ODBCConnection1.Connected:= True;
  12.  
  13.   // Transaction
  14.   SQLTransaction1.DataBase := ODBCConnection1;
  15.   SQLTransaction1.Action := caCommit;
  16.   SQLTransaction1.Active := false;
  17.   SQLTransaction1.StartTransaction;
  18.  
  19.   ODBCConnection1.GetTableNames(Listbox1.Items);
  20. end;
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

devEric69

  • Hero Member
  • *****
  • Posts: 567
Re: ODBC, Excel, GetTableNames
« Reply #5 on: August 02, 2021, 08:44:17 am »
[irrelevant on]
For info., here's how i was able to connect an Excel Worksheet as a database, each sheet = 1 table e.g. "SELECT column_name_1, column_name_2 FROM [Sheet1$]", with OLEDB (needed the Access + Excel driver i.e. needed to install AccessDatabaseEngine_X64.exe ( a native driver, with mode = SQL path through this driver, not necessarily through ODBC, AFAIK ), but already written in an object-oriented layer by MS), using Delphi TADOConnection + Microsoft.Jet.OLEDB.4.0 as db-provider:

Code: Pascal  [Select][+][-]
  1. { minimalist connection string }
  2. sConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;' +
  3.         'Data Source=' + sFullpathExcelFile + ';' +
  4.         'Extended Properties=Excel 8.0;';
  5.  

Code: Pascal  [Select][+][-]
  1. { at the most complete connection string }
  2. sConnectionString:=  'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;'+
  3.             'Data Source='+ sFullpathExcelFile +';'+
  4.             'Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";'+
  5.             'Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;'+
  6.             'Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;'+
  7.             'Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don''t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;';
[/irrelevant off]

I'm very interested to see how you do it with TODBCConnection.
« Last Edit: August 02, 2021, 09:14:57 am by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

dimer

  • Newbie
  • Posts: 4
Re: ODBC, Excel, GetTableNames
« Reply #6 on: August 02, 2021, 04:01:01 pm »
All these aren't a proper solution because:
  • non-based on ODBC universality (the same engine for Access and Excel)
  • laying out-of-bounds of Lazarus (ADO and so on)
Please help me (if possible) with the initial demands. All other solutions is well-known, and for myself too. Thanks, but I nee a real help in a described case...

PS Maybe it is something wrong in realization of TODBCConnection within Lazarus / Free pascal? Would anybody inspect a source code to find it?

LacaK

  • Hero Member
  • *****
  • Posts: 637
Re: ODBC, Excel, GetTableNames
« Reply #7 on: August 03, 2021, 08:05:08 am »
TODBCConnection uses internally call to ODBC API function SQLTables().
So IMO it is up to the ODBC driver (Excel driver) what return.
If "Table"="Sheet" then I expect that list of sheets should be returned, but I have no experience with Excel driver.

fan2006

  • New member
  • *
  • Posts: 5
Re: ODBC, Excel, GetTableNames
« Reply #8 on: August 03, 2021, 09:28:28 am »
Code: Pascal  [Select][+][-]
  1. program Project1;
  2. uses comobj,activex;
  3. var
  4.   cnn,rst:olevariant;
  5.   cnnstr:widestring;
  6.  
  7. begin
  8.   cnn:=createoleobject('adodb.connection');
  9.   cnnstr:='provider=microsoft.ace.oledb.12.0;extended properties=''excel 12.0'';data source=C:\Users\FanXiaoLei\Desktop\mm.xlsm';
  10.   cnn.open(cnnstr);
  11.   rst:=cnn.openschema(20);
  12.   writeln(rst.fields.count);
  13.    while not rst.EOF do
  14.       begin
  15.       writeln('Table name: ', rst.fields('TABLE_NAME').value,'   ','Table type: ', rst.fields('TABLE_TYPE').value);
  16.       //writeln(rst.fields('TABLE_NAME').value);
  17.       rst.MoveNext
  18.       end;
  19.    rst.Close;
  20.    cnn.Close;
  21.    cnn:=unassigned;
  22.    readln;
  23. end.  

dimer

  • Newbie
  • Posts: 4
Re: ODBC, Excel, GetTableNames
« Reply #9 on: August 05, 2021, 12:34:07 pm »
Dear fan2006, our honored newuser.
Great thanks for your theoretical efforts. But your code is not working at all. Anybody can get only OLEexception with it. Please try to improve your pure theory, at least you should:
- change OLE variant of 'rst' to 'ADODB.RecordSet';
- forget 'rst.fields.count' as unsupported for RecordSet method;
- your 'fields('TABLE_TYPE').value' can return only one - '(TABLE)', so it is totally not necessary;
Please improve your knowlege before the next answering.

Dear colleague devEric69,
your recommendations are good enough, but in a real case are needed only three parameters:
   Provider=Microsoft.Jet.OLEDB.4.0;
   Extended Properties=Excel 8.0;
   Data Source=...;
It really works with it! Thanks a lot!

Resume to all:
IMHO, Microsoft doesn't pay his attention on GetTableNames according to Excel workbook using as SQL Data Source. I will try to check if GetFieldNames demonstrate the same kind of behavior... Maybe I'd change ODBC access to OLE simulation. God knows...

 

TinyPortal © 2005-2018