ODBC, Excel, GetTableNames
lazarus / 16-Databases

----------------------------------------------------------------------
dimer · 2021-08-01 12:10

I need get a list of all tables (sheets) in Excel workbook, using ODBC
connection.  This is a code:

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 unit Unit1;
 {$mode objfpc}{$H+} interface uses  Classes, Forms, Controls,
StdCtrls, ExtCtrls,  ODBCconn, SQLDB, DB, DBGrids, DBCtrls; type   {
TForm1 }   TForm1 = class(TForm)    Connection: TODBCConnection;   
Transaction: TSQLTransaction;    Query: TSQLQuery;    DataSource:
TDatasource;     DBGrid2: TDBGrid;    DBNavigator2: TDBNavigator;   
ListBox1: TListBox;    Splitter2: TSplitter;     procedure
FormCreate(Sender: TObject);    procedure ListBox1Click(Sender:
TObject);  private  public  end;  var  Form1: TForm1;
 implementation {$R *.lfm} { TForm1 } procedure
TForm1.ListBox1Click(Sender: TObject);var  tblName: string;  SQL:
String;begin  if Listbox1.ItemIndex = -1 then    Exit;  tblName :=
Listbox1.Items[Listbox1.ItemIndex];  SQL := 'SELECT * FROM ' +
tblName;  Query.Close;  Query.SQL.Text := SQL; 
Query.Open;end; procedure TForm1.FormCreate(Sender: TObject);var 
dbName: String;begin  dbName := Application.Location + 'data.xls';  
// Connection  Connection.Driver:= 'Microsoft Excel Driver (*.xls)'; 
Connection.Params.Text := 'DBQ=' + dbName; 
Connection.Params.Add('DriverID=790'); 
Connection.Params.Add('ReadOnly=1'); 
Connection.Params.Add('AutoCommit=1'); 
Connection.Params.Add('FirstRowHasNames=1'); 
Connection.KeepConnection := True;  Connection.Open;   // Transaction 
Transaction.DataBase := Connection;  Transaction.Action := caCommit; 
Transaction.Active := False;  Transaction.StartTransaction;   // Get
table names  Connection.GetTableNames(Listbox1.Items);   // Important
settings:  Query.Database  :=  Connection;  Query.Transaction  :=
 Transaction;  Query.UsePrimaryKeyAsKey  :=  False;end; 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...

----------------------------------------------------------------------
sstvmaster · 2021-08-01 20:51

I think you have to use FPSpreadsheet:
https://wiki.lazarus.freepascal.org/FPSpreadsheet

You can install it via OPM.

----------------------------------------------------------------------
wp · 2021-08-01 21:44

Use the method GetTableNames of the ODBCConnection. See attached
project.

----------------------------------------------------------------------
sstvmaster · 2021-08-01 21:52

@wp, i thought he would read tables/sheets from an Excel file?
Quote from: dimer on August 01, 2021, 02:10:50 pm... I need get a list
of all tables (sheets) in Excel workbook ...
EDIT: Example with FPSpreadsheet in the attachment.

----------------------------------------------------------------------
wp · 2021-08-01 23:03

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);var  dbName: String;begin  dbName
:= Application.Location + 'datafile.xls';   // Connection 
ODBCConnection1.Driver := 'Microsoft Excel Driver (*.xls)'; 
ODBCConnection1.Params.Add('DBQ=' + dbName); 
ODBCConnection1.Params.Add('AUTOCOMMIT=1'); 
ODBCConnection1.Connected:= True;   // Transaction 
SQLTransaction1.DataBase := ODBCConnection1;  SQLTransaction1.Action
:= caCommit;  SQLTransaction1.Active := false; 
SQLTransaction1.StartTransaction;  
ODBCConnection1.GetTableNames(Listbox1.Items);end;

----------------------------------------------------------------------
devEric69 · 2021-08-02 06:44

[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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i {
minimalist connection string
}sConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;' +        'Data
Source=' + sFullpathExcelFile + ';' +        'Extended
Properties=Excel 8.0;'; 
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 { at the
most complete connection string }sConnectionString:=
 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;'+            'Data
Source='+ sFullpathExcelFile +';'+            'Mode=Share Deny
None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet
OLEDB:Database Password="";'+            'Jet OLEDB:Engine Type=35;Jet
OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;'+            'Jet OLEDB:New Database
Password="";Jet OLEDB:Create System Database=False;'+            '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.

----------------------------------------------------------------------
dimer · 2021-08-02 14:01

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 · 2021-08-03 06:05

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 · 2021-08-03 07:28

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 program
Project1;uses comobj,activex;var  cnn,rst:olevariant; 
cnnstr:widestring; begin  cnn:=createoleobject('adodb.connection'); 
cnnstr:='provider=microsoft.ace.oledb.12.0;extended properties=''excel
12.0'';data source=C:\Users\FanXiaoLei\Desktop\mm.xlsm'; 
cnn.open(cnnstr);  rst:=cnn.openschema(20); 
writeln(rst.fields.count);   while not rst.EOF do      begin     
writeln('Table name: ', rst.fields('TABLE_NAME').value,'   ','Table
type: ', rst.fields('TABLE_TYPE').value);     
//writeln(rst.fields('TABLE_NAME').value);      rst.MoveNext     
end;   rst.Close;   cnn.Close;   cnn:=unassigned;   readln;end.

----------------------------------------------------------------------
dimer · 2021-08-05 10:34

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...