CODEWITHSUNDEEP
sql-serversql-server-2008delphidelphi-xe2ado
iMan Biglari
iMan Biglari

Reputation: 4776

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

I'm sure this has a very simple solution, but I've exhausted my resources. I need to insert a bunch of WideString variables which may contain #0 into a NVARCHAR(1024) column in MSSQL2k8. #0 is treated as the string terminator somewhere in the transition... So, when I store #6'r'#0'abc', only #6'r' is stored. I'm using TADOQuery. What can I do about this?

Here's the code I use:

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;

Upvotes: 4

Views: 626

Answers (2)

David Dubois
David Dubois

Reputation: 3932

If you can change the schema, use VARBINARY or IMAGE instead. These field types are designed to hold binary data. 

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

Upvotes: 2

Gerry Coll
Gerry Coll

Reputation: 5975

If you want to store an string encrypted by any means that could have NULLs in it, encode and decode the encrypted data as Base64 using the routines in Soap.EncdDecd.pas (EncdDecd.pas in older versions).

And DO USE proper encryption, either from Microsoft CryptoAPI, or any of the native Delphi implementations such as DCPCrypt.

If you are storing passwords for access to your system, consider hashing (and salting) them instead. Obviously if they are passwords to connect to other systems you can't do that.

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

Upvotes: 5

Related Questions