Page 1 of 1

Impossibility of save content of IMAGE field

Posted: Wed 27 Jul 2011 10:41
by yamaco
Hello, I have an IMAGE field in my table, among another fields. If I try to post record where this field is empty, this error appears:

The data types image and image are incompatible in equal to operator.

If this field has a value (contains image), another message is generated:

Invalid value (status=2h)

Practically I cannot save records inte table with IMAGE field type.

I use Delphi 2007 together with MS SQL Server 2008R2.

Does anybody have same problem or know what is the problem ?

Thank you for any idea.

Sincerely,

Karel

Posted: Wed 27 Jul 2011 13:18
by AndreyZ
Hello,

It seems that you are using comparison operator in your query for the IMAGE field. You cannot use comparison operators for the TEXT, NTEXT, and IMAGE fields.

Posted: Wed 27 Jul 2011 16:44
by yamaco
Hello Andrey, thank you for advice but the problem seems to be more complex:

If table has a primary key constraint, I can compare also IMAGE, TEXT a NTEXT and POST works fine.

For example (table EPIPU has a primary key and field OBSAH is NTEXT):

UPDATE EPIPU
SET
CISLO = :CISLO, ZPRACOVATEL = :ZPRACOVATEL, D_ZADANI = :D_ZADANI, D_TERMIN = :D_TERMIN, OBSAH = :OBSAH, POPIS = :POPIS, D_SPLNENI = :D_SPLNENI, TAG = :TAG, POZNAMKA = :POZNAMKA
WHERE
CISLO = :Old_CISLO AND ZPRACOVATEL = :Old_ZPRACOVATEL


If table does not have a primary key and I use comparison of these types of (BLOB) fields, error message appears as is described above.

For example (table PRAVA does not have a primary key and field SIGNATURE is IMAGE):

UPDATE PRAVA
SET
DXFFFF = :DXFFFF, DYFFFF = :DYFFFF, EXA1 = :EXA1, EXA2 = :EXA2, EXA3 = :EXA3, EY = :EY, EZ = :EZ, IDENT1 = :IDENT1, IDENT2 = :IDENT2, IDENT3 = :IDENT3, IDENT4 = :IDENT4, PREDLOHY = :PREDLOHY, PL1 = :PL1, PL2 = :PL2, PL3 = :PL3, PL4 = :PL4, PL5 = :PL5, PL6 = :PL6, PL7 = :PL7, PL8 = :PL8, PL9 = :PL9, PL10 = :PL10, SIGNATURA = :SIGNATURA
WHERE
DXFFFF = :Old_DXFFFF AND DYFFFF = :Old_DYFFFF AND EXA1 = :Old_EXA1 AND EXA2 = :Old_EXA2 AND EXA3 = :Old_EXA3 AND EY = :Old_EY AND EZ = :Old_EZ AND IDENT1 = :Old_IDENT1 AND IDENT2 = :Old_IDENT2 AND IDENT3 = :Old_IDENT3 AND IDENT4 = :Old_IDENT4 AND PREDLOHY = :Old_PREDLOHY AND PL1 = :Old_PL1 AND PL2 = :Old_PL2 AND PL3 = :Old_PL3 AND PL4 = :Old_PL4 AND PL5 = :Old_PL5 AND PL6 = :Old_PL6 AND PL7 = :Old_PL7 AND PL8 = :Old_PL8 AND PL9 = :Old_PL9 AND PL10 = :Old_PL10 AND SIGNATURA = :Old_SIGNATURA

If table does not have a primary key and I remove comparison of these (BLOB) fields from WHERE caluse in UPDATE SQL, error message is: 0 records found - cannot update record.

For example:

