Syntax for embedded sql

Jeff

unread,

Jun 9, 2005, 1:10:56 PM6/9/05

to

I am writing a program and need to know the correct syntax inside Cobol
for SUBSTR.

Insert into catable
(member, namekey, namekey6, namekey10, name)
Select substr(name, 1, 2), name, 1, 6)
From Member
Where members not = "0000000000"
Order by Members
EXEC-END.

Is this Correct?

Jeff

unread,

Jun 9, 2005, 1:13:16 PM6/9/05

to

I meant END-EXEC.

Frederico Fonseca

unread,

Jun 9, 2005, 2:30:56 PM6/9/05

to

Which COBOL vendor/version, which Pre-compiler, which OS and which
Database.

Syntax will vary depending on all of the above.

Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com

Jeff

unread,

Jun 9, 2005, 2:46:52 PM6/9/05

to

Sorry I guess I should have included all of that, Oracle 9.1
Pro*Cobol Micro-Focus Cobol on an HP9000.

Frederico Fonseca

unread,

Jun 9, 2005, 3:09:31 PM6/9/05

to

On 9 Jun 2005 10:10:56 -0700, "Jeff" <jmoo...@hotmail.com> wrote:

There is not specific way of doing it with Oracle on ESQL.

so the following is correct

exec sql
insert into my_tbl (name)
select substr(name, 1, 2) from my_other_tbl
end-exec.

Note that you may have an extra ")" on your previous code.

Jeff

unread,

Jun 9, 2005, 3:33:48 PM6/9/05

to

Insert into catable
> (member, namekey, namekey6, namekey10, name)

> Select substr(name, 1, 2, name, 1, 6, name, 1, 10)

> From Member
> Where members not = "0000000000"
> Order by Members
>EXEC-END.

If I have multiple substrings like above, can I place them in 1 set of
( ) or do them individually? Thank you for getting back with me so
quickly

Jeff

unread,

Jun 9, 2005, 3:37:50 PM6/9/05

to

EXEC SQL INSERT INTO CANAMEDETL
( NAMEKEY, NAMEKEY6, NAMEKEY10, NAME, MBRSEP,
IMAXSOFT13_PATH_01, IMAXSOFT13_PATH_02, IMAXSOFT13_PATH_03,
IMAXSOFT13_PATH04, IMAXSOFT13_SEQ_NO )
SELECT SUBSTR(NAME, 1, 2), SUBSTR(NAME, 1, 6),
SUBSTR(NAME, 1, 10), NAME, MBRSEP, 0, 0, 0, 0
FROM MBRSEPMSTR
WHERE MBRSEP NOT = '0000000000'
ORDER BY NAME, MBRSEP
END-EXEC.

Frederico,

I have tried looking through the Pro*Cobol book and I cannot find
examples this detailed. Is this Code ok? This is what I meant to post
the first time.

Frederico Fonseca

unread,

Jun 9, 2005, 4:47:43 PM6/9/05

to

Yes, but the number of parameters do not match.

you have 10 field names, and only 9 select fields.

I think you better try the SQL first on SQL*Plus and then use it
within the COBOL program, as this is a simple SQL without host
variables.

Jeff

unread,

Jun 10, 2005, 6:50:14 AM6/10/05

to

I missed a 0 on one of the imaxsoft fields. Thanks for your help, I
really appreciate it.

Jeff

unread,

Jun 10, 2005, 10:35:54 AM6/10/05

to

Frederico,

I have written the program and I do a

procob /cadevel/symbolic/sortjm.cbl /cadevel/symbolic/sortjm.cob
I get the message

System default option values taken from:
/u00/app/oracle/product/10.1.0/client/p
recomp/admin/pcbcfg.cfg

When I do a ncobol sortjeff

I get 10 errors:

005166 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
* 149-S********
**
** No SQL directives have been set
005176 EXEC SQL END DECLARE SECTION END-EXEC.
* 149-S********
**
** No SQL directives have been set
005180 EXEC SQL
* 149-S********
**
** No SQL directives have been set
005183 EXEC SQL
* 149-S********
**
** No SQL directives have been set
005198 EXEC SQL
* 149-S********
**
** No SQL directives have been set

What am I missing? There is nobody here who has embedded sql in Cobol
or tried to connect thru Cobol etc.

Simon Tobias

unread,

Jun 10, 2005, 10:59:41 AM6/10/05

to

> When I do a ncobol sortjeff
>
> I get 10 errors:
>
> 005166 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
> * 149-S********
> **
> ** No SQL directives have been set

<snip>

You appear to be compiling the original, unprocessed source here, rather
than the output from the precompiler.

cob -it sortjm.cob

should compile the correct source (I assume that ncobol is a shell script?).

SimonT.

Frederico Fonseca

unread,

Jun 10, 2005, 12:56:05 PM6/10/05

to

On 10 Jun 2005 07:35:54 -0700, "Jeff" <jmoo...@hotmail.com> wrote:

>Frederico,
>
>I have written the program and I do a
>
> procob /cadevel/symbolic/sortjm.cbl /cadevel/symbolic/sortjm.cob
>I get the message
>
>System default option values taken from:
>/u00/app/oracle/product/10.1.0/client/p
>recomp/admin/pcbcfg.cfg

Within the Oracle/product/... structure you should have a few
makefiles and samples of how to compile a .pco ESQL source.

Search for them and see how it is done within your system.

Jeff

unread,

Jun 15, 2005, 8:19:55 AM6/15/05

to

I am trying to run my script. It does the truncate table part

SQL> select count(*) from coboltest;

COUNT(*)
----------
0

Elapsed: 00:00:00.01

When it tries to do the first insert, I get the error message below.
01 SQ0001 GLOBAL.
02 FILLER PIC X(25) VALUE "truncate TABLE COBOLTEST".

01 SQ0002 GLOBAL.
02 FILLER PIC X(150) VALUE "insert into
COBOLTEST(MBRSEP,IMA
- "XSOFT13_PATH_01,IMAXSOFT13_SEQ_NO)(select MBRSEP ,0 ,0
f
- "rom MBRSEPMSTR where MBRSEP<>'0000000000' order by
MBRSEP)".

EXEC SQL INSERT INTO COBOLTEST
( MBRSEP, IMAXSOFT13_PATH_01, IMAXSOFT13_SEQ_NO)
(SELECT MBRSEP, 0, 0
FROM MBRSEPMSTR
WHERE MBRSEP != '0000000000'
ORDER BY MBRSEP)
END-EXEC.
CALL "SQLADR" USING SQ0002 SQL-STMT
MOVE 1 TO SQL-ITERS
MOVE 20 TO SQL-OFFSET
MOVE 0 TO SQL-OCCURS
CALL "SQLADR" USING
SQLCUD
SQL-CUD
CALL "SQLADR" USING
SQLCA
SQL-SQLEST

06/15/05 BEGIN BILLING DATABASE SORT 08:13 AM

ORACLE ERROR DETECTED:

ORA-00907: missing right parenthesis

Does anyone have any thoughts of what I am doing wrong? I am kind of
out on an island with nobody to go to! Any help would be greatly
appreciated.

Richard

unread,

Jun 15, 2005, 3:10:35 PM6/15/05

to

02 FILLER PIC X(150) VALUE "insert into

Perhaps the literal is more than 150.