(SOLVED) SQLite3 No data in grid problem.

[JLWest]

Trying to learn to use SQL3.

Have created a small program using SQL3 database, SQLConnector, SQLTransaction, SQLQuery, DataSource and DBGrid.

The Database has 3 records with 4 fields.

The program compiles and runs but no data shows in the Grid.
 
Have no idea how to fix this. need help. Thanks
 

[Soner]

You must put your sql-query-command in SQLQuery component and connect the components among themselves.
Look at the examples  in folder lazarus\examples\database\ or put your example here.
Also tutorials are very good.

[JLWest]

Ok Here is my program attached. AS far as the tutorials go There are very few about SQl3 and Lazarus and they arn't very good.

[TRon]

AS far as the tutorials go There are very few about SQl3 and Lazarus and they arn't very good.

You shouldn't  explicitly be looking at sqlite3 tutorials, rather SQLDB related tutorials.

They also work for SQLite3, unless a specific underlying database feature is being used (the tutorials try to avoid that, but sometimes do mention some DB-specific related issues). The basics for working with databases and database related components is the same no matter what underlying database is being used (with the exception of a few specific ones that are customised, but those are not mentioned in the tutorials that user Soner linked to).

What is specific with regards to SQLite3 however are the SQL statements themselves. The differ between even so many (released) versions of SQL and are sometimes vendor specific. Therefor always try to verify your SQL statement related issues with the SQLite website's documentation, see https://sqlite.org/docs.html

I'm not going to state the wiki tutorials are plenty and good, but they were able to do the job for me. In case they don't for you, then you could perhaps consider adding some (specific) comments on that so that things could be improved.

[Soner]

I corrected the errors in your example and upload it.
1) you forgot the sql command like:
SQLQuery.SQL:='select * from flight';
2) you forgot to connect SQLQuery with DataSource
3) Give full path of the database to DBConnection.DatabaseName

Maybe you will see in DBGRid same text "(MEMO)", it isn't error. SQLite is not real database, it handles all strings as memo-field. Look at this thread.
For ZEOS-Components you must put "Undefined_Varchar_AsString_Length=255" in DBConnection.Params, but it doesn't worked here.

Ok Here is my program attached. AS far as the tutorials go There are very few about SQl3 and Lazarus and they arn't very good.

Only DBConnection.Connectortype changes, everthing else stays same.

You should read database wiki.

Edit:
You must change DBConnection.DatabaseName name in formular-designer, i set it to my folder. But in runtime-mode it set everytime right folder, see tform1.oncreate.

[JLWest]

Thank you. I Try it.

I have read and watched a few pf the tutorials. Managed to create a database, load it with data.

I'm now following Tweaking4All , Getting stated with SQLite3. However, it's dated 10/09/2013 which means it may be out of date.

I'll download you correcction and see if I Can get a little futher.

https://www.tweaking4all.com/software-development/lazarus-development/lazarus-pascal-getting-started-with-sqlite/#InstallationordistributionofSQLitewithyourLazarusapplication

[TRon]

I'm now following Tweaking4All , Getting stated with SQLite3. However, it's dated 10/09/2013 which means it may be out of date.

It is a bit outdated and minimalistic , but should still work.

In case you are new to databases in general then you really should take your time and read a complete database tutorial. There are so many different database formats, component, etc. that you can't learn all this in say 5 minutes to an hour 

On a personal note, I dislike just throwing around a bunch components on a form (or datamodule) and connect the dots between them, rather do it manually. That way it is possible to see much quicker what goes wrong where exactly. e.g. a missing query you do not see unless specifically selecting the query component and looking at its properties. If I did not add it manually in code then... well... it's obvious 

Whatever you do, just don't try to squeeze it in on a last moments notice into your project, rather take your time and read some tutorials on how database components interact with each other. It is a rather steep learning process.

[JLWest]

@Soner

Downloaded your code. I think the zip is corrupt. It hangs my Laurasia. So I haven't been able to try it.

 

[TRon]

I think the zip is corrupt.

There seem to be nothing wrong with the zip-file.

Is your 'hang' caused by extracting the zip-file or rather by loading the project into Lazarus ?

fwiw: User Soner seem to be using Lazarus 2.0.11 and perhaps forgot (or is unable) to save with compatibility mode ? (e.g. the lpi files are incompatible between newer and older Lazarus versions/revisions).

