Consume SQLserver varchar(1000) in Delphi

Question

i am working on delphi 7 and SQL server 2008 R2.

Sql server stored procedure is returning a varchar(1000) and delphi is not able to catch varchar(1000) it is able to catch less than 300 characters only. i am using TStoredProc component.

is it posible to catch varchar(1000) string in delphi or do i need to go with varbinary?

how to catch varbinary type in delphi?

SQL server Sample code

create procedure test
as
select testdata = convert( varchar(1000),'thousand characters data')

if i am returning varbinarydata how to catch that in delphi

create procedure test
as
select testdata = convert( varbinary(1000),'thousand characters data')

Below is my delphi code to load varbinary field and i am getting invalid class type cast and i am stuck here :(

button click
var
    blob: TStream;
begin
  fProcDesc := TStoredProc.Create(self);
  fProcDesc.DatabaseName := fDatabaseName;
  fProcDesc.StoredProcName := 'dbo.test';
  fProcDesc.open ;
  blob := fProcDesc.CreateBlobStream(fProcDesc.FieldByName('testdata'),bmWrite) //Invalid class typecast

end

Answer 1

"is it posible to catch varchar(1000) string in delphi " Yes it is. I think the reason that you seem to doubt it is that your test is flawed.

Down below is the full source and DFM extract of a D7 application which accesses Sql Server using Ado components, and shows conclusively that D7 can handle long strings to and from Sql Server. It returns a string of 2048 As. So does the equivalent project written to use the BDE. So your problem lies elsewhere. I hope, btw, the project shows how easy it is to do simple Sql Server access using Ado.

Next I modified the project above to execute your "test" stored proc. The length of the returned string is 24, just as I'd expect given that the length of the string 'thousand characters data' is 24. Doing your VarChar call to the Convert() function in your SP makes no difference to this because the 'Var' in 'VarChar' means 'variable length', i.e. not padded to the defined width of 1000.

If I change the definition of your 'test' SP to read

select testdata = convert( char(1000),'thousand characters data')

then the length of the returned string in both my Ado and BDE projects is 1000, which is exactly what I'd expect.

You said earlier you'd spent 4 hours trying w/o success to find your problem. You could try copying your project to a new folder, add a TAdoConnection and global search and replace its .Pas and .Dfm files to replace your BDE components with their Ado equivalents. See how far you get in another four hours.

StoredProc:

CREATE PROCEDURE [dbo].[spTestLongString](@input varchar(2048))
AS
BEGIN
  SET NOCOUNT ON;
  SELECT Upper(@Input)
END

Code:

uses
  Windows, Messages, SysUtils,  Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, StrUtils, DB, ADODB;

type
  TForm1 = class(TForm)
    btnOpen: TButton;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    Memo1: TMemo;
    procedure btnOpenClick(Sender: TObject);
  private
    procedure TestStoredProc;
  public
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.TestStoredProc;
var
  S1,
  S2 : String;
begin
  S1 := DupeString('a', 2048);
  AdoQuery1.SQL.Text := 'dbo.spTestLongString @input = :input';
  AdoQuery1.Parameters.ParamByName('input').Value := S1;
  AdoQuery1.Open;
  S2 := AdoQuery1.Fields[0].AsString;
  Memo1.Lines.Text := Format('Len:%d'#13#10'value:%s', [Length(S2), S2]);
end;

procedure TForm1.btnOpenClick(Sender: TObject);
begin
  TestStoredProc;
end;

DFM:

  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString =
      'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security In' +
      'fo=False;Initial Catalog=MATest;Data Source=mat410\ss2014'
    LoginPrompt = False
    Provider = 'SQLOLEDB.1'
  end
  object ADOQuery1: TADOQuery
    Connection = ADOConnection1
    CursorLocation = clUseServer
    Parameters = <
      item
        Name = 'input'
        Attributes = [paNullable]
        DataType = ftString
        NumericScale = 255
        Precision = 255
        Size = 2048
        Value = Null
      end>
    Prepared = True
    SQL.Strings = (
      'dbo.spTestLongString @input = :input)
    Left = 80
    Top = 16
  end
end