Re: PLEASE PLEASE PLEASE Help Me!!!

This is what you get for mixing SQL applications with C-ISAM applications. You have two fundamentally different views of the index structure, and no amount of SQL or 4GL fudging is going to reconcile things. The table has a composite index on two fields; the first field appears to be a CHAR(4), the second is a CHAR(24). Unless you specify them as two separate fields, there is no way at all of having the second part null while the first part is non-null. A field either has a value (is not null), or it doesn't have a value (is null); there is no hybrid state in SQL or 4GL. Note that the C-ISAM package should not be allowed to create the tables. If the table is to be accessed from SQL/4GL, then SQL/4GL should be used to create the table. C-ISAM can do things which SQL/4GL can't interpret, such as play with partially null fields. The redefinition of the keyfield as keyfield1 and keyfield2 in the SQL catalog would not actually affect the C-ISAM structure -- C-ISAM doesn't care about the internal structure of the data record (indeed, it doesn't have any structure for most purposes as far as C-ISAM is concerned). You can have variant record structures and all sorts of verboten constructs in C-ISAM which are not available to SQL. And, in particular, if the index is defined to be on bytes 1-28 (or 0-27) of the record, then that is what C-ISAM will use. The fact that SQL/4GL treats that section of the record in two chunks (keyfield1 and keyfield2) won't interfere with C-ISAM, though SQL/4GL won't be able to use the index (as the SQL/4GL index description would describe it in two parts which happen to be contiguous, rather than as one part which overlaps two fields). Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: tech@compsys.demon.co.uk ("tech) }Subject: PLEASE PLEASE PLEASE Help Me!!! }Date: Wed, 23 Nov 1994 12:00:30 +0000 }X-Informix-List-Id: <news.9941> } }This one has got me baffled - can anybody please help me ... } }I am using Informix v4.00 RDS, I4GL and ISQL. } }Is there any way of assigning a NULL value to part of a column value ? }For example, I have a column in a database table which is 28 characters }long. I need to insert a value into the first 4 characters whilst }leaving the remaining 24 characters as NULL. If I use a command similar }to: } let recordname.keyfield = "1234" } }and insert the row, the remaining part of the field is written as spaces. }I have tried assigning a program variable to null and building up the }field value as follows: } } initialize null_field to null } let recordname.keyfield = "1234", null_field } }but this makes no difference. I have also tried adding a NULL record }to the database and using SQLCA.SQLERRD[6] to obtain the rowid of the }new record. Then update the table setting keyfield[1,4] to "1234" }where rowid equals the rowid of the newly inserted row. Unfortunately, }this doesn't make any difference either. } }The reason I need to do this is because I am writing some software which }integrates with another 3rd party package. This 'other' package recognises }record locks by assigning the last 24 characters of the keyfield to NULL. }Although I can check for this using SQL similar to: } } select keyfield from table } where keyfield[1,4] = "1234" } and keyfield[5,28] is null } }I cannot seem to use 4GL code to make similar locks which would be }recognised by the 'other' package. } }Any sugestions would be greatfully received !! } }Regards, }Keith. } }*************** tech@compsys.demon.co.uk *************** } Tel: 044 (0)1482 803961 Fax: 044 (0)1482 804333 }********************************************************