UPDATE PRAVA
SET
DXFFFF = :DXFFFF, DYFFFF = :DYFFFF, EXA1 = :EXA1, EXA2 = :EXA2, EXA3 = :EXA3, EY = :EY, EZ = :EZ, IDENT1 = :IDENT1, IDENT2 = :IDENT2, IDENT3 = :IDENT3, IDENT4 = :IDENT4, PREDLOHY = :PREDLOHY, PL1 = :PL1, PL2 = :PL2, PL3 = :PL3, PL4 = :PL4, PL5 = :PL5, PL6 = :PL6, PL7 = :PL7, PL8 = :PL8, PL9 = :PL9, PL10 = :PL10, SIGNATURA = :SIGNATURA
WHERE
DXFFFF = :Old_DXFFFF AND DYFFFF = :Old_DYFFFF AND EXA1 = :Old_EXA1 AND EXA2 = :Old_EXA2 AND EXA3 = :Old_EXA3 AND EY = :Old_EY AND EZ = :Old_EZ AND IDENT1 = :Old_IDENT1 AND IDENT2 = :Old_IDENT2 AND IDENT3 = :Old_IDENT3 AND IDENT4 = :Old_IDENT4 AND PREDLOHY = :Old_PREDLOHY AND PL1 = :Old_PL1 AND PL2 = :Old_PL2 AND PL3 = :Old_PL3 AND PL4 = :Old_PL4 AND PL5 = :Old_PL5 AND PL6 = :Old_PL6 AND PL7 = :Old_PL7 AND PL8 = :Old_PL8 AND PL9 = :Old_PL9 AND PL10 = :Old_PL10

I think that this behavior is errorneous and I would like to ask you for any advice how to solve this issue.

Thank you for your time.

Sincerely,

Karel

Posted: Wed 27 Jul 2011 16:55
by yamaco
I apologize but I would like to add a little bit more to previous message:

Existing primary key does not affect the error appearance. In other words, only IMAGE fields cause the problem (with NTEXT works right) without fact that table has or has not primary key.

But further topics are still active, it means that if I compare IMAGE field, I obtain error message given above, and if I remove comparison of IMAGE field from WHERE clause of UPDATE SQL, I get Update failed - 0 records found.

Thank you for any solution.

Sincerely,

Karel

Posted: Thu 28 Jul 2011 07:03
by yamaco
Hello, I spent a lot of more time to find cause of impossibility of save graphics file into an IMAGE field in the database. Some isuues were solved by setting Options.StrictUpdate to False but one crucial still persists.

For example:

Table ETZMS has a primary key on SKUPINA field and IMAGE fields named FOTO1 and FOTO2 which have FieldType=ftGraphic.

Concernerd UPDATE SQL:

UPDATE OTAZKYMS
SET
SKUPINA = :SKUPINA, ZNENI = :ZNENI, ODPOVED = :ODPOVED, VZOR = :VZOR, LEGIS = :LEGIS, BODY = :BODY, FOTO1 = :FOTO1, FOTO2 = :FOTO2
WHERE
SKUPINA = :Old_SKUPINA

