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
}Subject: PLEASE PLEASE PLEASE Help Me!!!
}Date: Wed, 23 Nov 1994 12:00:30 +0000
}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
} 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 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 !!
} Tel: 044 (0)1482 803961 Fax: 044 (0)1482 804333