[JLWest]

@TRon

Just brand new with databases and have a meger understanding, well I guess a basic understanding of databases. Having trouble connecting SQL and Lazarus and FPC. Doing it all in code is probably the way to go in a project, however this is a small demo with one table and three records, not a project.

What really surprise  me most in all of these tutorials is the poor quality.

I think there should be a simple instruction set on how to:

Download and install SQLite3.
Downloading and installing DBmanager.
Creating a simple database with one table.
Importing a text or CVS file.
Creating a new project to connect to the database and displaying the data in a DBGrid.

A do it in as few as words as possible.

I think something like this would cut the learning curve on SQL and Lazarus by 75%.
 

[JLWest]

loading the project into Lazarus

My editor goes to full screen and hangs there. Have to use the task manager to get out.

[TRon]

loading the project into Lazarus

My editor goes to full screen and hangs there. Have to use the task manager to get out.

So it is indeed related to incompatible Lazarus project files.

Soner was so kind to mention each step that you forgot. I would suggest to do these steps manually in your original project.

The only code that he added was:

Code: Pascal  [Select][+][-]

1. procedure TForm1.FormCreate(Sender: TObject);
2. begin
3.   DBConnection.DatabaseName:=ExtractFilePath(Application.ExeName)+'Flights.db';
4.   DBConnection.Connected:=true;
5.   SQLQuery1.Active:=true;
6. end;
7.  
8. procedure TForm1.FormDestroy(Sender: TObject);
9. begin
10.   SQLQuery1.Active:=true;
11.   DBConnection.Connected:=false;
12. end;
13.  

Just let know if you are able to follow Soner's steps or not. I could attach a working version, but you should learn how to do these steps manually because you would need to do so each time you create a project using a database. So perhaps might be able to get into the flow of things this way ?

[TRon]

I think something like this would cut the learning curve on SQL and Lazarus by 75%.

And that is exactly what this tutorial is doing, https://wiki.freepascal.org/SQLdb_Tutorial1, albeit it is more general but does mention what to change in case using sqlite.

It starts from step 1, which components to use, and how to connect them. There is a part 0 (https://wiki.freepascal.org/SQLdb_Tutorial0) which mention some specific with regards to the used data and there is a Howto which mentions the specifics with regards to the the different underlying database-systems (https://wiki.freepascal.org/SqlDBHowto) such as required dll's etc.

There is a reason that the tutorials are a huge read... (as the topic of databases is a rather complex one). That is why I mentioned to not try this when you only have a few minutes to an hour to spare. It really requires some careful reading and understanding. I pretty much doubt if it could be written in such a way that it could be a shorter read.

So, you might be perfectly right in (the list of) your 'complaints' but afaik it is all part of the wiki. Hence why I asked for commenting on specifics as things could perhaps be improved ?

[JLWest]

Thanks

I deleted the demo and I'm starting over.

If I can't get it to work may ask for a working example.

Something that confuse me is installation of SQLlite3.

One demo said the SQLite.DLL should be in your project directory and C:\Windows\System

It's the only place I have seen this. Anyway I copied it to both places.

[TRon]

If I can't get it to work may ask for a working example.

That is perfectly ok.

Something that confuse me is installation of SQLlite3.

One demo said the SQLite.DLL should be in your project directory and C:\Windows\System

It's the only place I have seen this. Anyway I copied it to both places.

Windows.... and it s quirks 

Yes, both places is fine(*).

In an ideal world a user or end-user should only have to install sqlite onto his/her system and the dll's would be installed at the correct location. And that should be enough. However, using different bitness (32 vs 64) and installing different versions/revisions of SQLite makes things much more complicated then they really should.

The main concern for your development is making sure you use the correct 32/64 bit version (depending on your target for which you compile your project) and in case you are distributing your work, to make sure the end-user installs the correct version onto his/her system. As the latter proofs to be rather difficult, most developers choose to distribute the correct sqlite dll with their application, usually located in the application executable's directory (or instruct their end-user(s) to do so).

edit: (*) Some people tend to forget that placing the dll in your projects directory is not enough because Lazarus itself needs to have access to the dll in case you are working with the object inspector placing database components _and_ 'activate' them.