Focal Point
NULLS with VARCHAR

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9261008331

October 07, 2004, 12:08 AM
TerryW
NULLS with VARCHAR
I am having a problem all of a sudden that seems to be a result of changing a char(30) field in an Informix database, to a varchar(30).

It seems that the data field isn't being cleared out for each record read, so if the field of the current record is shorter than the field of the prior record, part of the prior record's field is in the current record's field (got you confused yet?).

For example, if the field in the first record is:
Jones, George.................

(the dots are the null character) and then, if the field in the next record should be 'Allen, Tom', it comes out as:
Allen, Tom.ge.................

and on and on, until the entire table is read.

Why am I getting these nulls, and prior record field data being carried over to later records?

Thanks,

TerryW

(oh, this is the old text based Focus 6.8.5 on HP-UX with Informix 7.30)
October 07, 2004, 12:45 AM
Piipster
Did you re-gen the master file description for the table after you made changes?
October 07, 2004, 01:24 AM
TerryW
Yes, sorry, I should have stated that I re-gen'd the master file definition. But it didn't change. It still shows up as a USAGE=A30, ACTUAL=A30.

The thing is, I believe the ACTUAL value should be a variable now, because a varchar will return the actual number of characters in the field rather than a fixed length. But I can't find where you are allowed to put anything else in that location. I've tried USAGE=TX30, ACTUAL=TX, and various other mixes, but this version of Focus doesn't like that.

Thanks for the reply.
October 07, 2004, 05:47 PM
<Pietro De Santis>
There are commands to resolve certain problems with text fields in Oracle:

SQL SQLORA SET ORACHAR FIXED
SQL SQLORA SET ORACHAR VAR

There may be the same type of commands for Informix.

When you generate the masters, are you using the WebFOCUS console? What did you set the "SET CONVERSION LONGCHAR" setting to? You have the option of Text or Alpha. Text will put the TX format in the Master, Alpha will put the A format.
October 07, 2004, 11:46 PM
TerryW
Well, I'm not using WebFocus, but the old text based version 6.8.5. And the Informix interface looks as if it was developed in 1992 !!

I couldn't solve this problem by the time I needed it, so I just re-wrote it using isql and awk. And we may start migrating over to Perl and Python anyway, so I don't know if I will be spending too much time on this.

Thanks anyway,

TerryW
November 03, 2004, 05:58 PM
TerryW
Well, I'm back on this varchar() problem again, and have tracked it down to $FOCHOME/focbin/infint and/or $FOCHOME/focbin/infint5.

Based on my personal experience developing ESQL programs, it seems that when IBI developed this Informix interface module, they defined character fields using the $char field type, instead of the $string field type. This NULL problem I am running in to is exactly what I have experienced when doing this. I can access the same database using the same master file description using WebFocus, and do not have a problem.

So, is it possible to get corrected versions of these files? Again, I am using FOCUS 6.8.5 on HP-UX 10.20, so I know they are old, and probably not even supported any more, but I sure would like to resolve this.

Thanks
November 05, 2004, 03:24 PM
<monte2000>
Hi TerryW:

The Informix interface hasn't been upgraded to handle true 'variable' length character strings, either because it's a new feature for that product, or because no one has requested it before.

Please open a HOTTRACK case with Tech Support. Programming is aware of the issue, and has already solved it for Oracle, so they should be able to provide a 'new' interface in short order.

Hope that helps.

Monica