Prepare statement truncates string parameter

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Prepare statement truncates string parameter

Post by lcoelho » Tue 15 May 2012 14:28

I am migrating Delphi5 BDE projects to use SDAC dataset components. I've come accross the following problem:

- A form with a TMSQuery (MSQuery1) component with the following SQL:
INSERT INTO Table (MyValue) VALUES (:Val)
- A Textbox (Edit1) in which a value is entered in
- A Button that inserts the value of Edit1.text into table 'MyTable'

Here is the code for the button's click event:
with MSQuery1 do
begin
close;
ParamByName('Val').AsString := Edit1.Text;
if not prepared then prepare;
ExecSQL;
end;

When I run the project, the first value I enter into the text box (Edit1) is inserted fine into the table. But, any subsequent values entered into Edit1, are truncated to the size of the first value. e.g. If the first value was 'Bob' and the next value entered is 'Jonathan', then only 'Jon' gets inserted. The problem is solved if I remove the prepare statement.

This is not a problem with BDE TQuery components.

Is there a work around? Is the only solution to remove the 'If not prepared then prepare' statement? If so, would it be safe for me to remove all 'prepare' statements from my project or are there cases where it is required?

Thank you for your kind and diligent assistance.

AndreyZ

Re: Prepare statement truncates string parameter

Post by AndreyZ » Wed 16 May 2012 12:12

Hello,

When any SQL statement is prepared on the server, the size of its parameters is fixed on the server. Your code prepares your SQL statement only once, and the size of the first value is used for the next executions. In this case SDAC sends full values, but SQL Server truncates them to the size fixed on preparation. To avoid such problem, you can use two ways:
- set the TMSQuery.Options.DescribeParams property to True. In this case SDAC will fill the correct size (received from SQL Server) for all parameters. Here is a code example:

Code: Select all

with MSQuery1 do begin
  if not Prepared then begin
    Options.DescribeParams := True;
    Prepare;
  end;
  ParamByName('Val').AsString := Edit1.Text;
  ExecSQL;
end;
- do not prepare your statements. Here is a code example:

Code: Select all

with MSQuery1 do begin
  ParamByName('Val').AsString := Edit1.Text;
  ExecSQL;
end;

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: Prepare statement truncates string parameter

Post by lcoelho » Wed 16 May 2012 13:25

Thanks for the reply.

I just have the following questions regarding your recommendations:

1. In your example, you put the 'if not prepared then...' before the 'ParamByName...'.
If the 'prepare' statement is before the ParamByName... statement, it works fine, no trucation is done, even without using Options.DescribeParams. The problem only occurs when the 'prepare' statement is after the ParamByName....

2. If you leave the Prepare after the ParamByName statement and use the Options.DescribeParams, the first value inserted into the table is NULL. Why does this happen?

Try it:
with MSQuery1 do begin
ParamByName('Val').AsString := Edit1.Text;
if not Prepared then begin
Options.DescribeParams := True;
Prepare;
end;
ExecSQL;
end;
(The above will insert NULL as the first value. Subsequent values work fine). This is could be a big problem if someone is not aware of it. Why does this occur?

3. If I were to choose your 2nd solution, i.e removing Prepare statement, could I do this for all queries in the project? Will removing 'prepare' everywhere cause a problem elsewhere? This would be the easiest option since there are a few projects that need to be migrated with lots of code.

4. Seeing that the problem does not happen when the 'prepare' statement is before the 'ParamByName....', could that also be a solution i.e. place the 'prepare' before setting 'paramByName' values?

Kind regards

AndreyZ

Re: Prepare statement truncates string parameter

Post by AndreyZ » Thu 17 May 2012 12:52

1. If the value of a parameter is set before preparation and TMSQuery.Options.DescribeParams is False, the parameter value size is used as the fixed size of the parameter on the server. That's why you should prepare statements before setting parameters.
2. TMSQuery.Options.DescribeParams fills the DataType parameters property. If DataType retrieved from the server doesn't match the one in the query, the value of such parameter is set to Unassigned. To solve the problem in this case, you should prepare statements before setting parameters, or set the correct DataType for parameters before preparation. Your code sets ParamByName('Val').DataType to ftString. If the MyValue field on the server is nvarchar, SQL Server will return ftWideString for the Val parameter. You can check this by executing the following code:

