none
OLE DB stores complete BLOB but retreives it truncated RRS feed

  • Question

  • Hey,

    I am having a (imao) weird Problem: I am using OleDbConnection for Database operations. The table Looks like this:

    CREATE TABLE [dbo].[ImportFile] (
    	[Id] [numeric] IDENTITY(0,1) NOT NULL PRIMARY KEY,
    	[Name] [nvarchar](255) NOT NULL,
    	[ArchivedOn] [DateTimeOffset],
    	[State] [numeric] NOT NULL
    		CONSTRAINT ImportFile_State_Default DEFAULT 0
    		CONSTRAINT ImportFile_State_ImportStateEnum_Value FOREIGN KEY REFERENCES [dbo].[ImportStateEnum] ( [Value] ),
    	[Content] [varbinary](max) NOT NULL,
    	CONSTRAINT [ImportFile_FileName_ArchivedOn_Unique] UNIQUE CLUSTERED (
    		[Name] ASC,
    		[ArchivedOn] ASC
    	) WITH (
    		PAD_INDEX = OFF,
    		IGNORE_DUP_KEY = OFF
    	)
    )

    The State Column is not of concern here, ist just an "enum" so to speak. I am using Stored Procedures for all operations:

    CREATE PROCEDURE [dbo].[uspAddFileToImport]
    	@Name nvarchar(255),
    	@Content varbinary(max),
        @ResultCode [int] OUTPUT,
        @Message [nvarchar](255) OUTPUT
    AS
    BEGIN
    	SET NOCOUNT ON
    	BEGIN TRY
    		SET @ResultCode = 0;
    		-- Track similar named files
    		DECLARE @SimilarFiles TABLE (
    			[Id] [numeric],
    			[State] [numeric],
    			[Content] [varbinary](max)
    		);
    		-- Get similar files (only not archived ones)
    		INSERT INTO @SimilarFiles SELECT [Id], [State], [Content] FROM [dbo].[ImportFile] WHERE [Name] = @Name AND [ArchivedOn] IS NULL;
    		
    		-- Check similar files content
    		IF ( SELECT COUNT(*) FROM @SimilarFiles ) > 0
    			BEGIN
    				IF ( SELECT COUNT(*) FROM @SimilarFiles WHERE [Content] = @Content ) > 0
    					BEGIN
    						SET @Message = 'Similar file was already added';
    						SET @ResultCode = 2;
    						RETURN 0;
    					END
    				ELSE
    					BEGIN
    						-- Ensure to wait for the already added files to finish 
    						IF ( SELECT COUNT(*) FROM @SimilarFiles WHERE [State] != 0 AND [State] != 1 ) = 0
    							BEGIN
    								SET @Message = 'Similar file was not yet processed';
    								SET @ResultCode = 3;
    								RETURN 0;
    							END
    						
    						DECLARE @now DateTime2;
    						SET @now = SYSDATETIMEOFFSET();
    						UPDATE [dbo].[ImportFile] SET [ArchivedOn] = @now WHERE [Id] IN ( SELECT [Id] FROM @SimilarFiles );
    						SET @Message = 'File was already present but differs, archived old file ' + CONVERT(nvarchar, @now, 127);
    					END
    			END
    		INSERT INTO [ImportFile] ( [Name], [ArchivedOn], [Content] ) VALUES ( @Name, NULL, @Content );
    	END TRY
    	BEGIN CATCH
    		SET @Message = ERROR_MESSAGE();
    		SET @ResultCode = 1;
    		RETURN 1;
    	END CATCH
    	RETURN 0;
    END
    CREATE PROCEDURE [dbo].[uspGetImportFile]
    	@Name [nvarchar](255),
    	@Content [varbinary](max) OUTPUT,
        @ResultCode [int] OUTPUT,
        @Message [nvarchar](255) OUTPUT
    AS
    BEGIN
    	SET NOCOUNT ON
    	BEGIN TRY
    		SET @ResultCode = 0;
    		DECLARE @Contents TABLE (
    			[Content] [varbinary](max)
    		)
    		INSERT INTO @Contents SELECT [Content] FROM [dbo].[ImportFile] WHERE [Name] = @Name AND [ArchivedOn] IS NULL;
    		IF ( SELECT COUNT(*) FROM @Contents ) != 1
    			BEGIN
    				SET @Message = 'Found more than one possible match, contact your Database administrator to resolve the issue';
    				SET @ResultCode = 2;
    				RETURN 0;
    			END
    		SET @Content = ( SELECT [Content] FROM @Contents );
    	END TRY
    	BEGIN CATCH
    		SET @Message = ERROR_MESSAGE();
    		SET @ResultCode = 1;
    		RETURN 1;
    	END CATCH
    	RETURN 0;
    END

    Now when i use the OleDbConnection to fire the upsAddImportFile, the data gets transferred to the database and a quick check confirims, that the data was not truncated:

    SELECT DATALENGTH(Content) FROM ImportFile


    But when i retreive the data with OleDbConnection, the Content OUTPUT Parameter contains only 8k Bytes in the PROGRAM (not in Management Studio, the actual value of the OleDbParameter does not contain the complete Byte array).

    I am at a total loss as how that could be, if the data gets send correctly, how does it come back truncated?!

    The code for retreival:

    public bool GetImportFile(string fileName, out OleDbParameterCollection results)
    {
    	OleDbCommand cmd = new OleDbCommand(USP_GETIMPORTFILE, Connection);
    	cmd.CommandType = CommandType.StoredProcedure;
    	cmd.Parameters.Add(createDbParameter("Name", OleDbType.VarWChar, fileName, ParameterDirection.Input));
    	cmd.Parameters.Add(createDbParameter("Content", OleDbType.VarBinary, null, ParameterDirection.Output));
    	cmd.Parameters.Add(createDbParameter("ResultCode", OleDbType.Integer, -1, ParameterDirection.Output));
    	cmd.Parameters.Add(createDbParameter("Message", OleDbType.VarWChar, string.Empty, ParameterDirection.Output));
    	results = cmd.Parameters;
    
    	return ExecuteStoredProcedure(cmd) == 0;
    }
    
    private int ExecuteStoredProcedure(OleDbCommand cmd)
    {
    	int retVal = -1;
    	try
    	{
    		retVal = cmd.ExecuteNonQuery();
    		retVal = (int)cmd.Parameters["ResultCode"].Value;
    	}
    	catch (Exception ex)
    	{
    		cmd.Parameters["Message"].Value = ex.Message;
    		retVal = -1;
    	}
    	return retVal;
    }


    Of course the database is opened and bla; the Connection string is put together:

    switch (config.DbProvider)
    {
    	case DBWrapper.DBProvider.MSAccess:
    		this.ConnectionString.Provider = "Microsoft.ACE.OLEDB.12.0";
    		this.ConnectionString.Add("Data Source", config.DbDataSource);
    		break;
    	case DBWrapper.DBProvider.MSSql:
    		this.ConnectionString.Provider = "SQLOLEDB";
    		if (!string.IsNullOrEmpty(config.DbDataSource) && config.DbPort.HasValue)
    			this.ConnectionString.Add("Data Source", string.Format("{0},{1}", config.DbDataSource, config.DbPort.Value));
    		else if (!string.IsNullOrEmpty(config.DbDataSource))
    			this.ConnectionString.DataSource = config.DbDataSource;
    
    		this.ConnectionString.Add("Initial Catalog", config.DbInitialCatalog);
    
    		if (string.IsNullOrEmpty(config.DbUser) || string.IsNullOrEmpty(config.DbPassword))
    			this.ConnectionString.Add("Trusted_Connection", "yes");
    		else
    		{
    			this.ConnectionString.Add("User Id", config.DbUser);
    			this.ConnectionString.Add("Password", config.DbPassword);
    		}
    		break;
    	case DBWrapper.DBProvider.Oracle:
    		break;
    }
    Connection.ConnectionString = ConnectionString.ConnectionString;

    ... in case there is something i can do with the Connection string to retreive longer data...

    Hence my question:

    How am i supposed to work with this OleDbConnection properly? The actuial database Provider used is MSSQL! (I cannot change to another Connector btw)


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.


    Tuesday, May 23, 2017 6:28 AM

All replies

  • Try OleDbType.Binary instead of OleDbType.BSTR and check the length of returned byte array.

    Probably OleDbType.BSTR is not good when the data contain null character.

    Tuesday, May 23, 2017 6:52 AM
  • Damn... still something in the code left from trying to make it work...

    My bad, the actual Code ofc contains VarBinary as a type not BSTR, that was just a Workaround test...

    I tried VarBinary and Binary, then BSTR as a last resort


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.

    Tuesday, May 23, 2017 7:42 AM
  • Hi MDeero,

    Thank you for posting here.

    According to your question is more related to ADO, I will move it to  ADO.NET Managed Providers forum for suitable support.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards.

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 24, 2017 6:44 AM