× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



My tests were on V5R2.

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




"Rusling, John B. (Alliance)" <jbrusling@xxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
04/25/2003 08:11 AM
Please respond to RPG programming on the AS400 / iSeries
 
        To:     "'rpg400-l@xxxxxxxxxxxx'" <rpg400-l@xxxxxxxxxxxx>
        cc: 
        Fax to: 
        Subject:        RE: Creating a file in qtemp with a unique key 
field using embedd ed   sql.


Thanks for replying Eric and Rob;

We are on Release 5.1. 

I use QTEMP and get what I want using, CREATE TABLE, CREATE INDEX but it's
TWO steps.

I hope it's changed for Release 5.2.  I'd prefer a ONE-step solution.

John B.


Message: 4
Date: Thu, 24 Apr 2003 15:52:38 -0500
From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
Subject: RE: Creating a file in qtemp with a unique key field using
 embedd ed               s ql.

John,

I ran across this originally in V4R4.  This seems to relate to the SQL
system catalog, which is supposed to be the global directory of file 
objects
on the system.  The problem is that the system won't add an entry to the
system file catalog if the file exists in QTEMP, because QTEMP cannot be
referred to globally.  I thought I saw that this was changed in V5, so 
that
if QTEMP is part of the pathname to a file, the system would bypass the
system catalog lookup code and access the file directly. 

The real problem is the use of QTEMP.  Either use another library that
exists globally, or leave off the Primary key constraint.

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Rusling, John B. (Alliance) [mailto:jbrusling@xxxxxxxxxxxxxxx]
Sent: Thursday, April 24, 2003 3:28 PM
To: 'rpg400-l@xxxxxxxxxxxx'
Subject: Creating a file in qtemp with a unique key field using embedded
s ql.


I'm trying to create a file in qtemp with a unique key field using 
embedded
sql from an interactive rpg program.

The code thats giving me a problem is -

     C                   EVAL      SqlString2= 'CREATE TABLE'
     C                              + ' qtemp/dollibs'
     C                              + ' ('
     C                              + 'dlLib   CHAR ( 10)' 
     C                             + ' NOT NULL WITH DEFAULT,'
     C                             + ' PRIMARY KEY(dlLib)'
     C                             + ' )'
 
     C/Exec SQL
     C+  PREPARE PP1 FROM :SqlString2 
     C/End-Exec
 
     C/Exec SQL
     C+  EXECUTE PP1 
     C/End-Exec

 
What happens is the file doesns't get created.

Checking the session job log gives me this message -

Constraint is not valid. 

press F1 on the message yields -

*---------------------------------------------------------------------------
------*
                         Additional Message Information

 

 Message ID . . . . . . :   CPD32B0

 Date sent  . . . . . . :   04/24/03      Time sent  . . . . . . :
15:11:51 
 

 Message . . . . :   Constraint is not valid.

 

 Cause . . . . . :   Constraint *N cannot be added for file DOLLIBS in
library 
   QTEMP for TYPE value *N. For a referential constraint (TYPE *REFCST), 
the

   parent file *N in library *N has a delete rule of *N and update rule of
*N. 
   The constraint was not added because of errors. The reason code is 15.
The 
   reason codes and their meanings are as follows:

     00 - See previous messages.

     01 - Constraint name not valid.

     02 - TYPE value not valid.

     03 - DLTRULE value not valid.

     04 - UPDRULE value not valid.

     05 - File is damaged.

     06 - File constraints are logically damaged.

     07 - System Cross Reference files are damaged.

     08 - A primary key constraint already exists.

     09 - A duplicate unique constraint exists.

     10 - The KEY length is too long.

     11 - The parent file does not exist.

     12 - The parent key does not exist.

     13 - The file can have a maximum of one member.

     14 - The file cannot be a source file.

     15 - The file cannot be in the QTEMP library.

     16 - The parent and dependent files must be in the same ASP.

     17 - The file does not allow write, update, or delete operations

     18 - The file must be an externally described file.

     19 - The file must not be a distributed file.


 Recovery  . . . :   Do one of the following based on the reason code 
shown,

   and then try the request again.

     01 - Correct the constraint name and retry the function.

     02 - Correct the TYPE value and retry the function.

     03 - Correct the DLTRULE value and retry the function.

     04 - Correct the UPDRULE value and retry the function.

     05 - Delete (DLTF) the file.

     06 - Remove (RMVPFCST) the damaged constraints.

     07 - Perform a reclaim storage (RCLSTG).

     08 - Remove the primary key constraint (RMVPFCST) and then try again.

     09 - Remove the unique constraint (RMVPFCST) and then try again.

     10 - Choose fields whose combined length does not exceed the maximum

          length allowed. See the Database User's Guide.

     11 - Create (CRTPF) the parent file.

     12 - Add primary key or unique constraint (ADDPFCST) to parent file.

     13 - Change the file (CHGPF) to maximum members of one.

     14 - Use a file other than a source file.

     15 - Use a library other than the QTEMP library.

     16 - Use files in the same ASP.

     17 - Use a file that allows write, update and delete operations.

     18 - Use an externally described file.

     19 - Use a file which is not a distributed file.

 Technical description . . . . . . . . :   X'00000000000000000000'

 

 

 
Bottom 
 Press Enter to continue.

 

 F1=Help   F3=Exit   F6=Print   F9=Display message details   F12=Cancel

 F21=Select assistance level


*---------------------------------------------------------------------------
------*

Does anyone know why this cannot be done, or if it can be done and how ?

I can do it if a create a non-keyed file in qtemp and then create a 
logical
over it in qtemp.
(using embedded sql, CREATE TABLE, CREATE INDEX)

Thanks for any help.

John B.
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.