Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
13
Erster Beitrag:
vor 6 Jahren, 9 Monaten
Letzter Beitrag:
vor 6 Jahren, 8 Monaten
Beteiligte Autoren:
Steven Sitas, Jim Carson, Peter H., DerekT, M. Beaven

Query multiple HFCS identical databases?

Startbeitrag von Jim Carson am 20.10.2011 16:26

I have multiple databases setup on my HFCServer. I want to build queries that compare similar data files/items from different databases. The data files are identical (different companies). I assume that I have to set up a connection for each database.

What is the best way to create the analysys? The data files will have the same file names and structures. Will there be a conflict with identical names for the individual data files?

Any input will be most appreciated.
Jim

Antworten:

Jim,

You should be able to use a common file analysis and just make your connections via code (HChangeConnection) to connect to your individual DBs. I have several companies using a central analysis but their own databases and this method works well.

HTH-

-Marc

von M. Beaven - am 20.10.2011 17:11
Thanks Marc,

I can see how (HChangeConnection) works to select different databases and the query or report would use the database I was connected to at the time. But I am confused as how I would use two or more connections within the same query or report.

Example: I want to compare in an inventory file between both databases
company 1 - hamburger-qty WITH company 2 - hamburger-qty
(same commodity number in both databases)
Company 1:Hamburger:200 LBS
Company 2:Hamburger:50 LBS

So far, I can only do this if my analysis has files from both databases with slightly different names. :confused: or in other words, my analysis needs to have all files from each database (referenced with unique names, but changing the "disk name" back to the common data file name in each company folder)



von Jim Carson - am 20.10.2011 17:33
Jim
Cannot see why this would not work as in each case the query will link to date the connection is pointing at.
The second connection should be the the one that you wish to return to for ongoing operations

HOpenConnection("MyConnection_01")
HChangeConnection(MyQuery,"MyConnection_01")
HExecuteQuery(MyQuery,hQueryDefault)
//Do stuff - store result)
HCloseConnection("MyConnection_01")

HOpenConnection("MyConnection_02")
HChangeConnection(MyQuery,"MyConnection_02")
HExecuteQuery(MyQuery,hQueryDefault)
//Do stuff - store result)


//Compare Result1 - Result2


von DerekT - am 20.10.2011 18:02
Thanks Derek,
So, I should set up a connection for each database on the HFCS? Only associating the data files to the first or primary DB. Then I can change with HChangeConnection, query and store results for all connections in the analysis. Is there a way to get a list of connections in the analysis at run time? or would it be better to describe the initial connection and any additional connections' info in a data table (so that I wouldn't have to regenerate the analysis each time a new 'company' was added to the Server)
Question 2:
Should I store results in a real table or memory table (are memory tables retained after a HChangeConnection?


von Jim Carson - am 20.10.2011 21:49
Hello,
I don't know if there is a better way, but here is what I do:
1. Set the connection with HChangeConnection.
2. run the query
3. add the "data" to a MEMORY TABLE

4. Change the connection with HChangeConnection
5 run the query again
6. add the new data to the previous MEMORY TABLE

and so on ...
Memory tables are NOT affected by HChangeConnection.

Ofcourse what would really be fantastic (and I have contacted PC Soft about this), is to have REAL MEMORY TABLES, just like the Clarion memory driver.
Then we could use these tables, just like HF tables and with all HF commands.
Ofcourse they must also NOT be affected by HChangeConnection ....



Steven Sitas




von Steven Sitas - am 21.10.2011 07:45
Hi Steven,

Actually you have memory tables as you describe it...
If you execute a query, the result is stored in memory until you call hfreequery() or hcanceldeclaration() or until the parent process is terminated and you can browse through this result by code. Also you can declare the query result as a data source type variable and pass it on throughout your program...

Simplified:

if hexecutequery(MyQuery1_on_Connection1, hquerydefault) then
if hexecutequery(MyQuery2_on_Connection2, hquerydefault) then
for all MyQuery1_on_Connection1
hreadseekfirst(MyQuery2_on_Connection2,KeyField,MyQuery1_on_Connection1.KeyValue)
if hFound(MyQuery2_on_Connection2) then
//Compare Records
end
end
hfreequery(MyQuery2_on_Connection2)
end

hfreequery(MyQuery1_on_Connection1)
end


Cheers,

Peter Holemans

von Peter H. - am 21.10.2011 07:56
Hi Peter,
>>Actually you have memory tables as you describe it..

Nope :)
I would like to be able to ADD, DELETE or MODIFY data in my memory tables.
You can't do this with your approach.

REAL memory tables - like Clarions implementation - would be a fantastic add on for Windev. Specially know with 64bit and cheap RAM memory ...


von Steven Sitas - am 21.10.2011 08:59
Hi Steven,

I don't know clarion but you can add/delete/modify records in your query result or data source using simlple h-commands.

As long as the query isn't executed with the hModifyable flag the additions, modifications, deletions are only reflected in the in-memory query result or data source. In other words, you can do anything with it like with a physical db file...

Simplified:

if hexecutequery(MyQuery1_on_Connection1, hquerydefault) then
if hexecutequery(MyQuery2_on_Connection2, hquerydefault) then
for all MyQuery1_on_Connection1
hreadseekfirst(MyQuery2_on_Connection2,KeyField,MyQuery1_on_Connection1.KeyValue)
if hFound(MyQuery2_on_Connection2) then
//Compare Records
blabla...
//Modify Record
MyQuery2_on_Connection2.MyFieldToModify = "You see it can be done"
if hmodify(MyQuery2_on_Connection2) then
Info("MODIFIED!")
end
else
//Record not found so insert it in query result
MyQuery2_on_Connection2.Field1 = MyQuery1_on_Connection1.Field1
MyQuery2_on_Connection2.Field2 = MyQuery1_on_Connection1.Field2
if hadd(MyQuery2_on_Connection2) then
Info("ADDED")
end
end
end
hfreequery(MyQuery2_on_Connection2)
end

hfreequery(MyQuery1_on_Connection1)
end


Cheers,

P.




von Peter H. - am 21.10.2011 09:26
Hi Peter,
I didn't know you could do these kind of things with a query ...
thank you very much

Steven Sitas

von Steven Sitas - am 21.10.2011 10:39
the only thing NOW missing is multiple indexes on a Query.


von Steven Sitas - am 21.10.2011 10:40
Hi Jim,

hAlias combined with hCangeConnection might also be an option apart from the other solutions offered in this thread. (Personnaly I would go for the query solution on different connections.)

Cheers,

P.

von Peter H. - am 21.10.2011 13:23
Thanks to all. Your input has been very helpful.

Best regards, :spos:

Jim

von Jim Carson - am 21.10.2011 14:45
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.