Code: Select all

with MSQuery1 do begin
  ParamByName('Val').AsWideString := Edit1.Text;
  if not Prepared then begin
    Options.DescribeParams := True;
    Prepare;
  end;
  ExecSQL;
end;
3. Removing preparation in your project may cause some unexpected problems in your project migrated from BDE, so we do not recommend you this way.
4. Yes, preparing statements before setting theirs parameters is the solution you should use. If you are not sure about the correct parameter properties (like ParamType, DataType, Size, etc.) you set, you should also set the TMSQuery.Options.DescribeParams to True before preparation.

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: Prepare statement truncates string parameter

Post by lcoelho » Wed 23 May 2012 04:33

Thanks for the detailed reply.

I just have another question. When using a StoreProcedure with parameters, we use 'PrepareSQL' before making use of the ParamByName property. Would I also need to use the standard 'Prepare' statement for these (storedprocedures) or is this only for Queries?

Thank you and kind regards.

AndreyZ

Re: Prepare statement truncates string parameter

Post by AndreyZ » Wed 23 May 2012 07:48

The TMSStoredProc.PrepareSQL method builds a query for TMSStoredProc based on the TMSStoredProc.Params and TMSStoredProc.StoredProcName properties, and assign it to the TMSStoredProc.SQL property. Generated query is then verified to be valid and, if necessary, the list of parameters is modified.
The TMSStoredProc.Prepare method prepares a query on the server. Calling TMSStoredProc.Prepare before executing a query improves application performance. Actually, TMSStoredProc.Prepare calls TMSStoredProc.PrepareSQL to get a valid query before its executing.
So, if you want only to get parameter list of a stored procedure without its preparing on the server, you should call TMSStoredProc.PrepareSQL . If you want to execute a stored procedure several times, you should call TMSStoredProc.Prepare .

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: Prepare statement truncates string parameter

Post by lcoelho » Wed 23 May 2012 08:44

Thanks AndreyZ, that is very clear.

So, apart from the time overhead, there should be no harm in having the following:

with spCCInsertActionLog do
begin
close;
if not prepared then Prepare;
PrepareSQL;
ParamByName('ActionType').asString := Edit1.Text;
ParamByName('CompleteYN').asString := 'Y';
execproc;
...

AndreyZ

Re: Prepare statement truncates string parameter

Post by AndreyZ » Wed 23 May 2012 14:52

TMSStoredProc.Prepare calls TMSStoredProc.PrepareSQL to get a valid query before its executing. So, you can use the following code:

Code: Select all

with spCCInsertActionLog do begin
  Close;
  if not Prepared then
    Prepare;
  ParamByName('ActionType').AsString := Edit1.Text;
  ParamByName('CompleteYN').AsString := 'Y';
  ExecProc;
...
But if you are not sure whether parameters were changed or not after preparation, you can use the following code:

Code: Select all

with spCCInsertActionLog do begin
  Close;
  if not Prepared then
    Prepare
  else
    PrepareSQL;
  ParamByName('ActionType').AsString := Edit1.Text;
  ParamByName('CompleteYN').AsString := 'Y';
  ExecProc;
...

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: Prepare statement truncates string parameter

Post by lcoelho » Wed 23 May 2012 18:39

Thanks AndreyZ,

I understand your comment but would there be any danger in having both statements one after the other like in my example? I need to do a automated insertion of 'Prepare' in all parapaterized queries and stored procedures. I am doing this after the 'Close' statement. So, in the cases where a stored procedure is involved, there may already be an PrepareSQL. So, will it be okay if there is both Prepare and PrepareSQL?

AndreyZ

Re: Prepare statement truncates string parameter

Post by AndreyZ » Thu 24 May 2012 08:50

Yes, you can use both statements.

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: Prepare statement truncates string parameter

Post by lcoelho » Thu 24 May 2012 14:58

Thank you very much for your kind assistance with this

AndreyZ

Re: Prepare statement truncates string parameter

Post by AndreyZ » Fri 25 May 2012 06:53

Feel free to contact us if you have any further questions about SDAC.

Post Reply