Access Violation on ExecSql (Sqlite3Connection)

[juancruz.ojeda]

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][+][-]

1. object DataModule1: TDataModule1
2.   OnCreate = DataModuleCreate
3.   OldCreateOrder = False
4.   Height = 294
5.   HorizontalOffset = 1064
6.   VerticalOffset = 480
7.   Width = 420
8.   PPI = 120
9.   object SQLite3Connection1: TSQLite3Connection
10.     Connected = False
11.     LoginPrompt = False
12.     DatabaseName = 'D:\InformeMedico\Archivos temporales\Maxity Proyect\DB\Maxity.db'
13.     KeepConnection = False
14.     Transaction = SQLTransaction1
15.     AlwaysUseBigint = False
16.     Left = 80
17.     Top = 30
18.   end
19.   object SQLQuery1: TSQLQuery
20.     IndexName = 'DEFAULT_ORDER'
21.     MaxIndexesCount = 4
22.     FieldDefs = <    
23.       item
24.         Name = 'idarchivo'
25.         DataType = ftInteger
26.         Precision = -1
27.       end    
28.       item
29.         Name = 'nombrearchivo'
30.         DataType = ftString
31.         Precision = -1
32.         Size = 50
33.       end    
34.       item
35.         Name = 'fechacreacion'
36.         DataType = ftDateTime
37.         Precision = -1
38.       end    
39.       item
40.         Name = 'ultimamodificacion'
41.         DataType = ftDateTime
42.         Precision = -1
43.       end    
44.       item
45.         Name = 'estado'
46.         DataType = ftString
47.         Precision = -1
48.         Size = 35
49.       end    
50.       item
51.         Name = 'descripcion'
52.         DataType = ftMemo
53.         Precision = -1
54.       end>
55.     Database = SQLite3Connection1
56.     Transaction = SQLTransaction1
57.     SQL.Strings = (
58.       'select * from archivos'
59.     )
60.     InsertSQL.Strings = (
61.       ''
62.     )
63.     Params = <>
64.     Macros = <>
65.     Left = 256
66.     Top = 30
67.     object SQLQuery1idarchivo: TLongintField
68.       FieldKind = fkData
69.       FieldName = 'idarchivo'
70.       Index = 0
71.       LookupCache = False
72.       ProviderFlags = [pfInUpdate, pfInWhere]
73.       ReadOnly = False
74.       Required = False
75.     end
76.     object SQLQuery1nombrearchivo: TStringField
77.       FieldKind = fkData
78.       FieldName = 'nombrearchivo'
79.       Index = 1
80.       LookupCache = False
81.       ProviderFlags = [pfInUpdate, pfInWhere]
82.       ReadOnly = False
83.       Required = False
84.       Size = 50
85.     end
86.     object SQLQuery1fechacreacion: TDateTimeField
87.       FieldKind = fkData
88.       FieldName = 'fechacreacion'
89.       Index = 2
90.       LookupCache = False
91.       ProviderFlags = [pfInUpdate, pfInWhere]
92.       ReadOnly = False
93.       Required = False
94.     end
95.     object SQLQuery1ultimamodificacion: TDateTimeField
96.       FieldKind = fkData
97.       FieldName = 'ultimamodificacion'
98.       Index = 3
99.       LookupCache = False
100.       ProviderFlags = [pfInUpdate, pfInWhere]
101.       ReadOnly = False
102.       Required = False
103.     end
104.     object SQLQuery1estado: TStringField
105.       FieldKind = fkData
106.       FieldName = 'estado'
107.       Index = 4
108.       LookupCache = False
109.       ProviderFlags = [pfInUpdate, pfInWhere]
110.       ReadOnly = False
111.       Required = False
112.       Size = 35
113.     end
114.     object SQLQuery1descripcion: TMemoField
115.       FieldKind = fkData
116.       FieldName = 'descripcion'
117.       Index = 5
118.       LookupCache = False
119.       ProviderFlags = [pfInUpdate, pfInWhere]
120.       ReadOnly = False
121.       Required = False
122.       BlobType = ftMemo
123.       Transliterate = False
124.     end
125.   end
126.   object SQLTransaction1: TSQLTransaction
127.     Active = False
128.     Database = SQLite3Connection1
129.     Left = 119
130.     Top = 125
131.   end
132. end
133.  

Then on the DataModule1.Create I have this:

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

1. procedure TDataModule1.DataModuleCreate(Sender: TObject);
2. begin
3.   SQLite3Connection1.Connected := True;
4. end;  

And here's a method I implemented to to the Insert:

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

1. procedure TForm1.InsertPath(APath: string);
2. var
3.   lSqlQuery: TSQLQuery;
4. begin
5.   lSqlQuery := TSQLQuery.Create(nil);
6.   try
7.     lSqlQuery.DataBase := DataModule1.SQLite3Connection1;
8.     lSqlQuery.SQLTransaction := DataModule1.SQLTransaction1;
9.     lSqlQuery.Sql.Text :=
10.         'INSERT INTO Archivos (nombrearchivo, fechacreacion, estado) VALUES (:path, Now(), ''Registrado'')';
11.     lSqlQuery.ParamByName('path').Text:='hola';
12.     lSqlQuery.ExecSQL;  // <------ Here I get the access violation.
13.     DataModule1.SQLTransaction1.Commit;
14.     DataModule1.SQLQuery1.Active:= False;
15.   finally
16.     lSqlQuery.Free;
17.   end;
18. end;

The error message doesn't say too much:

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

1. El proyecto monitoreo2 ha lanzado una excepción 'External: ACCESS VIOLATION' con el mensaje:
2. Access violation executing address $0000000000000000.

Any hint?

[rvk]

Welcome to the forum.

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

1.     lSqlQuery.ParamByName('path').Text:='hola';

Shouldn't that be

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

1. lSqlQuery.ParamByName('path').AsString := 'hola';

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

1.     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]

Are you sure, you're in the right forum?

Here's my .dfm

btw: What's this supposed to do?

'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][+][-]

1.  'INSERT INTO Archivos (nombrearchivo, fechacreacion, estado) VALUES (:path, DateTime(''now'',''localtime''), ''Registrado'')';

Or better: Use a Parameter for the DateTime

[juancruz.ojeda]

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]

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.

Remember: DateTime() without any modifier returns UTC!!

[rvk]

And SQLite also supports CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP.

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