Skip to content

odbc: fix long string parameter bindings

Ondrej Pokorny requested to merge fpc-ondrej/source:odbc_varcharmaxparam into main

When writing a long string into a nvarchar(max) parameter, ODBC throws this exception:

Exception EODBCException
Could not bind parameter 0.
ODBC error details: LastReturnCode: SQL_ERROR;
Record 1: SqlState: HY104; NativeError: 0;
Message: [Microsoft][ODBC Driver 13 for SQL Server]Invalid precision value;

The problem is the wrong parater type: SQL_WLONGVARCHAR must be used for long strings instead of SQL_WVARCHAR.

Test project:

program ODBCTest;

uses SQLDB, SysUtils, odbcconn;

var
  C: TODBCConnection;
  T: TSQLTransaction;
  Q: TSQLQuery;
  S, L: string;
  I: Integer;
begin
  try
    C := TODBCConnection.Create(nil);
    T := TSQLTransaction.Create(C);
    Q := TSQLQuery.Create(C);
    C.DatabaseName := 'SQLSERVER'; // replace this with your DSN, if you use any
    C.Connected := True;

    T.SQLConnection := C;
    Q.SQLConnection := C;
    Q.Transaction := T;

    Q.SQL.Text := 'CREATE TABLE [dbo].[NVARCHARMAX]([Longtext] [nvarchar](max) NULL, [Shorttext] [nvarchar](127) NULL)';
    Q.ExecSQL;

    Q.SQL.Text := 'INSERT INTO [dbo].[NVARCHARMAX] ([Longtext], [Shorttext]) VALUES (:longtext, :shorttext)';

    // build a short text
    S := '0123456789'+sLineBreak;
    // insert the short text into DB
    Q.Params[0].AsString := S;
    Q.Params[1].AsString := S;
    Q.ExecSQL; // <<< this goes fine

    // build a very long text
    L := S;
    for I := 0 to 20 do
      L := L + L;

    // insert the long text into DB
    Q.Params[0].AsString := L;
    Q.Params[1].AsString := S;
    Q.ExecSQL; // <<< this throws an ODBCException: [Microsoft][ODBC Driver 13 for SQL Server]Invalid precision value

    Q.SQL.Text := 'DROP TABLE [dbo].[NVARCHARMAX]';
    Q.ExecSQL;

    C.Free;
  except
    on E: Exception do
    begin
      Writeln('Exception ', E.ClassName);
      WriteLn(E.Message);
    end;
  end;
  WriteLn('Enter to close');
  ReadLn;
end.

Merge request reports