(SOLVED) SQLite3 No data in grid problem.

[Soner]

I made mistake in example:

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

1. procedure TForm1.FormDestroy(Sender: TObject);
2. begin
3.   SQLQuery1.Active:=false;  // this should false
4.   DBConnection.Connected:=false;
5. end;
6.  

There was nothing wrong with zip, now I saved it with Lazarus 1.8.4.
When you cannot see some files then go [menu]>[project]>[project inspector] and click on some file that you want see.
When you cannot see some form then go [menu]>[window]> [center lost window] and select any window that you want to see.

When you open some project and lazarus shows error something like "property not existent", then click ingore and save form again, because some property does not more exist. I am using modified LCL, especially DBGrid.

[Soner]

When you have Windows 64 bit then put:
 32-Bit sqlite3.dll in C:\Windows\SysWOW64 folder,
 and 64-bit sqlite3.dll in C:\Windows\C:\Windows\System32 folder.

Also you can put the rigt version(32 or 64bit) in your application and lazarus folder.

[TRon]

When you have Windows 64 bit then put:
 32-Bit sqlite3.dll in C:\Windows\SysWOW64 folder,
 and 64-bit sqlite3.dll in C:\Windows\C:\Windows\System32 folder.

.. and for more detailed information, see also https://www.howtogeek.com/326509/whats-the-difference-between-the-system32-and-syswow64-folders-in-windows/

[TRon]

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.

I have taken the liberty of attaching JLWest's project that do most things manually, instead of relying on the object inspector (actually it does rely on it for adding some basic events) and solving the (MEMO) issue (which I found somewhere here on the forums). Instructions/comments are provided in the main form unit.

However my approach of connecting OnGetText is a very naive one (most certainly wrong for most (other) situations) and should probably be solved by defining/customising the grid columns and/or datasource fields manually. I have no idea how that works exactly with SQLite.

[JLWest]

@Tron
Thank you

I'll take a look

[JLWest]

@TRon

Compiles but when I press Button1 I get an error:

'Cannot Load SQLite library' SQLite3.DLL  it can't find SQLDLL Check your instlation;

TheSQLite3.dll is int the directory along with Flights.db.

I don't have a SQlConection to confighre so it must be a line of code ot looking for,
 

[TRon]

I don't have a SQlConection to confighre so it must be a line of code ot looking for,

I would have to look into that. My sqlite shared library is installed globally and didn't cause such an issue for me.

[JLWest]

I Wonder. I'm using 32bit SQlite for sure. My IDE I think May be 64Bit: How can I confirm that.

https://forum.lazarus.freepascal.org/index.php/topic,49974.0.html

[TRon]

I was trying to figure out something else first :-)

Ok, so unit sqlite3conn uses (global) variable SQLiteLibraryName to locate/name the (in case for windows) dll.

However, the use of that variable is deprecated, and we should really use (global variable) sqlite3dyn.SQLiteDefaultLibrary (meaning unit sqlite3dyn). By default (again for windows) that uses const Sqlite3Lib to declare the name. For windows that reads: "sqlite3.dll"

So, that is the first order of business.. e.g to make sure the name matches 

