Dynamic cursors for SQL Server 7.0

Dynamic cursors for SQL Server 7.0

Post by sagas.. » Sat, 08 Apr 2000 04:00:00



hi all,

How do I declare a dynamic cursor in SQL Server 7.0 ?

I want to get the count of rows using a Select as a dynamic SQL.

I am not able to get the exact Prepare and execute syntax from the
Online help files.

thanks,
Programmers.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Dynamic cursors for SQL Server 7.0

Post by C. E. Buttle » Sat, 08 Apr 2000 04:00:00


It would be difficult to give you the exact statements when you don't give
enough information.

In the meantime, what is wrong with SELECT COUNT(*)?


> hi all,

> How do I declare a dynamic cursor in SQL Server 7.0 ?

> I want to get the count of rows using a Select as a dynamic SQL.

> I am not able to get the exact Prepare and execute syntax from the
> Online help files.

> thanks,
> Programmers.

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

1. Bug with SQL Server 7.0 and ODBC dynamic cursors

We have a problem with SQL Server 7.0 and dynamic cursors.
This problem was not there with 7.0 beta 3.

We can open a recordset where MoveFirst does not move to the first
record.

Any hints (besides not to use dynamic cursor)?

Here is how to reproduce this bug:

ISQLW part
==========

Use ISQLW (SQL Server Query Analyser) to create the following
table, index and 2 records.
I have used a fresh database, but this should not be important

1. Step
create table ttt (
  tttid int identity,
  category int,
  infotext char(20),
  userid int
)

2. Step
create unique index tttindex on ttt (
  category,
  infotext,
  userid
)

3. Step
insert into ttt(category, infotext, userid) values (1, 'abc', 1)
insert into ttt(category, infotext, userid) values (1, 'abc', 2)

ODBC Test part
==============

1. Step
Create an ODBC connection to the database containing table ttt

2. Open the connection in ODBC Test

3. Switch to dynamic cursor via Menu Attr/SetStmtAttr
   Parameter "Attribute":   SQL_ATTR_CURSOR_TYPE=6  (2.0)
   Parameter "ValueType":   SQL_CURSOR_DYNAMIC=2  (1.0)

4. Execute the statement
select
   tttid, category, infotext, userid
from
   ttt
where
   infotext='abc'  and
   category=1  and
   (userid = 1 or userid = 2)
ORDER BY
  userid DESC

===========================================================================
You now have a recordset where MoveFirst doesn't point to the first
record.
Try and execute a MovePrev after MoveFirst
===========================================================================

This is on SQL Server 7.0 Enterprise Edition 7.00.623 from MSDN
January 1999
installed as Desktop Version on a  Windows NT Workstation 4.0 with
SP4.

ODBC SQL Server Driver   is version 3.70.06.23

ODBC Test   is version 3.510.3002.13

---

CVS Ingenieurgesellschaft mbH, Bremen, Germany

2. Pdox & AntiVirus SW

3. Dynamic SQL and Cursors for 6.5 or 7.0

4. Sp3 and SQL Profiler problem

5. ? on Dynamic SQL and arrays in SQL Server 7.0

6. what component are you using?

7. Server Side Cursors in MS SQL Server 7.0

8. Security hole in RTI ingres

9. SQL Server 7.0 on NT Server to SQL Server 7.0 on win2k Pro

10. server killed when using dynamic sql within cursors in stored procedure