0

I am trying to query one of the SQLite 3 tables from Delphi. (My databse is world and I ahve a table called City in it). My code is:

procedure TForm1.Button1Click(Sender: TObject);
begin
// Set the path of your database file.
  // Replace "full_path_to_your_database_file" with the absolute path
  // to your SQLite database file.
  SQLConnection1.Params.Add('World.db3');
  try
    // Establish the connection.
    SQLConnection1.Connected := true;
    Button1.Enabled := true;
    Memo1.Text := 'Connection established!';
  except
    on E: EDatabaseError do
      ShowMessage('Exception raised with message' + E.Message);
  end;
end;

procedure TForm1.ShowSelectResults(results: TDataSet);
var
  names: TStringList;
  i: Integer;
  currentField: TField;
  currentLine: string;
begin
  if not results.IsEmpty then
  begin
    results.First;
    names := TStringList.Create;
    results.GetFieldNames(names);
    while not results.Eof do
    begin
      currentLine := '';
      for i := 0 to names.Count - 1 do
      begin
        currentField := results.FieldByName(names[i]);
        currentLine := currentLine + ' ' + currentField.AsString;
      end;
      memo1.Lines.Add(currentLine);
      results.Next;
    end;
  end;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  results: TDataSet;
  query: String;
begin
  Memo1.Clear;
  // A random query
  query := 'SELECT * FROM City;';

  try
  // Execute the query on the database.
    SQLConnection1.Execute(query, nil, results);
  except
    on E: Exception do
      Memo1.Text := 'Exception raised with message: ' + E.Message;
  end;
  // Show the results of the query in a TMemo control.
  ShowSelectResults(results);
end;

When I compile and execute this code, it is getting connected to the database; but throws this error. "Exception raised with message: no such table: City" I have spent hours and hours to figure out why I am hitting on this error. Tried out numerous versions of this code too. Doesn't seem to work either. Any help in this regard is highly appreciated.

jimsweb
  • 1,082
  • 2
  • 17
  • 37

1 Answers1

3

When SQLite cannot find a database file, it is happy to create and open a new one.
You should always use an absolute path for your database file.

Furthermore, you forgot to include the parameter name of the connection parameter:

SQLConnection1.Params.Add('Database=' + ExtractFilePath(ParamStr(0)) + 'World.db3');
CL.
  • 173,858
  • 17
  • 217
  • 259
  • I have crosschecked it. World.db3 stays in the same directory. – jimsweb Jan 10 '13 at 13:24
  • 2
    is _"the same directory"_ the _current directory_ of your application at runtime? – jachguate Jan 10 '13 at 13:37
  • yes the "same directory" and "current directory" are same. :) – jimsweb Jan 10 '13 at 13:58
  • It is: Rad studio\projects\Win32\debug this is the where I have my exe too. – jimsweb Jan 10 '13 at 14:05
  • 1
    As the path to the database contains spaces it might help to surround it with double quotes when you add it to the SQLConnection Params. IE `'Database="' + ExtractFilePath(ParamStr(0)) + 'World.db3"'` – Marjan Venema Jan 10 '13 at 18:17
  • 1
    @MarjanVenema won't it then create another database file, where it is split by space ? And topicstarter told there are no alien database files... Anyway when in doubt if the correct file was open, the answer is always the same: run SysInternals Process Monitor and set its filters to track file I/O of your applications and get list of actual operations when connecting to database. PS. Using DBXpress for embedded databases like SQLite and Firebird Embedded/Interbase ToGo seems quite strange choice to me... – Arioch 'The Jan 11 '13 at 06:39
  • 1
    @Arioch'The: That is what I would expect and what would explain the table not being found. And Process Monitor is indeed your friend! – Marjan Venema Jan 11 '13 at 17:26
  • 1
    @MarjanVenema There may be possibility that his database designer ide uses wrong file. And i hope "Rad studio\projects\Win32\debug " is in Documents, not in Program Files – Arioch 'The Jan 12 '13 at 19:45