mORMot Open Source

Mo0211

Member

Registered: 2018-12-30

Posts: 24

Access Violations getting empty Strings from sqlite

Hi Guys,

i have the following issue getting data from a sqlite Table:

CREATE TABLE Test(
      testColumn TEXT DEFAULT NULL);

INSERT INTO Test VALUES ('')

element.str_list.CommaText := fQuery.FieldByName('testColumn ').AsString;

This Code written and compiled with fpc version 3.3.1 on a 64bit linux distro takes a long time to execute and even after few executions in a row it results in a access violation.

Is there something wrong in my code or is there a problem getting empty strings from database?

if i replace the emptry string in the sql command with NULL (dbnull) everything works as expected.

Thanks for your feedback and feel free to ask if my questions is not clear.

Moe

Edit:

some more maybe needed information:

i load the dll dynamically
i use tquery for my sql queries
i use this class for the connection: TSQLDBSQLite3ConnectionProperties

Last edited by Mo0211 (2018-12-30 22:09:18)

Mo0211

Member

Registered: 2018-12-30

Posts: 24

Re: Access Violations getting empty Strings from sqlite

Hi,

few days but no response, so i tried to make it a little easier for you.
i created a programm to test the problem in a fast way.
You only need to copy mormot Files to mORMot-Folder and sqlite3 file to the directory.

Please execute this program several times.
In some cases it needs long time to finish the select and also in some cases it ends up i a access violation.

I hope this helps to find the problem.

Thanks for your support!

program SQLITE_ISSUE;
{$APPTYPE CONSOLE}

{$IFDEF FPC}
	{$MODE Delphi}
{$ENDIF}

// fpc -MDelphi -O3 -FumORMot/ sqltest.pas

uses
    {$IFDEF MSWINDOWS} Windows, {$ENDIF}
    {$IFNDEF MSWINDOWS}cmem,{$ENDIF} SysUtils, Classes, math, SyncObjs, SynDBSQLite3, SynDB, SynSQLite3;

Var
  C : TSQLDBSQLite3ConnectionProperties = nil;
  Q: TQuery;
begin
	sqlite3 := TSQLite3LibraryDynamic.Create({$IFDEF MSWINDOWS}SQLITE_LIBRARY_DEFAULT_NAME{$ELSE}'./libsqlite3.so'{$ENDIF});
	C := TSQLDBSQLite3ConnectionProperties.Create('testdb.db', '', '', '');
	C.MainSQLite3DB.LockingMode := lmExclusive;
        C.MainSQLite3DB.WALMode := True;
	C.MainSQLite3DB.Execute('CREATE TABLE IF NOT EXISTS Test (testColumn TEXT NOT NULL DEFAULT unknown);');
	
	Q := TQuery.Create(C.ThreadSafeConnection);
	try
		Q.SQL.Text := 'INSERT INTO Test (testColumn) VALUES (:value);';
		Q.ParamByName('value').AsString := ''; // empty string
		try
			Q.ExecSQL;
		except on E: Exception do
			Writeln('Error: '+ E.Message);
		end;
	finally
	  Q.Free;
	end;
Writeln('test4!');
	try
		Q := TQuery.Create(C.ThreadSafeConnection);
		try
			Q.SQL.Text := 'SELECT testColumn FROM Test';
			try
				Q.Open;
				While not Q.EOF do
				  begin
				  writeln(Q.FieldByName('testColumn').AsString);
				  Q.Next
				  end;
				Q.Close;
			except on E: Exception do
				Writeln(E.Message);
			end;
		finally
		  Q.Free;
		end;
	finally
	C.Free;
	sqlite3.Free;
	writeln('done!');
	end;
end.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Access Violations getting empty Strings from sqlite

Please don't post code directly in the forum.

See the forum rules!

I will look into it.

Mo0211

Member

Registered: 2018-12-30

Posts: 24

Re: Access Violations getting empty Strings from sqlite

Sorry for posting this peace of code.

I will not do it again, but any way thanks for checking!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Access Violations getting empty Strings from sqlite

I deleted both those lines:

sqlite3 := TSQLite3LibraryDynamic.Create({$IFDEF MSWINDOWS}SQLITE_LIBRARY_DEFAULT_NAME{$ELSE}'./libsqlite3.so'{$ENDIF});
....
sqlite3.Free;

Then I use the proper units and defines:

{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER

// fpc -MDelphi -O3 -FumORMot/ sqltest.pas

uses
    {$I SynDprUses.inc} // includes FastMM4 (Delphi 7) or cthreads (FPC-Linux)
    SysUtils, Classes,
    SynCommons, SynDBSQLite3, SynDB, SynSQLite3, SynSQLite3Static;

No problem under Delphi and Windows.
Only with FPC under Linux, some trouble with WideString variant process.
I guess there is a bug in there...

Then I fixed https://synopse.info/fossil/info/0b695586a1
And now I have no problem with your code.

Mo0211

Member

Registered: 2018-12-30

Posts: 24

Re: Access Violations getting empty Strings from sqlite

thanks ab!

it is working now