Browser - server cursors - IRowsetFind - etc...

Browser - server cursors - IRowsetFind - etc...

Post by Daniel MOSMONDOR - Mos » Sat, 11 Sep 1999 04:00:00



Please read it through!

As a part of one business application, I am supposed to write browser
interface, which would have following features:

- developer should call it with the query which it wants to browse on
- developer should set any column name information, and column formatting
types
- user could scroll up and down through the information
- user could select a column to sort data on - if there is index on that
row - OK, if it isn't, ask user if he wants to kill SQL server and proceed
- user could select a column, and perform jump to some row based on the
query on that column data.  I was hoping I could use IRowsetFind, but there
I found many problems.  Another way of doing it was to open another rowset
specifying starting row, get bookmark of that row and use that bookmark on
original rowset.  But, no bookmarks.

Maybe the problem lies in that I want to use server cursors, and that is the
only way it may work, because table sizes are very big and goes from 1000 to
1mil rows.

Another thing I don't want is to make SQL server build table in tempdb (Yes,
I am using MS SQL 7.0).  That would be possible if index exist, and with no
index, I am prepared to sacrifice server, and notify user that he will have
to wait.

So, he questions:

1) Am I able to use IRowsetFind interface with MS SQL 7.0, server side
cursors?
2) Am I able to use BOOKMARKS at the same time?
3) If yes, how and if no, how to do it then?  Do I have to give up on that
and take some other developing tool, because I think that it must be
possible to solve.

If I was to complete this browser, I would really want to share it with
anyone interested, because I think it would be very usable, and make 50% of
any database application.

For the record, I am opening my rowset with some of this combinations of
properties:

  CDBPropSet propset(DBPROPSET_ROWSET);
  propset.AddProperty(DBPROP_SERVERCURSOR,true);
  propset.AddProperty(DBPROP_OTHERINSERT,true);
  propset.AddProperty(DBPROP_OTHERUPDATEDELETE,true);
  propset.AddProperty(DBPROP_OWNINSERT,true);
  propset.AddProperty(DBPROP_OWNUPDATEDELETE,true);
  propset.AddProperty(DBPROP_REMOVEDELETED,true);
  propset.AddProperty(DBPROP_CANSCROLLBACKWARDS,true);
/*  propset.AddProperty(DBPROP_CANFETCHBACKWARDS,true);
  propset.AddProperty(DBPROP_UPDATABILITY,(long)0);
  propset.AddProperty(DBPROP_BOOKMARKS,true);
  propset.AddProperty(DBPROP_IRowsetLocate,true);
  propset.AddProperty(DBPROP_IRowsetFind,true);
*/  hresult=m_Recordset.Open(m_Session,m_QueryString,&propset);
  if (FAILED(hresult)) {
   TRACE_HRESULT(hresult);
   m_LastError=BROWSER_RECORDSET_ERROR;
  }
  else {
...


 
 
 

Browser - server cursors - IRowsetFind - etc...

Post by Gabriel Knizni » Sun, 12 Sep 1999 04:00:00


SQL Server apparently uses primary keys when it builds bookmarks, so a table
doesn't support IRowsetLocate unless a primary key is defined on it. If you
can do that, IRowsetLocate and bookmarks is the way to go. Otherwise, you
can do a new SELECT (and hence a new Rowset) using a unique value stored in
your browser for the selected row.

Gabriel Kniznik



> Please read it through!

> As a part of one business application, I am supposed to write browser
> interface, which would have following features:

> - developer should call it with the query which it wants to browse on
> - developer should set any column name information, and column formatting
> types
> - user could scroll up and down through the information
> - user could select a column to sort data on - if there is index on that
> row - OK, if it isn't, ask user if he wants to kill SQL server and proceed
> - user could select a column, and perform jump to some row based on the
> query on that column data.  I was hoping I could use IRowsetFind, but
there
> I found many problems.  Another way of doing it was to open another rowset
> specifying starting row, get bookmark of that row and use that bookmark on
> original rowset.  But, no bookmarks.

> Maybe the problem lies in that I want to use server cursors, and that is
the
> only way it may work, because table sizes are very big and goes from 1000
to
> 1mil rows.

> Another thing I don't want is to make SQL server build table in tempdb
(Yes,
> I am using MS SQL 7.0).  That would be possible if index exist, and with
no
> index, I am prepared to sacrifice server, and notify user that he will
have
> to wait.

> So, he questions:

> 1) Am I able to use IRowsetFind interface with MS SQL 7.0, server side
> cursors?
> 2) Am I able to use BOOKMARKS at the same time?
> 3) If yes, how and if no, how to do it then?  Do I have to give up on that
> and take some other developing tool, because I think that it must be
> possible to solve.

