Help to find database error

[Columbo 1]

I changed the code in the connection procedure as follows:

 

Procedure TfrmDatabaseTest.btnConnectClick(Sender:TObject);
  begin
  try
      if not FileExists(‘dat2.sqlite’) then   // I added this code to see if it was finding the database.
         begin
                memData.Text := ‘Cannot connect to database!’;
         end
      Else
         Begin
                //Establish connection
                FDConnection1.Connected := true
                btnExecute.Enabled := true;
                memData.Text := ‘Connection Established!’;
         end;
end;

 

When I run the program now I get the message ‘Cannot connect to database!’ so obviously there is a problem finding and connecting to the database which is why I was getting the 'no such table' error.  The path to the database is correct.  I even tried placing the dat2.sqlite file into the same folder as the program and changing the path but still can't connect.

 

Edited December 5, 2023 by Columbo

[Gary 18]

Very helpful to have the tutorial link.

You can set everything up in the designer to make sure it works, then try doing it all in code. Set the Connection's Connected property to true, you will get errors if everything is not set up. You may be missing the connection's driver name, notice the drop down. Select SQLite or in code:

FDConnection1.DriverName := 'SQLite';

Add the Query to the FDQuery1' SQL property in the editor and set it's Active property to true, you'll get errors if it's not setup right.

The Database path is wrong, you are setting the Connection's "Database" Parameter which is a file path.  So it's "Database=" then path. You can set it 2 ways:

FDConnection1.Params.Add('Database=' + Your path here);
FDConnetion1.Params.Database := Your Path here;

You can use "ShowMessage" for a little logging.

After setting the Database path:

ShowMessage(FDConnection1.Params.Database);

Test after opening the connection:

 if FDConnection1.Connected then
      ShowMessage('Connected');

After setting the SQL:

ShowMessage(FDQuery1.SQL.Text);

 

[Columbo 1]

I changed the code in the connection procedure as follows:

 

Procedure TfrmDatabaseTest.btnConnectClick(Sender:TObject);
  begin

   try

      if not FileExists(‘dat2.sqlite’) then

         begin

                memData.Text := ‘Cannot connect to database!’;

         end

    Else

         Begin

                //Establish connection

                FDConnection1.Connected := true

                btnExecute.Enabled := true;

                memData.Text := ‘Connection Established!’;

         end;

end;

[Serge_G 87]

12 hours ago, Columbo said:

The path to the database is correct.  I even tried placing the dat2.sqlite file into the same folder as the program

Are you sure of that? Program in debug mode is in a xxx\win32\debug directory where xxx is where are your project, unit and dfm path

[Columbo 1]

Hi Gary,

 

I had the driver set up in the properties as SQLite.

I set the 'Connected' property to true.

I set the path to:

FDConnection1.Params.Database := 'D:\Delphi_11_Community\MyProjects\Data\dat2.sqlite';

After the path I added:

ShowMessage(FDConnection1.Params.Database);

When program is run the message I get the path D:\Delphi_11_Community\MyProjects\Data\dat2.sqlite.

I also left in the code:

ShowMessage(FDQuery1.SQL.Text);

When program is run this gives me the message: query := 'SELECT * FROM DAT2';

In the FDQuery1 Properties I opened the SQL editor and added:

query := 'SELECT * FROM DAT2';

 

It still doesn't connect.

 

[Columbo 1]

37 minutes ago, Serge_G said:

\win32\debug directory

If I go into the Win32 directory I have 2 files. FDtest_p.exe and FDTest_u.dcu. If I run the FDtest_p.exe I get the same ShowMessage as I mentioned in my previous post to Gary,  'D:\Delphi_11_Community\MyProjects\Data\dat2.sqlite '.

[JonRobertson 70]

15 minutes ago, Columbo said:

3 minutes ago, Columbo said:

If I run the FDtest_p.exe I get the same ShowMessage as I mentioned in my previous post to Gary,  'D:\Delphi_11_Community\MyProjects\Data\dat2.sqlite '.

Sure, because that is the path that you hardcoded to FDConnection1.Params.Database:

 

15 minutes ago, Columbo said:

