I have an MS SQL output variable that's NVARCHAR(MAX) and it works fine when I test it in a backend call and I know it would work find in any vb.net code I'd write because I've done this many times there but when I try to get its value using TMSStoredProc I get nothing. It comes back as a zero length string. If I change it to NVARCHAR(1000) on the backend I get a correct result but I don't want that. I want NVARCHAR(MAX). On the front end I do exactly as in the SDAC example code for getting an output variable from a stored procedure. What do I need to do to be able to handle "MAX" with TMSStoredProc?
Thanks,
Keith
TMSStoredProc and NVARCHAR(MAX)
Re: TMSStoredProc and NVARCHAR(MAX)
Hi,
Unfortunately, we couldn't reproduce the issue. To investigate this behavior of SDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html
Best regards,
Sergey
Unfortunately, we couldn't reproduce the issue. To investigate this behavior of SDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html
Best regards,
Sergey
Re: TMSStoredProc and NVARCHAR(MAX)
Just in case this helps you answer this (before I take the time to create an entire sample project), here's my front end code. When @iudErrors in the backend is NVARCHAR(MAX) (OUTPUT variable) I get what I explained in my OP. I tried "AsWideString" as well but same result.
sp.Execute;
iudErrors := sp.Params.ParamByName('@iudErrors').AsString;
if iudErrors <> '' then
begin
Windows.MessageBox(Self.Handle, PChar(iudErrors), PChar(sAppTitle), MB_ICONEXCLAMATION + MB_OK);
end;
Does this help you at all? This seems like it should be something simple. I'm using Delphi 10.2 and SDAC 8.0.5
sp.Execute;
iudErrors := sp.Params.ParamByName('@iudErrors').AsString;
if iudErrors <> '' then
begin
Windows.MessageBox(Self.Handle, PChar(iudErrors), PChar(sAppTitle), MB_ICONEXCLAMATION + MB_OK);
end;
Does this help you at all? This seems like it should be something simple. I'm using Delphi 10.2 and SDAC 8.0.5
Re: TMSStoredProc and NVARCHAR(MAX)
I don't confirm:
Tested on Lazarus 2.3 with FPC 3.2.2 x86, Windows 10 x64, SDAC 10.0.2 and MSSQL 2017
Code: Select all
uses
MSAccess;
procedure TForm1.Button1Click(Sender: TObject);
var
conn: TMSConnection;
q: TMSQuery;
sp: TMSStoredProc;
i: Integer;
begin
Memo1.Lines.Clear;
conn := TMSConnection.Create(nil);
conn.Server := '.';
conn.Database := 'testdb';
conn.Username := 'sa';
conn.Password := '123';
conn.Connect;
q := TMSQuery.Create(nil);
q.Connection := conn;
q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_max'') ');
q.SQL.Add(' drop procedure proc_test_max ');
q.ExecSQL;
q.SQL.Clear;
q.SQL.Add(' create procedure proc_test_max @repeat int, @text nvarchar(max) output ');
q.SQL.Add(' as ');
q.SQL.Add(' begin ');
q.SQL.Add(' declare @i int=0 ');
q.SQL.Add(' select @text='''' ');
q.SQL.Add(' while @i<@repeat ');
q.SQL.Add(' select @text=@text+''sample text '', @i=@i+1 ');
q.SQL.Add(' end ');
q.ExecSQL;
sp := TMSStoredProc.Create(nil);
sp.Connection := conn;
sp.StoredProcName := 'proc_test_max';
sp.ParamByName('repeat').AsInteger := 500;
sp.Execute;
for i := 0 to sp.Params.Count - 1 do
begin
Memo1.Lines.Add('Param index: ' + IntToStr(i));
Memo1.Lines.Add('Param name: ' + sp.Params[i].Name);
Memo1.Lines.Add('Param value: ' + sp.Params[i].Text);
Memo1.Lines.Add('====================================');
end;
sp.Free;
q.SQL.Clear;
q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_max'') ');
q.SQL.Add(' drop procedure proc_test_max ');
q.ExecSQL;
q.Free;
conn.Disconnect;
conn.Free;
end;
Re: TMSStoredProc and NVARCHAR(MAX)
I figured it out. The way you set up your connection object gave me an idea. I have a TMSConnection object (for new things) on my main data form along with my original TADOConnection object. I was using the same connection string for both of them. The TMSConnection object connected just fine and most things worked fine but something in my connection string made TMSConnection unhappy so I simplified it and it solved the entire problem.