Author: Ricardo Araoz
Posted: 2012-03-02 16:06:58 Link
I need to send a user defined query to the dbms and recieve more than
one result set. In fox I would send the query and recieve "cursor",
"cursor1", ..., "cursorN".
Is that possible?
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4F514482.8070901@gmail.com
Author: Ed Leafe
Posted: 2012-03-02 16:09:41 Link
On Mar 2, 2012, at 4:06 PM, Ricardo Araoz wrote:
> I need to send a user defined query to the dbms and recieve more than
> one result set. In fox I would send the query and recieve "cursor",
> "cursor1", ..., "cursorN".
> Is that possible?
I'm not sure I understand. Do you mean some sort of pagination of results? It sounds like you want to send one query and receive multiple result sets. That seems confusing to me.
-- Ed Leafe
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/DEEE963A-1EB8-438C-AF71-A3509D4C2FB5@leafe.com
Author: Ricardo Araoz
Posted: 2012-03-02 16:31:42 Link
El 02/03/12 19:09, Ed Leafe escribió:
> On Mar 2, 2012, at 4:06 PM, Ricardo Araoz wrote:
>
>> I need to send a user defined query to the dbms and recieve more than
>> one result set. In fox I would send the query and recieve "cursor",
>> "cursor1", ..., "cursorN".
>> Is that possible?
> I'm not sure I understand. Do you mean some sort of pagination of results? It sounds like you want to send one query and receive multiple result sets. That seems confusing to me.
Let's say I have a really complex and time consuming set of queries that
result in a temp table in the dbms. Now I want to get a couple of
different views of this data, say I want a subset of this result table
grouped and another subset in detail. So I obviously don't want to do
these queries twice to get both data sets.
I feel I'm not being clear. Bottom line, I want to send in one query two
or more selects and recieve from the dbms two or more result sets.
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4F514A4E.4020301@gmail.com
Author: John Fabiani
Posted: 2012-03-02 16:49:16 Link
On Friday, March 02, 2012 07:31:42 PM Ricardo Araoz wrote:
> El 02/03/12 19:09, Ed Leafe escribió:
> > On Mar 2, 2012, at 4:06 PM, Ricardo Araoz wrote:
> >> I need to send a user defined query to the dbms and recieve more than
> >> one result set. In fox I would send the query and recieve "cursor",
> >> "cursor1", ..., "cursorN".
> >> Is that possible?
> >>
> > I'm not sure I understand. Do you mean some sort of pagination of
> > results? It sounds like you want to send one query and receive
> > multiple result sets. That seems confusing to me.
> Let's say I have a really complex and time consuming set of queries that
> result in a temp table in the dbms. Now I want to get a couple of
> different views of this data, say I want a subset of this result table
> grouped and another subset in detail. So I obviously don't want to do
> these queries twice to get both data sets.
> I feel I'm not being clear. Bottom line, I want to send in one query two
> or more selects and recieve from the dbms two or more result sets.
I'm not sure what you mean and what I think I understand - I can't recall
doing anything like that in VFP.
But if I have the general idea right - I'll bring this up in hopes that it
will help.
Assume that I have executed a large SQL statement. Now I need to create
several sub-queries based on the data I retrieved. You can do this in several
ways. You could filter it - similar to what VFP did. You could execute SQL
statement against the retrieved data.
something like:
myDataSet = bizobj.getDataSet()
newDataSet1 = myDataSet.execute("Select somefields from dataset where pkid =
someValue")
newDataSet2 = myDataSet.execute("Select somefields from dataset where pkid =
someOtherValue")
Therefore, only one query was made on the database ( a really complex and time
consuming query) and the rest was made from the retrieved data.
Now you have two different datasets plus you still have the first one.
Johnf
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/3027799.WDcYKG2Dqm@linux-12
Author: Ricardo Araoz
Posted: 2012-03-02 16:58:48 Link
El 02/03/12 19:49, John Fabiani escribió:
> On Friday, March 02, 2012 07:31:42 PM Ricardo Araoz wrote:
>> El 02/03/12 19:09, Ed Leafe escribió:
>>> On Mar 2, 2012, at 4:06 PM, Ricardo Araoz wrote:
>>>> I need to send a user defined query to the dbms and recieve more than
>>>> one result set. In fox I would send the query and recieve "cursor",
>>>> "cursor1", ..., "cursorN".
>>>> Is that possible?
>>>>
>>> I'm not sure I understand. Do you mean some sort of pagination of
>>> results? It sounds like you want to send one query and receive
>>> multiple result sets. That seems confusing to me.
>> Let's say I have a really complex and time consuming set of queries that
>> result in a temp table in the dbms. Now I want to get a couple of
>> different views of this data, say I want a subset of this result table
>> grouped and another subset in detail. So I obviously don't want to do
>> these queries twice to get both data sets.
>> I feel I'm not being clear. Bottom line, I want to send in one query two
>> or more selects and recieve from the dbms two or more result sets.
> I'm not sure what you mean and what I think I understand - I can't recall
> doing anything like that in VFP.
>
> But if I have the general idea right - I'll bring this up in hopes that it
> will help.
>
> Assume that I have executed a large SQL statement. Now I need to create
> several sub-queries based on the data I retrieved. You can do this in several
> ways. You could filter it - similar to what VFP did. You could execute SQL
> statement against the retrieved data.
>
>
>
> something like:
> myDataSet = bizobj.getDataSet()
> newDataSet1 = myDataSet.execute("Select somefields from dataset where pkid =
> someValue")
>
> newDataSet2 = myDataSet.execute("Select somefields from dataset where pkid =
> someOtherValue")
>
> Therefore, only one query was made on the database ( a really complex and time
> consuming query) and the rest was made from the retrieved data.
>
> Now you have two different datasets plus you still have the first one.
Let's say your large SQL statement has millions of rows and you want to
group those rows in one data set and you want a subset of those rows in
another data set. I'd rather recieve two datasets with 100 rows each
(and processed in the dbms) than several million rows in one dataset and
having to process it locally to obtain both final data set results.
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4F5150A8.5010100@gmail.com
Author: Paul McNett
Posted: 2012-03-02 17:24:58 Link
On 3/2/12 2:58 PM, Ricardo Araoz wrote:
> Let's say your large SQL statement has millions of rows and you want to
> group those rows in one data set and you want a subset of those rows in
> another data set. I'd rather recieve two datasets with 100 rows each
> (and processed in the dbms) than several million rows in one dataset and
> having to process it locally to obtain both final data set results.
Python's dbapi provides for this with cursor.fetchmany(num_rows). Dabo does not
provide for this as it stands, because we always do cursor.fetchall().
Paul
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4F5156CA.6050308@ulmcnett.com
Author: Adrian Klaver
Posted: 2012-03-02 17:31:51 Link
On Friday, March 02, 2012 3:24:58 pm Paul McNett wrote:
> On 3/2/12 2:58 PM, Ricardo Araoz wrote:
> > Let's say your large SQL statement has millions of rows and you want to
> > group those rows in one data set and you want a subset of those rows in
> > another data set. I'd rather recieve two datasets with 100 rows each
> > (and processed in the dbms) than several million rows in one dataset and
> > having to process it locally to obtain both final data set results.
>
> Python's dbapi provides for this with cursor.fetchmany(num_rows). Dabo does
> not provide for this as it stands, because we always do cursor.fetchall().
In Dabo could you not use a tempCursor to get the same result?
http://paul.dabodev.com/doc/api/dabodoc/dabo.biz.dBizobj.dBizobj.html#Methods_getTempCursor
>
> Paul
>
Adrian Klaver
adrian.klaver@gmail.com
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/201203021531.51831.adrian.klaver@gmail.com
Author: Ricardo Araoz
Posted: 2012-03-02 17:57:30 Link
El 02/03/12 20:24, Paul McNett escribió:
> On 3/2/12 2:58 PM, Ricardo Araoz wrote:
>> Let's say your large SQL statement has millions of rows and you want to
>> group those rows in one data set and you want a subset of those rows in
>> another data set. I'd rather recieve two datasets with 100 rows each
>> (and processed in the dbms) than several million rows in one dataset and
>> having to process it locally to obtain both final data set results.
> Python's dbapi provides for this with cursor.fetchmany(num_rows). Dabo does not
> provide for this as it stands, because we always do cursor.fetchall().
Wouldn't that be fetching sets of rows of the SAME data set?
What I need is to recieve two data sets from a single instruction.
query = """
select *
into #TempTable
from someBigTable
left join someOtherTable
on someConditions
where someRestraints
select someField, someOtherField, sum(someValue) as 'Total'
from #TempTable
group by someField, someOtherField
order by someField, someOtherField
select *
from #TempTable
left join #OtherTempTableFromSomeOtherPreviousQuery
on SomeConditions
where someOtherRestraints
"""
If I sent this query to the dbms from VFP I would recieve two cursors,
whose names would be by default (you may specify other) "cursor" and
"cursor1".
The "query" is defined by the user, so there is no way I may think of
better or worse strategies, I want to get two different data sets so
that I may later include them in a spread sheet or a csv file.
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4F515E6A.7000705@gmail.com
Author: Adrian Klaver
Posted: 2012-03-02 18:21:24 Link
On Friday, March 02, 2012 3:57:30 pm Ricardo Araoz wrote:
> El 02/03/12 20:24, Paul McNett escribió:
> > On 3/2/12 2:58 PM, Ricardo Araoz wrote:
> >> Let's say your large SQL statement has millions of rows and you want to
> >> group those rows in one data set and you want a subset of those rows in
> >> another data set. I'd rather recieve two datasets with 100 rows each
> >> (and processed in the dbms) than several million rows in one dataset and
> >> having to process it locally to obtain both final data set results.
> >
> > Python's dbapi provides for this with cursor.fetchmany(num_rows). Dabo
> > does not provide for this as it stands, because we always do
> > cursor.fetchall().
>
> Wouldn't that be fetching sets of rows of the SAME data set?
> What I need is to recieve two data sets from a single instruction.
> query = """
> select *
> into #TempTable
> from someBigTable
> left join someOtherTable
> on someConditions
> where someRestraints
>
> select someField, someOtherField, sum(someValue) as 'Total'
> from #TempTable
> group by someField, someOtherField
> order by someField, someOtherField
>
> select *
> from #TempTable
> left join #OtherTempTableFromSomeOtherPreviousQuery
> on SomeConditions
> where someOtherRestraints
> """
> If I sent this query to the dbms from VFP I would recieve two cursors,
> whose names would be by default (you may specify other) "cursor" and
> "cursor1".
> The "query" is defined by the user, so there is no way I may think of
> better or worse strategies, I want to get two different data sets so
> that I may later include them in a spread sheet or a csv file.
>
Exactly. This is not VFP so it will require a different set of glasses:) In Dabo
you can work with multiple DataSets, just in a different manner. More detail see:
http://wiki.dabodev.com/DataSet
Just substitute DataSet for TempTable.
--
Adrian Klaver
adrian.klaver@gmail.com
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/201203021621.25284.adrian.klaver@gmail.com
Author: John Fabiani
Posted: 2012-03-02 18:33:50 Link
On Friday, March 02, 2012 08:57:30 PM Ricardo Araoz wrote:
> El 02/03/12 20:24, Paul McNett escribió:
> > On 3/2/12 2:58 PM, Ricardo Araoz wrote:
> >> Let's say your large SQL statement has millions of rows and you want
> >> to
> >> group those rows in one data set and you want a subset of those rows
> >> in
> >> another data set. I'd rather recieve two datasets with 100 rows each
> >> (and processed in the dbms) than several million rows in one dataset
> >> and
> >> having to process it locally to obtain both final data set results.
> >
> > Python's dbapi provides for this with cursor.fetchmany(num_rows). Dabo
> > does not provide for this as it stands, because we always do
> > cursor.fetchall().
> Wouldn't that be fetching sets of rows of the SAME data set?
> What I need is to recieve two data sets from a single instruction.
> query = """
> select *
> into #TempTable
> from someBigTable
> left join someOtherTable
> on someConditions
> where someRestraints
>
> select someField, someOtherField, sum(someValue) as 'Total'
> from #TempTable
> group by someField, someOtherField
> order by someField, someOtherField
>
> select *
> from #TempTable
> left join #OtherTempTableFromSomeOtherPreviousQuery
> on SomeConditions
> where someOtherRestraints
> """
> If I sent this query to the dbms from VFP I would recieve two cursors,
> whose names would be by default (you may specify other) "cursor" and
> "cursor1".
> The "query" is defined by the user, so there is no way I may think of
> better or worse strategies, I want to get two different data sets so
> that I may later include them in a spread sheet or a csv file.
What is wrong with:
def getTwoDataSets(self, query1, query2):
dataSet1 = doSomethingWithQuery1
dataSet2 = doSomethingWithQuery2
return dataSet1,dataSet2
cursor1, cursor2 = getTwoDataSets("where pkid = 1", "where pkid =15")
Johnf
Johnf
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/2233470.vyPhYIkV3P@linux-12