About TUniConnection.ExecSQL/ExecSQLEx Params!

[TinTin]

I am using Firebird 2.5.1.xxx,Delphi XE+UniDAC 3.60

const Sqls =
' Update Table1 Set CheckID = :CheckID where ID =:ID ';

Field CheckID and ID is BigInt DataType Field.
TUniConnection.ExecSQL can not support negative params,
Such as TUniConnection.ExecSQL(Sqls,[-100,1]) , it raised error :Unknown Data type.

But I Use TUniConnection.ExecSQL(Sqls,[1,1]) ,it runs normal!

[AndreyZ]

Hello,

Thank you for the information. We have reproduced this problem and fixed it. We are going to release a new UniDAC version today, and we cannot include this fix in it. That's why this fix will be included in the next nearest UniDAC build.

[TinTin]

Do you still have no to fix this bug?(3.70.0.19 19-Jul-11/3.70.0.18 15-Jun-11
)?


---------------------
3.70.0.19 19-Jul-11

Bug in TCustomDADataSet.PSGetKeyFields method when persistent fields are created in dataset is fixed
Bug in TUniTable with creating SQL query when Options.QuoteNames is set to True and provider is changed is fixed
Bug with assertion failure on using master/detail relationship with TClientDataset is fixed
Bug with several connections and when OCIUnicode=True for the Oracle data provider is fixed
Bug with getting ServerList for the PostgreSQL data provider is fixed
Bug with returning "not an error" for the SQLite data provider is fixed.


3.70.0.18 15-Jun-11

Possibility to load data in TVirtualTable without recreating persistent fields is added
Working in design time editor of the TVirtualTable component is improved
Loading files opened by other applications is improved
Bug with the "BAD HANDSHAKE" error on connecting to some versions of MySQL server in the MySQL provider is fixed
Bug with processing key fields on record posting is fixed
Bug with empty string parameters is fixed for ASE data provider
Bug with DateTime parameter is fixed for ASE data provider
Bug with "SQL statement doesn't return rows" is fixed for ASE data provider
Several bugs with design-time editors for Lazarus are fixed

[AndreyZ]

This fix is included in UniDAC 3.70.0.19.

[TinTin]

I found this problem in V4.2.7,
firebid 2.5,Field ID Is bigint datatype.


test code:
procedure TForm10.Button1Click(Sender: TObject);
var
I:Integer;
begin
UniConn.Open;
UniQuery1.Close;
UniQuery1.SQL.Text := 'Select * from SYS_UPGRADE where ID = :ID ';
UniQuery1.ParamByName('ID').Value := 1;
UniQuery1.Open;

if UniQuery1.IsEmpty then
ShowMessage('IsEmpty');
end;

monitor sql:
Connect: SYSDBA@
Start:
Select * from SYS_UPGRADE where ID = :ID
:ID(Byte)=1

=========
if I modify the variant type,it is ok:

procedure TForm10.Button1Click(Sender: TObject);
var
I:Integer;
begin
UniConn.Open;
UniQuery1.Close;
UniQuery1.SQL.Text := 'Select * from SYS_UPGRADE where ID = :ID ';
I := 1;
UniQuery1.ParamByName('ID').Value := i;
UniQuery1.Open;

if UniQuery1.IsEmpty then
ShowMessage('IsEmpty');
end;


Connect: SYSDBA@
Start:
Select * from SYS_UPGRADE where ID = :ID
:ID(Integer)=1

==============
You made a repeat mistake!!!

[ZEuS]

The TParam.Value property is of the Variant type. So when you set the value of the TParam in such a way, Delphi itself decides what type the parameter has, depending on the value (see the TParam.SetAsVariant method implementation in the DB.pas unit).
To avoid such problem you can try one of the following approaches:
- set the parameter value using the TParam.AsLargeInt property instead of TParam.Value;
- in your example, set DescribeParams in the UniQuery1.SpecificOptions property to True and explicitly call the UniQuery1.Prepare method before setting the parameter value. In this case, the correct parameter type will be queried from the server.

[TinTin]

THANKS,ZEuS,But you say wrong!
1.Byte DataType is belong to Integer DataType.
2.TUniParam why not override "TParam.SetAsVariant"?
3. DescribeParams is IB/FB SpecificOptions ,but not in SQLITE/Oracle....Provider....
4.I made a public function with using TUniParam data,so i have to Passes parameters with variant data: TUniParam.Value := InputData....
This is a serious mistakes,All execute SQL are failure.
5.Last Version you can correct it,but inVersion 4.2.7 with using data type mapping,You made a repeat mistake!!!

[ZEuS]

Overriding TParam.SetAsVariant will have no effect. When setting a value to the Variant variable, Delphi itself decides which type the variable has, if there is no explicit type definition. The simple example below demonstrates this behaviour:

Code: Select all

procedure TfMain.bt1Click(Sender: TObject);
var
 v: Variant;
 i: Int64;
begin
 v := 1; // Delphi determines the variable type as Byte
 if VarType(v) = varByte then
   ShowMessage('Variant is Byte');
 v := i; // here we explicitly set the variable type
 i := 1; // and Delphi determines the variable type as BigInt
 if VarType(v) = varInt64 then
   ShowMessage('Variant is BigInt')
end;

So, if there is a need to use the TUniParam.Value property, the only way to obtain the correct parameter type is to execute the Prepare method before setting the parameter value, in order to retrieve the parameter type from the server.
Yes, DescribeParams is an Interbase provider specific option, and you should set it to True in order to retrieve the correct parameter types when executing the Prepare method.

[TinTin]

I Make a MidWare Server with more providers ,so i can not uses "DescribeParams (interbase)" options!TUniStoredProc.PrepareSQL make params with truely data type!But the TUnniQuery have not this method,And TUnniQuery.Params.ParseSQL(Sql,True) only make params with a variant data type!

Maybe you can provider Method of TUnniQuery.PrepareSQL, like TUniStoredProc.PrepareSQL,Or provider a standard method ,dont using "DescribeParams " options!

[ZEuS]

We have already fixed an issue with handling query parameters in the InterBase provider. So, in your example, the code

Code: Select all

UniQuery1.ParamByName('ID').Value := 1;

will work correctly without need to use the 'DescribeParams' option.
The fix will be included in the next UniDAC build.