firebird-net-provider

[Firebird-net-provider] Convert BLOB to string

[Martin <mc...@we...>]

Hi,

I am retrieving a BLOB field from a firebird db via .net provider 1.5

I am using ExecuteScalar() to get the first single result from execution of
the stored procudure
I am trying to retrieve the value like so

Dim strDesciption As Byte
strDesciption = objCMD.ExecuteScalar()   <---  this line throws an
exception..

I am then trying to put the result into a string -- but execution does not
get this far so  don't know if it is correct

Return strDesciption.ToString()

I am getting a "System.InvalidCastException"  (produced by the
ExecuteScalar() line)

message "Cast from type 'Byte()' to type 'Byte' is not valid."

I would appreciate any advice on how to avoid this error and be able to
return the string from the blob field my dot net code as a string

many thanks in advance.

martin.

Re: [Firebird-net-provider] Convert BLOB to string

[<car...@te...>]

Hello:

> I am retrieving a BLOB field from a firebird db via .net provider 1.5
> 
> I am using ExecuteScalar() to get the first single result from execution of
> the stored procudure I am trying to retrieve the value like so

It's a blob field with subtype text ?? if not the provider will return 
an array of bytes and you will need to convert it to an string using the 
Encoding classes.



--
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Convert BLOB to string

[Martin <mc...@we...>]

Thanks for that, I am a little confusesd.
I'm not sure what the sub type has to do with it. Does this mean that I can
retrive the data directly if  it has a subtype of text??

I have never used the "Encoding" classes before. Do these classes belong to
.net framework or firebirdsql.
I would appreciate knowing the full namespace of the classes to use then I
can start looking through the documentation.
Any pointers on articles that may be helpful in doing such a thing is
appreciated  (it is totally new to me.)



just for back ground this is the create statement for my table

CREATE TABLE EE_SP_NAME (
    ID                   PK_ID NOT NULL,
    PROCEDURE_NAME       VC80 NOT NULL,
    DISPLAY_TEXT         VC80 NOT NULL,
    DISPLAY_DESCRIPTION  BLOB_TEXT_DATA NOT NULL,
    CREATED              TIMESTAMP_NOW NOT NULL
);

the definition of the blob text domain is

BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET NONE

cheers

martin



"Carlos Guzmán Álvarez" <car...@te...> wrote in message
news:400...@te......
> Hello:
>
> > I am retrieving a BLOB field from a firebird db via .net provider 1.5
> >
> > I am using ExecuteScalar() to get the first single result from execution
of
> > the stored procudure I am trying to retrieve the value like so
>
> It's a blob field with subtype text ?? if not the provider will return
> an array of bytes and you will need to convert it to an string using the
> Encoding classes.
>
>
>
> --
> Best regards
>
> Carlos Guzmán Álvarez
> Vigo-Spain
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>

Re: [Firebird-net-provider] Convert BLOB to string

[<car...@te...>]

Hello:

> BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET NONE

It's correct, which version of the .NET Data Provider are you using ??

I have tested this with 1.5, and runs fine:

FbConnection connection = new FbConnection(connectionString);
connection.Open();

string sql = "select clob_field from test where int_field = 1";

FbCommand command = new FbCommand(sql, connection);

string value = (string)command.ExecuteScalar();

connection.Close();




--
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Convert BLOB to string

[Martin <mc...@we...>]

Hi,

I am using .net provider 1.5 RC2.
I am using vb.net code. The line that is throwing the exception is

strDesciption = CType(objCMD.ExecuteScalar(), String)

and the exception description is

Cast from type 'Byte()' to type 'String' is not valid.

I have checked that a record is being broght back. Perhaps I have the cast
syntax wrong...

The full code that I am using is below. If I comment the cat out and just
use

objCMD.ExecuteScalar()

then all works fine.

I am executing a stored procedure. and the full code that I am using is
below for reference.


cheers

martin.



Try
            Dim objConn As FirebirdSql.Data.Firebird.FbConnection = New
FirebirdSql.Data.Firebird.FbConnection
            Dim objCMD As FirebirdSql.Data.Firebird.FbCommand = New
