Hi all!
I have started other discussions talking about the same problem and have searched for similar problems in this forum but I reallyy feel frustated because I don't know which is the solution to my problem.
My problem is that I have a ProDataSet that joins some tables from my database and what I want to do is to apply a sentence to it for filtering the data contained in the ProDataSet. I know that it is impossible to with FILL-WHERE-STRING because it only works with a single table and I am trying to use QUERY-PREPARE method but I don't receive what I want, don't know if it is because I am doing something wrong with it or because I have to use any other method to achieve my goal. The following is the code that I use to construct my ProDataSet:
CLASS base_informes.crearDataSetDinamico:
DEFINE PRIVATE VARIABLE bindingSource1 AS Progress.Data.BindingSource NO-UNDO.
CONSTRUCTOR PUBLIC crearDataSetDinamico (
INPUT pcBuffers AS CHARACTER,
INPUT pcSources AS CHARACTER,
INPUT sentencia AS CHARACTER,
INPUT pcFields AS CHARACTER,
INPUT pcTablas AS CHARACTER,
INPUT lp_batch AS LOGICAL,
OUTPUT bindingSource1 AS Progress.Data.BindingSource):
DEFINE VARIABLE iEntry AS INTEGER NO-UNDO.
DEFINE VARIABLE hDataSource AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE phDataSet AS HANDLE NO-UNDO.
CREATE DATASET phDataSet.
DO iEntry = 1 TO NUM-ENTRIES(pcBuffers):
phDataSet:ADD-BUFFER(WIDGET-HANDLE(ENTRY(iEntry, pcBuffers))).
END.
DEFINE VARIABLE t1 AS INTEGER NO-UNDO.
DEFINE VARIABLE t2 AS INTEGER NO-UNDO.
DEFINE VARIABLE tablas AS CHARACTER NO-UNDO.
DEFINE VARIABLE iw_relaciones AS INTEGER NO-UNDO.
iw_relaciones = NUM-ENTRIES(pcFields, "|").
DO iEntry = 1 TO iw_relaciones:
tablas = ENTRY(iEntry,pcTablas,"|").
t1 = INT(ENTRY(1,tablas,",")).
t2 = INT(ENTRY(2,tablas,",")).
phDataSet:ADD-RELATION(phDataSet:GET-BUFFER-HANDLE(t1),
phDataSet:GET-BUFFER-HANDLE(t2),
ENTRY(iEntry,pcFields,"|")).
END.
DO iEntry = 1 TO NUM-ENTRIES(pcSources):
CREATE DATA-SOURCE hDataSource.
CREATE BUFFER hBuffer FOR TABLE ENTRY(iEntry, pcSources).
hDataSource:ADD-SOURCE-BUFFER(hBuffer, "").
phDataSet:GET-BUFFER-HANDLE(iEntry):ATTACH-DATA-SOURCE(hDataSource).
IF lp_batch THEN
phDataSet:GET-BUFFER-HANDLE(iEntry):BATCH-SIZE = 100.
END.
phDataSet:FILL().
bindingSource1 = NEW Progress.Data.BindingSource (phDataSet).
END METHOD.
END CLASS.
If somebody could give me the path to the victory I would be very grateful. Thanks a lot!
I think what you want to do is create a dataset with just a single buffer in it.
This buffer contains fields from all tables in the join that you are interested in.
Then you populate this buffer with your joined query selecting just the records you want to filter on.
Not really Peter. I use this way to construct to the DataSet because I pass the bindingSource to a control that needs all the data from different sources but showed in a kind of tree that the user can know to which table correspond each field on the dataSet so I need to use the the AddRelation method as it join the tables in a kind of tree that it is perfect for my purpose. I attach an image that shows how a receive the dataSet in my application.
mikelcid wrote:
Hi all!
I have started other discussions talking about the same problem and have searched for similar problems in this forum but I reallyy feel frustated because I don't know which is the solution to my problem.
My problem is that I have a ProDataSet that joins some tables from my database and what I want to do is to apply a sentence to it for filtering the data contained in the ProDataSet. I know that it is impossible to with FILL-WHERE-STRING because it only works with a single table and I am trying to use QUERY-PREPARE method but I don't receive what I want, don't know if it is because I am doing something wrong with it or because I have to use any other method to achieve my goal. The following is the code that I use to construct my ProDataSet:
The code has no use of fill-where-string or query-prepare, so it is a bit difficult to understand the actual problem you are running into. Yes, it is impossible to use fill-where-string with a single table, but this is when you have more than one table in a single data-source mapped to one of the dataset buffers. In your code example you only use one table per data-source so you should be able to apply a filter with fill-where-string. Keep in mind that you need to be careful with fill-where-string on the child tables, since this is where the relationship that is used during the fill is defined. You can still change it, but you must keep the relationship expression intact.
If the fill-where-string is too limiting, which is quite common, you need to use a query. You can create the query in addition to the data-source, add the buffers to the query instead of the data-source, add the query critieria to the query with the query-prepare and then add the query to the datasource as in hDataSource:Query = hQueryHandle. (You can also add multiple buffers directly to the datasource and then change the default Query, but I have very rarely tried this, so I do not know what limitations exists. I suspect it has some compared to using your own query)
Hi Havard!
First of all thanks for replying to my question. I have try to apply it and this is the code that I have written based on your words:
DO iEntry = 1 TO NUM-ENTRIES(pcSources):
CREATE DATA-SOURCE hDataSource.
CREATE BUFFER hBuffer FOR TABLE ENTRY(iEntry, pcSources).
CREATE QUERY hQuery.
hQuery:ADD-SOURCE-BUFFER(hBuffer, "").
hQuery:QUERY-PREPARE(sentence).
hDataSource:QUERY = hQuery.
phDataSet:GET-BUFFER-HANDLE(iEntry):ATTACH-DATA-SOURCE(hDataSource).
IF lp_batch THEN
phDataSet:GET-BUFFER-HANDLE(iEntry):BATCH-SIZE = 100.
END.
phDataSet:FILL().
Don't know if it is exactly what you were trying to say, I am only a scholar The thing now is that what my control receives is the structure from the tables but it is totally empty no matter which is the sentence (I have tried to use FOR EACH tt NO-LOCK).
I have also put hDataSource:ADD-SOURCE-BUFFER(hBuffer, "") before adding the buffer to the hQuery but it worse, it doesn't give the structure.
Probably I am not placing the sentences it the right order or something similar, can you guess which can be the problem?
Don't know if it is exactly what you were trying to say, I am only a scholar The thing now is that what my control receives is the structure from the tables but it is totally empty no matter which is the sentence (I have tried to use FOR EACH tt NO-LOCK).
The query need to be against the database table.
I have also put hDataSource:ADD-SOURCE-BUFFER(hBuffer, "") before adding the buffer to the hQuery but it worse, it doesn't give the structure.
You need to add the buffer to the query (hQuery:add-buffer(hBuffer) or hQuery:set-buffers(hBuffer). The datasource only needs the query.