I'm using D3 and MSSQL 6.5 Enterprise Edition and I have a problem when
I send more than 255 characters to a stored procedure in MSSQL. I have
two fields defined as "Text" in MSSQL and I'm using TStoredProc with the
Params Data type set by Delphi as "String". The two fields are Problem
and Solution which are Param 4 and 5 respectively. In the first trace,
Problem.Text := "1234567890" and Solution.Text := "Solution Text". With
this data the stored procedure works properly.
------8>< trace 1 start ><8-------
Param = 2, Name = @CallID, Type = fldINT32, Precision = 0, Scale = 0,
Data = 6983
Param = 3, Name = @RespLifeNo, Type = fldZSTRING, Precision = 4, Scale =
0, Data = 2501
Param = 4, Name = @Problem, Type = fldZSTRING, Precision = 10, Scale =
0, Data = 1234567890
Param = 5, Name = @Solution, Type = fldZSTRING, Precision = 14, Scale =
0, Data = Solution Text.
Param = 6, Name = @ProbDate, Type = fldTIMESTAMP, Precision = 0, Scale =
0, Data = 7/17/1998 12:7:0:0
Param = 7, Name = @SolDate, Type = fldTIMESTAMP, Precision = 0, Scale =
0, Data = 7/17/1998 12:6:0:0
Param = 8, Name = @Cat, Type = fldZSTRING, Precision = 1, Scale = 0,
Data = 5
Param = 9, Name = @SubCat, Type = fldZSTRING, Precision = 1, Scale = 0,
Data = 0
Param = 10, Name = @Stat, Type = fldZSTRING, Precision = 1, Scale = 0,
Data = 0
Param = 11, Name = @Pri, Type = fldZSTRING, Precision = 1, Scale = 0,
Data = 2
Param = 12, Name = @ResolveTime, Type = fldINT32, Precision = 0, Scale =
0, Data = 5
Param = 13, Name = @CallSiteID, Type = fldZSTRING, Precision = 3, Scale
= 0, Data = MTL
Reset
Set statement type
Result = dbo.uhd_HelpDeskCall;1 :@CallID, :@RespLifeNo, :@Problem,
:@Solution, :@ProbDate, :@SolDate, :@Cat, :@SubCat, :@Stat, :@Pri,
:@ResolveTime, :@CallSiteID,
------8>< trace 1 end ><8-------
In the next trace, Problem.Text := 123456789012 123456 for a total
of 256 characters. Param 4 is not even seen in the trace and the MSSQL
stored procedure stores what seems to be three character pointer and the
data is lost.
------8>< trace 2 start ><8-------
Param = 1, Name = Result, Type = fldINT32, Precision = 0, Scale = 0,
Data = NULL
Param = 2, Name = @CallID, Type = fldINT32, Precision = 0, Scale = 0,
Data = 6983
Param = 3, Name = @RespLifeNo, Type = fldZSTRING, Precision = 4, Scale =
0, Data = 2501
Param = 5, Name = @Solution, Type = fldZSTRING, Precision = 14, Scale =
0, Data = Solution Text.
Param = 6, Name = @ProbDate, Type = fldTIMESTAMP, Precision = 0, Scale =
0, Data = 7/17/1998 12:7:0:0
Param = 7, Name = @SolDate, Type = fldTIMESTAMP, Precision = 0, Scale =
0, Data = 7/17/1998 12:6:0:0
Param = 8, Name = @Cat, Type = fldZSTRING, Precision = 1, Scale = 0,
Data = 5
Param = 9, Name = @SubCat, Type = fldZSTRING, Precision = 1, Scale = 0,
Data = 0
Param = 10, Name = @Stat, Type = fldZSTRING, Precision = 1, Scale = 0,
Data = 0
Param = 11, Name = @Pri, Type = fldZSTRING, Precision = 1, Scale = 0,
Data = 2
Param = 12, Name = @ResolveTime, Type = fldINT32, Precision = 0, Scale =
0, Data = 5
Param = 13, Name = @CallSiteID, Type = fldZSTRING, Precision = 3, Scale
= 0, Data = MTL
Reset
Set statement type
:Result = dbo.uhd_HelpDeskCall;1 :@CallID, :@RespLifeNo, :@Problem,
:@Solution, :@ProbDate, :@SolDate, :@Cat, :@SubCat, :@Stat, :@Pri,
:@ResolveTime, :@CallSiteID,
------8>< trace 2 end ><8-------
I have tried changing the Data type in Delphi but the data is still lost
when more than 256 characters are entered. Using the SQL Query in MSSQL
I've been able to use the stored procedure and store more than 255
characters.
When I've used TQuery I never had a problem. I'm assuming either I'm
not setting something correctly in the Delphi TStoredProc since it works
with the TQuery or there's a bug with TstoredProc. Does anyone have any
ideas or come across this problem?
Thanks,
Mark Comeau
Lockheed Martin Canada
(514) 340-8310 x8600
mark.com...@lmco.com
