Access violation with GetTableNames
lazarus / 16-Databases

----------------------------------------------------------------------
tatamata · 2009-09-23 06:35

When executing following code, I get "Access violation" error, though
connection is fine, and I can get dataset without no problem. But
GetTableNames raise "Access violation". Why?
Code: [Select]procedure TFormMain.ButtonSQLTablesSchemaClick(Sender:
TObject);
var I:Integer;
begin
  try
    //Establish connection
    with SQLConnector1 do begin
      SQLConnectors.Edit;
      SQLConnectors.Post;
      //Set properties
      CharSet:=SQLConnectors.FieldByName('CHARSET').AsString;
     
ConnectorType:=SQLConnectors.FieldByName('CONNECTORTYPE').AsString;
     
DatabaseName:=SQLConnectors.FieldByName('DATABASENAME').AsString;
      HostName:=SQLConnectors.FieldByName('HOSTNAME').AsString;
      UserName:=SQLConnectors.FieldByName('USERNAME').AsString;
      Password:=SQLConnectors.FieldByName('PASSWORD').AsString;
      Role:=SQLConnectors.FieldByName('ROLE').AsString;
      //Try to connect
      try
        Transaction.Active:=True;
        Connected:=True;
        ShowMessage ('Connection O.K.');
        //Populate table SQLTables
        try
          //Delete SQLTables for current SQLConnector
          SQLTables.First;
          while not SQLTables.EOF do begin
            SQLTables.Delete;
          end;
          //Get information about tables in the database.
          GetTableNames(ListBoxSQLTables.Items,False);
          //Iterate through ListBoxSQLTables and insert into SQLTables
          for I:=0 to ListBoxSQLTables.Items.Count-1 do begin
            SQLTables.Append;
            SQLTables.FieldByName('SQLTABLENAME').AsString:=ListBoxSQL
Tables.Items[I];
            SQLTables.Post;
          end;
        except
          on E:Exception do begin
            ShowMessage('I could not get tables schema. '+E.Message);
          end;
        end;
      except
        on E:Exception do begin
          ShowMessage('I could not establish connection. '+E.Message);
        end;
      end;
    end;
  finally
    SQLConnector1.Connected:=False;
  end;
end;

----------------------------------------------------------------------
tatamata · 2009-09-24 06:54

Is there anything different with GetTableNames and GetFieldNames than
in Delphi? I don't understand, if Connected:=True works and I can view
data from tables using that connection, why I can't use GetTableNames?

----------------------------------------------------------------------
alter · 2009-09-24 13:47

As far as I remember it raises error if some transaction is active.
Try to put Transaction.Active:=True after GetTableNames or at least
ensure that when GetTableNames is called there is no transaction and
see if that works.

----------------------------------------------------------------------
tatamata · 2009-09-25 04:46

No success till now...
Maybe I misunderstood your point.
Anyway, to be honest, I don't understand SQLTransaction component at
all...Why is it needed, in which cases? It seems in most cases
(retrieving data) it is enough to assign it to a connection and leave
it "None"....In which cases is it required to use it ecplicetely
(SQLTransaction.StartTransaction, .EndTransaction, .Commit,
.CommitRetaining) etc?...It's a mess and I don't understand it at
all...

----------------------------------------------------------------------
alter · 2009-09-25 13:04

Sorry, my fault with this. I looked on the wrong part of this code.
Here is the sample code for sqlite:
Code: [Select]procedure SetupSQLite;
  begin
    Result:=0;
    Database.SQLiteConnection:=TSQLite3Connection.Create(nil);
    Database.SQLQuery:=TSQLQuery.Create(nil);
    Database.Trans:=TSQLTransaction.Create(nil);
    Database.SQLQuery.Database:=Database.SQLiteConnection;

    Database.SQLiteConnection.DatabaseName:='Some file name';
    try
      Database.SQLiteConnection.Open;
    except
      Result:=3;
    end;

    if Result=0 then begin
        Database.Trans.DataBase:=Database.SQLiteConnection;
        Database.SQLiteConnection.Transaction:=Database.Trans;
        Database.SQLQuery.Transaction:=Database.Trans;

        Tables:=TStringList.Create;
        Database.Trans.StartTransaction;
        Database.SQLiteConnection.GetTableNames(Tables);
        Database.Trans.Commit;

        Tables.Free;
      end;
  end;It's sub for some function so don't be fooled by Result var.
Note that I have no tables nor queries opened when I call
GetTableNames (didn't try with table or query opened)

----------------------------------------------------------------------
tatamata · 2009-09-28 16:21

Damn, still no success...I just don't get it....

----------------------------------------------------------------------
tatamata · 2009-09-30 03:29

OK., I switched to Zeoslib. In Zeos, GetTableNames works without
problem.

----------------------------------------------------------------------
Loesje · 2009-10-03 21:47

It would be usefull when you find a bug to post it to the bug-tracker

----------------------------------------------------------------------
tatamata · 2009-10-06 06:33

Well, I am not sure whether it was bug or feature:)