TMSParam.Size

[Lithium™]

Hello!

I use TMSSQL to execute a query which returns a string.
I don't know size of returned string. It can be small or huge. Please advice me how should I configure output param type and size.

I've already tried to do the following:
Paramtype=ptResult, DataType=ftString, size=2147483648 - when TMSSQL executes it raises an error "Out of memory";
Paramtype=ptResult, DataType=ftMemo(or ftBlob) - when TMSSQL executes it raises an error "Bad parameter. Output parameters text, ntext or image are not acceptable";

Thanks.

[AndreyZ]

Hello,

To solve the problem, you should set the TMSSQL.DescribeParams property to True and call the TMSSQL.Prepare method before query executing. Here is a code example:

Code: Select all

MSSQL.DescribeParams := True;
MSSQL.SQL.Text := 'query';
MSSQL.Prepare;
// setting values of input parameters
MSSQL.Execute;

In this case, the correct values of the Name, ParamType, DataType, Size, and TableTypeName properties of all parameters will be obtained from the server. For more information, please refer to the SDAC documentation.

[Lithium™]

Hello AndreyZ!

Thank you for reply! I've read the SDAC documentation, there isn't anything else there but you wrote above about TMSSQL.DescribeParams property.
I followed your advice exactly. Please look at my results:

Code: Select all

cmd := TMSSQL.Create(Sender);
cmd.Assign(cmdOriginalByContinuousTemplate);
// please notice that property cmdOriginalByContinuousTemplate.DescribeParams is True
// and TMSSQL.Assign() doesn't assign this property (is it correct behavior?) so I do it manually
cmd.DescribeParams := True;
// set macros values and states
cmd.Prepare;  // preparing raises error "Syntax error, permission violation, or other nonspecific error.", it occurs without sending anything to SQL server
// set input param values
cmd.Execute;

My query performs correctly if I comment call of the TMSSQL.Prepare method. The SDAC documentation says: SQL statements which have output parameters and aren't stored procedures calls or some of system functions such as sp_setapprole, should be executed without prior call to the Prepare method.

So, as I understand, in order to use Prepare I need to move my query into stored proc and then to call stored proc and to get output params from stored proc. I did it. Take a look on cmd.SQL.Text:

Code: Select all

--some statements are here

-- old version
-- set :KeyPlans = @KeyPlans 

-- new version
exec [dbo].[test_ReturnParam] @Input = @KeyPlans, @Output = :KeyPlans output

The [dbo].[test_ReturnParam] stored proc has very simple code:

Code: Select all

CREATE PROCEDURE [dbo].[test_ReturnParam]
	@Input	varchar(max),
	@Output	varchar(max) output
AS
BEGIN
	set @Output = @Input
END

But cmd.Prepare is still raising error "Syntax error, permission violation, or other nonspecific error."
Do you have any ideas?

[AndreyZ]

TMSSQL.Assign() doesn't assign this property (is it correct behavior?)

Thank you for the information. We have fixed this problem.

The error occurs because you are using the wrong SQL statement. To avoid the problem, you can use one of the following ways:

Code: Select all

MSSQL.DescribeParams := True;
MSSQL.SQL.Text := 'exec [dbo].[test_ReturnParam] @Input = :Input, @Output = :Output output';
MSSQL.Prepare;
MSSQL.ParamByName('Input').AsString := 'testing';
MSSQL.Execute;
ShowMessage(MSSQL.ParamByName('Output').AsString);

, or

Code: Select all

MSSQL.DescribeParams := True;
MSSQL.CreateProcCall('[dbo].[test_ReturnParam]');
MSSQL.Prepare;
MSSQL.ParamByName('Input').AsString := 'testing';
MSSQL.Execute;
ShowMessage(MSSQL.ParamByName('Output').AsString);

[Lithium™]

MSSQL.SQL.Text := 'exec [dbo].[test_ReturnParam] @Input = :Input, @Output = :Output output';

Ok, I have tested it. It realy works, if MSSQL.SQL.Text has only call of stored proc, as you wrote above. But I still need some statements to calculate result string before stored proc call. If I put into MSSQL.SQL.Text anything except stored proc call (even a comment!):

Code: Select all

-- anything
exec [dbo].[test_ReturnParam] @Input = :Input, @Output = :Output output

the error occurs.
I wouldn't like to move query completely into stored proc beacause I would like to use TMSSQL functionality. Moreover it's bad practice to create stored proc for every query, isn't it?.

Let me return to begining of post. Is there a way to execute my query (not only stored proc) with TMSSQL and to get result param as string of unknown size?

[AndreyZ]

To execute such query, you should set the parameter data type to the correct blob type and set the parameter type to the output type. Here is a code example:

Code: Select all

MSSQL.SQL.Text := 'exec [dbo].[test_ReturnParam] @Input = :Input, @Output = :Output output';
MSSQL.ParamByName('Input').AsString := 'testing';
MSSQL.ParamByName('Output').DataType := ftMemo;
MSSQL.ParamByName('Output').ParamType := ptOutput;
MSSQL.Execute;
ShowMessage(MSSQL.ParamByName('Output').AsString);