FirebirdSql.Data.Firebird.FbCommand
            Dim objTran As FirebirdSql.Data.Firebird.FbTransaction
            objConn.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings("DbConnectionString")
            objConn.Open()
            objCMD.Connection = objConn
            objTran = objConn.BeginTransaction()
            objCMD.Transaction = objTran
            objCMD.CommandText = "SP_EE_GET_GROUP_DESCRIPTION"
            objCMD.CommandType = CommandType.StoredProcedure
            Dim P1 As New FirebirdSql.Data.Firebird.FbParameter
            P1.Direction = ParameterDirection.Input
            P1 = objCMD.Parameters.Add("@ID", 87)
            Dim strDesciption As String 'Variable to hold the description
string that will be returned

            strDesciption = CType(objCMD.ExecuteScalar(), String)
<<<<<<<<<<<<<<<<<<<<---------  THROWS EXCEPTION


            objCMD.Dispose()
            objTran.Commit()
            objTran.Dispose()
            objConn.Close()
        Catch ex As Exception
            Trace.Write(ex.Message())
        End Try






"Carlos Guzmán Álvarez" <car...@te...> wrote in message
news:400...@te......
> Hello:
>
> > BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET NONE
>
> It's correct, which version of the .NET Data Provider are you using ??
>
> I have tested this with 1.5, and runs fine:
>
> FbConnection connection = new FbConnection(connectionString);
> connection.Open();
>
> string sql = "select clob_field from test where int_field = 1";
>
> FbCommand command = new FbCommand(sql, connection);
>
> string value = (string)command.ExecuteScalar();
>
> connection.Close();
>
>
>
>
> --
> Best regards
>
> Carlos Guzmán Álvarez
> Vigo-Spain
>
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>

Re: [Firebird-net-provider] Convert BLOB to string

[Martin <mc...@we...>]

Hi,

I have now tried the C# code below using the cast that was provided.
I still get an exception of

    "Specified cast is not valid."

the C# code that I have used is shown below. The stored procedure that I am
using is shown below also.
There is definatly a record comming back. at least if I use a fbdatareader
and loop then the loop only ueses a single record.
I am not sure how to get the value back from a data reader.
It seems strange that the cast works for you and not me I am using RC2.

Thanks again for any advice.

cheers

martin.

try
   {
    Context.Trace.Warn("Page_Load","Entered function");
    FirebirdSql.Data.Firebird.FbConnection objConn = new
FirebirdSql.Data.Firebird.FbConnection();
    FirebirdSql.Data.Firebird.FbCommand objCMD = new
FirebirdSql.Data.Firebird.FbCommand();
    objConn.ConnectionString =
"Database=C:\\Inetpub\\wwwroot\\nz\\_database\\RESTOREDB.GDB;User=SYSDBA;Pas
sword=masterkey;Dialect=3;Server=localhost;Pooling=false;";
    objConn.Open();
    objCMD.Connection = objConn;
    Context.Trace.Write("Page_Load","connection is open");
    objCMD.CommandText = "SP_EE_GET_GROUP_DESCRIPTION";
       objCMD.CommandType = CommandType.StoredProcedure;
    FirebirdSql.Data.Firebird.FbParameter P1 = new
FirebirdSql.Data.Firebird.FbParameter();
       P1.Direction = ParameterDirection.Input;
       P1 = objCMD.Parameters.Add("@ID", 87);
    FirebirdSql.Data.Firebird.FbDataReader dr;
    dr = objCMD.ExecuteReader();
    string value = (string)objCMD.ExecuteScalar();
    objConn.Close();
    Context.Trace.Warn("Page_Load","Exited function");
   }
   catch (Exception ex)
   {
    Context.Trace.Warn("Excepetion thrown");
    Context.Trace.Write(ex.Message);
   }

store procedure text

CREATE PROCEDURE SP_EE_GET_GROUP_DESCRIPTION (
    ID INTEGER)
RETURNS (
    DISPLAY_DESCRIPTION BLOB SUB_TYPE 0 SEGMENT SIZE 80)
AS
BEGIN
  FOR
    SELECT EE_SP_NAME.DISPLAY_DESCRIPTION
    FROM EE_SP_SCHEDULE
       INNER JOIN EE_SP_NAME ON (EE_SP_SCHEDULE.EE_SP_NAME_ID =
EE_SP_NAME.ID)
    WHERE
       (
          (EE_SP_SCHEDULE.ID = :ID)
       )
    INTO :DISPLAY_DESCRIPTION
  DO
  BEGIN
    SUSPEND;
  END
