Parametrized Access SQL Query
lazarus / 16-Databases

----------------------------------------------------------------------
konikula · 2026-01-12 18:21

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMyThread.Execute;var  cmd: TSQLQuery;  i: Integer;  attr: DWord; 
sql, sattr, md5code, accfp, filePath: String;  lastProgress: Integer; 
trans: TSQLTransaction;  c, m, a: FileTime;  csh, csl, msh, msl, ash,
asl, ats: String;begin  connAccess := TODBCConnection.Create(pForm); 
connAccess.Driver := RegistryGetAccessDriver(); 
connAccess.Params.Add('DBQ=' + dbpath); 
connAccess.Params.Add('DSN=test');  connAccess.Params.Add('Locale
Identifier=1031');  connAccess.Params.Add('ExtendedAnsiSQL=1'); 
connAccess.Params.Add('CHARSET=ansi');  connAccess.Connected := True; 
connAccess.KeepConnection := True;   trans :=
TSQLTransaction.Create(pForm);  trans.DataBase := connAccess; 
trans.Active := True;   cmd := TSQLQuery.Create(pForm);  cmd.DataBase
:= connAccess;  cmd.Transaction := trans;  cmd.UsePrimaryKeyAsKey :=
False;   // vytvoření tabulky – čistý Access SQL  cmd.SQL.Text :=   
'CREATE TABLE fHash('+    'id COUNTER NOT NULL,'+    'path String,'+ 
 'hash String,'+    'attr TEXT(20),'+    'dcreatedh TEXT(20),
dcreatedl TEXT(20),'+    'dmodifiedh TEXT(20), dmodifiedl TEXT(20),'+ 
  'daccessh TEXT(20), daccessl TEXT(20))';  cmd.ExecSQL; 
trans.Commit;  trans.Active := True;  // můžeš optional udělat
trans.Commit + trans.Active := True, ale pro Access to není nutné,
pokud zůstaneš v jedné session   self.progress := 1; 
Synchronize(@Showstatus);   cmd.SQL.Text :=    'INSERT INTO fHash(path
,hash,attr,dcreatedh,dcreatedl,dmodifiedh,dmodifiedl,daccessh,daccessl
) '+    'VALUES (?,?,?,?,?,?,?,?,?)';         cmd.Params.Clear;      
 cmd.Params.CreateParam(ftString,  'p1', ptInput);     
 cmd.Params.CreateParam(ftString,  'p2', ptInput);     
 cmd.Params.CreateParam(ftString,  'p3', ptInput).Size := 20;      //
attr       cmd.Params.CreateParam(ftString,  'p4', ptInput).Size :=
20;       cmd.Params.CreateParam(ftString,  'p5', ptInput).Size :=
20;       cmd.Params.CreateParam(ftString,  'p6', ptInput).Size :=
20;       cmd.Params.CreateParam(ftString,  'p7', ptInput).Size :=
20;       cmd.Params.CreateParam(ftString,  'p8', ptInput).Size :=
20;       cmd.Params.CreateParam(ftString,  'p9', ptInput).Size :=
20;      for i := 0 to work.Count - 1 do  begin    filePath :=
work[i];    md5code := MurmurHash3File(filePath);   
GetFileDates2(filePath, c, m, a, attr);         if Length(filePath) >
255 then  ShowMessage('PATH TOO LONG: ' +
IntToStr(Length(filePath)));     str(c.dwHighDateTime, csh);
str(c.dwLowDateTime, csl);    str(m.dwHighDateTime, msh);
str(m.dwLowDateTime, msl);    str(a.dwHighDateTime, ash);
str(a.dwLowDateTime, asl);    str(attr, ats);    
cmd.Params[0].AsString := filePath;    cmd.Params[1].AsString :=
md5code;    cmd.Params[2].AsString := ats;     cmd.Params[3].AsString
:= csh;    cmd.Params[4].AsString := csl;    cmd.Params[5].AsString :=
msh;    cmd.Params[6].AsString := msl;    cmd.Params[7].AsString :=
ash;    cmd.Params[8].AsString := asl;          cmd.Prepare;   
cmd.ExecSQL;     lastProgress := self.progress;    self.progress :=
(1000 * i) div (work.Count - 1);    if lastProgress <> self.progress
then      Synchronize(@Showstatus);  end;   cmd.Free;  trans.Commit; 
trans.Free;  connAccess.Free;  Synchronize(@Finished);end;

----------------------------------------------------------------------
Xenno · 2026-01-13 05:53

No need Params.CreateParam.

If using "?", directly assign their values by index:
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  
cmd.Params[0].AsString := fileParh;  ...
It would be easier to name the parameters and assign their values by
names:
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  
cmd.SQL.Text :=    'INSERT INTO fHash(path, hash, attr, dcreatedh,
dcreatedl, dmodifiedh, dmodifiedl, daccessh, daccessl) '+    'VALUES
(:path, :hash, :attr, :dcreatedh, :dcreatedl, :dmodifiedh,
:dmodifiedl, :daccessh, :daccessl)';   ...  
cmd.Params.ParamByName('path').AsString := filePath;
Additionally, better use try-finally.

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

----------------------------------------------------------------------
H₂SO₄ · 2026-01-13 06:12

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
interface type  TAccessConnection = class(TODBCConnection)  protected 
  procedure LoadBlobIntoBuffer(FieldDef: TFieldDef; FieldBuf:
PBufBlobField;                                 Cursor: TSQLCursor;
Trans: TSQLTransaction); override;  end; implementation //
+-------------------------------------+// |          TAccessConnection
         |// +-------------------------------------+ ///
TODBCConnection descendant providing patches for the hopeless mess
known as the Microsoft Access driver. /// Fixes an issue whereby the
length of `long text` (aka SQL_LONGVARCHAR) fields is inflated by a
factor of two.../// ...when the contained data is longer than 2 KiB
(e.g. possible length values: 2047, 2048, 4098, 4100, 4102, etc.).///
There is no rational explanation for this because we're talking about
the $#!?^&! MS Access driver.procedure
TAccessConnection.LoadBlobIntoBuffer(FieldDef: TFieldDef; FieldBuf:
PBufBlobField;                                               Cursor:
TSQLCursor; Trans: TSQLTransaction);begin   inherited
LoadBlobIntoBuffer(FieldDef, FieldBuf, Cursor, Trans);   with
FieldBuf^.BlobBuffer^ do if (FieldDef.DataType = ftMemo) and (Size >
$800) then begin    Size := Size div 2;    ReAllocMem(Buffer, Size); 
end;end;

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

----------------------------------------------------------------------
Zvoni · 2026-01-13 08:45

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 · 2026-02-03 20:28

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