Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
15
Erster Beitrag:
vor 5 Jahren, 4 Monaten
Letzter Beitrag:
vor 5 Jahren, 4 Monaten
Beteiligte Autoren:
JP, Al, Fabrice Harari

Searching on a string + datetime composite key

Startbeitrag von JP am 04.04.2013 12:05

Hi All,

I have a file with a composite key made of a string item + a datetime item. I want to search for a record using only the string and the date portion i.e. ignoring the time portion. I have tried the following:

HReadSeekFirst( MyFile , Comp_Key , MyString + MyDate , hGeneric )

HReadSeekFirst( MyFile , Comp_Key , [ MyString , MyDate ] , hGeneric )

bufSoughtVal is Buffer
bufSoughtVal = HBuildKeyValue( MyFile , String_Date_Key , MyString , MyDate )
HReadSeekFirst( MyFile , Comp_Key , bufSoughtVal , hGeneric )

But they all fail. I have also tried without the hGeneric keyword. How does one search on a string plus just the date portion of a datetime item ?

TIA

Antworten:

Hi JP

I haven't tried this specific case, BUT, if the key is string+DateTime, then you HAVE TO search on string+DATETIME...

therefore, you could try:
MyDateTIme is DateTime
MyDateTime=MyDate (or if automatic conversion fails, mydateime..year=mydate..year... and so on)
hreadseek(MyFile, String_Date_Key, [MyString, MyDateTIME])

Best regards

von Fabrice Harari - am 04.04.2013 12:17
Hello JP

HReadSeekFirst( MyFile , Comp_Key , [ MyString , MyDate ] , hGeneric ) will probably work using the complete function on the string portion and adding HMinVal to replace the missing time component of the key

HReadSeekFirst( MyFile , Comp_Key , [ Complete(MyString,x) , MyDate+HMinVal ],HGeneric )

I still doubt if using a "Buffer" type variable will work with HBuildKeyValue()
The help states that HBuildKeyValue() returns a string value as the result and a buffer variable is a binary memory zone.


HReadSeekFirst() is an exact match by default and adding HGeneric turns that off and makes it a starts with match, so you could use HReadSeek() instead as it is HGeneric by default.

I think that you are on the right track using HGeneric (or HreadSeek()) because by excluding the time portion, you will never get an exact match ?

Regards
Al

von Al - am 04.04.2013 12:25
Hi Al,

Just a side note - the online help says the HBuildKeyValue() returns a buffer and the examples all use buffer so I am not sure on this. See here:

http://doc.windev.com/en-US/?3044258&name=hbuildkeyvalue_function&q=HBuildKeyValue

I have tried HReadSeek() - if I search only on the string portion with hGeneric then I do indeed find the first record. So far, so good. But if I try to add any portion of the DateTime variable it fails to find any record. here are more variations I have tried:

dtSearch_DateTime is DateTime = dtData_DateTime..Date
dtSearch_DateTime..Hour = 0
dtSearch_DateTime..Minute = 0
dtSearch_DateTime..Second = 0
dtSearch_DateTime..Millisecond = 0

HReadSeek( MyFile , Comp_Key , MyString + dtSearch_DateTime , hGeneric )


bufSoughtVal is Buffer
bufSoughtVal = HBuildKeyValue( MyFile , Comp_Key , dtSearch_DateTime )

HReadSeek( MyFile , Comp_Key , bufSoughtVal , hGeneric )



I think that I will create a seperate item in the file for just the date portion and use that in the composite key instead - seems simpler than messing around with this. I would have thought it shouldnt be so hard to figure out :)

von JP - am 04.04.2013 13:05
Thanks Fabrice. I have tried these variations now as well but no luck (see response to Al). I think I go with creating a date only item in the file and for the index.

von JP - am 04.04.2013 13:06
Hi again

without creating another key... What I do (and it works ALL the time) is to use a filter then readfirst/next

By example

hfilter(MyFile, MYCompositeKey, [string, datetimeWithTimeEqualZERO)],[String,datetimeWithTimeMAX])
hreadfirst(MyFile, MyCompositeKey)
while not hout(MyFile)
//Process
hreadnext(MyFile,MyCompositeKey)
end

And I'm doing SEEKs only when I want to find an EXACT value...


Best regards

von Fabrice Harari - am 04.04.2013 13:52
Thanks Fabrice. Thats a good idea. I shall definetly investigate that. Have you noticed if the application of Hfilter create any performance issue with large files, say over 1m records? 10m records?

Thanks

