Error when passing parameters to query (ODBC Access .mdb)
lazarus / 16-Databases

----------------------------------------------------------------------
ironphil · 2025-06-26 00:34

I am accessing a .mdb database and it usually works as intended.
However, recently I noticed that when I try to pass a parameter (as a
string) to a query, I get an error message only when the string is
longer than 127 characters even though the field is defined as a short
text (which should be a maximum of 255 characters). For example, this
does not work when the length of Memo1.Lines.Text is from 128 to 255
characters: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
Query.SQL.Text:= 'UPDATE Table1 SET Memo1 = :pMemo WHERE Id =
:pId';Query.Params.ParamByName('pMemo').AsString:=
Copy(Memo1.Lines.Text, 1,
255);Query.Params.ParamByName('pId').AsInteger:= 1;
I then get this error at Query.ExecSQL (no error when less than 127
characters and the database gets updated as intented): 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 Could not
bind parameter 0. ODBC error details: LastReturnCode: SQL_ERROR;
Record 1: SqlState: HY104; NativeError: 98; Message: [Microsoft][ODBC
Microsoft Access Driver]Invalid precision value;
But avoiding the use of parameters works when Memo1.Lines.Text is more
than 127 characters (and up to 255). So this equivalent code works:
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 Id:=
1;Query.SQL.Text := 'UPDATE Table1 SET Memo1 = ' +
QuotedStr(Copy(Memo1.Lines.Text, 1, 255)) + ' WHERE Id = ' +
IntToStr(Id);
Strangely, I get the same error when I change the field to a long
text, as soon as the length reaches 128 and up.

I was able to quickly reproduce the issue by creating a test database
and a small application, which are attached. Let me know if you have
any idea what is going on or need more info. For now, I will hard code
a 127 characters limit on the Memo control.

----------------------------------------------------------------------
Zvoni · 2025-06-26 06:07

Found 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    
Query.SQL.Text:= 'UPDATE Table1 SET Memo1 = :pMemo WHERE Id = :pId'; 
 Query.Params.ParamByName('pMemo').DataType:=ftMemo;  // THIS ONE!!  
Query.Params.ParamByName('pMemo').AsString:= Copy(Memo1.Lines.Text, 1,
255);    Query.Params.ParamByName('pId').AsInteger:= 1;

----------------------------------------------------------------------
H₂SO₄ · 2025-06-26 08:21

I've also encountered this issue. While I don't know what causes
Microsoft Access to lower the limit from 255 to 127, I do know what
causes  fcl-db  to use the short text type.

Parameter types aren't known until they are assigned, and 
TParam.SetAsString  sets the field type to  ftString.
The problem is that  TODBCConnection  maps  fcl-db's  ftString  to
ODBC's  SQL_VARCHAR  type, which in turn is further limited by
Microsoft Access (or rather the Microsoft JET Engine).
For large text you'll want to use ODBC's  SQL_LONGVARCHAR  type, which
is mapped to  ftMemo  by  fcl-db. This means that you'll have to use 
TParam.AsMemo  instead of  TParam.AsString.

I was initially confused by how similiar  TField  and  TParam  look
when used (e.g.  FieldByName('foo').AsString  and 
ParamByName('bar').AsString), despite the fact that they are quite
different in some ways.
For example,  TField  has no  AsMemo  property;  AsString  just
works.  TParam  however needs to make this distinction, introducing a
subtle pitfall (in my opinion).


Sidenote: TParam.AsLargeInt  is paywalled by Microsoft (!!!), so
you'll have to stick to  TParam.AsInteger  instead:

Quote from: Micro$oftNote: The Large Number data type is only
available if you have an Office 365 subscription.

----------------------------------------------------------------------
ironphil · 2025-06-26 10:32

Thanks for your suggestions. Simply passing the parameter as a Memo
makes it work. It seems I don't need to change the datatype to ftMemo.
Only changing the datatype to ftMemo did not work, I will still the
getting the error message. Changing both also works. Finally, I only
changed 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
Query.Params.ParamByName('pMemo').AsMemo:= Copy(Memo1.Lines.Text, 1,
255);