[Lithium™]

Thank you for reply. Now I can see that matter is in choice between ptResult and ptOutput.
I did as you said:

Code: Select all

cmd.ParamByName('KeyPlans').ParamType := ptOutput;
cmd.ParamByName('KeyPlans').DataType  := ftMemo;

but I got another error:
"Assertion failure (D:\Projects\Dac\SqlServer\Source\OLEDBAccess.pas, line 6476)"
There isn't drive with letter "D:" on my machine, I think it is yours.
I'm waiting for next instructions.

[Lithium™]

I've simplified example as far as possible. Take a look please.

Code: Select all

cmd := TMSSQL.Create(Sender);
cmd.SQL.Text := 'set :KeyPlans = convert(varchar(max), ''test'') ';
cmd.ParamByName('KeyPlans').ParamType = ptOutput;
cmd.ParamByName('KeyPlans').DataType = ftMemo;
cmd.Execute

After execute an error occurs depending on ParamType:
ptOutput - "Assertion failure (D:\Projects\Dac\SqlServer\Source\OLEDBAccess.pas, line 6476)"
ptResult - "Bad parameter. Output parameters text, ntext or image are not acceptable"
ptInputOutput - "At least one parameter can not be derived from the binding info. Set parameter information first"

[AndreyZ]

The problem with using ptOutput parameters does not occur with the latest SDAC version 6.7.14. To avoid this problem, you should upgrade SDAC at least to this version.
We have reproduced and fixed the problem with using ptResult parameters. This fix will be included in the next SDAC build.
We have reproduced the problem with using ptInputOutput parameters. We are investigating this problem. As soon as we have any results, we will let you know.

[Lithium™]

To use ptOutput I have upgraded SDAC to version 6.7.14 as you said. Error doesn't disappear. I have been shown another message "Assertion failure (D:\Projects\Dac\SqlServer\Source\OLEDBAccess.pas, line 6665)".
Is it possible that versions of RAD and OS do matter? Currently I have installed SDAC Version 6.7.14 for RAD Studio XE2, Delphi XE2 Update4 Hotfix1, WindowsXP Professional.

On the way I glad to tell you that other problem with particular call of TMSStoredProc have been solved with latest 6.7.14. I was going to start new post about the day before.

[AndreyZ]

I cannot reproduce the problem with ptOutput parameters using Delphi XE2 Update 4 Hotfix 1 and SDAC 6.7.14 under Windows XP. Most probably, the problem occurs because there are files from the old SDAC version on your computer. Please try the following:
- remove SDAC;
- remove all *dac*.bpl and *crcontrols*.bpl files from your computer;
- remove *.dcu files from your project;
- install SDAC 6.7.14.
Does the problem persist after this? If yes, please specify the exact version of SQL Server server and client you are using. You can learn it from the Info sheet of TMSConnection Editor.

[Lithium™]

Hello, AndreyZ!
I've performed all instructions. The problem has not been solved. Info sheet of TMSConnection Editor displays the following:
Microsoft SQL Server: 10.00.1600
Microsoft SQL Server Native Client 10.0: 10.0.1600.22

When I was trying with TMSConnection I had noticed that TMSConnection.LoginPrompt property stopped work at design time. I always set this property to False and use Authentication = auWindows but with last update 6.7.14 the window with connection params opens independently of my settings.

[AndreyZ]

I have reproduced the problem. It is caused by the fact that SQL Server versions earlier than 2012 do not support output parameters of stream types. Varchar(max) is considered as a stream type. To solve the problem, you can use the following code:

Code: Select all

begin
  cmd.SQL.Text := 'set :KeyPlans = convert(varchar(max), ''testing'') ';
  cmd.ParamByName('KeyPlans').ParamType := ptOutput;
  cmd.ParamByName('KeyPlans').DataType := ftString;
  cmd.Execute;
  ShowMessage(cmd.ParamByName('KeyPlans').AsString);
end;

[Lithium™]

cmd.ParamByName('KeyPlans').DataType := ftString;

If I do that, the default size of returned string will be about 4000 or 8000 bytes (I think it depends on whether string type is unicode or not).
So if actual size of returned string will be more, it will be truncated. This is unacceptable. In the first post I wrote, that if I set any great number to TMSParam.Size I will get an error "Out of memory".

It is caused by the fact that SQL Server versions earlier than 2012 do not support output parameters of stream types. Varchar(max) is considered as a stream type.

To solve the problem I need to upgrade SQL Server up to 2012 or to use TMSQuery with resultset instead of TMSSQL with output params.
Thank you for answers!

... and what about TMSConnection.LoginPrompt property? Did you try it?

[AndreyZ]

To solve the problem I need to upgrade SQL Server up to 2012 or to use TMSQuery with resultset instead of TMSSQL with output params.

That is correct.

and what about TMSConnection.LoginPrompt property?

We have fixed this problem. This fix will be included in the next SDAC build.