Prepared Query using WKB with size over 8000 fails data truncated

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Time-X
Posts: 3
Joined: Tue 28 Apr 2015 21:56

Prepared Query using WKB with size over 8000 fails data truncated

Post by Time-X » Wed 02 Mar 2016 02:36

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Prepared Query using WKB with size over 8000 fails data truncated

Post by azyk » Thu 10 Mar 2016 12:24

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.

Post Reply