von JP - am 04.04.2013 16:06
Hi JP

I haven't noticed ANY delay with the use of hfilter... After all, it does only the setting of min and max positions in the file's index (it's not a query, and it doesn't read or return any data).

But if you need to PROCESS large amount of records, most of the time it's faster with a query. On another hand, if you often HAVE TO process 10 000 000 record and you use a query, you better have a very big data server :-)

Best regards

von Fabrice Harari - am 04.04.2013 21:09
Hello JP

Thanks for the help link re use of buffer.
It appears that PCSoft have changed their approach to composite keys yet again.
The introduction of the buffer variable as a result for HBuildKeyValus() is new in V17 as all versions prior say the result is a string.

One of the features of Windev is its loose variable typing, so I suppose that, as a composite key is a binary string it doesn't matter if the result goes into string or an array, but it is disturbing the think that they are still fiddling around with something as vital and basic as search methods in an established product like Windev. The HyperFile we currently use was released in V7.5 and we are now at V18 so what is going on ? Composite keys and indexing have always been a weak point in Windev and it looks like they are still having problems presenting a consistent method of finding partial keys.

In my code all my composite keys are combinations of numerics or a numeric and a fixed length alpha string so I don't have too many problems, but on occassion I have had to do as Fabrice suggested and use HFilter() with an upper and lower range.

A few years ago on the forum I think it Guenter who suggested that in some cases we should build our own composite keys as concatenated strings in a normal string field rather than using the inbuilt composite key. The downside was that you had to remember to code it on each HAdd and HModify ( or use a before trigger) but in your case with your string+DateTime key it may be something to consider.

At times like these, I look back fondly to my old DOS based Foxpro which offered a much bigger range of indexing options ( partial keys, conditional build keys etc) and wonder why PCSoft still can't offer better indexing options and a composite key function that is less problematic and more straightforward to code

Regards
Al

von Al - am 04.04.2013 21:40
Thanks for the feedback on hfilter. The reason I ask is because in Visual FoxPro the filter command was virtually worthless on large files; it could be applied instantly but subsequent browsing or looping through records could become terrible.

iro large files, 10m+ records, we either use query or scan a subset of records from a known start to end index key. And then not extracting more than perhaps a few thousand records at a request. All good there!

von JP - am 05.04.2013 05:14
Hi Al,

Yeah, VFP is my background prior to WinDev. Certainly this particular problem was a no-brainer in VFP's indexing scheme. Not sure why WinDev wont let me search on a partial datetime but I suspect this has to do with the conversion of the key to some sort of internal structure. In VFP you could just search on var_string + substr( var_datetime, 1, 8 ) and drop the time portion. But learning each language's different ways of handling things is OK, after all, each language has its own funnies including VFP. All learning ... :)

What does surprise me though is how much smaller VFP index files on disk are compared to WinDev - really signicantly smaller.

von JP - am 05.04.2013 05:20
Hello JP

I was reminded of your problem in something I was coding -
If you are going to have a go using the HFilter() have a look at HFilterStartsWith() as it should work ok with your partial key.

Regards
Al

von Al - am 07.04.2013 12:49
Thanks very much Al. Will certainly check into that!

von JP - am 08.04.2013 07:56
Hi Al,

That didnt workout - I suspect because the help says the type of the parameter must correspond to the type of the key item component (which for my case is a DateTime and not just the Date);

Value of Filter for Component 1 of Key: Type of first component of the search key

Value sought for the first component (the 2nd respectively) of the composite key.
The type of this parameter must correspond to the type of the key item component on which the filter is defined.


It's OK though - I created an index with just the date item and it works fine.

von JP - am 08.04.2013 08:22
Quote
Fabrice Harari
Hi again

without creating another key... What I do (and it works ALL the time) is to use a filter then readfirst/next

By example

hfilter(MyFile, MYCompositeKey, [string, datetimeWithTimeEqualZERO)],[String,datetimeWithTimeMAX])
hreadfirst(MyFile, MyCompositeKey)
while not hout(MyFile)
//Process
hreadnext(MyFile,MyCompositeKey)
end

And I'm doing SEEKs only when I want to find an EXACT value...


Best regards


Hi Fabrice,

So I finally had a chance to revisit this problem and apply your technique. Works perfectly. I studied the help some more and it clearly indicates that generic search does not work when using dates, integers, currencies, etc. as part of a composite key. So one either has to resort to using string items or your solution. Thanks again.

von JP - am 12.04.2013 16:21
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.