However, before tampering with that variable directly, it is stated (https://www.freepascal.org/docs-html/fcl/sqldb/universalconnectors.html) that:

From this mechanism it follows that before a particular connection type can be used, its definition must be present in the list of connector types. This means that the unit of the connection type (ibconnection, pqconnection etc.) must be included in the uses clause of the program file: if it is not included, the connection type will not be registered, and it will not be available for use in the universal connector.

afaik, that part I have covered in my comments/instructions, e.g. that you should add unit "sqlite3conn" to your uses clause.

then it reads:

The universal connector only exposes the properties common to all connection types (the ones in TSQLConnection). It does not expose properties for all the properties available in specific TSQLConnection descendents. This means that if connection-specific options must be used, they must be included in the Params property of the universal connector in the form Name=Value. When the actual connection instance is created, the connection-specific properties will be set from the specified parameters.

... and I seem unable to locate any evidence of the existence of any code that provides the name of the library using the parameters property 

Now, to come back to your question

I Wonder. I'm using 32bit SQlite for sure. My IDE I think May be 64Bit: How can I confirm that.

Lazarus/help/about or Lazarus/view/IDE internals/about FPC (don't forget about the second tab)

But, nothing of that matters. You can have a 16, 32, 64 , 128, or 256 bit version of Lazarus installed  but it will produce a 32-bit application as long as you selected that in your project options.
Lazarus/project/project options, then from the left list select Config and target, and you can see the options at the right: Target OS, Target CPU, Target processor.

That should read win32, i386 and default respectively.

Don't forget to save your changes before (re)compiling.


edit: and there is one other possibility is able to cause failure, even though you use the correct bitness of the dll, namely the dependencies for that dll. see also https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q11 :

(11) Why do I get a DllNotFoundException (for "sqlite3.dll" or "SQLite.Interop.dll") when trying to run my application?

Either the named dynamic link library (DLL) cannot be located or it cannot be loaded due to missing dependencies. Make sure the named dynamic link library is located in the application directory or a directory along the system PATH and try again. Also, be sure the necessary Visual C++ runtime redistributable has been installed unless you are using a dynamic link library that was built statically linked to it.

And also with regards to the dependencies that means that those redistributable should match the bitness of your produced executable.

[JLWest]

"Lazarus/project/project options, then from the left list select Config and target, and you can see the options at the right: Target OS, Target CPU, Target processor."

Says Win32 Gui Application and all the rest are set as default.

[JLWest]

@Soner And TRon

I have I think 32Bit Target set in IDE, 32Bit DB browser that created the DB and SQLiite3.dll in C:\Windows\System32 and the project directory.

 

[TRon]

I have I think 32Bit Target set in IDE,

If you previous post is indeed correct then you have created a 32-bit application.

32Bit DB browser that created the DB

The data can be created with any application, even with an application that runs on linux/macos or otherwise. The data itself is platform agnostic.

and SQLiite3.dll in C:\Windows\System32 and the project directory.

And what bitness does your OS have ? ( https://www.howtogeek.com/howto/21726/how-do-i-know-if-im-running-32-bit-or-64-bit-windows-answers/ )

What about the dependencies ? (I added that part later to my previous post).

[JLWest]

@Soner

Download and compiled your latest. Still get the error can't load Sqlite.dll. It is in the project directory and C:\windows\system32
What about trying a different Database. Is there one that's easier on Windows.

I made mistake in example:

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

1. procedure TForm1.FormDestroy(Sender: TObject);
2. begin
3.   SQLQuery1.Active:=false;  // this should false
4.   DBConnection.Connected:=false;
5. end;
6.  

There was nothing wrong with zip, now I saved it with Lazarus 1.8.4.
When you cannot see some files then go [menu]>[project]>[project inspector] and click on some file that you want see.
When you cannot see some form then go [menu]>[window]> [center lost window] and select any window that you want to see.

When you open some project and lazarus shows error something like "property not existent", then click ingore and save form again, because some property does not more exist. I am using modified LCL, especially DBGrid.

[JLWest]

I have a 64bit operating system, Is that the problem.

 

---------------------
System Information
------------------
      Time of this report: 1/12/2019, 21:53:47
             Machine name: DESKTOP-FRCN4JR
               Machine Id: {9926B69C-099E-4BA8-A06C-F52E5C69E7A0}
         Operating System: Windows 10 Pro 64-bit (10.0, Build 17134) (17134.rs4_release.180410-1804)
                 Language: English (Regional Setting: English)
      System Manufacturer: System manufacturer
             System Model: System Product Name
                     BIOS: BIOS Date: 07/23/17 14:04:11 Ver: 05.0000C (type: BIOS)
                Processor: Intel(R) Core(TM) i7-7700K CPU @ 4.20GHz (8 CPUs), ~4.2GHz
                   Memory: 32768MB RAM
      Available OS Memory: 32702MB RAM
                Page File: 3735MB used, 33829MB available
              Windows Dir: C:\WINDOWS
          DirectX Version: DirectX 12
      DX Setup Parameters: Not found
         User DPI Setting: 120 DPI (125 percent)
       System DPI Setting: 144 DPI (150 percent)
          DWM DPI Scaling: Disabled
                 Miracast: Available, with HDCP
Microsoft Graphics Hybrid: Not Supported
           DxDiag Version: 10.00.17134.0001 64bit Unicode

[TRon]

I have a 64bit operating system, Is that the problem.

In that case, please update your signature to include that.

Also, as Soner already wrote:

When you have Windows 64 bit then put:
 32-Bit sqlite3.dll in C:\Windows\SysWOW64 folder,
 and 64-bit sqlite3.dll in C:\Windows\C:\Windows\System32 folder.

So, in case you are absolutely sure that you have downloaded the 32-bit version of the sqlite dll from the official SQLite.org website, then remove all previous copies (*) because you copied it into the wrong location. It should go into SysWow64 (and not System32). See also the link that I posted as a reaction to Soners post, in case you wish to know why/how.

If that still doesn't work then you have a dependency issue, in which case you should make sure to install the microsoft visual c distribution libraries for 32-bit windows. I have no idea what minimal version that should be though 

(*) really make sure that windows is unable to find any sqlite dll version(s) installed at the wrong location. In case you leave remnants of that then the issue will not be solved and continue to exist.

I still find it odd though that it does not seem to favour the dll alongside your exe and instead continues to try loading from the system directory, unless it really is a dependency issue.