Stored procedure and ftString, ftWideString parameters

dualsoft · Post by **dualsoft** » Fri 07 Oct 2011 07:53

I have SQL Server 2008 R2, Delphi XE and dbxsda 4.80.29 (same problem with 5.0.1)

I first suposed that if I create parameters for stored procedures and set parameter type as ftString I will see (in dbMonitor) String and for ftWideString I will see WideString.
Wrong. For MySQL I see that UseUnicode sets all string parameters to String or WideString accordingly.

With MySql if I change UseUnicode (connection param) to True or False in dbMonitor all string parameters "Data Type" is WideString or String.

With SQL Server regardless of UseUnicode, dbMonitor always show "Data Type" as String. And I can not use NVARCHAR in stored procedure. Nothing change if I set parameter to ftWideString.

I create parameters in this way:

Code: Select all

sqlspUSP_ValidareCodIntern.Params.CreateParam(ftWideString, FSQL_PrefixParam + 'MesajRaspuns', ptOutput).Size := 200;

Not important but if you ask: FSQL_PrefixParam is '' for MySql and '@' for SQL Server

AndreyZ · Post by **AndreyZ** » Mon 10 Oct 2011 09:43

The point is that all MySQL string types can store Unicode characters. SQL Server allows storing Unicode characters only in the nchar, nvarchar, and ntext types. This is a specificity of SQL Server work with Unicode. Therefore the UseUnicode option works only for these SQL Server types. When UseUnicode is set to True, all nchar, nvarchar, and ntext parameters and fields are mapped as ftWideString (TWideStringField).

dualsoft · Post by **dualsoft** » Mon 10 Oct 2011 13:12

I have UseUnicode=True at connection and into the procedure the parameter declaration:

Code: Select all

@MesajRaspuns NVARCHAR(200) OUTPUT

But still I can not use ftWideString for parameters creation because I see only Chinese (in my application). If I create parameter with ftString I see correctly what I write into the procedure.

This is my test procedure:

Code: Select all

ALTER PROCEDURE [dbo].[usp_ValidareCodIntern]
(
@IDUnicOferta NVARCHAR(20),
@CodUnicComanda INTEGER,
@TabelOferte NVARCHAR(11),
@CodIntern NVARCHAR(20),
@CodIntern2 NVARCHAR(20),
@MesajRaspuns NVARCHAR(200) OUTPUT,
@Invalid INTEGER OUTPUT
)
AS
  SELECT @MesajRaspuns = 'Mesaj raspuns!';
  SELECT @Invalid = 0;
RETURN 1

dualsoft · Post by **dualsoft** » Tue 11 Oct 2011 09:57

Now I see that when I set StoredProcName from code, parameters in Params are automatically created.
But the problem persist.
In dbMonitor always I see String and not WideString and Unicode characters are replaced with '?' char.
I made a test.
Please modify connection parameters for your database.

Code: Select all

    SQLConnection1: TSQLConnection;
    SQLStoredProc1: TSQLStoredProc;
    Button1: TButton;
    SQLMonitor1: TSQLMonitor;

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
  function ProcedureExists(ProcedureName: String): Boolean;
  var
    SList: TStringList;
  begin
    SList := TStringList.Create;
    try
      SQLConnection1.GetProcedureNames(SList);
      Result := (SList.IndexOf(ProcedureName) >= 0);
    finally
      SList.Free;
    end;
  end;
begin
  SQLConnection1.Params.Add('Database=database');
  SQLConnection1.Params.Add('HostName=server');
  SQLConnection1.Params.Add('User_Name=test');
  SQLConnection1.Params.Add('Password=password');

  SQLConnection1.Connected := True;

  if not ProcedureExists('usp_Test') then
    SQLConnection1.ExecuteDirect(
      'CREATE PROCEDURE usp_Test' + #13#10 +
      '(@Message NVARCHAR(200) OUTPUT)' + #13#10 +
      'AS SELECT @Message = ''My text "Кириллица ĂÎȘȚÂ"!'' RETURN 1');

  SQLStoredProc1.StoredProcName := 'usp_Test';
  SQLStoredProc1.ExecProc;
  ShowMessage(SQLStoredProc1.ParamByName('Message').AsString);

  SQLConnection1.Connected := False;
end;

AndreyZ · Post by **AndreyZ** » Tue 11 Oct 2011 10:48

We have reproduced this problem. We will fix this problem in the next build of dbExpress driver for SQL Server.