Page 1 of 2
Error using blob with Unidac
Posted: Thu 27 Sep 2012 08:32
by 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.
Re: Error using blob with Unidac
Posted: Tue 02 Oct 2012 11:57
by 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.
Re: Error using blob with Unidac
Posted: Tue 02 Oct 2012 15:45
by 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:
Regards,
Gaby23.
Re: Error using blob with Unidac
Posted: Wed 03 Oct 2012 09:54
by 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.
Re: Error using blob with Unidac
Posted: Thu 04 Oct 2012 08:13
by Gaby23
Hello,
Thank you for the research.
I'm sending to you the full project with the database and XML files.
Regards,
Gaby23
Re: Error using blob with Unidac
Posted: Fri 05 Oct 2012 12:05
by 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.
Re: Error using blob with Unidac
Posted: Fri 05 Oct 2012 12:30
by 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
Re: Error using blob with Unidac
Posted: Fri 05 Oct 2012 12:46
by 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
Re: Error using blob with Unidac
Posted: Mon 08 Oct 2012 11:45
by 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.
Re: Error using blob with Unidac
Posted: Mon 08 Oct 2012 13:52
by Gaby23
Hello,
Thank you very much !!
The workaround "AsMemo" works fine with UniDAC 3.70.017 and UniDAC 4.5.9.
Regards,
Gaby23
Re: Error using blob with Unidac
Posted: Mon 08 Oct 2012 14:01
by AndreyZ
Feel free to contact us if you have any other questions about UniDAC.
Re: Error using blob with Unidac
Posted: Wed 12 Dec 2012 11:22
by 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
Re: Error using blob with Unidac
Posted: Tue 18 Dec 2012 10:49
by Gaby23
Any idea ?
Sincerely,
Gaby23
Re: Error using blob with Unidac
Posted: Tue 18 Dec 2012 10:55
by 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.
Re: Error using blob with Unidac
Posted: Tue 18 Dec 2012 16:39
by 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