Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
16
Erster Beitrag:
vor 4 Wochen, 1 Tag
Letzter Beitrag:
vor 6 Tagen, 3 Stunden
Beteiligte Autoren:
André Labuschagné, Fabrice Harari, Paulo Oliveira, DW, Peter Holemans, Ericus, Dutch Developer, DarrenF

WB22 - Data Source Search

Startbeitrag von André Labuschagné am 17.07.2018 09:49

Hi All

Is there anyway that a data source can be searched for a value on a field without having to loop thought the data source?

Cheers
André

Antworten:

Of course: queries !

Cheers,

DD

von Dutch Developer - am 17.07.2018 10:12
Hi André

easily

lets says that your data source is a query. you can do a hreadseek (or anyhting else hxxxx) on any of the fields of your query, as your datasource, once initialized, is the same as any other file of your db.

Best regards

von Fabrice Harari - am 17.07.2018 11:14
Hi Fabrice

The data source is not a query. It is filled programmatically using HExecuteSQLQuery. I seem to get an error using any of the H commands on that.

Cheers
André

von André Labuschagné - am 18.07.2018 13:08
Hi Andre

Did you do a HReadFirst after running your HExecuteSQLQuery?

Then do your search.

Also you did test that your HExecuteSQLQuery did not fail?

Regards


Ericus Steyn

von Ericus - am 18.07.2018 20:08
Hi Ericus

There are records - the execute did not fail.

Nope - will try that.

Cheers
André

von André Labuschagné - am 18.07.2018 22:19
André,

If that doesn't work, show us some code; it might help us to understand...

von DarrenF - am 19.07.2018 07:54
Hi Darren

I am back onto trying to get this to work:

dsMyDataSource

sSqlStatement = "select * from myfile;"

IF HExecuteSQLQuery(dsMyDataSource,MyConnection,hQueryWithoutCorrection,sSqlStatement) = False
HReadSeekFirst(dsMyDataSource,MyField,"12345")
IF HFound(dsMyDataSource) = True THEN
HNext(dsMyDataSource)
END
END

It crashes on HNext with:

No browse started for item on file.

MyFileSysID is the PK field of the file myfile.

I clearly have no idea what I am doing here.

My question is can one "index" and search for a value in a data source and if so how.

Cheers
André

von André Labuschagné - am 08.08.2018 12:14
Hi André,

You're using the wrong keyword (hReadSeekFirst instead of hReadSeek, hNext instead of hReadNext).

It should be:

IF HExecuteSQLQuery(dsMyDataSource,MyConnection,hQueryWithoutCorrection,sSqlStatement) THEN
HReadSeek(dsMyDataSource,MyField,"12345")
WHILE HFound(dsMyDataSource)
Trace(dsMyDataSource.FieldName)
HReadNext(dsMyDataSource)
END
END


Cheers,

Peter Holemans

von Peter Holemans - am 08.08.2018 13:23
Andre,
If you use your code change the HNEXT to HREADNEXT.

For performance you should use:
dsMyDataSource is data source
Your_var is int=12345
sSqlStatement is string= "select * from myfile where MyField="+you_var
IF HExecuteSQLQuery(dsMyDataSource,MyConnection,hQueryWithoutCorrection,sSqlStatement) then
HreadFirst(dsMyDataSource)
while not hout(dsMyDataSource)
....
HREADNext(dsMyDataSource)
END
END
Hcanceldeclaration(dsMyDataSource)

von Paulo Oliveira - am 08.08.2018 16:44
Hi Peter

Thanks for that - I still get the error.

All I am wanting to do is load the records for table A into a data source and then retrieve a specific record from the data source depending on criteria in another data source. I am just wanting to find a specific record in the data source and get it into a buffer. The strange thing is that HReadSeek does *not* crash anything but the page never displays. HReadNext returns the same error as before.


Any other ideas?

Cheers
André

von André Labuschagné - am 08.08.2018 22:28
Hi Paulo

You wrote:

sSqlStatement is string= "select * from myfile where MyField="+you_var

This is what I have been doing. I want to speed it up. Let me describe what I am doing:

1. Load from table A into data source A

2. Load from table B into data source B

3. Data source A and B share a common field that links them - parent child stuff

Now, with this data in RAM I want to:

1. Loop through data source A

2. Seek related record in data source B and if exists pick up data from there.

It is really that simple. I am trying to avoid going back to the database for data source B after each record loop through data source A.

Your code does work and is the route that I have been taking. Just trying to limit the fetches from the database.

Does that make sense?

Cheers
André

von André Labuschagné - am 08.08.2018 22:37
Hello Andre

Don't know if this will help but you should be able to query both tables with the relation and only have to loop thru 1 data source.
I use this code to open 2 external files read them get what I need then close them.
Example:


// The "sSONo" is a parameter passed to this code.
//Data source of files not in my Analysis that I open with declare external.
dsDELIVERY is data source
dsJob_Info is data source

dsMyQuery is data source //Data source for the query.

//Easier to build the string like this to see what is going on.
sSql = [
Select %1.Del_ID, %1.JN ,%1.SO_Number , %1.Man_ID ,%1.PO_Number, %2.PO
From %1,%2
where %1.JN = %2.JN AND SO_Number LIKE '%3%'
]

sSql = StringBuild(sSql,(dsDELIVERY),(dsJOB_INFO),sSONo)
IF HExecuteSQLQuery("dsMyQuery",m_ConnectionName,hQueryWithoutHFCorrection,sSql) THEN
IF HReadFirst(dsMyQuery) THEN
WHILE not HOut(dsMyQuery)
ArrayAddLine(arrDataReturn,[dsMyQuery.JN,dsMyQuery.PO,dsMyQuery.PO_Number,dsMyQuery.Del_ID,dsMyQuery.Man_ID,"Delivery"])
HReadNext(dsMyQuery)
END
END
END


If you put a break point on the hExecuteSQLQuery In the debugger once that code is run then look at the data source you should be able to see what fields are in it.

DW

Good luck!

von DW - am 08.08.2018 23:53
Thanks DW - will have a look at that approach.

von André Labuschagné - am 09.08.2018 00:55
Hi

Coming late to the discussion, but I see something VERY strnge in your original code... Your wrote :

IF HExecuteSQLQuery(dsMyDataSource,MyConnection,hQueryWithoutCorrection,sSqlStatement) = False
HReadSeekFirst(dsMyDataSource,MyField,"12345";)
IF HFound(dsMyDataSource) = True THEN
HNext(dsMyDataSource)
END
END

on the first line, it should be = TRUE, not false... With the code you are showing us, you are doing the hreadseek only when the query does not succeed, and that makes not sense.

Now, you should also test the result of the function hreadseek, and we don't know what is in MyField.... So displaying the error details on hreadseek would be a must.

Best regards

von Fabrice Harari - am 09.08.2018 12:44
Hi Fabrice

That was a typo - well spotted. When testing the data source did contain records. I must be doing something strange or not understanding the concept at all. I never did get it to work. I may revisit this later.

I have solved the problem with a structured array and the page loads instantly. There is now only one call to the database.

Cheers
André

von André Labuschagné - am 09.08.2018 21:53
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.