Re: Opinions on approach, please...

On Thu, 29 May 2008 23:50:59 +1200, "Pete Dashwood" <dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:



"Robert" <no@xxxxxx> wrote in message
news:7i3t34tmtq8u8v410f3rk9jbn99j254jh8@xxxxxxxxxx
On Thu, 29 May 2008 22:39:51 +1200, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

"Frederico Fonseca" <real-email-in-msg-spam@xxxxxxxxx> wrote in message
news:7bos345far6d9sglmuc59ga9mfdlu1qe4n@xxxxxxxxxx
On Wed, 28 May 2008 11:55:01 +1200, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

When using dynamic SQL you almost always need to PREPARE.
You also need to use DESCRIBE in some situations, but NOT in all of
them.
small example below. Take in consideration that dependign on how you
go on the dynamic sql route, you will not be able to do it exactly as
follows.
move "SELECT TABNAME FROM SYSCAT.TABLES
- " ORDER BY 1

"ORDER BY 1" ? I don't understand this.

It's a Cobol continuation line. You're not yet at the end of SELECT.

I'm familiar with COBOL syntax, Robert, having coded 1 or 2 lines of it over
a number of decades... :-)

It was ORDERing something BY 1, that I didn't recognise. (Your comment
didn't explain it either)

It means the first column in the result set. One could alternatively use its name TABNAME.
They allow it because of UNIONs (intersect, minus, etc.), where the first columns in the
select lists might have different names. It's not necessary because the result set uses
names from the first select in the union. The same notation can be used for GROUP BY.

I'm surprised you haven't seen this because it is frequently used (by two finger typists).

- " WHERE TABNAME <> ?" to st.
EXEC SQL PREPARE s1 FROM :st END-EXEC.
EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC.

Yes, this is pretty close to what I'm looking at. I used the examples in
the
Fujitsu docs and it is the same as this, except that it makes the declare
of
the cursor part of the statement to be prepared. It is interesting to see
that it it possible to prepare part of a statement. Thanks.

No, it is NOT possible to prepare part of a statement.

You misunderstood.

He has prepared a statement that is then embedded in another statement.
That's what I meant by my comment (I realize it was ambiguous).

Declare is a statement to the precompiler; it is not sent to the server. It defines a
local handle for the cursor. PREPARE creates a remote handle to the compiled select.
OPEN sends either the select, in the case of static, or its remote handle, in the case of
dynamic.

.

Relevant Pages

  • Re: help with tables
    ... EXEC SQL ... BEGIN DECLARE SECTION ... A cursor need not be declared in the data division, ... Funny thing is that in VSE you *have* to put it in the procedure division. ...
    (comp.lang.cobol)
  • Re: Can I use ESQL/C datetime type to define non-host variables / parameters (oops)
    ... Do I need to put the parameters with esql/c datatypes between EXEC SQL BEGIN DECLARE SECTION; and EXEC SQL END DECLARE SECTION;. ... If you want to write datetime as the type name without a typedef, then you have to use the ESQL/C preprocessor to change the datetime into dtime_t for you. ...
    (comp.databases.informix)
  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)
  • Re: Opinions on approach, please...
    ... to update more than 32k records without a commit in-between. ... Translating the above to SQL it would be as follows. ... I advise you to do cursor definitions on working storage). ... exec sql open file-a end-exec. ...
    (comp.lang.cobol)
  • Re: Optimize function that uses cursors
    ... > The function can be made recursive as there are no much recursions (we ... > groups and ancestor groups (i.e. the parents of the parents and so on) for ... > a recursive call in the select of the cursor), and in the cursor look it ... > declare @more bit ...
    (microsoft.public.sqlserver.programming)