Mega Code Archive
Fetching and Inserting Unicode Data in Delphi 5, BDE
<![CDATA[
Title: Fetching and Inserting Unicode Data in Delphi 5, BDE
Question: How do I fetch and Insert Unicode data using Delphi 5 /BDE
Answer:
The data type nChar, nVarchar and nText support Uniocode data in MS SQL.
This Solution is to get the Unicode data from SQL stored in nChar and nVarchar only. The nText data type cant be converted to varbinary.
When getting the Unicode data form a SQL database the BDE version of Delphi 5 treats the Unicode field as a TStringfield.
So the Unicode characters are not recognized by the TStringfield and are therefore replaced by ? character.
The way to get around it is to cast the nChar and the nVarchar field to varbinary while getting the data from the SQL database.
The Size of varbinary should be double the size of nChar or nVarchar column in the table. When specifying the length of a binary/varbinary data type, every two characters count as one.
For Example: Let us get the Unicode data from the employee table with the following table structure.
Table: Employee
---------------
EmpNo int
EmpName nChar(50)
EmpAddress vVarchar(250)
DateJoined DateTime
When selecting from the above Employee table the select statement should be
QueryGet.Sql.Text = Select EmpNo, Cast (EmpName as varbinary(100)) as EmpName, Cast (EmpAddress as varbinary(500)) as EmpAddress, DateJoined from Employee
Let us create a stored procedure to insert the record into the Employee table in a different database with the same table structure as show above.
Create procedure InsertEmployee @EmpNo int, EmpName nChar(50), EmpAddress nVarchar(250), @DateJoined Datetime
As
Insert into Employee Values (@EmpNo, @EmpName, @EmpAddress, @DateJoined)
Use a TQuery or a TStoredProcedue component to call the Insert Stored procedure.
The insert record code in Delphi should be
QueryInsert.Close;
QueryInsert.SQL.Text := exec InsertEmployee :@EmpNo, :@EmpName, :@EmpAddress, :@DateJoined
QueryInsert.ParamByName('@EmpNo').DataType := ftInteger;
QueryInsert.ParamByName('@EmpName').DataType := ftBytes;
QueryInsert.ParamByName('@EmpAddress').DataType := ftBytes;
QueryInsert.ParamByName('@DateJoined').DataType := ftDateTime;
QueryInsert.Prepare;
QueryGet.First;
While (not QueryGet.Eof) do
Begin
QueryInsert.ParamByName('@EmpNo').Value := QueryGet.FieldByName('@EmpNo').Value;
QueryInsert.ParamByName('@EmpName').Value := QueryGet.FieldByName(EmpName).Value;
QueryInsert.ParamByName('@EmpDetails').Value := QueryGet.FieldByName('EmpDetails').Value;
QueryInsert.ParamByName('@DateJoined').Value := QueryGet.FieldByName('DateJoined').Value;
Try
QueryInsert.ExecSql;
Except
End;
QueryGet.Next;
End;
The above solution is based from the following:
1. The varbinary in SQL is treated as ftBytes in Delphi, which is supported by BDE & Delphi 5.
2. The insert stored procedures must have the input parameters as nChar or nVarchar used for passing the Unicode data.
3. So when inserting the data, SQL implicitly converts the varbinary data to nChar, nVarchar
]]>