empty string and null

[lao]

Hi,
With the bde when i execute the sql query like this:
select * from mytable where myfield =''
the result return rows where myfield ='' and myfield is null.
With unidac returned rows contains only where myfield =''.
is it possible to return rows where myfield is empty and null?
i don't like to do :
select * from mytable where myfield ='' and myfield is null
because i migrate from bde and the code of my application is very big
and sometime the empty string is in sql, sometime is in the filter of query or table.
i try the option setEmptyStrToNull without success.
i use unidac 4.5.9 with sources code.

Thanks

[AlexP]

Hello,

Please specify the name and version of the DB you work with, and the ODBC driver you used for BDE connection

[lao]

Hi,
thank you for your response;
with the bde i work with interbase and use tdatabase to connect database(no odbc).
i try unidac with interbase and postgresql and it's same, return only where myfield =''
thanks

[AlexP]

Hello,

We checked the behavior of BDE when performing such a query ( select * from mytable where myfield =" ) in InterBase and PostgreSQL, and the results are similar to UniDAC: when where myfield =", rows with a empty myfield value, and when myfield is null - rows with a null value in this field.
The TDataBase component uses an installed PostgreSQL ODBC driver and configured DSN to connect to PostgreSQL, to connect to InterBase, both ODBC and the standard drivers can be used, and in all these cases, the results of the query agreed with UniDAC

[lao]

Hi,
Sorry, i try again and the problem occur only when we use filter:
sql: select * from mytable;
filter: myfield='';
in the bde this return empty and null.
in unidac this return only empty.

and, i am not investigate a long time, but when i use fetchall = false and queryreccount=true with postgresql
when the query is filtered the returned recordcount is wrong.
Thanks

[AlexP]

Hello,

The behavior of the filter in UniDAC is correct, as the server logic is used in the selection of records on the condition NULL and "null string", and this behavior will not change.
When using the Filter property, independently on the FetchAll option value, all data is loaded out from the client. After applying the filter, the RecordCount property displays the number of records included in the filter condition

[lao]

hi,
the problem of recordcount occur when:
in object inspector enter filter condition.
in object inspector set filtered to true.
add button to open the query and get recordcount.
the recordcount is wrong.
if you do query.last and get recordcount the result is good.
the error ocur only if you set filtered = true in design time.
regards

[AlexP]

Hello,

We couldn't reproduce the problem performing the steps you described. Please send a small sample reproducing the problem and the script for creating and filling the table to alexp*devart*com

[lao]

hi,
i forget to give you some informations:
on a form put: 1 uniconnection, 1 uniquery, 1 button,1 PostgreSQLUniProvide.
don't put anything more like a dbgrid(because the result is depend of visible rows in dbgrid).
uniquery specificoptions fetchall must set to false.
uniquery.fetchrows =1 (maybe it's same if fetchrows < returned filtered rows).
in object inspector set filter like: myfiledstring='something'
in object inspector set filtered to true and active to true.
in object inspector set queryreccount to true.
on buttonclick add:

showmessage(inttostr(uniquery.recordcount));
uniquery.last;
showmessage(inttostr(uniquery.recordcount));


run application and click on the button.
returned recordcount will be different.

i try with table with more than 200 records
and filtered records more than 60.
regards

[AlexP]

Hello,

To correctly display the number of records, you should either set the FetchAll property to True (since, when using a filter, all data is fetched on a client anyway), or open the table first and then apply the filter.

[lao]

hi,
yes, i understand you but if in design time if i set filtered to true
and after running the program
i set filtered to true after open it the result recordcount is wrong.
if in design time filtered is set to false everything is ok.
regards

[AlexP]

Hello,

To use a local filter correctly, the FetchAll property should be set to True, independently on when the filter is applied and DataSet is opened (design-time or run-time). There is no sense to set this property to False when using a local filter.