At 11:40 AM 12/02/2008, you wrote:

>--- In IBObjects@yahoogroups.com, Aage Johansen <aagjohan@...> wrote:
>>
>> nosix06 wrote:
>> > Hello Helen thank you for the reply and I have looked over the blob
>> > example in the Files section for the group and while it does explain
>> > how to show the images from the database once they are there it
>really
>> > doesn't show how to insert them there in the first place so what I
>> > need to know now is how do I insert the images, text, ... in the
>first
>> > place.
>> >
>>
>> (Untested:)
>> Start with an (insert) query in a TIB_DSQL component:
>> insert into PICTS (ID, ..., PICTBLOB)
>> values (:ID, ..., :PICTBLOB)
>> Prepare it, and set the parameters. For your picture you can e.g.
>> load it from a file:
>> dsqlPICT.ParamByName('PICTBLOB').LoadFromFile(SomeFilename);
>> dsqlPICT.ExecSQL;
>> You can also load it from a MemoryStream if you so wish.
>>
>>
>> --
>> Aage J.
>>
>
>Hello Aage and thank you for your reply but I think I am doing
>something wrong because I keep getting this error message when trying
>to insert an image to the db.
>
>ISC ERROR CODE:335544665
>
>ISC ERROR MESSAGE:
>Violation of PRIMARY or UNIQUE KEY contraint "INTEG_2" on table IMAGES
>
>To help track down my mistake here is an outline of what I did.
>
>I created a new database and gave it 1 table with the name of "IMAGES"
> which has 2 fields in it named.
>
>- IMAGE_ID (Integer, PK, not null)
>- THEIMAGE (Blob, subtype binary segment size 4096, not null)
>
>I then setup a trigger & generator for the IMAGE_ID field because this
>is what I thought I would need to do to make sure each new image I
>inserted into the table didn't simply overwrite any previous image
>that was there. Anyway the SQL for my generator & trigger looks like
>this.
>
>CREATE TRIGGER INSERT_IMAGES FOR IMAGES
>ACTIVE BEFORE INSERT POSITION 0
>AS
>BEGIN
> IF (NEW.IMAGE_ID IS NULL) THEN
> NEW.IMAGE_ID = GEN_ID(GEN_IMAGES_ID,1);
>END^
>
>
>Finally I started a new project in delpi and placed the following
>controls on my form.
>
>1 Button
>1 OpenPictureDialog
>1 IB_Connection
>1 IB_DSQL
>
>I left the name for each component as they are by default and then
>linked the IB_Connection component upto my database and linked up the
>IB_DSQL component to the IB_Connection component and set up the SQL
>for it like so.
>
>INSERT into IMAGES (IMAGE_ID, THEIMAGE) VALUES(:IMAGE_ID, :THEIMAGE)

This is the statement that is causing the primary key violation. The trigger will take care of generating the value for the key. As it stands, you are reading the value of an existing key from a variable in your application and thus causing a duplication. The DSQL statement should be

INSERT into IMAGES (THEIMAGE) VALUES(:THEIMAGE)

But this still might except, as you haven't given us enough information about your application's workflow.

>And finally I added the following code to my buttons Onclick event.
>
>procedure TForm1.Button1Click(Sender: TObject);
>begin
> If OpenPictureDialog1.Execute then

with IB_DSQL1 do
begin
if not ib_Transaction.InTransaction then
ib_Transaction.StartTransaction;
if not Prepared then Prepare;
TIB_ColumnBlob(ParamByName('THEIMAGE')).LoadFromFile(OpenPictureDialog1.FileName);
ExecSQL;
ib_Transaction.Commit;
end;
end;

>I then ran the program and tried to select an image (bmp) to insert
>the database but once the image was selected the program gave me the
>error mentioned above and this where I am at now and don't really know
>how to continue on from here so again if you or anyone else can see
>where I made my mistake then I would be greatful if you could show me
>how I can fix it.

We can't tell here whether you have an editable TIB_Query involved in the interface, with an Insert event that kicked off the call to the Gen_ID() function -- that would occur if you had defined GeneratorLinks. Posting that event would have inserted a new record with a new ID fetched by that Gen_ID() call. If you then went on, using your button click event to grab the same value as input to your detached INSERT statement (as you had it) then that's how you got the key violation.

So, what I'm saying is that correcting the detached INSERT statement will fix the key violation but you'll probably have two new records where you intended to have just one: the first record with a null blob on it and the second one as you intended.

If I'm assuming right, then either disable inserting from the ib_query (in ColumnAttributes select or add THEIMAGE=NOINSERT) or take out the button and shift your inserting code into the BeforeInsert event of the dataset.

Helen