END






""Martin"" <mc...@we...> wrote in message
news:bu79g2$38v$1...@ne......
> Hi,
>
> I am using .net provider 1.5 RC2.
> I am using vb.net code. The line that is throwing the exception is
>
> strDesciption = CType(objCMD.ExecuteScalar(), String)
>
> and the exception description is
>
> Cast from type 'Byte()' to type 'String' is not valid.
>
> I have checked that a record is being broght back. Perhaps I have the cast
> syntax wrong...
>
> The full code that I am using is below. If I comment the cat out and just
> use
>
> objCMD.ExecuteScalar()
>
> then all works fine.
>
> I am executing a stored procedure. and the full code that I am using is
> below for reference.
>
>
> cheers
>
> martin.
>
>
>
> Try
>             Dim objConn As FirebirdSql.Data.Firebird.FbConnection = New
> FirebirdSql.Data.Firebird.FbConnection
>             Dim objCMD As FirebirdSql.Data.Firebird.FbCommand = New
> FirebirdSql.Data.Firebird.FbCommand
>             Dim objTran As FirebirdSql.Data.Firebird.FbTransaction
>             objConn.ConnectionString =
>
System.Configuration.ConfigurationSettings.AppSettings("DbConnectionString")
>             objConn.Open()
>             objCMD.Connection = objConn
>             objTran = objConn.BeginTransaction()
>             objCMD.Transaction = objTran
>             objCMD.CommandText = "SP_EE_GET_GROUP_DESCRIPTION"
>             objCMD.CommandType = CommandType.StoredProcedure
>             Dim P1 As New FirebirdSql.Data.Firebird.FbParameter
>             P1.Direction = ParameterDirection.Input
>             P1 = objCMD.Parameters.Add("@ID", 87)
>             Dim strDesciption As String 'Variable to hold the description
> string that will be returned
>
>             strDesciption = CType(objCMD.ExecuteScalar(), String)
> <<<<<<<<<<<<<<<<<<<<---------  THROWS EXCEPTION
>
>
>             objCMD.Dispose()
>             objTran.Commit()
>             objTran.Dispose()
>             objConn.Close()
>         Catch ex As Exception
>             Trace.Write(ex.Message())
>         End Try
>
>
>
>
>
>
> "Carlos Guzmán Álvarez" <car...@te...> wrote in message
> news:400...@te......
> > Hello:
> >
> > > BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET NONE
> >
> > It's correct, which version of the .NET Data Provider are you using ??
> >
> > I have tested this with 1.5, and runs fine:
> >
> > FbConnection connection = new FbConnection(connectionString);
> > connection.Open();
> >
> > string sql = "select clob_field from test where int_field = 1";
> >
> > FbCommand command = new FbCommand(sql, connection);
> >
> > string value = (string)command.ExecuteScalar();
> >
> > connection.Close();
> >
> >
> >
> >
> > --
> > Best regards
> >
> > Carlos Guzmán Álvarez
> > Vigo-Spain
> >
> >
> >
> > -------------------------------------------------------
> > The SF.Net email is sponsored by EclipseCon 2004
> > Premiere Conference on Open Tools Development and Integration
> > See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> > http://www.eclipsecon.org/osdn
> > _______________________________________________
> > Firebird-net-provider mailing list
> > Fir...@li...
> > https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
> >
>
>
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>

Re: [Firebird-net-provider] Convert BLOB to string

[<car...@te...>]

Hello:

> CREATE PROCEDURE SP_EE_GET_GROUP_DESCRIPTION (
>     ID INTEGER)
> RETURNS (
>     DISPLAY_DESCRIPTION BLOB SUB_TYPE 0 SEGMENT SIZE 80)
 > ...

Huummmm ... try to change this to :


CREATE PROCEDURE SP_EE_GET_GROUP_DESCRIPTION (
     ID INTEGER)
RETURNS (
     DISPLAY_DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80)
...

Re: [Firebird-net-provider] Convert BLOB to string

[martin <mc...@we...>]

Hi Carlos,

After making the change to the sp all worked well. Thank you for pointing
out that error.
I had IB EXPERT build the procedure and did not spot the difference in BLOB
SUB_TYPE.

cheers

martin