> If I was to complete this browser, I would really want to share it with
> anyone interested, because I think it would be very usable, and make 50%
of
> any database application.

> For the record, I am opening my rowset with some of this combinations of
> properties:

>   CDBPropSet propset(DBPROPSET_ROWSET);
>   propset.AddProperty(DBPROP_SERVERCURSOR,true);
>   propset.AddProperty(DBPROP_OTHERINSERT,true);
>   propset.AddProperty(DBPROP_OTHERUPDATEDELETE,true);
>   propset.AddProperty(DBPROP_OWNINSERT,true);
>   propset.AddProperty(DBPROP_OWNUPDATEDELETE,true);
>   propset.AddProperty(DBPROP_REMOVEDELETED,true);
>   propset.AddProperty(DBPROP_CANSCROLLBACKWARDS,true);
> /*  propset.AddProperty(DBPROP_CANFETCHBACKWARDS,true);
>   propset.AddProperty(DBPROP_UPDATABILITY,(long)0);
>   propset.AddProperty(DBPROP_BOOKMARKS,true);
>   propset.AddProperty(DBPROP_IRowsetLocate,true);
>   propset.AddProperty(DBPROP_IRowsetFind,true);
> */  hresult=m_Recordset.Open(m_Session,m_QueryString,&propset);
>   if (FAILED(hresult)) {
>    TRACE_HRESULT(hresult);
>    m_LastError=BROWSER_RECORDSET_ERROR;
>   }
>   else {
> ...




 
 
 

1. Browser - server cursors - IRowsetFind - etc...

Please read it through!

As a part of one business application, I am supposed to write browser
interface, which would have following features:

- developer should call it with the query which it wants to browse on
- developer should set any column name information, and column formatting
types
- user could scroll up and down through the information
- user could select a column to sort data on - if there is index on that
row - OK, if it isn't, ask user if he wants to kill SQL server and proceed
- user could select a column, and perform jump to some row based on the
query on that column data.  I was hoping I could use IRowsetFind, but there
I found many problems.  Another way of doing it was to open another rowset
specifying starting row, get bookmark of that row and use that bookmark on
original rowset.  But, no bookmarks.

Maybe the problem lies in that I want to use server cursors, and that is the
only way it may work, because table sizes are very big and goes from 1000 to
1mil rows.

Another thing I don't want is to make SQL server build table in tempdb (Yes,
I am using MS SQL 7.0).  That would be possible if index exist, and with no
index, I am prepared to sacrifice server, and notify user that he will have
to wait.

So, he questions:

1) Am I able to use IRowsetFind interface with MS SQL 7.0, server side
cursors?
2) Am I able to use BOOKMARKS at the same time?
3) If yes, how and if no, how to do it then?  Do I have to give up on that
and take some other developing tool, because I think that it must be
possible to solve.

If I was to complete this browser, I would really want to share it with
anyone interested, because I think it would be very usable, and make 50% of
any database application.

For the record, I am opening my rowset with some of this combinations of
properties:

  CDBPropSet propset(DBPROPSET_ROWSET);
  propset.AddProperty(DBPROP_SERVERCURSOR,true);
  propset.AddProperty(DBPROP_OTHERINSERT,true);
  propset.AddProperty(DBPROP_OTHERUPDATEDELETE,true);
  propset.AddProperty(DBPROP_OWNINSERT,true);
  propset.AddProperty(DBPROP_OWNUPDATEDELETE,true);
  propset.AddProperty(DBPROP_REMOVEDELETED,true);
  propset.AddProperty(DBPROP_CANSCROLLBACKWARDS,true);
/*  propset.AddProperty(DBPROP_CANFETCHBACKWARDS,true);
  propset.AddProperty(DBPROP_UPDATABILITY,(long)0);
  propset.AddProperty(DBPROP_BOOKMARKS,true);
  propset.AddProperty(DBPROP_IRowsetLocate,true);
  propset.AddProperty(DBPROP_IRowsetFind,true);
*/  hresult=m_Recordset.Open(m_Session,m_QueryString,&propset);
  if (FAILED(hresult)) {
   TRACE_HRESULT(hresult);
   m_LastError=BROWSER_RECORDSET_ERROR;
  }
  else {
...


2. SQL statement ?

3. IRowsetFind - How does it work

4. Ranking List

5. APPLY CURSOR-UP TO BROWSER ==> malfunction

6. ESQL/C and C++

7. Cursors and rowids and cyclic lists, etc.

8. Stored Procedure

9. FREEing the cursor, etc

10. runtime retrieve from select, cursors etc.

11. Runtime determined select queries, cursors, connections etc.

12. Position browser column to centre of browser window

13. Linking a viewer to a browser linked to another browser in V8