Re: Prob: using varbinary in iBatis asp.net

[Jain Mohit <j....@yahoo.com>]

As per requirement, it should be encrypted and can not be changed to any
other type.


Yaojian wrote:
> 
> why not define the ANSWERTEXT  as Varchar or NVarchar ?
> 
> On Tue, Apr 7, 2009 at 9:56 PM, Jain Mohit <j....@yahoo.com> wrote:
> 
>>
>> Hi,
>>
>> I am using ASP.Net 2.0 framework/C# & SQL server 2000 and using iBatis
>> for
>> database interactions. In the code snippet below (bold text), ANSWERTEXT
>> field is of type Varbinary. While inserting data to this field, I used
>> convert function.
>>
>> INSERT INTO SecretAnswer(ParticipantId, QuestionId, QuestionCode,
>> ANSWERTEXT)
>>
>> SELECT ParticipantId, #questionId#, #QuestionCode#,
>> CONVERT(varbinary(255),#Answer#)
>>
>> From table.....(some join conditions)
>>
>>
>> Case 1. In case of 'Answer' being a normal alpha numeric string
>> "test123",
>> it converts the string to varbinary properly and while validating, I am
>> able
>> to validate properly.
>> select convert(varchar(100), answer) from table
>>
>> Result:
>> test123
>>
>> Case 2. In case of 'Answer' having any special character (e.g.
>> apostrophe),
>> it does the same. However, when I execute the following in query
>> analyzer,
>> the result is not equal to the original string.
>>
>> E.g. Answer = "test'123"
>>
>> Inserted this using above insert statement. Executed following: select
>> convert(varchar(100), answer) from table
>>
>> The result comes only single char:
>> t
>>
>> While select convert(nvarchar(100), answer) from table, returns
>> "test'123",
>> which is a problem.
>>
>> Can someone look into this and respond accordingly?
>> Regards,
>>
>> Mohit Jain
>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Prob%3A-using-varnibary-in-iBatis-asp.net-tp22929580p22929580.html
>> Sent from the iBATIS - User - Cs mailing list archive at Nabble.com.
>>
>>
> 
> 

-- 
View this message in context: http://www.nabble.com/Prob%3A-using-varnibary-in-iBatis-asp.net-tp22929580p22930420.html
Sent from the iBATIS - User - Cs mailing list archive at Nabble.com.

Re: Prob: using varbinary in iBatis asp.net

[Yaojian <sk...@gmail.com>]

My guess is to put 'dbType' in IBatis.NET sqlMaps.

strategy 1: Defines the column as varbinary and convert to string at the
client-side (using C#)

define
    1)  the sqlserver database type of the ANSWERTEXT  column is
varbinary(100)
    2)  and the C# type of the corresponding 'AnswerText' field/property is
Byte[]
and in sqlMap:
    1) the insert statement may looks like:
        <insert ...>INSERT INTO SecretAnswer (..., ANSWERTEXT) values
(#AnswerText,dbType=VarBinary#)</insert>
    2) the select statement may looks like:
        <resultSet ...>
              <result property="AnswerText" column="ANSWERTEXT"
dbType=VarBinary/>
        </resultSet>
        <select...>SELECT ..., ANSWERTEXT from SecretAnswer

There's no need to call the CONVERT function in the SELECT statement.

strategy 2: Defines the column as varbinary and convert to string at the
server-side
define
     1)  the sqlserver database type of the ANSWERTEXT  column is
varbinary(100)
      2) the AnswerText field/property as String
and in sqlMap:
1)        <insert ...>INSERT INTO SecretAnswer (..., ANSWERTEXT) values
(CONVERT(varbinary(100), #AnswerText,dbType=NVarChar#)</insert>
2)        <result property="AnswerText" column="ANSWERTEXT"
dbType=NVarChar/>
           <select ...> SELECT ..., CONVERT(nvarchar, ANSWERTEXT) from
SecretAnswer




On Tue, Apr 7, 2009 at 10:30 PM, Jain Mohit <j....@yahoo.com> wrote:

>
> As per requirement, it should be encrypted and can not be changed to any
> other type.
>
>
> Yaojian wrote:
> >
> > why not define the ANSWERTEXT  as Varchar or NVarchar ?
> >
> > On Tue, Apr 7, 2009 at 9:56 PM, Jain Mohit <j....@yahoo.com> wrote:
> >
> >>
> >> Hi,
> >>
> >> I am using ASP.Net 2.0 framework/C# & SQL server 2000 and using iBatis
> >> for
> >> database interactions. In the code snippet below (bold text), ANSWERTEXT
> >> field is of type Varbinary. While inserting data to this field, I used
> >> convert function.
> >>
> >> INSERT INTO SecretAnswer(ParticipantId, QuestionId, QuestionCode,
> >> ANSWERTEXT)
> >>
> >> SELECT ParticipantId, #questionId#, #QuestionCode#,
> >> CONVERT(varbinary(255),#Answer#)
> >>
> >> From table.....(some join conditions)
> >>
> >>
> >> Case 1. In case of 'Answer' being a normal alpha numeric string
> >> "test123",
> >> it converts the string to varbinary properly and while validating, I am
> >> able
> >> to validate properly.
> >> select convert(varchar(100), answer) from table
> >>
> >> Result:
> >> test123
> >>
> >> Case 2. In case of 'Answer' having any special character (e.g.
> >> apostrophe),
> >> it does the same. However, when I execute the following in query
> >> analyzer,
> >> the result is not equal to the original string.
> >>
> >> E.g. Answer = "test'123"
> >>
> >> Inserted this using above insert statement. Executed following: select
> >> convert(varchar(100), answer) from table
> >>
> >> The result comes only single char:
> >> t
> >>
> >> While select convert(nvarchar(100), answer) from table, returns
> >> "test'123",
> >> which is a problem.
> >>
> >> Can someone look into this and respond accordingly?
> >> Regards,
> >>
> >> Mohit Jain
> >>
> >>
> >> --
> >> View this message in context:
> >>
> http://www.nabble.com/Prob%3A-using-varnibary-in-iBatis-asp.net-tp22929580p22929580.html
> >> Sent from the iBATIS - User - Cs mailing list archive at Nabble.com.
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Prob%3A-using-varnibary-in-iBatis-asp.net-tp22929580p22930420.html
> Sent from the iBATIS - User - Cs mailing list archive at Nabble.com.
>
>