Hello everybody!
I have a small table "ABC" like this:
id_position | value
---------------------------
1 | 11
2 | 22
3 | 33
I try to use a dynamic cursor as below.
When the statement "order by id_position" in declare part of the cursor_abc
is omitted - cursor work as it should.
But when the statement "order by id_position" is used, cursor behave as
static one.
What's the matter, does anybody know?
Code:
declare @id_position as int, @value as int
DECLARE cursor_abc CURSOR
FOR
select id_position, value from abc
order by id_position
set nocount on
open cursor_abc
FETCH NEXT FROM cursor_abc
INTO @id_position, @value
WHILE @@FETCH_STATUS = 0
BEGIN
print @id_position
print @value
print '----------------------------'
update abc set value=666 --next reading should give value=666
FETCH NEXT FROM cursor_abc
INTO @id_position, @value
END
CLOSE cursor_abc
DEALLOCATE cursor_abc
GO
Regards
Lucas 2 2240
£ukasz W. wrote:
I try to use a dynamic cursor as below.
Cursors should be avoided if at all possible.
print @id_position
print @value
print '----------------------------'
Is this just a quick-and-dirty test? If you're trying to generate an
actual production file like this, then you should seriously consider
having the database output raw data, and using some separate tool to
apply formatting.
update abc set value=666 --next reading should give value=666
This is obviously dummy code. What are you actually trying to do
here - apply some function and use the result to control which row is
output next? What does that function look like? It may be possible
to rewrite the whole thing without cursors; failing that, you should
seriously consider having the database output data unsorted, or sorted
in a simple fashion, and using some separate tool (possibly the same
one used to apply formatting) to apply the complex sort rule.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
In many years of writing SQL, I have seldom found a need for a cursor.
They usually run 1-2 orders of magnitude slwoer than a relational
solution.
When someone uses one, it is generally becasue they are mimicing a
magnetic tape file system, and probably violating the basic principle
of a tiered architecture that display is done in the front end and
never in the back end. This a more basic programming principle than
just SQL and RDBMS.
Finally, id_position is not an ISO-11179 data element name and it makes
no sense. Identifier of what? Position of what? You have two
adjectives without a noun. But I bet you mant it to be PHYSICAL
location because you are mimicing a magnetic tape file system, instead
of using SQL for an RDBMS.
What is your real problem? Show us and perhaps we can help you. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Kevin Frey |
last post by:
Hello,
I have a test database with table A containing 10,000 rows and a table
B containing 100,000 rows. Rows in B are "children" of rows in A -
each row in A has 10 related rows in B (ie. B has a foreign key to A).
Using ODBC I am executing the following loop 10,000 times, expressed
below in pseudo-code:
"select * from A order by a_pk option (fast 1)"
|
by: Michael Winter |
last post by:
The following declaration was posted to a forum:
cursor: url('path/some.cur'), hand, pointer, text, default;
and I'm having a hard time convincing the poster that it's invalid.
First of all, I suppose it should be established that it /is/ invalid.
The following excerpt is taken from the CSS 2 specification (and
appears unaltered in CSS 2.1):
|
by: Shailesh |
last post by:
If I'm not mistaken, C++ doesn't have support for dynamic class
members. I'm considering if such a facility would be useful, and what
method would make a good workaround.
I have a generic buffer class, which can have one or more cursor
classes attached to it. Right now, they are both separate classes,
and I use them as follows:
class Buffer {
...
|
by: Joško Šugar |
last post by:
On this site:
http://www.sommarskog.se/dynamic_sql.html
I have found an example how to use cursor with dynamic SQL:
DECLARE @my_cur CURSOR
EXEC sp_executesql
N'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN
@my_cur',
N'@my_cur cursor OUTPUT', @my_cur OUTPUT
FETCH NEXT FROM @my_cur
|
by: CJM |
last post by:
How can I dynamically sort the results from a Stored Procedure? Or more
importantly, what is the fastest and most efficient way?
I know I can do the sorting within the recordset in ASP, but AFAIK this is
not the most efficient method.
Ideally, I'd like to pass a parameter to the SP to indicate sorting field
order...
How do you guys go about this?
| |
by: Alan Searle |
last post by:
I find that I can display structured data very nicely using XML with an
XSL template. As an extra 'goodie', I would like to give users the
ability to sort that data (for example with a button above a particular
column). What I need to know now is whether this is possible with
XML/XSL? Or do I need to resort to a programming language (maybe JScript?).
So far I have worked with the XML and XSL split into separate documents.
However, I...
|
by: Todd Huish |
last post by:
I have noticed something disturbing when retrieving datasets over a
relatively slow line (multiple T1). I am looking at about 25 seconds to
retrieve 500 rows via a php-odbc link. This same select from the cli is
for all intents practicaly instantaneous. After much research I discovered
that PHP by default uses a dynamic cursor type which can be quite a bit
slower than a forward only cursor. BTW I have been searching forward
only/read...
|
by: Todd Peterson |
last post by:
I'm a newbie to DB2 and am trying to figure out how to write a stored
procedure, using dynamic SQL statements to return a result set. I
believe the majority of the hurdles I have been facing might be due to
the fact that the samples and postings I have read have been related
to the UDB... I believe our company is on some version of MVS or
OS/390, but I am not sure which one, at the time of this posting.
I have an example, below, of a...
|
by: tickle |
last post by:
Need to convert this PL/SQL script to Dynamic SQL Method 2
* copybook - celg02u3.sql SIR 24265 *
* updates dt_deny for all rows in *
* the removal_eligibility_link table for all persons *
* in all stages associated with the victim who has *
* has had a specific legal status change *
EXEC SQL EXECUTE
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |