Calling stored procedure from TQuery object

[Roger]

We are migrating our application from BDE to SDAC, and it works overall very well. Where there are differences between BDE and SDAC in the behaviour we can at some points consider them ok, but we have some issues that we want to have some feedback on if it is possible.

Issue#1

Code: Select all

  // Query : TMSQuery
  Query->Close();
  Query->SQL->Text = "exec spStroredProc :Param";
  for(int i = 0; iParamByName("Param")->AsString = i;
    Query->ExecSQL();
  }

when we execute this code, the parameter :Param will always be 0. If we change the SQL a little (adding a space for example) for each loop, it works.


Issue#2
When using CreateParam in the parameter class together with a stored procedure object, it will not work.

Code: Select all

  //StoredProc : TMSStoredProc
  StoredProc->StoredProcName = "spStoredProc"
  StoredProc->Params->Clear();
  StoredProc->Params->CreateParam(ftInteger, "@Param", ptInput);
  StoredProc->Params->ParamByName("@Param")->AsInteger = 1;
  StoredProc->ExecProc(); //If the param is required, an error will be thrown.

Issue#3

Under some conditions (have not found any pattern yet) string parameters get truncated on the way down to the database. we resolve it through always setting the size after assigning the string:

Code: Select all

  //SDACParam : TMSParam
  SDACParam->AsString = newValue;
  SDACParam->Size = newValue.Length();

Issue #4
When using a dataset with FetchAll=false and the fast forward cursor, RecordCount only reflects the number of records currently read into the dataset. Is it possible that this will be changed in the future, so it reflects the number of records that can be fetched ?

Issue #5
When using TMSMetadata in order to get Primary keys, it sometimes does not return it correctly. Some of our tables that have more than one primary key still only returns the first one. The strange thing is that other tables that some have more than one primary key works. When removing and adding the PK:s a few times, it suddenly starts to work. We changed and started to read indexes instead, and checked which where primary keys. Any ideas what this can be ?


Issue #6
The SDAC stored procedure class behaves differently from BDE when re-assigning a StoredProcName. BDE did a Param check even when the New name was the same as the old. SDAC does not. If the user does

Code: Select all

   sp->StoredProcName = "AStoredProcedureName"
   sp->Params->Clear()
   sp->StoredProcName = "AStoredProcedureName";

the stored procedure will fail. Therefore setting the storedprocname to something differently, so we force a param check.

Code: Select all

if(NewName == SDACStoredProc->StoredProcName)
   SDACStoredProc->StoredProcName = "DUMMYSTOREDPROCNAME";
SDACStoredProc->StoredProcName = NewName;

We are using Borland C++Builder 5.0 Update 1
Microsoft SQL Server 2000
SDAC 3.00.2.9
Microsoft SQL Server: 08.00.0760
Microsoft OLE DB Provider for SQL Server: 08.50.1022

thanks in advance

Roger Månsson

[Ikar]

> Issue#1

We couldn't reproduce the problem.
Please send us complete small sample to demonstrate it and include script to create spStroredProc.

By the way, in OLEDB and SDAC text of calling StoredProc is generated with another syntax. Certainly you can use this one but is it less effective.

> Issue#2

If at the moment of setting TMSStoredProc.StoredProcName property a connection to the server has already been established then parameters and a text of the query will be filled automatically. It is more convenient to set StoredProcName at design-time.

If you would like to set parameters manually use property ParamCheck := False

> Issue#3

This problem can happen if set Param.Size and pass a value longer than Param.Size by Param.Value.

> Issue#4

Set Options.QueryRecCount := True.

> Issue#5

Try using Enterprise Manager to generate full scripts for these both tables. Probably they will help to get an answer.

> Issue#6

It allows to optimize a number of connections to the server. To renew a list of parameters you should use an empty name of the procedure that let avoid unnecessary reference to the server.

[Roger]

Thank you for your reply.

> Issue#1

We couldn't reproduce the problem.
Please send us complete small sample to demonstrate it and include script to create spStroredProc.

By the way, in OLEDB an SDAC text of calling StoredProc is generated with another syntax. Certainly you can use this one but is it less effective.

I have now found a pattern. If you call Execute() in a query that returns a result set, you must use Close() after, otherwise the new parameters are not applied if the SQL statement not has changed.

Code: Select all

  // Query : TMSQuery 
  Query->Close(); 
  Query->SQL->Text = "insert into table (col1) values(:col1) \n select 0"

  for(int i = 0; iParamByName("Param")->AsString = i; 
    Query->Execute(); 
    // If we call Query->Close() here it will work.
  }

This will result in three 0 -values in col1. Now that we know of it it is not a problem, since we can handle it in our data access layer.


> Issue#2

If at the moment of setting TMSStoredProc.StoredProcName property a connection to the server has already been established then parameters and a text of the query will be filled automatically. It is more convenient to set StoredProcName at design-time.

If you would like to set parameters manually use property ParamCheck := False

We solved this issue by prohibit the use of CreateParam, so it is actually not a problem to us anymore.

> Issue#3

This problem can happen if set Param.Size and pass a value longer than Param.Size by Param.Value.

Yes, but somehow, sometimes, the Size param seems to be set implicitly, but now I can't reproduce it of course. We set the Size property in our data access layer just to be sure.

> Issue#4

Set Options.QueryRecCount := True.

I have not noticed this option before. Thanks!

> Issue#5

Try using Enterprise Manager to generate full scripts for these both tables. Probably they will help to get an answer.

No, they seem quite normal. But searching for PK:s as indexes works fine, so I'll stick to that.

> Issue#6

It allows to optimize a number of connections to the server. To renew a list of parameters you should use an empty name of the procedure that let avoid unnecessary reference to the server.

Yes. Thanks for the suggestion.

[Ikar]

> Issue#1
> If you call Execute() in a query that returns a result set, you must
> use Close() after, otherwise the new parameters are not applied if
> the SQL statement not has changed.


Please specify if you use FetchAll = False.

[Roger]

The behaviour is the same for both true and false on FetchAll.

[Ikar]

Thank you for information. We reproduced your problem and fixed it. This fix will be included in the next SDAC build.