Parametrized Access SQL Query

[konikula]

Are there some issues with parametrised SQL Queries in Lazarus? I can't insert row in Access table, with String, Integer and Decimal columns. It continues throwing precision errors. I even replaced column types to String, and still precision error

Sample. Not functioning

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

1. procedure TMyThread.Execute;
2. var
3.   cmd: TSQLQuery;
4.   i: Integer;
5.   attr: DWord;
6.   sql, sattr, md5code, accfp, filePath: String;
7.   lastProgress: Integer;
8.   trans: TSQLTransaction;
9.   c, m, a: FileTime;
10.   csh, csl, msh, msl, ash, asl, ats: String;
11. begin
12.   connAccess := TODBCConnection.Create(pForm);
13.   connAccess.Driver := RegistryGetAccessDriver();
14.   connAccess.Params.Add('DBQ=' + dbpath);
15.   connAccess.Params.Add('DSN=test');
16.   connAccess.Params.Add('Locale Identifier=1031');
17.   connAccess.Params.Add('ExtendedAnsiSQL=1');
18.   connAccess.Params.Add('CHARSET=ansi');
19.   connAccess.Connected := True;
20.   connAccess.KeepConnection := True;
21.  
22.   trans := TSQLTransaction.Create(pForm);
23.   trans.DataBase := connAccess;
24.   trans.Active := True;
25.  
26.   cmd := TSQLQuery.Create(pForm);
27.   cmd.DataBase := connAccess;
28.   cmd.Transaction := trans;
29.   cmd.UsePrimaryKeyAsKey := False;
30.  
31.   // vytvoření tabulky – čistý Access SQL
32.   cmd.SQL.Text :=
33.     'CREATE TABLE fHash('+
34.     'id COUNTER NOT NULL,'+
35.     'path String,'+
36.     'hash String,'+
37.     'attr TEXT(20),'+
38.     'dcreatedh TEXT(20), dcreatedl TEXT(20),'+
39.     'dmodifiedh TEXT(20), dmodifiedl TEXT(20),'+
40.     'daccessh TEXT(20), daccessl TEXT(20))';
41.   cmd.ExecSQL;
42.   trans.Commit;
43.   trans.Active := True;
44.   // můžeš optional udělat trans.Commit + trans.Active := True, ale pro Access to není nutné, pokud zůstaneš v jedné session
45.  
46.   self.progress := 1;
47.   Synchronize(@Showstatus);
48.  
49.   cmd.SQL.Text :=
50.     'INSERT INTO fHash(path,hash,attr,dcreatedh,dcreatedl,dmodifiedh,dmodifiedl,daccessh,daccessl) '+
51.     'VALUES (?,?,?,?,?,?,?,?,?)';
52.  
53.  
54.        cmd.Params.Clear;
55.  
56.        cmd.Params.CreateParam(ftString,  'p1', ptInput);
57.        cmd.Params.CreateParam(ftString,  'p2', ptInput);
58.        cmd.Params.CreateParam(ftString,  'p3', ptInput).Size := 20;      // attr
59.        cmd.Params.CreateParam(ftString,  'p4', ptInput).Size := 20;
60.        cmd.Params.CreateParam(ftString,  'p5', ptInput).Size := 20;
61.        cmd.Params.CreateParam(ftString,  'p6', ptInput).Size := 20;
62.        cmd.Params.CreateParam(ftString,  'p7', ptInput).Size := 20;
63.        cmd.Params.CreateParam(ftString,  'p8', ptInput).Size := 20;
64.        cmd.Params.CreateParam(ftString,  'p9', ptInput).Size := 20;
65.  
66.  
67.  
68.  
69.   for i := 0 to work.Count - 1 do
70.   begin
71.     filePath := work[i];
72.     md5code := MurmurHash3File(filePath);
73.     GetFileDates2(filePath, c, m, a, attr);
74.  
75.         if Length(filePath) > 255 then
76.   ShowMessage('PATH TOO LONG: ' + IntToStr(Length(filePath)));
77.  
78.     str(c.dwHighDateTime, csh); str(c.dwLowDateTime, csl);
79.     str(m.dwHighDateTime, msh); str(m.dwLowDateTime, msl);
80.     str(a.dwHighDateTime, ash); str(a.dwLowDateTime, asl);
81.     str(attr, ats);
82.  
83.     cmd.Params[0].AsString := filePath;
84.     cmd.Params[1].AsString := md5code;
85.     cmd.Params[2].AsString := ats;
86.  
87.     cmd.Params[3].AsString := csh;
88.     cmd.Params[4].AsString := csl;
89.     cmd.Params[5].AsString := msh;
90.     cmd.Params[6].AsString := msl;
91.     cmd.Params[7].AsString := ash;
92.     cmd.Params[8].AsString := asl;
93.           cmd.Prepare;
94.     cmd.ExecSQL;
95.  
96.     lastProgress := self.progress;
97.     self.progress := (1000 * i) div (work.Count - 1);
98.     if lastProgress <> self.progress then
99.       Synchronize(@Showstatus);
100.   end;
101.  
102.   cmd.Free;
103.   trans.Commit;
104.   trans.Free;
105.   connAccess.Free;
106.   Synchronize(@Finished);
107. end;

