TCustomSQLQuery.SetSchemaInfo problem
lazarus / 16-Databases

----------------------------------------------------------------------
ecalogiuri · 2015-06-09 09:58

Hi guys,
I need to get a list of the indexes from a database access, but
TCustomSQLQuery.SetSchemaInfo generates an error such that the current
database does not support this type of metadata. But it is not true,
because I created the indexes on some tables with other software (MDB
Viewer plus).

This is the code i use (under Windows 7 Professional):

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
 Var Path : String;
begin
 Path :=
IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName));
 Path := Path + 'RWDBase.MDB';

 Conn.Driver := 'Microsoft Access Driver (*.mdb)';
 Conn.Params.Add('DBQ=' + Path);

 Conn.Connected := True;

 Qry.DataBase := Conn;
 Qry.Transaction := Tr;

 DS1.DataSet := Qry;

 Qry.SetSchemaInfo(stIndexes, 'IDTables1', '');

  //If i use Qry.SetSchemaInfo(stColums, 'IDTables1', ''); work fine
and i see the colums data into dbgrid!

 Try
   Qry.Open;
 Except
 end;
end;

Where is the mistake? I have to get a list of the indexes for the
table, suits me any way!

greetings...

----------------------------------------------------------------------
rvk · 2015-06-09 11:10

From fpc\packages\fcl-db\src\sqldb\odbc\odbcconn.pas:
Code: [Select]function TODBCConnection.GetSchemaInfoSQL(SchemaType:
TSchemaType; SchemaObjectName, SchemaObjectPattern: string): string;
begin
  if SchemaType in [stTables, stSysTables, stColumns, stProcedures]
then
  begin
    //...
So only stTables, stSysTables, stColumns and stProcedures are
supported with SchemaInfo in TODBCConnection.

But... aren't the Qry.ServerIndexDefs accessible (and filled in) for
you??

----------------------------------------------------------------------
LacaK · 2015-06-10 05:29

Quote from: ecalogiuri on June 09, 2015, 11:58:05 amWhere is the
mistake? I have to get a list of the indexes for the table, suits me
any way!

Problem is, that in case of ODBC we can rely only on ODBC API
functions.
And there is no one which returns indexes for given table.
There is SQLStatistics functions, which can be used, but it returns
also columns included in index not only index list ...

----------------------------------------------------------------------
rvk · 2015-06-10 07:05

Quote from: LacaK on June 10, 2015, 07:29:08 amThere is SQLStatistics
functions, which can be used, but it returns also columns included in
index not only index list ...
Isn't that exactly what TODBCConnection.UpdateIndexDefs does? It
retrieves all the statistics (including indexes) and fills in
ServerIndexDefs.

Hence my question, if reading out Qry.ServerIndexDefs wasn't enough to
get the indexes.

----------------------------------------------------------------------
ecalogiuri · 2015-06-10 07:18

My TODBCConnection does not have a method UpdateIndexDefs, and my
TCustomSQLQuery not has a property called ServerIndexDefs.

Using Lazarus version 1.0.2, this is the problem?

----------------------------------------------------------------------
rvk · 2015-06-10 07:45

Quote from: ecalogiuri on June 10, 2015, 09:18:44 amMy TODBCConnection
does not have a method UpdateIndexDefs, and my TCustomSQLQuery not has
a property called ServerIndexDefs.

Using Lazarus version 1.0.2, this is the problem?
UpdateIndexDefs is not a method you can use directly. It is used
internally to fill in the ServerIndexDefs.

What kind of TCustomSQLQuery component are you using?
Because TSQLQuery.ServerIndexDefs is accessible as far as I can see
back (2011).

Maybe you're using a custom SQLQuery where ServerIndexDefs isn't
published.

----------------------------------------------------------------------
ecalogiuri · 2015-06-10 08:01

I use TCustomSQLQuery from c:\lazarus\fpc\2.6.0\source\packages\fcl-
db\src\sqldb\sqldb.pp

In fact, into this source, i see the property ServerIndexDefs, but
can't use it!

----------------------------------------------------------------------
ecalogiuri · 2015-06-10 08:03

Since the program will run only on Windows, there is a call to some
dll that returns me a list of the indexes for the table?

----------------------------------------------------------------------
rvk · 2015-06-10 08:14

Quote from: ecalogiuri on June 10, 2015, 10:01:25 amI use
TCustomSQLQuery from c:\lazarus\fpc\2.6.0\source\packages\fcl-
db\src\sqldb\sqldb.pp

In fact, into this source, i see the property ServerIndexDefs, but
can't use it! 
That's because ServerIndexDefs is still protected in TCustomSQLQuery.
It's up to its decendents to publish the property.

Why don't you use TSQLQuery? In that one the ServerIndexDefs is
published and accessible.


Or if you made your own descendant from TCustomSQLQuery publish the
ServerIndexDefs yourself.
I don't think you should ever use the TCustomxxx classes directly.
Only when inheriting them you can use TCustomxxxx and you need to
decide which properties you want to publish in that case.

----------------------------------------------------------------------
ecalogiuri · 2015-06-10 08:54

I try to convert TCustomSQLQuery with TSQLQuery. SetSchemaInfo work
fine for tables, views and fields. But not work for index (same
message to TCustomSQLQuery). I try to use this code:

Code: [Select]procedure TForm1.TestIndex;
begin
 Qi.SQL.Text := 'Select * From IDTables1';
 Qi.ParseSQL := False;
 Qi.UsePrimaryKeyAsKey := False;
 Ti.Active := True;

 Try
   //Qi.Open;
   Qi.Prepare;

   Qi.ServerIndexDefs.Update;

   ShowMessage(IntToStr(Qi.ServerIndexDefs.Count));
 Except
 end;

 Qi.Close;
 Ti.Commit;
 Ti.Active := False;
end;

This code show me value 0. But table IDTables1 have 2 index!

----------------------------------------------------------------------
rvk · 2015-06-10 09:43

Ok, this went a big further than I expected... but I got some code for
you

The problem is that UpdateIndexDefs expects that UsePrimaryKeyAsKey is
true and is supported. But the problem is that it's not supported by
ODBC. So I did some "hacking".

In UpdateIndexDefs there is first some code to get the Primary Key.
That's the one that's causing the problem. After that all the normal
indexes are retrieved via SQLStatistics (like LacaK also said). So you
can just use the SQLStatistics function to retrieve the indexes. I've
created a small class-helper and copied the UpdateIndexDefs and
deleted the code which tried to retrieve the Primary Key.

If your version doesn't support class helpers yet you can rebuild the
function to work without it. This example is just to show that with
SQLStatistics it is possible to retrieve the indexes.

(I had to include the ODBCCheckResult functions because they are not
public but you can rewrite all this)

See the attached project for complete code but here is the essence:
Code: [Select]    Indexes := TIndexDefs.Create(nil);
    try
      oConn.MyUpdateIndexDefs(Indexes, 'CUSTOMER');
      Memo1.Lines.Add(format('%d indexes found', [Indexes.Count]));
      for I := 0 to Indexes.Count - 1 do
      begin
        Memo1.Lines.Add('-------');
        Memo1.Lines.Add(Indexes[I].Name);
        Memo1.Lines.Add(Indexes[I].Fields);
      end;
    finally
      Indexes.Free;
    end;

----------------------------------------------------------------------
ecalogiuri · 2015-06-10 10:05

Thanks a lot, rvk! You saved my day! In fact even I was looking to
change something in class TODBCConnection ... Thanks again!