Informix .NET Provider Error
Hi everybody!
I'm having a error when i'm trying to update a row that contains a BLOB
datatype. I'm using a Microsoft C# Application with a specific function to
execute an update in a blob datatype. I've had the same error when i was using
Informix CSDK 3.50 TC3DE, but now, i'm using CSDK 3.50 TC5 (last version).
Now, the insert class and SQL statement is working properly, but the update
doesn't.
This is the error:
ERROR [HY000] [Informix .NET Provider]Illegal attempt to use Text/Byte host
variable.
Here is the Table DDL:
informix@dvohp006:/informix/v11# dbschema -d imagem -t tb_anexo_guia -p all -ss
DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.FC5
grant dba to "informix";
grant dba to "imgteste";
{ TABLE "informix".tb_anexo_guia row size = 552 number of columns = 17 index
size
= 0 }
create table "informix".tb_anexo_guia
(
id_anexo_guia serial not null ,
cd_guia_prestador varchar(20) not null ,
cd_carteirinha varchar(30),
cd_operadora_ans varchar(6),
nm_arquivo varchar(255),
ds_extensao_arquivo varchar(3),
nu_tamanho_arquivo bigint,
nu_sequencial integer,
id_status_anexo_guia integer,
dt_inclusao datetime year to fraction(5),
dt_ultimo_processamento datetime year to fraction(5),
id_prestador integer,
id_operadora integer,
id_maquina integer,
nm_servico varchar(100),
id_thread integer,
bl_arquivo "informix".blob
) extent size 102400 next size 102400 lock mode row;
alter table "informix".tb_anexo_guia PUT bl_arquivo in
(
sbs_photospace01
);
revoke all on "informix".tb_anexo_guia from "public" as "informix";
grant select on "informix".tb_anexo_guia to "public" as "informix";
grant update on "informix".tb_anexo_guia to "public" as "informix";
grant insert on "informix".tb_anexo_guia to "public" as "informix";
grant delete on "informix".tb_anexo_guia to "public" as "informix";
grant index on "informix".tb_anexo_guia to "public" as "informix";
This is the C# code with the function who update records on a table:
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using IBM.Data.Informix;
namespace testeInformix
{
class Program
{
static void Main(string[] args) {
insert();
update();
}
private static void insert() {
System.IO.FileStream fc = new
System.IO.FileStream(@"C:\\\\downloads\\\\000515-28592-5593-253869457200-01-pdf.p7s",
System.IO.FileMode.Open);
byte[] bCont = new byte[fc.Length];
fc.Read(bCont, 0, Convert.ToInt32(fc.Length)); fc.Close();
IfxConnection con = new IBM.Data.Informix.IfxConnection("Database=imagem;
Host=172.22.10.185; Server=df06_d; Service=3668; Protocol=onsoctcp;
UID=imgteste; Password=imgteste;");
StringBuilder sb = new StringBuilder();
sb.AppendLine("Insert into TB_ANEXO_GUIA (");
sb.AppendLine("cd_guia_prestador");
sb.AppendLine(",bl_Arquivo");
sb.AppendLine(") values (");
sb.AppendLine("?");
sb.AppendLine(",?");
sb.AppendLine(")");
IfxCommand cmd = new IfxCommand(sb.ToString(), con);
IfxParameter param = new IfxParameter("guia", IfxType.VarChar);
param.Value = "1234";
cmd.Parameters.Add(param);
param = new IfxParameter("blob", IfxType.Blob);
param.Value = bCont;
cmd.Parameters.Add(param);
con.Open();
cmd.ExecuteNonQuery();
}
private static void update() {
System.IO.FileStream fc = new
System.IO.FileStream(@"C:\\\\downloads\\\\000515-28592-5593-253869457200-01-pdf.p7s",
System.IO.FileMode.Open);
byte[] bCont = new byte[fc.Length];
fc.Read(bCont, 0, Convert.ToInt32(fc.Length)); fc.Close();
IfxConnection con = new IBM.Data.Informix.IfxConnection("Database=imagem;
Host=172.22.10.185; Server=df06_d; Service=3668; Protocol=onsoctcp;
UID=imgteste; Password=imgteste;");
StringBuilder sb = new StringBuilder();
sb.AppendLine("Update TB_ANEXO_GUIA ");
sb.AppendLine("set bl_Arquivo = ?");
sb.AppendLine("Where Id_Anexo_Guia=35");
IfxCommand cmd = new IfxCommand(sb.ToString(), con);
IfxParameter param = new IfxParameter("blob", IfxType.Blob);
param.Value = bCont;
cmd.Parameters.Add(param);
con.Open();
cmd.ExecuteNonQuery();
}
}
}
Can anybody help me???
Tks,
Alberto Pessonio
Orizon Brasil
www.orizonbrasil.com.br
Alberto
Try the workaround:
First delete the whole row... after insert the same row with new content of
that blob column.
BR
RFo
> To: ids@iiug.org
> From: arfilho@orizonbrasil.com.br
> Subject: Informix .NET Provider Error [17122]
> Date: Mon, 21 Sep 2009 14:21:05 -0400
>
> Hi everybody!
>
> I'm having a error when i'm trying to update a row that contains a BLOB
> datatype. I'm using a Microsoft C# Application with a specific function to
> execute an update in a blob datatype. I've had the same error when i was
using
> Informix CSDK 3.50 TC3DE, but now, i'm using CSDK 3.50 TC5 (last version).
>
> Now, the insert class and SQL statement is working properly, but the update
> doesn't.
>
> This is the error:
>
> ERROR [HY000] [Informix .NET Provider]Illegal attempt to use Text/Byte host
> variable.
>
> Here is the Table DDL:
>
> informix@dvohp006:/informix/v11# dbschema -d imagem -t tb_anexo_guia -p all
> -ss
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.FC5
> grant dba to "informix";
> grant dba to "imgteste";>
> { TABLE "informix".tb_anexo_guia row size = 552 number of columns = 17 index
> size
>
> = 0 }
> create table "informix".tb_anexo_guia
> (
>
> id_anexo_guia serial not null ,
>
> cd_guia_prestador varchar(20) not null ,
>
> cd_carteirinha varchar(30),
>
> cd_operadora_ans varchar(6),
>
> nm_arquivo varchar(255),
>
> ds_extensao_arquivo varchar(3),
>
> nu_tamanho_arquivo bigint,
>
> nu_sequencial integer,
>
> id_status_anexo_guia integer,
>
> dt_inclusao datetime year to fraction(5),
>
> dt_ultimo_processamento datetime year to fraction(5),
>
> id_prestador integer,
>
> id_operadora integer,
>
> id_maquina integer,
>
> nm_servico varchar(100),
>
> id_thread integer,
>
> bl_arquivo "informix".blob
> ) extent size 102400 next size 102400 lock mode row;
> alter table "informix".tb_anexo_guia PUT bl_arquivo in
> (
>
> sbs_photospace01
> );
>
> revoke all on "informix".tb_anexo_guia from "public" as "informix";>
> grant select on "informix".tb_anexo_guia to "public" as "informix";
> grant update on "informix".tb_anexo_guia to "public" as "informix";
> grant insert on "informix".tb_anexo_guia to "public" as "informix";
> grant delete on "informix".tb_anexo_guia to "public" as "informix";
> grant index on "informix".tb_anexo_guia to "public" as "informix";>
> This is the C# code with the function who update records on a table:
>
> using System;
> using System.Data;
> using System.Collections.Generic;
> using System.Text;
> using IBM.Data.Informix;
>
> namespace testeInformix
> {
>
> class Program
>
> {
>
> static void Main(string[] args) {
>
> insert();
>
> update();
>
> }
>
> private static void insert() {
>
> System.IO.FileStream fc = new
>
System.IO.FileStream(@"C:\\\\downloads\\\\000515-28592-5593-253869457200-01-pdf.p7s",
> System.IO.FileMode.Open);
>
> byte[] bCont = new byte[fc.Length];
>
> fc.Read(bCont, 0, Convert.ToInt32(fc.Length)); fc.Close();
>
> IfxConnection con = new IBM.Data.Informix.IfxConnection("Database=imagem;
> Host=172.22.10.185; Server=df06_d; Service=3668; Protocol=onsoctcp;
> UID=imgteste; Password=imgteste;");
>
> StringBuilder sb = new StringBuilder();
>
> sb.AppendLine("Insert into TB_ANEXO_GUIA (");
>
> sb.AppendLine("cd_guia_prestador");
>
> sb.AppendLine(",bl_Arquivo");
>
> sb.AppendLine(") values (");
>
> sb.AppendLine("?");
>
> sb.AppendLine(",?");
>
> sb.AppendLine(")");
>
> IfxCommand cmd = new IfxCommand(sb.ToString(), con);
>
> IfxParameter param = new IfxParameter("guia", IfxType.VarChar);
>
> param.Value = "1234";
>
> cmd.Parameters.Add(param);
>
> param = new IfxParameter("blob", IfxType.Blob);
>
> param.Value = bCont;
>
> cmd.Parameters.Add(param);
>
> con.Open();
>
> cmd.ExecuteNonQuery();
>
> }
>
> private static void update() {
>
> System.IO.FileStream fc = new
>
System.IO.FileStream(@"C:\\\\downloads\\\\000515-28592-5593-253869457200-01-pdf.p7s",
> System.IO.FileMode.Open);
>
> byte[] bCont = new byte[fc.Length];
>
> fc.Read(bCont, 0, Convert.ToInt32(fc.Length)); fc.Close();
>
> IfxConnection con = new IBM.Data.Informix.IfxConnection("Database=imagem;
> Host=172.22.10.185; Server=df06_d; Service=3668; Protocol=onsoctcp;
> UID=imgteste; Password=imgteste;");
>
> StringBuilder sb = new StringBuilder();
>
> sb.AppendLine("Update TB_ANEXO_GUIA ");
>
> sb.AppendLine("set bl_Arquivo = ?");
>
> sb.AppendLine("Where Id_Anexo_Guia=35");
>
> IfxCommand cmd = new IfxCommand(sb.ToString(), con);
>
> IfxParameter param = new IfxParameter("blob", IfxType.Blob);
>
> param.Value = bCont;
>
> cmd.Parameters.Add(param);
>
> con.Open();
>
> cmd.ExecuteNonQuery();
>
> }
>
> }
> }
>
> Can anybody help me???
>
> Tks,
> Alberto Pessonio
> Orizon Brasil
> www.orizonbrasil.com.br
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Show them the way! Add maps and directions to your party invites.
http://www.microsoft.com/windows/windowslive/products/events.aspx