How to insert row containing # 0 in NVARCHAR () column in MSSQL?

Question

I am sure that this is a very simple solution, but I have exhausted my resources. I need to insert a bunch of WideString variables that can contain #0 in the NVARCHAR(1024) column in MSSQL2k8. #0 seen as a line terminator somewhere in the transition ... So when I store #6'r'#0'abc' , only #6'r' . I am using TADOQuery . What can I do about this?

---

Here is the code I'm using:

 var S: string; begin S := #6'r'#0'abc'; ADOQuery1.Append; ADOQuery1S.Value := S; { At this point, S = #6'r'#0'abc' and ADOQuery1S.Value = #6'r';} ADOQuery1.Post; end; 

Answer 1

If you want to save a string encrypted by any means that may have NULL in it, encode and decode the encrypted data as Base64 using the routines in Soap.EncdDecd.pas (EncdDecd.pas in older versions).

And Use the correct encryption, either from Microsoft CryptoAPI, or from embedded Delphi implementations, such as DCPCrypt .

If you store passwords to access your system, consider hashing (and salting) instead. Obviously, if these are passwords for connecting to other systems, you cannot do this.

 var B64: string; begin B64 := EncodeString(#6'r'#0'abc'); ADOQuery1.Append; ADOQuery1S.Value := B64; ADOQuery1.Post; end; 

Answer 2

If you can change the schema, use VARBINARY or IMAGE instead. These field types are for storing binary data.

 create table MY_DATA ( FLD_1 varbinary(2048), FLD_2 image )