Unknown Value in Character Field

[Pavan Yadav]

Hi All,

I have a small query regarding Unknown Value display in Character field. For ex:
There is a field Phone format Char x(15).

As per my understanding the default value in character filed in NULL i.e Blank.
But, when i queries the field Phone it equals to ? rather then "?". And even if assigns explicitly then I didn't get the way to assign ? rather then "?" into it. Also when checked for Asc value i.e asc(phone), it returns ?.

So this is something as Unknown value. So please assist me and let know what are the scenarios which can cause ? value in character field and how can I check and control this, because it's causing issue while displaying and searching the record.

In Current scenario, this value gets populated through a Oracle Application which interfaces data into Progress Database through dataserver.

Progress Version we are using : Progress 9.1D, with UNIX Platform application. Although it's an older progress version , but need to workout on this and need to get root cause for this, along with solution.

 

[RealHeavyDude]

If the value of a field (fields of any data type can be NULL) is NULL means it is not populated or does not have a defined value. Blank for a character field usually means an empty string which is already defined. So this two are and behave different.

Every database field can define an initial value. If it is not defined the default for a character field is the empty string. You can check the initial value in the data dictionary whether it is defined as NULL (? is NULL and "?" is a string containing the question mark, so these two are different too). Whenever you create a record in the database all fields that you don't explicitly assign will automatically be assigned with their initial values.

I doubt that an Oracle application will write into a Progress database using the Data Server product from Progress. Instead I would think that it is the other way round: An application developed in the 4GL (that's how it was called back then) writes into an Oracle database using the Data Server product ...
Heavy Regards, RealHeavyDude.

 

[Pavan Yadav]

Thanks for replying Dude.

There is no initial value assigned for this field so it should be Blank (empty string)if nothing has been assigned to it. So my concern is: How ? has been entered into it because I doubt if it has something to do with Oracle DB putting data into this Progress DB Field.
As per my understanding if I displays a int field with default value then it should show 0.00, for date it should be ? and for char field it should be empty string. Then how come it will be ? and for some records it's empty string.
So does it differentiated on behalf of some value in Oracle Field mapped to this Progress DB Field. So what can be the possibilities for this value, even if I want to explicitly assign this NULL value in char field then how can I do that?

 

[RealHeavyDude]

I am afraid that I have no expertise in the Data Server product and/or Oracle databases.

But, the Data Server product from Progress is a real Progress database that holds only the schema of the foreign database (which in your case is an Oracle database) but does not store any data. One of the steps to set it up is to pull the schema from the foreign database. This is so that you can compile 4GL procedures against it.

I would speculate that the difference in behavior to what I have described above comes from the Oracle database. Therefore you should check the database fields in question, respectively their definition, in the Oracle database rather than in the Progress database that only holds the schema.

Other than that I would suggest to post your question in the data servers forum here to attract the attention of somebody more knowledgeable about the Data Server product from Progress.

Heavy Regards, RealHeavyDude.

 

[Stefan]

If, on Oracle you assign '' (quote quote) to a varchar2 field, Oracle will translate this to NULL which will be seen as unknown (?) on the Progress side. If you want an empty varchar2 on the Oracle side you will need to assign ' ' (quote space quote).

 

[Pavan Yadav]

Thanks Dude..!!
Might be oracle field need to be cross checked again.

Thanks Stefan,
This sounds fruitful in this scenario. So, if this is an issue because we are populating data in Progress Table through Oracle DB Tables. Is there anyway we can restrict on DB level, with some code-page or any other way out or we have to do the changes/validation at program level only?

Also, if this is the case then it should not hang up the application screens if it tries to read such NULL (?) value?
Also, is there any why I can create such NULL value explicitly?

 

[Stefan]

This sounds fruitful in this scenario. So, if this is an issue because we are populating data in Progress Table through Oracle DB Tables. Is there anyway we can restrict on DB level, with some code-page or any other way out or we have to do the changes/validation at program level only?

If you want to prevent NULL on the Oracle side you can add a NOT NULL constraint to your field.

Also, if this is the case then it should not hang up the application screens if it tries to read such NULL (?) value?

I have no idea why your application is hanging when it encounters an unknown value, perhaps it is a value that you are not expecting and are doing silly things based on it. Remember that unknown + anything = unknown.

Also, is there any why I can create such NULL value explicitly?

Yes, from the Progress side this is the unknown value (?):

def var cc as char.

cc = ?.