Prepared Query using WKB with size over 8000 fails data truncated

[Time-X]

I have an Insert SQL statement:

Code: Select all

INSERT INTO large_shape (id, shape)
VALUES (:id, geography::STGeomFromWKB(:shape, 4236))

I can insert WKB with size over 8000 into the Database just fine when the Query is unprepared.

Code: Select all

Query := TMSQuery.Create(nil);

try
   Query.Connection := cntMain;
   Query.Options.DescribeParams := True;
   Query.SQL.Text := 'INSERT INTO large_shape (id, shape) ' +
      'VALUES (:id, geography::STGeomFromWKB(:shape, 4236))';

   try
      Query.ParamByName('id').Value := GetNextId();
      Query.ParamByName('shape').Value := ConvertToArray(WKB_LARGE);
      Query.ExecSQL();
   except
   end;
finally
   FreeAndNil(Query);
end;

This works with any size of WKB.

If however I prepare the Query:

Code: Select all

Query := TMSQuery.Create(nil);

try
   Query.Connection := cntMain;
   Query.Options.DescribeParams := True;
   Query.SQL.Text := 'INSERT INTO large_shape (id, shape) ' +
      'VALUES (:id, geography::STGeomFromWKB(:shape, 4236))';
   Query.Prepare();

   try
      Query.ParamByName('id').Value := GetNextId();
      Query.ParamByName('shape').Value := ConvertToArray(WKB_LARGE);
      Query.ExecSQL();
   except
   end;
finally
   FreeAndNil(Query);
end;

I get the follow message when using WKB with size over 8000.

Error occurred: The statement has been terminated.
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24209: Unexpected end of input. Check that the input data is complete and has not been truncated.
System.FormatException:
at Microsoft.SqlServer.Types.WellKnownBinaryReader.ReadBytes(Int32 n, ByteOrder byteOrder)
at Microsoft.SqlServer.Types.WellKnownBinaryReader.ReadNextPoint(ByteOrder byteOrder, Boolean readZ, Boolean readM)
at Microsoft.SqlServer.Types.WellKnownBinaryReader.ReadLineStringPoints(ByteOrder byteOrder, UInt32 cPoints, Boolean readZ, Boolean readM)
at Microsoft.SqlServer.Types.WellKnownBinaryReader.ParseWkbLineStringWithoutHeader(ByteOrder byteOrder, Boolean readZ, Boolean readM)
at Microsoft.SqlServer.Types.WellKnownBinaryReader.ParseWkb(OpenGisType type)
at Microsoft.SqlServer.Types.WellKnownBinaryReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromBinary(OpenGisType type, SqlBytes wkbGeography, Int32 srid)
.

Parameter[1] :shape - data truncated (Status = 4h).

How do I fix this?

[azyk]

The reason for such behavior is that SQL Native Client provider returns the ftBytes type and size 8000 for varbinary(max) parameters of a prepared query. So in your sample, only the first 8000 bytes are passed to the parameter for the prepared query, and the mentioned error occurs on query execution. We can't affect this behavior.

To solve the issue, you can set Query.Options.DescribeParams to False before calling the Query.Prepare() method, and set the TMSParam.DataType property of the shape parameter to ftBlob.

Or use the standard OLEDB provider for connection. For this, before establishing connection, set the TMSConnection.Options.Provider property to prSQL.