FDConnection1.Params.Database := 'D:\Delphi_11_Community\MyProjects\Data\dat2.sqlite';

Try this instead:

FDConnection1.Params.Database := ExtractFilePath(Application.ExeName) + 'dat2.sqlite';

If ExtractFilePath is an undefined identifier, add SysUtils to the uses statement below the implementation keyword.

[Columbo 1]

25 minutes ago, JonRobertson said:

Sure, because that is the path that you hardcoded to FDConnection1.Params.Database:

 

Try this instead:

FDConnection1.Params.Database := ExtractFilePath(Application.ExeName) + 'dat2.sqlite';

If ExtractFilePath is an undefined identifier, add SysUtils to the uses statement below the implementation keyword.

I commented out the original path and replaced it with:

FDConnection.Params.Database := ExtractFilePath(Application.ExeName) + 'dat2.sqlite';

SysUtils was already in the uses statement.  Still no connection. 

I am almost ready to give up on this thing and try something else.

 

 

Edited December 5, 2023 by Columbo

[weirdo12 19]

Can you copy and paste or attach all of your .pas source code here once again.

Edited December 5, 2023 by weirdo12

[JonRobertson 70]

His project was building to Project\Win32\Debug and dat2.sqlite was in the root Project folder.

[Serge_G 87]

12 hours ago, Columbo said:

  'D:\Delphi_11_Community\MyProjects\Data\dat2.sqlite '.

so, use this one not only dat2.sqlite.

you can also use '..\..\Data\dat2.sqlite'

(second  ..\   go up one level win32 first ..\ to project) 

Quote

If I go into the Win32 directory I have 2 files. FDtest_p.exe

if you want to use only dat2.sqlite database have to be in this directory

Edited December 6, 2023 by Serge_G

[Gary 18]

Don't give up!!

Start over with fresh project and break it into smaller parts:

1) Connect to Database

2) Open Query

3) Process your data

I Created a small test app to complete the first 2 steps and test them in less than 1 Minute with the Demo data, you can too!

 

1) With a fresh project drop a FDConnection on the form.

In Object inspector Set DriverName to SQLite.

Set Name to conMain (Easier to type latter)

Open Params look for Database, use ... to navigate to your Database.

Now set Connected to true. If all is well it will connect.

Set Connected to False

Add a button to the form

Set name to btnConnect and Caption to Connect.

Double click button and add code:

  conMain.Connected := True;
  if conMain.Connected then
  begin
    ShowMessage('Connected');
   // btnOpen.Enabled := True;
  end;

Run and press Connect. If all is well you'll get the message.

Step 1 Done

Add a FDQuery to form

Set Connection to conMain (Designer will probably already have done it for you)

set Name to qryDat2

Enter your SQL statement into the SQL Object inspector

Set Connection back to Connected <-Important

Right click Query, you'll get a dialog box with your SQL in it. Press Execute. If all is well you'll see your data.

Set Connected back to False.

Add another Button

Set enabled to false.

Name to btnOpen, Caption to Open.

Double click and add code:

  qryDat2.Open;
  if qryDat2.Active then
  begin
    ShowMessage('Query Active');
   // btnProcess.Enabled := True;
  end;

Uncomment btnOpen.Enabled := True;

Run and try it!!

Add third button

Set Name btnProcess

Enabled to False;

Caption Process

Double click button and add your processing code here

Uncomment btnProcess.Enabled := True;

 

You can delete each setting individually and try adding them in code 1 at a time to get a better understanding.

Edited December 6, 2023 by Gary

[Columbo 1]

Thanks for all of the help.  I took a day off to clear my head and I'll try again starting from scratch and see what happens.

 

[Columbo 1]

Hi Gary,

 

I started a new project as you suggested called FDTest2.  Following your instructions I added a FDConnection and set the DriverName to SQLite, and set the name to conMain.  You then said:

Quote

Open Params look for Database, use ... to navigate to your Database.

When I select ... it opens the String List Editor but I am not sure what to put in here.  Currently it says "DriverID=SQLite"  .  Do I leave that in there and add a a path to the sqlite database on the next line or do I replace the "DriverID-SQLite" with the path to the database or is there something else that goes in here?

 

Thanks.

 

