Lazarus

Programming => Databases => Topic started by: Picard on June 26, 2019, 03:58:24 pm

Title: [REQ] Example Lazarus/SQLite Application
Post by: Picard on June 26, 2019, 03:58:24 pm
Hopefully someone has a sample like this that does a "TWO WAY LOOKUP".  First of all I'm having trouble connecting a table in an SQLite database that I made to a Pascal application.  I've looked through this forum, YouTube and Google.  I just cannot get them to connect.  Secondly, I need to do a two way lookup just like it's done in this YouTube video, but I need to do it in Pascal.  This example in the Excel video shows what I'm ultimately trying to do with Lazarus and SQLite.  Please help.

Quote
Excel Magic Trick 1480: VLOOKUP & MATCH Two-Way Lookup Quantity & Product to Get Price
https://www.youtube.com/watch?v=GlVzMrGoknA
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: marcov on June 26, 2019, 04:06:06 pm
I'm sorry, but watching videos in hot weather gives me headaches. How about you just describing what you mean?
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Picard on June 26, 2019, 04:15:21 pm
In my post I describe what I'm trying to do.  I just added a video to help clarify my message. What part of the message in not clear, excluding the video?
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: MacWomble on June 26, 2019, 04:28:36 pm
You did not need such a complicated thing like 'Two-Way-Lookup' in Excel.
Just make your SQL-Query to get the right values.

I think you should learn SQL basics first. A simple Query is almost all you need.

'Select product, price from table where qty > 10 and qty < 20'

You have to learn SQL but it's worth it!

what is the problem with your SQLite-connection. You should post the code you have, so that someone could help you.
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Picard on June 26, 2019, 08:47:36 pm
The first problem is that I've read through this forum, Googled and watched YouTube videos on how to connect a Pascal application to a database and none of the procedures worked in making the connection.  I keep getting errors.  The best way to show you the steps that I took is to make my own video and post a link to it with my explanation of the problem.  I'll have to work on the video and post it when I'm finished.  This will take some time.

With all the discussions of SQLite and Pascal in this forum, I was hoping that someone would already have an example of a portion of their project to share with me.
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: mangakissa on June 27, 2019, 02:50:29 pm
take your advantage. Only download sqlite3-64.dll to connect the database on 64 bits otherwise sqlite3.dll
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Picard on June 28, 2019, 07:54:13 am
Thanks for your sample project, Mangakissa.  I found sqlite3.dll, but I cannot find sqlite3-64.dll.  Where do I place this library file to compile the project?  I'm on 64 bit Windows 10 and I prefer to use all 64 bit components.  Where do I get sqlite3-64.dll?
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Pascal on June 28, 2019, 08:00:31 am
%WINDIR%\system32
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Pascal on June 28, 2019, 08:02:56 am
Download it from here (https://www.sqlite.org/download.html): Precompiled Binaries for Windows
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Picard on June 30, 2019, 03:29:16 am
Thanks Pascal.  The sqlite3.dll is in the %WINDIR%\system32.  If I'm not mistaken, all the 32 bit libraries are there.  So it seems that there is no sqlite3-64.dll, since the content of the file you advised me to download doesn't call it by that name.  It has the same name as the 32 bit version.

Ok.  Aside from that issue, do I need to place a copy of the dll together with my project folders?

Attached is a screen shot of the tables that I have in my database.  I imported the data from CSV files into SQLite.  Do these tables need to be compiled or is importing the CSV files all that I have to do?
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: bee on June 30, 2019, 05:16:58 am
You might want to read this tutorial on how to connect to an sqlite database using sqlDB classes in Free Pascal. It's originally written in Indonesian, but Google Translate could help you read it in any language of your choice.

Example, here's in english (https://translate.google.com/translate?hl=&sl=auto&tl=en&u=https%3A%2F%2Fpaklebah.github.io%2Ffpc-sqldb-dan-sqlite.html). The original text is here (https://paklebah.github.io/fpc-sqldb-dan-sqlite.html).

HTH.
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: lucamar on June 30, 2019, 05:29:43 am
Thanks Pascal.  The sqlite3.dll is in the %WINDIR%\system32.  If I'm not mistaken, all the 32 bit libraries are there.

I may be wrong but in 64 bit Windows, aren't normal, 64bit libs in system32 and the 32 bit ones in wow64 (or something like that)?
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Pascal on June 30, 2019, 06:51:00 am
Thanks Pascal.  The sqlite3.dll is in the %WINDIR%\system32.  If I'm not mistaken, all the 32 bit libraries are there.

I may be wrong but in 64 bit Windows, aren't normal, 64bit libs in system32 and the 32 bit ones in wow64 (or something like that)?

Correct!
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: mangakissa on July 01, 2019, 08:32:35 am
That's why I used TSQLLoadLibrary to find the dll in the place I want ;D
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Picard on July 01, 2019, 09:21:17 am
Thanks lucamar.  I remember now that the "wow64" folder is the correct place.  I saw somewhere that the dll also needs to be in one of the project folders.  Is that correct?

I may be wrong but in 64 bit Windows, aren't normal, 64bit libs in system32 and the 32 bit ones in wow64 (or something like that)?
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Picard on July 01, 2019, 09:22:43 am
Thanks bee.  The translated page looks helpful.  I'll study it too.

You might want to read this tutorial on how to connect to an sqlite database using sqlDB classes in Free Pascal. It's originally written in Indonesian, but Google Translate could help you read it in any language of your choice.

Example, here's in english (https://translate.google.com/translate?hl=&sl=auto&tl=en&u=https%3A%2F%2Fpaklebah.github.io%2Ffpc-sqldb-dan-sqlite.html). The original text is here (https://paklebah.github.io/fpc-sqldb-dan-sqlite.html).

HTH.
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: lucamar on July 01, 2019, 01:46:25 pm
Thanks lucamar.  I remember now that the "wow64" folder is the correct place.  I saw somewhere that the dll also needs to be in one of the project folders.  Is that correct?

Not "also", but "instead" (if you want). In the absence of an explicit path, the DLL is searched for (IIRC) first in the same directory the executable is in, then in the corresponding system folders.

And maybe, if not yet foud, in the directories pointed to in "PATH"? Though I'm not sure of this.

I'm sure Microsoft has this info somewhere in their docs, but bottom-line recommendation is: put extra DLLs with your executable and use system ones from the system folders.

That's it, basically :)
Title: Re: [REQ] Example Lazarus/SQLite Application
Post by: Pascal on July 03, 2019, 03:17:21 pm
Thanks lucamar.  I remember now that the "wow64" folder is the correct place.  I saw somewhere that the dll also needs to be in one of the project folders.  Is that correct?
No. Only if the dll is not available in the default system directory it has to be in the directory of the executable or you use SetDllDirectory for you app.
TinyPortal © 2005-2018