problem while using Locate function of TMSQuery

[Balaji]

Hi,

I am using licensed version (3.80) of SDAC components.

I am facing a problem while using Locate function of TMSQuery of SDAC component.

Data base Table has column ‘groupno’. Value of groupno could be NULL
I am doing locate on column ‘groupno’.
Code reference is as follows...

Code: Select all

MSQuery1.Locate('groupno',MSQuery2.fieldbyname('groupno').AsString,[])

When MSQuery2.fieldbyname('groupno').AsString = NULL locate fails even though there is record in database where groupno = NULL.

Can you please help me to solve this problem?

Regards
Balajee

[Japhar]

Hi,

I found the problem and fixed on my system. It is locating fine now.

Problem is in the file MemDS.pas.

function TMemDataSet.LocateRecord
....
CopyFieldValue(Value, Values, Types, TFieldDesc(Fields));
....
if (Values = nil) or Data.GetNull(FieldDesc.FieldNo, RecBuf) then
//Result := (Values = nil) and Data.GetNull(FieldDesc.FieldNo, RecBuf)
Result := Data.GetNull(FieldDesc.FieldNo, RecBuf)

Please go through the commented line above. I have just removed Values in the condition. This is because, if the value is NULL then Values should have NIL value and the above condition should return TRUE.
But it is not happening as expected. It is returning False.

I'm sending values to CopyFieldValue() as below
CopyFieldValue ('', Values[0], Types[0], TFieldDesc(Fields[0]));

When i'm coming back Values[0] is having some value which i can't view and the condition is failing.

Core Lab: Please let me know the change which i have made is OK? or it leads to any issue?

[Antaeus]

The AsString method cannot return NULL values, so you should pass values of the Variant data type instead:

Code: Select all

   MSQuery2.fieldbyname('groupno').Value

It is very unlikely that we will change behaviour of this functionality.

[Japhar]

We have tried with this option too. It is not working.

Problem is not with that...problem exist in

Code: Select all

Result := (Values[i] = nil) and Data.GetNull(FieldDesc.FieldNo, RecBuf)

For CopyFieldValue(Value, Values, Types, TFieldDesc(Fields));
I'm passing
1) Value = ''
1) Before copying Values is nil.
2) After copying Values is having address something like this $6062C4C

So when it comes to the below line
if (Values = nil) or Data.GetNull(FieldDesc.FieldNo, RecBuf) then

(Values = nil) is showing as False. Why?

Data.GetNull(FieldDesc.FieldNo, RecBuf), i'm getting True. This is because my RecBuf is having NULL value which is correct.

So now it enters into if condition and it checks below condition
Result := (Values = nil) and Data.GetNull(FieldDesc.FieldNo, RecBuf)

Result := (False) and (True) and my Result is False. Which is not correct. I'm expecting here True.

So the condtion (Values = nil) destroyed my expectations.

Solution as per my understanding is:
Replace the below code and it works fine. Instead of checking Values = nil just compare the Values[i] with RecBuf.

Code: Select all

Res := TMemData(Data).CompareFieldValue(Values[i], Types[i], FieldDesc, RecBuf, Options);
Result := (Res = 0);

Please let me know if i'm wrong anywhere and also let me know the change which i'm suggesting is OK??

[Antaeus]

I just tested the following approach with both SDAC 3.80.0.38, and SDAC 4.10.0.9 (latest builds of both versions).

Code: Select all

MSQuery1.Locate('groupno',MSQuery2.fieldbyname('groupno').Value,[])

I used Delphi 7 for test. Please specify what IDE do you use.

Note, if you use MSQuery2.fieldbyname('groupno').AsString, the NULL values are converted to empty strings, as the string data type does not cover full scopes of the variant data type. This conversion is not reversible as there is no possible to determine the whether passed empty string was received from a NULL value.

[Antaeus]

I removed the new thread you have created, as it duplicates this one. Please do not duplicate threads in the future.

[Japhar]

I'm using Delphi 7

ok...i will explain you complete scenario where it is not working in my application.

1. I have query1

select col1, col2 from #table1 --temp table
union
select col1 AS '', col2 AS 'xyz' from table2

this retuns me

col1 col2
----- -----
xyz -- here col1 is empty string
1 a
2 b

2. Now i have one more query 'query2'
select col1, col2 from table3

this returns me

col1 col2
---- -----
1 abc
NULL xyz
3 bcd

3. Now i want to locate in query2
my actual locate statement is as below

Code: Select all

query2.locate('col1', query1.fieldbyname('col1').AsString), []);

after your suggetion i have changed to

Code: Select all

query2.locate('col1', query1.fieldbyname('col1').Value), []);

Note: query1.fieldbyname('col1').AsString/Value is having empty string value.

In both the ways it is not working and locate is returning False.

I have modified my query1 to select NULL instead of empty string in union. But still it did not work. So i started debugging the code and found the problem with the below statement

Code: Select all

Result := (Values[i] = nil) and Data.GetNull(FieldDesc.FieldNo, RecBuf)

and replaced the above statement with

Code: Select all

Res := TMemData(Data).CompareFieldValue(Values[i], Types[i], FieldDesc, RecBuf, Options); 
Result := (Res = 0);

So please let me know how to solve this issue if my suggestion to you is not worth.

[Japhar]

Hi,

Can you please suggest me the change is ok or not? We got production issue. So i have to fix it as soon as possible. We are waiting for your reply. Pl send few comments on this. Only concern is that the change which i have done should not effect in other way. This is because, Locate is used many places in my application.

Thanks
jaffer

[Ludek]

I'm nobody from corelab, but... why are you again and again trying to locate NULL values using an empty string? NULL and empty string are 2 completely different values - locate of NULL value should never find empty strings in the query and locate for empty string should never find NULL values...

if somebody wants to locate NULL value in query, the locate expression must be set to NULL:

Locate (, Null, )

if somebody wants to locate empty strings in query, the following expression must be used:

Locate (, '', )

Null is a function from unit Variants.

If you have a query field, which contains NULL or empty string, and you use the AsString method, the NULL value converts to an empty string (as the AsString method is typed to string and string variables cannot contain NULLs). therefore, you must use other method (I prefer AsVariant), which does not convert NULLs to empty strings. But (be aware of!) the field must still contain NULL, because the AsVariant method does not convert empty strings to NULLs...

I hope, I explained it really thoroughly [/b]

[Antaeus]

I tried to use two SELECT statements united with UNION like in your example, but I replaced the empty string with the NULL value, and Locate works correctly with FieldByName('xxx').Value. You should check what exact fields values (NULL or empty strings) do you have on the client to find the reason of the problem.
We think that the current SDAC behaviour is correct, and should be changed. This behaviour persists in SDAC for a long time, and it is similar to standard components (like ADO). If you want, you can perform the change in your source code of SDAC, but I suggest you determine the reason of the problem in order to use the standard behaviour of Locate.

[Japhar]

Thanks Antaeus...

We sloved the problem by modifing our application code. Really nice discussing with you on this topic....