I can update or insert records that do not have data in FOTO1 or FOTO2 field. After assigning data with these fields, mentioned error (Parameter [6] FOTO1 - Invalid value (status=2h) appears and changes will not save.

I think that problem is in your components - previous version of my app with the same code and ADO component set worked correctly.

I would like to ask for quick solving of the problem because users of our application need to use it immediately.

Thank you.

Karel

Posted: Thu 28 Jul 2011 07:19
by yamaco
Hello, I tried to change BlobType property of concerned fields. If I set it from ftGraphics to ftBlob, Access violation has been reported. When I changed the value to ftFmtMemo, saving record works OK.

It seems that problem is solved but the way how to achieve it a little bit confusing - why should be IMAGE fiels FmtMemo and not Graphics ???

Thank you for exaplanation and correction.

Karel

Posted: Thu 28 Jul 2011 07:19
by yamaco
Hello, I tried to change BlobType property of concerned fields. If I set it from ftGraphics to ftBlob, Access violation has been reported. When I changed the value to ftFmtMemo, saving record works OK.

It seems that problem is solved but the way how to achieve it a little bit confusing - why should be IMAGE fields FmtMemo and not Graphics ???

Thank you for exaplanation and correction.

Karel

Posted: Thu 28 Jul 2011 08:51
by AndreyZ
I cannot reproduce this problem. I used the following script to create the OTAZKYMS table:

Code: Select all

CREATE TABLE [dbo].[OTAZKYMS](
	[SKUPINA] [int] PRIMARY KEY NOT NULL,
	[ZNENI] [varchar](20) NULL,
	[FOTO1] [image] NULL,
	[FOTO2] [image] NULL
)
, and the following update SQL statement:

Code: Select all

UPDATE OTAZKYMS 
SET 
SKUPINA = :SKUPINA, ZNENI = :ZNENI, FOTO1 = :FOTO1, FOTO2 = :FOTO2 
WHERE 
SKUPINA = :Old_SKUPINA
Both FOTO1 and FOTO2 fields are TBlobField (datatype is ftBlob). I used the following code in the MSQuery.BeforeUpdateExecute event:

Code: Select all

procedure TMainForm.MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  Params.ParamByName('FOTO1').LoadFromFile('filename', ftBlob);
  Params.ParamByName('FOTO2').LoadFromFile('filename', ftBlob);
end;
There were no problems with updating fields regardless of the fact that the FOTO1 and FOTO2 fields had data or not.
Please provide me the script to create the OTAZKYMS table and code you are using to update this table. Also please specify the exact version of SDAC you are using. You can learn it from the About sheet of TMSConnection Editor.

Posted: Thu 28 Jul 2011 10:33
by yamaco
Hello, thank you for your reply.

Definition of concerned table is here:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[OTAZKYMS](
[SKUPINA] [dbo].[DOM_ADRESA] NOT NULL,
[ZNENI] [dbo].[DOM_BLOB] NULL,
[ODPOVED] [dbo].[DOM_BOOL] NULL,
[VZOR] [dbo].[DOM_BLOB] NULL,
[LEGIS] [dbo].[DOM_TEXT] NULL,
[BODY] [int] NULL,
[FOTO1] [dbo].[DOM_BLOB] NULL,
[FOTO2] [dbo].[DOM_BLOB] NULL,
CONSTRAINT [PK_OTAMS] PRIMARY KEY CLUSTERED
(
[SKUPINA] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

I use the latest version of SDAC which is 5.10.0.8 for RAD Studio 2007.

For loading JPG file into IMAGE field (FOTO1) I use specialized visual component (similar to standard DBImage but with methods for loading and converting image) that is provided by local vendor and uses following method for it:

function WritePictureToJPGField(F: TField; P: TPicture; Quality: Integer): boolean;
var JPEGImage: TJPEGImage;
begin
Result:=false;
try
JPEGImage:=TJPEGImage.Create;
try
JPEGImage.Assign(P.Graphic);
if JPEGImage.Empty then F.Clear
else
begin
JPEGImage.CompressionQuality:=Quality;
JPEGImage.Compress;
F.Assign(JPEGImage);
end;
finally
JPEGImage.Free;
end;
Result:=true;
except
on E: Exception do ShowErr('',E,false);
end;
end;

Thank you for help.

Karel

Posted: Thu 28 Jul 2011 14:52
by AndreyZ
I've tried to use the WritePictureToJPGField function in the following way:

Code: Select all

MSQuery.Open;
MSQuery.Edit;
WritePictureToJPGField(MSQuery.FieldByName('FOTO1'), Image.Picture, 100);
MSQuery.Post;
, and there were no problems. Please try creating a small sample (without using third-party visual components) that demonstrates the problem and send it to andreyz*devart*com. Also please specify the definition for the DOM_ADRESA, DOM_BLOB, DOM_BOOL, DOM_TEXT types you are using to create the OTAZKYMS table.