Fail to insert blob field with stored procedures

[sklim82]

Dear all,

I try to use stored procedure to insert record and one of the fields is blob type. The blob field value is always NULL, if I use the following code:

Code: Select all

AnsiString szName = "john master 3";
TParam *param1 = MyStoredProc1->Params->CreateParam(ftBlob, "param1", ptInput);
param1->Value = szName;
MyStoredProc1->ExecProc();

But if I use the following code, the value will be inserted:

Code: Select all

MyStoredProc1->Params->ParamByName("param1")->Value = szName;
MyStoredProc1->ExecProc();

The following is the DDL:

Code: Select all

CREATE TABLE `table1` (
  `col` BLOB

)ENGINE=InnoDB;

The following is the stored procedure:

Code: Select all

CREATE PROCEDURE `SP_INSERTBLOB`(IN param1 BLOB)
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
	INSERT INTO table1 VALUES(param1);
END;

Above is the sample program that I created with the following:
- MySQL5.0.45
- MyDac 5.20.0.12
- c++ builder 6.0
- Direct connection

Please advice.

[Dimon]

I try to use stored procedure to insert record and one of the fields is blob type. The blob field value is always NULL, if I use the following code:

Code: Select all

AnsiString szName = "john master 3";
TParam *param1 = MyStoredProc1->Params->CreateParam(ftBlob, "param1", ptInput);
param1->Value = szName;
MyStoredProc1->ExecProc();

MyDAC creates parameters for stored procedure and uses it. If you want to create your own parameters, then, before creating them, you should delete created parameters using the following code:

Code: Select all

MyStoredProc1->Params->Clear;

But if I use the following code, the value will be inserted:

Code: Select all

MyStoredProc1->Params->ParamByName("param1")->Value = szName;
MyStoredProc1->ExecProc();

Why don't you use this way?

[sklim82]

Hi Dimon,

Yes, I would like to create my own parameters. I did call clear method before create the parameters but it is still NULL value in the BLOB field.

I am not using the following:

MyStoredProc1->Params->ParamByName("param1")->Value = szName;

due to the project design that need to assign the database connection later on.

Please advise how to insert BLOB field with createparam way?

[Dimon]

You should change the type of param1 from TParam to TDAParam:

Code: Select all

TDAParam *param1 = MyStoredProc1->Params->CreateParam(ftBlob, "param1", ptInput);

[sklim82]

Thanks Dimon, changing the data type helps!

[FerCastro]

Hello,

I am trying to insert a BLOB field using a Store Procedure and this is my code:

Code: Select all

  var
  tptStream: TMemoryStream;
  ftblob : tblobtype;
begin

  tptStream := TMemoryStream.Create();
  tptStream.write(template.tpt^, template.size);
  With DATA.mSPHuellasUSBInsert do
    Begin
      close;
      Params.ParamByName('`pIDEmpresa`').Value :=1;
      Params.ParamByName('`pIDEmpleado`').Value :=StrToInt(FMain.nIDEmpleado.Text);
      Params.ParamByName('`pTemplate`').LoadFromStream(tptStream,ftblob );
      Execute;
    End;

But in this line:
Params.ParamByName('`pTemplate`').LoadFromStream(tptStream,ftblob );

I get the message:

Data Type is not supported.

Here is my store procedure:

Code: Select all

CREATE PROCEDURE `SPHuellasUSBInsert`(IN `pIDEmpresa` INT, IN `pIDEmpleado` INT, IN `pTemplate` BLOB)
BEGIN

INSERT INTO huellasusb
         (
           IDEmpresa, 
           IDEmpleado,            
	   Template
         )
    VALUES
         ( 
           pIDEmpresa, 
           pIDEmpleado,            
	   pTemplate
	  ) ; 

END

Any idea?


Thanks in advance

Fernando Castro

[eduardosic]

Hello,

I am trying to insert a BLOB field using a Store Procedure and this is my code:

Code: Select all

  var
  tptStream: TMemoryStream;
  ftblob : tblobtype;
begin

  tptStream := TMemoryStream.Create();
  tptStream.write(template.tpt^, template.size);
  With DATA.mSPHuellasUSBInsert do
    Begin
      close;
      Params.ParamByName('`pIDEmpresa`').Value :=1;
      Params.ParamByName('`pIDEmpleado`').Value :=StrToInt(FMain.nIDEmpleado.Text);
      Params.ParamByName('`pTemplate`').LoadFromStream(tptStream,ftblob );
      Execute;
    End;

But in this line:
Params.ParamByName('`pTemplate`').LoadFromStream(tptStream,ftblob );

I get the message:

Data Type is not supported.

Here is my store procedure:

Code: Select all

CREATE PROCEDURE `SPHuellasUSBInsert`(IN `pIDEmpresa` INT, IN `pIDEmpleado` INT, IN `pTemplate` BLOB)
BEGIN

INSERT INTO huellasusb
         (
           IDEmpresa, 
           IDEmpleado,            
	   Template
         )
    VALUES
         ( 
           pIDEmpresa, 
           pIDEmpleado,            
	   pTemplate
	  ) ; 

END

Any idea?


Thanks in advance

Fernando Castro

You don´t need of a variable ftblob : tblobtype;

only set flBlob in Params.ParamByName('`pTemplate`').LoadFromStream(tptStream,ftblob );