String or TMemo into a Blob field using stored procedure
Please take a look at the following and see if you can tell what I am doing
wrong...
I create a table in IB using the following SQL statement...
Create Table "NOTES"
( "NOTEID" INTEGER,
"NOTETEXT" BLOB SUB_TYPE TEXT SEGMENT SIZE 80);
This works fine. I then create a stored procedure to use when adding
records...
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;
/* Stored procedures */
CREATE PROCEDURE "ADDNOTE"
(
"MYID" INTEGER,
"NOTETEXT" BLOB
)
RETURNS
(
"NOTEID" INTEGER
)
AS
BEGIN EXIT; END ^
ALTER PROCEDURE "ADDNOTE"
(
"MYID" INTEGER,
"NOTETEXT" BLOB
)
RETURNS
(
"NOTEID" INTEGER
)
AS
DECLARE VARIABLE TestNoteID int;
BEGIN
/* see if the vehicle already exists */
SELECT NOTEID FROM NOTES
WHERE MYID = :MYID
INTO :TestNoteID;
IF ((:TestNoteID IS NULL) OR (:TestNoteID = 0)) THEN
BEGIN
/* it doesn't exist, add it */
NOTEID = Gen_ID (NotesGen, 1);
INSERT INTO NOTES VALUES (
:NOTEID,
:MYID,
:NOTETEXT
);
END ELSE
/* if it does exist, simply return its id */
NOTEID = NOTEID;
SUSPEND;
END
^
SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;
This creates the stored procedure and this may be where the problem lies. I
then set up an IBStoredProc which pulls in the Params designated in the
ADDNOTE stored procedure. The NOTETEXT field is an ftBlob field type as it
should be. In the code, I use the following code to assign the params and
execute the query:
ibtransNotes.StartTransaction;
try
procAddNotes.Prepare;
procAddNotes.ParamByName ('MYID').AsString := ANote.MYID;
procAddNotes.ParamByName ('NOTETEXT').SetBlobData(@ANote.Notes[1],
Length(ANote.Notes)); {where ANotes.Notes is a string}
procAddNotes.ExecProc;
ANotes.NOTEID := procAddNotes.ParamByName ('NOTEID').AsInteger;
ibtransNotes.Commit;
except
ibtransNotes.Rollback;
end; { try }
This works fine in all my stored procedures which do not use BLOB fields.
When I try to use this I get an error "invalid BLOB ID".
Any help would be greatly appreciated.
Thanks,
Mark
