RE: Creating a file in qtemp with a unique key field using embedd eds ql.

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

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.