Help to find database error

[Gary 17]

You can also try adding a FDTable instead of a FDQuery. Drop it on the form, make sure it's connection property is set to your connection, make sure the connection is set to connected. With the FDTable selected, In the object inspector  look for the TableName property. There will be a dropdown listing all tables in the DataBase. Select your table, then set the FDTable Active property to true. If no errors you are connected

[Columbo 1]

SELECT statement had no quotes or semi colon.  What Databse tool?

[Gary 17]

The tool in your previous post showing the table in the Database. Try the FDTable first

[Gary 17]

Some like to always use a Query, but if your just doing a SELECT *, a table component is way easier. You just set the connection and TableName properties, no need for a SQL Statement at all. 

[Columbo 1]

I put the FDTable component on the form, set the connection property to conMain, Set the Connection to Connected, Set the TableName property dropdown menu to DAT2 and set the Active to True.

Nothing happeded.  How do I test it?  If I right click I don't see an Execute button.

 

Edited December 8, 2023 by Columbo

[Gary 17]

If the Active property is checked as true your good!!!

 

You can try hooking up a DBGrid just to give yourself an extra boost. Drop a TDataSource on the form, set it's DataSet property to your table. Now a DBGrid and set it's DataSource property to the DataSource. If the Table is active you'll see the data

[Columbo 1]

Thank you Gary,  It works! 

 

The FDTable works fine if I want to retrieve all data,  but if I need to pull the data from a specific column of a table row the FDTable would not work so well.  I have a long way to go in learning Delphi and I am not sure yet how to get the data from a specific column in a row.  I am guessing that it would be something like:

 

SELECT hline1 FROM DAT2 WHERE hyear = 1942

But at least now I know that the database dat2.sqlite can be accessed, I now have to figure out why it is not working in the FDQuery.

Thanks again for your patience on this.  VERY MUCH appreciated.

 

Edited December 8, 2023 by Columbo

[Gary 17]

Now use your original code to populate the memo, just use the Table

[Columbo 1]

I'll try it tomorrow.  I don't know where you are but I am in Toronto, Canada and it is 12:36 am and I think I'm going to hit the sack for the night.

Thanks again.

 

[Lajos Juhász 239]

You should read more carefully the error message. You to open the query using the open method of the TFDQuery instead of ExecSQL.

[Stano 140]

Here I dare to write that it is mandatory to use MyQurery.Open and not ExecSQL.

[Gary 17]

@ColumboYour 2 hours ahead I'm in California

 

You definitely need to figure out the SQL. If the tool you use doesn't let you test SQL statements, try DB Browser for SQLite (sqlitebrowser.org) Free/Donate.

Now that you know it works with the table, you can try experimenting in the FDQuery editor. Right click FDQuery select Query Editor, enter your SQL and press Execute. 

The Delphi sample data retrieves the employees table with any of these 3 statements:

SELECT * FROM employees

SELECT * FROM 'employees'

SELECT * FROM 'employees'; 

Can't help but think your table name being all uppercase is the problem. Try putting it in single quotes like above. I don't use SQLite so maybe someone else knows the problem for sure.

 

Cool thing about Delphi is there is always "more than one way to skin a cat". In the Object Inspector for the Table set the Filter property to hyear = '1942', you only need the quotes around 1942 if it's a string, if the field is a number their are not needed, and the Filtered property to true.

[JonRobertson 39]

8 minutes ago, Gary said:

Can't help but think your table name being all uppercase is the problem. Try putting it in single quotes like above. I don't use SQLite so maybe someone else knows the problem for sure.

 

I was able to get his project working on my machine. I don't use SQLite either. So as a test I tried several combinations of character case on the table name and they all worked. Table names (and likely any object name) do not seem to be case sensitive.

 

His project also works with the database connection and query assigned 100% in code, with zero design-time configuration.

[Gary 17]

@JonRobertson That's great. I didn't see where he uploaded his database. Did you create it from his image?

[Columbo 1]

20 hours ago, Lajos Juhász said:

You should read more carefully the error message. You to open the query using the open method of the TFDQuery instead of ExecSQL.

I was following Gary's instructions.  Didn't want to confuse myself with other methods until I was able to do it by the instructions.  I finally got it to work using his instructions.

[Columbo 1]

I have downloaded a few videos on Delphi and on databases and I am going to try something a bit different as a means of learning Delphi.  I notice that a few people have said that they never use SQLite.  I used it because I already had 7 SQLite databases containing a lot of data ranging from the year 1900 to 1997.  I didn't want to have to redo all of that.  What database format is most commonly used for Delphi programming?  I also have the same databases in DBF format from my original program written in CA-Clipper.  Is there a way to convert those to a Delphi usable format?