Edited December 7, 2023 by Columbo

[Lajos Juhász 292]

Double click on the FDConnection to open the FireDAC Connection Editor.

[Gary 18]

When you look at the Object inspector there are 4 properties to set, see the image below. 

Line 4 DriverName has a dropdown list set it to SQLite

Line 9 LoginPrompt if you do not have passwords set this to false and no login prompt will show

Line 10 Name change to something better than default I like conMain or something more descriptive of your DB

Expand the Params Property and the third line is the Database path. the ... will open a File Open dialog.

Now set Line 1 Connected to true, if you get no errors and it stays checked your good.

 

Even easier is Lajos suggestion, Right click FDConnection select Connection Editor, you can set all parameters except LoginPrompt there and it even has a Test button.

 

This is the great thing about Delphi. The sample image attached I created in about 30 Seconds without a single line of code. I know you don't need the grid but it is a way to easily confirm that you have a valid DataSet. From your posts I suspect there are simple typo's and a mixture of trying to set properties in code and at design time. I think your SQL statement is the culprit, not the actual statement but how you are trying to set it in code, forget about that and just enter it in the FDQuery SQL property. If you want we can walk through setting up your Dataset like the sample and you can concentrate on your processing code.

[Columbo 1]

Thanks Gary for your time and your patience.

I followed your instructions on Step1, (FDConnection1) component.  I called it conMain.  I added the button as per your instructions and when run it I get the message 'Connected!'.

 

Then I added the btnConnect and when I run it I get the 'Connected!' message again.  So far so good...

Then I added the FDQuery component.  I set the name to qryDat2.  I went to SQL in the Inspector, clicked on the ... next to 'Database' menu item and entered:

SELECT * FROM DAT2

Then you said

Quote

Set Connection back to Connected <-Important

I wasn't sure as to where this was but I clicked the dropdown menu for 'Connection' and set 'Connected' to True.  When I right click Query and select Execute I get a dialog that says:  'Cannot execute command returning result sets, Hint use Open method for Select-lite commands.'  So I have probably entered something wrong somewhere and I can't go any further on your instructions until I get this resolved.

 

 

 

Edited December 8, 2023 by Columbo

[Gary 18]

Look at the image of the  Object inspector, it is the first property. You connect to the database by setting the connection to "Connected True". Check or uncheck the box in the Object Inspector.

 

Image's I posted are for the connection, look close, there is no SQL this is the connection, only properties having to do with the connection to your Database. Any SQL will be in the Query component.

 

Right Click the FDQuery and select Query editor, see image. Enter your SQL statement. No quotes or semicolon SELECT * FROM DAT2. Press execute if good you will see your data below and the SQL for the component will be populated, if not your SQL Statement is the problem. Even though you can see the table in the Database it doesn't mean your SQL is valid. I once had a problem when migrating a MySQL server from Windows to a NAS drive. The default on the NAS did not allow capitols in table names and the whole program would not work because all my tables were a mix of lower and uppercase,  SQL was now invalid. If the program you use to create/modify your SQLite database allows you to write queries do so. Make sure it works and then copy it into the FDQuery 

Edited December 8, 2023 by Gary

[Gary 18]

BTW this is all in the designer you don't need to even run the program to get it working

[Columbo 1]

Yes, I did have it set it to Connected : True.  Here is a screenshot of the FDConnection Editor.

 

[Gary 18]

What happens when you press "Test"?

[Columbo 1]

I get a dialog saying 'Connection established successfully'.

[Gary 18]

Yahoo! So no problem connecting. Now on to the Query. Make sure the connection property of the FDQuery Component is set to your connection then right click and select Query editor, enter your SQL statement. See my previous image. Then click Execute. What happens?

[Columbo 1]

Connection property is set to conMain.  When I right click and click on 'Execute' I get that dialog about ' [FireDAC][Phys][SQLite]-310 Cannot execute command returning result sets, Hint use Open method for Select-lite commands. '  Not sure what the -310 means.

 

Edited December 8, 2023 by Columbo

[Gary 18]

Problem in your SELECT statement. Did you enter it without any quotes ? Just SELECT * FROM DAT2 ? Can you get the same query to work in your Database tool?