[Xenno]

No need Params.CreateParam.

If using "?", directly assign their values by index:

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

1.   cmd.Params[0].AsString := fileParh;
2.   ...

It would be easier to name the parameters and assign their values by names:

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

1.   cmd.SQL.Text :=
2.     'INSERT INTO fHash(path, hash, attr, dcreatedh, dcreatedl, dmodifiedh, dmodifiedl, daccessh, daccessl) '+
3.     'VALUES (:path, :hash, :attr, :dcreatedh, :dcreatedl, :dmodifiedh, :dmodifiedl, :daccessh, :daccessl)';
4.  
5.   ...
6.  
7.   cmd.Params.ParamByName('path').AsString := filePath;

Additionally, better use try-finally.

More: https://wiki.lazarus.freepascal.org/Working_With_TSQLQuery

[Khrys]

My condolences for having to work with MS Access 🤢🤮

Now, using  TParam.AsString  may cause Access to interpret the parameter's type as  VARCHAR(255),  which is probably what's happening here.  TParam.AsMemo  does not have this problem and is actually usable for attaching long strings to parameters.

TParam.AsLargeInt  is paywalled behind an Office 365 subscription (!!!), so it's best to avoid it.


Also be cautious when retrieving long strings; I'll just share the relevant snippet here:

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

1. interface
2.  
3. type
4.   TAccessConnection = class(TODBCConnection)
5.   protected
6.     procedure LoadBlobIntoBuffer(FieldDef: TFieldDef; FieldBuf: PBufBlobField;
7.                                  Cursor: TSQLCursor; Trans: TSQLTransaction); override;
8.   end;
9.  
10. implementation
11.  
12. // +-------------------------------------+
13. // |          TAccessConnection          |
14. // +-------------------------------------+
15.  
16. /// TODBCConnection descendant providing patches for the hopeless mess known as the Microsoft Access driver.
17.  
18. /// Fixes an issue whereby the length of `long text` (aka SQL_LONGVARCHAR) fields is inflated by a factor of two...
19. /// ...when the contained data is longer than 2 KiB (e.g. possible length values: 2047, 2048, 4098, 4100, 4102, etc.).
20. /// There is no rational explanation for this because we're talking about the $#!?^&! MS Access driver.
21. procedure TAccessConnection.LoadBlobIntoBuffer(FieldDef: TFieldDef; FieldBuf: PBufBlobField;
22.                                                Cursor: TSQLCursor; Trans: TSQLTransaction);
23. begin
24.  
25.   inherited LoadBlobIntoBuffer(FieldDef, FieldBuf, Cursor, Trans);
26.  
27.   with FieldBuf^.BlobBuffer^ do if (FieldDef.DataType = ftMemo) and (Size > $800) then begin
28.     Size := Size div 2;
29.     ReAllocMem(Buffer, Size);
30.   end;
31. end;

Note that this applies to the 2019  ODBCJT32.dll  (32-bit, 10.00.19041.01)  "Microsoft Access Driver (*.mdb)"  ODBC data source.

[Zvoni]

And don't add DBQ and DSN to the Connection-String at the same time
It's usually either/or.

And don't use pForm as owner for Transaction and Query. Use the Connection

[konikula]

I switched my project to SQLite. With Access it was incompatible with multiple platforms and settings. Now I can distribute to more systems, even on linux