[Gary 17]

What version of Delphi are you using? I have Pro + Devart Data Access components. Pro edition supports SQLite & Interbase Lite with Firedac out of the box, but not client/server (MySQL, Oracle..), not sure if the community edition supports any Databases. If you are using a trial edition Database support will end.

 

If community edition supports SQLite I wouldn't discount it so fast, especially if you want to do any mobile apps. I'm trying to decide to use it or IBLite that comes with Pro on my next app since I want to support mobile.

 

You can use Devart UniDac (commercial), or Zeos Database components (Open Source) for Database access with the community edition as long as your not making >5K annually from it. Both allow you to connect to many Databases.

 

Firebird is a favorite of many Delphi developers, and has a client/server but also what is considered an embedded version. You just distribute a few files with your exe. This works well for small hobbyist apps. Firebird is actively supported. I used it in my last 2 small apps that will run from a USB drive.

 

Another option is an older Database, "Advantage". SAP discontinued support a few years back at version 12, but the source is still available. It allowed you to use the local (embedded) version with up to 5 users before having to purchase the client/server version. It only works on Windows, but you just deploy 2 dll's minimum. The good thing with it is it has it's own data access components, just like the FD ones you just used. It has it's own GUI for creating and testing your databases. I just installed it on Delphi 12 and it still works just fine.

 

 

 

 

[Columbo 1]

Thanks Gary,  I am currently using Delphi 11 Community on my son's computer with Windows 10 on it.  The computer that my son loan me has Windows 7 Professional  on it and has Delphi 7 on it.  It doesn't have fireDAC of course and I don't know the requirements of FireBird so I don't know if it runs on Windows 7.

I just checked the Ebarcadero web site and it says:

"Delphi CE includes a code editor, powerful debugging tools, built-in access to popular local databases with live data at design time, Bluetooth capabilities, and a visual UI designer with support for pixel perfect, platform-specific styling. "   So it appears to support some databases.  At this point in time I am not concerned with mobile apps.  I first want to learn Delphi before doing anything too serious but most of the stuff that I do use databases.  I'll have to check and see if the Community version supports Interbase Lite.  Is there any way that import or convert either .DBF or SQLite to Interbase?

 

BTW:  You are actually 3 hours behind me in California.

 

Edited December 9, 2023 by Columbo

[Stano 140]

Inporting data from one DB to another has nothing to do with Delphi.
Firebird (FB) runs on all Windows and many other platforms.
I, as a layman, would not go into Interbase Lite. FB is always free for all states. That will not change in the future. I don't use any other DB.
Importing data can be done in many ways. It depends on what you have available.
One option is offered by Delphi itself. Something like BatchMove. It has been discussed here recently. You'll have to look it up. Given knowledge, I don't recommend it at the moment.
I've done importing data from DBF as follows:
I got the data in *.csv format
I opened them in Excel and made the necessary adjustments

• field names
• corrections to the data. E.g. 1500 to 1500, dates, etc.

Using DB Workbench, a commercial product, I performed the import

[Columbo 1]

Thanks Stano,.

Quote

Inporting data from one DB to another has nothing to do with Delphi.

I am aware that importing data from one DB to another has nothing to do with Delphi.  I was asking if there was perhaps a utility out there that may convert .DBF files.  I have heard of Firebird and a companion program called FireRobin but I would need to check it out..

 

Quote

I, as a layman, would not go into Interbase Lite.

Does it have a steep learning curve or something?

 

Thanks again Stano

 

[Gary 17]

I would agree with Stano! However, this means you need to install 3rd party packages.

You will need Data Access components. Zeos is popular. 

You will need to install and learn Firebird.

Firebird and Interbase have the same roots. They were forked from same source some time ago. Embarcadero continues to develop it as commercial software, even if the community edition lets you build with IBLite, you will need to pay a license to use it outside the IDE. Firebird.org continues on the open source version. I believe they just released version 5? 

 

I did some projects with Firebird. They still work, but I personally find it difficult to work with. No doubt it is my lack of experience.

 

I'm sure there is freeware that can at least export your old data to a .csv file, then as Stano said you can use FireDac BatchMove component to import into your Database of choice. I just added this feature to the one and only commercial app I have. It downloads GPS data in .csv format from the company's vehicle tracking service and imports it into a MYSQL Database.

[Columbo 1]

FYI:  I was able to convert all of my SQLite files to csv using SQLite Studio.  It was quick and easy.