Hi all,
In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.
As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.
Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?
I'm looking for something similar or works similar to the pseudocode
below:
i.e.
DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;
5  8173 
Patrick wrote:
Hi all,
In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.
As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.
Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?
I'm looking for something similar or works similar to the pseudocode
below:
i.e.
DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;
When you DECLARE the CURSOR, specify WITH RETURN TO CLIENT, and OPEN
the CURSOR, but do not CLOSE it, before the END of the PROCEDURE.
Ther CLIENT will then get an OPEN CURSOR on all the results.
B.
Hm, what if I need to continue processing inside the procedure?
Brian Tkatch wrote:
Patrick wrote:
Hi all,
In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.
As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.
Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?
I'm looking for something similar or works similar to the pseudocode
below:
i.e.
DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;
When you DECLARE the CURSOR, specify WITH RETURN TO CLIENT, and OPEN
the CURSOR, but do not CLOSE it, before the END of the PROCEDURE.
Ther CLIENT will then get an OPEN CURSOR on all the results.
B.
Patrick wrote:
Hm, what if I need to continue processing inside the procedure?
Brian Tkatch wrote:
Patrick wrote:
Hi all,
>
In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.
>
As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.
>
Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?
>
I'm looking for something similar or works similar to the pseudocode
below:
>
i.e.
>
DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;
When you DECLARE the CURSOR, specify WITH RETURN TO CLIENT, and OPEN
the CURSOR, but do not CLOSE it, before the END of the PROCEDURE.
Ther CLIENT will then get an OPEN CURSOR on all the results.
B.
I do not think it is possible then. You must define host variables.
B.
Patrick wrote:
Hi all,
In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.
As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.
Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?
I'm looking for something similar or works similar to the pseudocode
below:
i.e.
DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;
FOR rowVariable AS SELECT c1, c2, c3... FROM myTable DO
myVariable1 = rowVariable.c1;
myVariable2 = rowVariable.c2;
END FOR;
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference http://www.iiug.org/waiug/present/Fo...Forum2006.html
Thanks!!!! This is what I was looking for!
Serge Rielau wrote:
Patrick wrote:
Hi all,
In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.
As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.
Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?
I'm looking for something similar or works similar to the pseudocode
below:
i.e.
DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;
FOR rowVariable AS SELECT c1, c2, c3... FROM myTable DO
myVariable1 = rowVariable.c1;
myVariable2 = rowVariable.c2;
END FOR;
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Aakash Bordia |
last post by:
Hello,
Does anybody know what is the documented and known behavior of
inserting/updating binary columns using host variables from a client to a
server which have different code pages? Will any code page / character set
conversion take place? I am particulary interested in insert/update from
subqueries.
eg:
insert into t1(binarycol) select :HV1 from t2
versus
|
by: Oliver Corona |
last post by:
I am wondering if anyone has any insights on the performance benefit (or
detriment) of declaring local variables instead of referencing members.
Is allocating memory for a new variable more efficient than repeatedly
referencing the member in a loop?
Maybe using a string isn't the best example, but hopefully you get the
idea!
* example (referencing member):
|
by: N.V.Dev |
last post by:
Hi,
Would like to know if I could define function parameters as Host
variables; This can be defined in Informix as below
int
print_table_info ( table_name )
EXEC SQL BEGIN DECLARE SECTION;
PARAMETER char *table_name;
|
by: Anthony Robinson |
last post by:
We're encountering an issue with working with CLOB's. We're doing a
basic insert into a table that has a CLOB column. Sometimes the CLOB
is fairly tame in size (10k-15k), and once in a while they're fairly
large (30k-60k).
When the CLOB (character string) is greater than 32K, the insert
fails. Found some documentation which mentions that a LOB can't be
larger than 32K, and if it is, it needs to be handled via assignment
of host...
|
by: Tim.D |
last post by:
People,
I've ventured into the wonderful world of Stored Procedures. My first
experience has been relatively successful however I am stuck on using
host variables to specifiy actualy table or column names in a FROM
clause. After many hours or reading all manner of manuals I've
discovered it appears this is not possible and that in order to so I
need to further venture into dynamic SQL.
My present procedure is based on all static SQL...
| | |
by: G Patel |
last post by:
Hi,
If I want to call functions that don't return int without declaring
them, will there be any harm?
I only want to assign the function(return value) to the type that it
returns, so I don't see how the return value comes to play here.
Ex
|
by: Frank Swarbrick |
last post by:
I'm just learning about embedded SQL, so be gentle...
My basic question is, if I use a fixed length host variable for a column
defined as VARCHAR, will trailing spaces be removed (or not) upon INSERT or
UPDATE of this column? I tried it, and it appears they are *not* stripped.
However, the Programming Client Applications manual leads me to believe that
the spaces should be stripped. A quote from that manual:
-------------------------...
|
by: majel |
last post by:
I just read an older post that described declaring global variables. I tried this but I'm getting an error and the text stays red.
What am I doing wrong? This code is behind a form.
Thanks.
Option Compare Database
Global theDate As String
Function DetermineDate()
thedate = InputBox("Enter Volume Date YYYYMM")
|
by: misha |
last post by:
Hello.
I was wandering if someone could explain to me (or point to some
manual) the process of mapping the addresses of host variables by DB2.
Especially I would like to know when DB2 decides to reinitialize the
addresses and even more when it decides not to do it.
Recently I've ben strucked with a problem of host variables defined in
LINKAGE SECTION, and it
took me some time to find the cause and solution for the problem.
|
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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: 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
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
