WB22 - Data Source Search

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é

Of course: queries !

Cheers,

DD

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

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é

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

Hi Ericus

There are records - the execute did not fail.

Nope - will try that.

Cheers
André

André,

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

Autor: André Labuschagné

Link zum Beitrag: [27130.foren.mysnip.de]

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é

Autor: Peter Holemans

Link zum Beitrag: [27130.foren.mysnip.de]

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

Autor: Paulo Oliveira

Link zum Beitrag: [27130.foren.mysnip.de]

Andre,
If you use your code change the HNEXT to HREADNEXT.

For performance you should use:
dsMyDataSource
Your_var is int=12345
sSqlStatement = "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)

Autor: André Labuschagné

Link zum Beitrag: [27130.foren.mysnip.de]

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é

Autor: André Labuschagné

Link zum Beitrag: [27130.foren.mysnip.de]

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é

Autor: DW

Link zum Beitrag: [27130.foren.mysnip.de]

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,)
HReadNext(dsMyQuery)
END
END
END


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

DW

Good luck!

Autor: André Labuschagné

Link zum Beitrag: [27130.foren.mysnip.de]

Thanks DW - will have a look at that approach.

Autor: Fabrice Harari

Link zum Beitrag: [27130.foren.mysnip.de]

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

Autor: André Labuschagné

Link zum Beitrag: [27130.foren.mysnip.de]

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é