Get Byte[] from Db2 without Encoding

Question

In GetEmpIDInBytes_INDIRECT method listed below, I get the following exception:

Unable to cast object of type 'System.String' to type 'System.Byte[]'.

I know that this error can be avoided if I use the following line (i.e. encoding instead of byte[] casting)

result = Encoding.ASCII.GetBytes(odbcCommand.ExecuteScalar().ToString());

But I cannot use the encoding since I don’t know what was the encoding used in the DB2 function. I need to get it back from DB2 as byte[]. How can we get it?

CODE

string sqlGetEncryptedEmpID_INDIRECT = String.Format(SQLGetEncryptedID_INDIRECT, empIDClearText);
byte[] empIDData_INDIRECT = (byte[])GetEmpIDInBytes_INDIRECT(sqlGetEncryptedEmpID_INDIRECT);



public const string SQLGetEncryptedID_INDIRECT = @"SELECT  SSS.id_encrypt ('E','0000000{0}') 
                                                AS ENCRYPT_ID FROM FFGLOBAL.ONE_ROW FETCH FIRST 1 ROW ONLY WITH UR;";



private byte[] GetEmpIDInBytes_INDIRECT(string sqlQuery)
    {
        byte[] result = null;
        string db2connectionString = ConfigurationManager.ConnectionStrings[UIConstants.DB2ConnectionString].ConnectionString;
        using (OdbcConnection odbcConnection = new OdbcConnection(db2connectionString))
        {
            using (OdbcCommand odbcCommand = new OdbcCommand(sqlQuery, odbcConnection))
            {
                odbcCommand.CommandType = System.Data.CommandType.Text;
                odbcConnection.Open();
                result = (byte[])odbcCommand.ExecuteScalar();
                //result = Encoding.ASCII.GetBytes(odbcCommand.ExecuteScalar().ToString());
            }
        }
        return result;
}

DB2 Connection String

<add name="DB2ConnectionString_XA"
connectionString="Driver={IBM DB2 ODBC DRIVER};Database=MyDB;Hostname=DB2GWTST;
Protocol=TCPIP;Port=3700;Uid=remotxa;Pwd=xxxx;"/>

What is `odbcCommand.ExecuteScalar().GetType()` returning? What data type returns `SSS.id_encrypt`? — Alex Filipovici, Mar 01 '13 at 08:04
Seems to be sql job. Use `convert` function. E.g.: `... convert(varbinary(2024), SSS.id_encrypt ('E','0000000{0}'), 0) ...`. Have not tested, btw. — Leri, Mar 01 '13 at 08:06

score 3 · Answer 1 · edited May 23 '17 at 12:32

3

Consider the following method (use this answer as a reference):

static byte[] GetBytes(string str)
{
    byte[] bytes = new byte[str.Length * sizeof(char)];
    System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
    return bytes;
}

Use it like this:

result = GetBytes(odbcCommand.ExecuteScalar().ToString());

edited May 23 '17 at 12:32

Community

1
1

answered Mar 01 '13 at 08:16

Alex Filipovici

29,732
5
50
76

LCJ · Accepted Answer · 2016-09-07T15:38:28.473

0

At the end, I used CAST AS CHAR(100) FOR BIT DATA in the Db2 query itself. That worked.

public const string SQLGetEncryptedIDDirect =
@"SELECT  CAST ( SSS.id_encrypt ('E','0000000{0}') AS CHAR(100) FOR BIT DATA)  
AS ENCRYPT_ID FROM FFGLOBAL.ONE_ROW FETCH     FIRST 1 ROW ONLY WITH UR;";

edited Sep 07 '16 at 15:38

answered Mar 01 '13 at 10:41

LCJ

20,854
59
228
387

Get Byte[] from Db2 without Encoding

2 Answers2

Linked