PLEASE PLEASE PLEASE Help Me!!!

PLEASE PLEASE PLEASE Help Me!!!

Post by Jonathan Leffl » Fri, 25 Nov 1994 02:48:07



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,


}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.
}

}   Tel: 044 (0)1482 803961   Fax: 044 (0)1482 804333
}********************************************************

 
 
 

PLEASE PLEASE PLEASE Help Me!!!

Post by Malcolm Weallan » Fri, 25 Nov 1994 02:56:41


Keith,
It sounds to me as if your third party product is very strange.

I have seen problems with TETRA, which uses NULL terminated character
strings rather than fixed length ones as INFORMIX does.  I have never
encountered any product which needs 24 characters to indicate a record
lock.  
Also, Beware the word NULL!!!!  In 'C' a null string is a single
character containing the hex value x'00' .  In INFORMIX a NULL string
would not be equal to X'00' and a NULL Numeric field is not equal to
zero.  INFORMIX uses special codes for NULL character fields and NULL
numerics.  In INFORMIX   -  NULL is not equal to NULL and NULL is not NOT
EQUAL to NULL.

Malcolm Weallans
OnLIne Database Consultancy
(+44)628-72154 Fax (+44)628-37463

 
 
 

PLEASE PLEASE PLEASE Help Me!!!

Post by Neil S. Brisco » Fri, 25 Nov 1994 03:31:46


You simply can't do what you want.  In databases, NULL means unknown.  
As soon as you place any characters into a character variable, it now
has a known value.

Is the reason for trying to put a "NULL" after the data so that you
don't get a load of spaces in reports?  Then look at the CLIPPED
function, which will eradicate trailing spaces in variables.

Regards
Neil

 
 
 

PLEASE PLEASE PLEASE Help Me!!!

Post by Nic » Fri, 25 Nov 1994 05:33:08



} This one has got me baffled - can anybody please help me ...
}
} Is there any way of assigning a NULL value to part of a column value ?
} long.  
}      
}      initialize null_field to null
}      let recordname.keyfield = "1234", null_field
}
} but this makes no difference.  I have also tried adding a NULL record
}
} 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.
}
} Keith.

}    Tel: 044 (0)1482 803961   Fax: 044 (0)1482 804333
} ********************************************************

  Keith,
    [FMI], forgive my ignorance, but can you take a new tack &
  give up the notion of concatenating a null to the end of the
  field (assuming the 3d party pkg knows your kind of null). Try
  instead creating a file or even a small table for use as a
  semaphore, & inserting keys for locked records. Just an idear :-)
  *********************************
    Nick Nobbe, Library of Congress
    NLS/BPH
    Off: (202) 707-0548
    Fax: (202) 707-0711

  *********************************
 
 
 

PLEASE PLEASE PLEASE Help Me!!!

Post by "te » Thu, 24 Nov 1994 21:00:30


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.


   Tel: 044 (0)1482 803961   Fax: 044 (0)1482 804333
********************************************************

 
 
 

PLEASE PLEASE PLEASE Help Me!!!

Post by Timothy E. Wa » Fri, 25 Nov 1994 09:26:52


: 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.  .........

One way of having a "real" value in the first part of a column and having
the rest undefined is to use the VARCHAR data type.  If you insert a
column with length of 4, the remaining 24 bytes are logically null.
If your version of Informix doesn't support VARCHAR, then this won't
help you too much.  (and I have a funny feeling this is the case .... )

|-----------------------|       O<   ----------- DDI Systems Inc -----------|

|Tim Ward < Data Duck > |  \>>> / necessarily mine, but those of my Employer|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 
 
 

PLEASE PLEASE PLEASE Help Me!!!

Post by O.P. » Fri, 02 Dec 1994 03:43:29


|> 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.
|>

|>    Tel: 044 (0)1482 803961   Fax: 044 (0)1482 804333
|> ********************************************************

how about a push and pop to a c routine (using 4gl) that loads,
modifies and returns a host variable.

                              o.p.p.


                     Prodigy  : dwnp44a

 
 
 

PLEASE PLEASE PLEASE Help Me!!!

Post by Stylis » Fri, 02 Dec 1994 13:30:13


|> 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.

{SNIP}

|> 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.

  Can't you move it to a VARCHAR(28)

and select keyfield from table where keyfield[1,4] = "1234" and
      LENGTH(keyfield)=4;