Access Violation on ExecSql (Sqlite3Connection)
lazarus / 16-Databases

----------------------------------------------------------------------
juancruz.ojeda · 2022-05-30 13:21

Hi, I'm trying to insert some data into an Sqlite3 database and get an
Access Violation when calling ExecSQL.

Here's my .dfm

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i object
DataModule1: TDataModule1  OnCreate = DataModuleCreate  OldCreateOrder
= False  Height = 294  HorizontalOffset = 1064  VerticalOffset = 480 
Width = 420  PPI = 120  object SQLite3Connection1: TSQLite3Connection 
  Connected = False    LoginPrompt = False    DatabaseName =
'D:\InformeMedico\Archivos temporales\Maxity Proyect\DB\Maxity.db'   
KeepConnection = False    Transaction = SQLTransaction1   
AlwaysUseBigint = False    Left = 80    Top = 30  end  object
SQLQuery1: TSQLQuery    IndexName = 'DEFAULT_ORDER'    MaxIndexesCount
= 4    FieldDefs = <          item        Name = 'idarchivo'       
DataType = ftInteger        Precision = -1      end          item    
  Name = 'nombrearchivo'        DataType = ftString        Precision =
-1        Size = 50      end          item        Name =
'fechacreacion'        DataType = ftDateTime        Precision = -1   
 end          item        Name = 'ultimamodificacion'        DataType
= ftDateTime        Precision = -1      end          item        Name
= 'estado'        DataType = ftString        Precision = -1       
Size = 35      end          item        Name = 'descripcion'       
DataType = ftMemo        Precision = -1      end>    Database =
SQLite3Connection1    Transaction = SQLTransaction1    SQL.Strings =
(      'select * from archivos'    )    InsertSQL.Strings = (      '' 
  )    Params = <>    Macros = <>    Left = 256    Top = 30    object
SQLQuery1idarchivo: TLongintField      FieldKind = fkData     
FieldName = 'idarchivo'      Index = 0      LookupCache = False     
ProviderFlags = [pfInUpdate, pfInWhere]      ReadOnly = False     
Required = False    end    object SQLQuery1nombrearchivo:
TStringField      FieldKind = fkData      FieldName = 'nombrearchivo' 
    Index = 1      LookupCache = False      ProviderFlags =
[pfInUpdate, pfInWhere]      ReadOnly = False      Required = False  
  Size = 50    end    object SQLQuery1fechacreacion: TDateTimeField  
  FieldKind = fkData      FieldName = 'fechacreacion'      Index = 2 
   LookupCache = False      ProviderFlags = [pfInUpdate, pfInWhere]  
  ReadOnly = False      Required = False    end    object
SQLQuery1ultimamodificacion: TDateTimeField      FieldKind = fkData  
  FieldName = 'ultimamodificacion'      Index = 3      LookupCache =
False      ProviderFlags = [pfInUpdate, pfInWhere]      ReadOnly =
False      Required = False    end    object SQLQuery1estado:
TStringField      FieldKind = fkData      FieldName = 'estado'     
Index = 4      LookupCache = False      ProviderFlags = [pfInUpdate,
pfInWhere]      ReadOnly = False      Required = False      Size = 35 
  end    object SQLQuery1descripcion: TMemoField      FieldKind =
fkData      FieldName = 'descripcion'      Index = 5      LookupCache
= False      ProviderFlags = [pfInUpdate, pfInWhere]      ReadOnly =
False      Required = False      BlobType = ftMemo      Transliterate
= False    end  end  object SQLTransaction1: TSQLTransaction    Active
= False    Database = SQLite3Connection1    Left = 119    Top = 125 
endend 
Then on the DataModule1.Create I have this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TDataModule1.DataModuleCreate(Sender: TObject);begin 
SQLite3Connection1.Connected := True;end; 
And here's a method I implemented to to the Insert:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.InsertPath(APath: string);var  lSqlQuery: TSQLQuery;begin 
lSqlQuery := TSQLQuery.Create(nil);  try    lSqlQuery.DataBase :=
DataModule1.SQLite3Connection1;    lSqlQuery.SQLTransaction :=
DataModule1.SQLTransaction1;    lSqlQuery.Sql.Text :=        'INSERT
INTO Archivos (nombrearchivo, fechacreacion, estado) VALUES (:path,
Now(), ''Registrado'')';   
lSqlQuery.ParamByName('path').Text:='hola';    lSqlQuery.ExecSQL;  //
<------ Here I get the access violation.   
DataModule1.SQLTransaction1.Commit;    DataModule1.SQLQuery1.Active:=
False;  finally    lSqlQuery.Free;  end;end;

The error message doesn't say too much:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i El proyecto
monitoreo2 ha lanzado una excepción 'External: ACCESS VIOLATION' con
el mensaje:Access violation executing address $0000000000000000.
Any hint?

----------------------------------------------------------------------
rvk · 2022-05-30 13:44

Welcome to the forum.

Quote from: juancruz.ojeda on May 30, 2022, 03:21:55 pmCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
lSqlQuery.ParamByName('path').Text:='hola'; Shouldn't that be
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
lSqlQuery.ParamByName('path').AsString := 'hola';
Quote from: juancruz.ojeda on May 30, 2022, 03:21:55 pmCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
DataModule1.SQLQuery1.Active:= False; You can remove that one.
Active := false or Close is only for when a query is opened.
ExecSQL just executes it and there isn't a returndataset to open or
close.

----------------------------------------------------------------------
Zvoni · 2022-05-30 14:18

Are you sure, you're in the right forum?
QuoteHere's my .dfm
btw: What's this supposed to do?
Quote'INSERT INTO Archivos (nombrearchivo, fechacreacion, estado)
VALUES (:path, Now(), ''Registrado'')';There is no "Now()"-Function in
SQLite-SQL
It's either
"SELECT Date()" if you only need Date (Acc. to UTC!!!)
Or
"SELECT DateTime()" if you need Date with Time (acc. to UTC!!)
If you need localtime you have to use a localtime-modifier
see here: https://www.sqlite.org/lang_datefunc.html

Your INSERT would look like this (with localtime-modifier)
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  'INSERT
INTO Archivos (nombrearchivo, fechacreacion, estado) VALUES (:path,
DateTime(''now'',''localtime''), ''Registrado'')';Or better: Use a
Parameter for the DateTime

----------------------------------------------------------------------
juancruz.ojeda · 2022-05-30 16:30

Thank you, replacing "now()" for DateTime() appears to work okay.
What generated confusion was mainly the "access violation" message
instead of a SQLite error message.

----------------------------------------------------------------------
Zvoni · 2022-05-30 18:18

Quote from: juancruz.ojeda on May 30, 2022, 06:30:22 pmThank you,
replacing "now()" for DateTime() appears to work okay.
What generated confusion was mainly the "access violation" message
instead of a SQLite error message.
Remember: DateTime() without any modifier returns UTC!!

----------------------------------------------------------------------
rvk · 2022-05-30 18:21

And SQLite also supports CURRENT_DATE, CURRENT_TIME, and
CURRENT_TIMESTAMP.

And datetime('now', 'localtime') will convert now to localtime.