Error when passing parameters to query (ODBC Access .mdb)

[ironphil]

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

1. Query.SQL.Text:= 'UPDATE Table1 SET Memo1 = :pMemo WHERE Id = :pId';
2. Query.Params.ParamByName('pMemo').AsString:= Copy(Memo1.Lines.Text, 1, 255);
3. 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][+][-]

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

1. Id:= 1;
2. 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]

Found this:

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

1.     Query.SQL.Text:= 'UPDATE Table1 SET Memo1 = :pMemo WHERE Id = :pId';
2.     Query.Params.ParamByName('pMemo').DataType:=ftMemo;  // THIS ONE!!
3.     Query.Params.ParamByName('pMemo').AsString:= Copy(Memo1.Lines.Text, 1, 255);
4.     Query.Params.ParamByName('pId').AsInteger:= 1;

[Khrys]

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:

Note: The Large Number data type is only available if you have an Office 365 subscription.

[ironphil]

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

1. Query.Params.ParamByName('pMemo').AsMemo:= Copy(Memo1.Lines.Text, 1, 255);