odbc: fix long string parameter bindings
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.