Error using blob with Unidac

[Gaby23]

Hi,

I have to send a XML file to a stored procedure of SQL Server (i tried 2005 and 2008 R2).
I'm using BSD 2006 and Unidac 3.70.017 (I tried the lastest Unidac in trial version too).

When i send a tiny file (<8k) it work fine, but with a bigger file i have this error:

OLE DB error occured. Code 80040E07h.

The TUniConnection initialisation:

Code: Select all

FServerCnx.ProviderName:=FDBType;
FServerCnx.SpecificOptions.Values['Direct']:= 'True';
FServerCnx.LoginPrompt :=False;
FServerCnx.Connected := False;
FServerCnx.SpecificOptions.Values['ThreadSafety']:= 'True';
FServerCnx.SpecificOptions.Values['ConnectionTimeout'] := '3000';  //3s
FServerCnx.Server:=FIPAdress;
FServerCnx.Username:=FUser;
FServerCnx.Password:=FPassword;
FServerCnx.Database:=FSchema;
FServerCnx.Port:=StrToInt(FPort);

The file read and sql execution:

Code: Select all

AssignFile(afile, 'C:\TEST_XML\TEST.XML');
Reset(afile);
SetLength(buffer, FileSize(afile));
For i := 1 to FileSize(afile) do
   Read(afile, buffer[i - 1]);
CloseFile(afile);
SetString(AnsiStr, PAnsiChar(@buffer[0]), Length(buffer));


FDBQuery.SQL.Clear;
FDBQuery.SQL.add('EXEC dbo.PROC_UPDATE_XML :xml_datas;');
FDBQuery.parambyname('xml_datas').AsBlob:= AnsiStr;
FDBQuery.parambyname('xml_datas').paramtype:=ptInput;
FDBQuery.ExecSQL;

Can you help me ?

Regards,
Gaby23.

[AlexP]

hello,

Please provide the scripts for creating the PROC_UPDATE_XML procedure and the DB objects used in this procedure, in order that we can reproduce this problem.

[Gaby23]

Hello,

Thanks for the reply.

The script of the stored procedure is:

Code: Select all

USE [Test_Bdd]
GO
/****** Objet :  StoredProcedure [dbo].[PROC_UPDATE_XML]    Date de génération du script : 10/02/2012 17:38:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PROC_UPDATE_XML] 
	-- Add the parameters for the stored procedure here
	@XML_DATAS XML
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE dbo.TEST SET XML_DATAS = @XML_DATAS WHERE REFERENCE = 'FRANCE'
END

The table column:

Code: Select all

[XML_DATAS] [xml] NULL

Regards,
Gaby23.

[AlexP]

Hello,

We have checked your sample on Delphi7 and 2006, UniDAC 4.5.9, SQL Server 2005 and 2008, and, when calling the procedure, the error you had specified doesn't occur even on heavy XML files (1Mb and heavier).
Please send the full project and the source XML file to alexp*devart*com, in order that we try to reproduce the error once more on your real sample.

[Gaby23]

Hello,

Thank you for the research.
I'm sending to you the full project with the database and XML files.

Regards,
Gaby23

[AlexP]

hello,

Thank you, I've received your letter, however, all the XML data is loaded to your DB with no errors.
Please try to load large files directly using the SQL Server methods in Management Studio, having executed the following code:

DECLARE @xml XML
SELECT @xml = CONVERT(XML, BulkColumn, 2)
FROM OPENROWSET(BULK 'E:\TEMP\MESVSR17.XML', SINGLE_BLOB) AS x
EXEC PROC_VSR_UPDATE_PNX_XML @xml


where E:\TEMP\MESVSR17.XML is a local path on the server to your XML file.

[Gaby23]

Hello,

Your code works fine in SQL management studio, no problem with large files.
Do you have any idea about the probleme with the source code? The developpement machine is under Windows XP.

I has created a new project, the problem is the same but the error message is:
Operand type clash: image is incompatible with xml

Regards,
Gaby23

[Gaby23]

Hello,

I has tested the application on another machine (Windows 7), and it works with large file !!

I don't know why it don't work into the developpement environement (Windows XP).
SQL Management Studio is not installed into the developpement environnement.

Any idea ?

Thanks.

Regards,
Gaby23

[AndreyZ]

This problem is caused by the OLEDB provider. To solve the problem, you can use one of the following ways:

• use the AsMemo property instead of AsBlob. Here is an example:

  Code: Select all

  FDBQuery.ParamByName('xml_datas').AsMemo := AnsiStr;

• use the SQL Native Client provider instead of OLEDB. For this, you should install the SQL Native Client provider on your development computer with Windows XP.
  If SQL Native Client is installed, UniDAC automatically uses it. Otherwise, OLEDB is used.

[Gaby23]

Hello,

Thank you very much !!

The workaround "AsMemo" works fine with UniDAC 3.70.017 and UniDAC 4.5.9.

Regards,
Gaby23

[AndreyZ]

Feel free to contact us if you have any other questions about UniDAC.

[Gaby23]

Hello,

The workaround "AsMemo" works well whith SQL Server but not whith Oracle.
I tried with UniDAC 3.70.017 and UniDAC 4.5.10 and Oracle 11.2.0.1.0.

When i send a tiny file (<32k) it work fine, but with a bigger file i have an error.
I tried "AsBlob" and "AsString", same error.

Project is in direct mode, no native client installed.

The Oracle stored procedure wait for an Clob parameter.

Any idea ?

Thanks.
Gaby23

[Gaby23]

Any idea ?

Sincerely,
Gaby23

[AlexP]

Hello,

Please provide the error text or screenshot you get when attempting to load files >32k, the stored procedure text, and the Delphi code where the parameters are set and the procedure is called.

[Gaby23]

Hello,

Thank you for the research.
I sent to you the test project with the XML files, the stored procedure and table.

Regards,
Gaby23