Insert to char(8) field appends spaces/need to store as is

Posted by Admin on 01-Apr-2008 15:09

The questions keep on coming. I have a table, say tableA with a primary key id. There are about 30,000 rows. I am creating a table tableB, that has the same field id of type char(8) , and will have a 1 for 1 correlation to tableA based on this id field. So when I am populating tableB, I am in effect doing a select on id from tableA, then insert id into tableB. I define tableB as below:

CREATE TABLE tableB

(

id char(8) NOT NULL,

updated_date TIMESTAMP NOT NULL DEFAULT SYSTIMESTAMP

);

The problem is that storing the id to tableB differs in some cases. For example if I get an id from tableA, say 'ato', then storing that value to tableB and selecting it later returns a value of 'ato ', which is obviously a different value given the trailing spaces at the end. I am sure that the value I am storing does not have trailing spaces. Then later when I do a select like below:

select id from tableA where not exists (select * from tableB where tableB.id = tableA.id)

I get a number of records that don't match. If I create the table then go into data dictionary and delete the field 'id' in tableB, then copy the field from tableA, then the values store and return correctly. This would suffice as a solution, however, I need to be able to create the table using SQL, for automation purposes , so how could/should I modify my table creation SQL so that it has the same behavior as it does when I copy the field from tableA? Is there a way I can use the data dictionary or other tool to sort of dump the sql definition of this tableA so I can use that sql to mimic what happens when I do a copy from field in the data dictionary? Any comments or suggestions will be greatly appreciated.

All Replies

Posted by Thomas Mercer-Hursh on 01-Apr-2008 15:28

Well, at the risk of being obvious again, have you considered doing the change with the Data Dictionary, if that fixes the problem, and then propagating the change to the other Progress databases with a .df file, i.e., OpenEdge specific DDL.

I suspect that, if you look in the Data Dictionary at table B, you will find that the datatype of id in that table is fixchar, not character. If so, be advised that you have created a big potential problem unless you only ever access this data with SQL. In fact, not only can you not display a fixchar value in ABL, you can't even use the dictionary dump routines on it.

To avoid this, either create the field from the ABL side or specify VARCHAR instead of CHAR or CHARACTER.

There are several datatypes like this which are SQL specific and not accessible from ABL. I think they are to be ardently avoided. Another one to watch for is to use DECIMAL instead of FLOAT

Posted by Thomas Mercer-Hursh on 01-Apr-2008 15:29

Oh, the other thing I meant to mention is that it would be some very simple ABL to go through and test for trailing blanks. But, I suspect the real problem is fixchar, so check that first.

Posted by Admin on 01-Apr-2008 15:55

This was very helpful, but I still don't quite have the answer. I changed the sql definition to varchar as opposed to char and now the type is character in the data dictionary for id in tableB. So I populated tableB with the id from tableA. Now an even stranger thing appears to happen. Those records that did not match previously on the query below still do not match.

select id from tableA where not exists (select * from tableB where tableB.id = tableA.id)

The very strange thing is that when I perform these queries, the values returned look identical, yet the query above indicates that they do not match:

select id from tableA where id = 'ato';

select id from tableB where id = 'ato';

In both cases above, id is 'ato'. When I performed these queries before changing the id type of tableB to varchar from char, the first one would return 'ato', and the second would return 'ato '; Now after this change they look the same but still somehow do not match. Like I said before, it will work correctly if I use the copy field functionality in the data dictionary. How progress could consider two values 'ato' and 'ato' different is indeed very, very strange. Also I appreciate the warnings on readability from the ABL side. In this case, none of the fields I am adding will need to be read/written from the ABL side, except the timestamps referenced in the ABL triggers. Thanks again.

Posted by Thomas Mercer-Hursh on 01-Apr-2008 16:14

Of course, my first inclination would be to do the copy in ABL code. This might include going through tableA and doing a TRIM() first to make sure that there were no mystery trailing spaces. And/or, you could check both tables for the presence of trailing spaces in the suspect records.

My other thought is to wonder if there is a codepage here. Again, from the ABL it would be easily to pull up the suspect records and display the length and ASCII for each.

But, step one is probably back to the data dictionary and looking very carefully at all the settings on the two fields. SQL doesn't give you nearly the control that a .df does and is one of the reasons I would prefer making the dictionary changes using a .df. I'll bet there is a difference and it may well be that it is a difference you can't control from SQL.

Posted by Admin on 01-Apr-2008 20:47

Hi Tom,

Dont you love SQL92 as a "standard"

I would expect that if you compared the fields tableA.id & tableB.id via the data dictionary you will find that tableB hase the "case-sensitive" box ticked whilst tableA does not. The down side to this is that the "case-sensitive" column has in effect been upshifted, so the compare becomes 'ATO' = 'ato'.

Two options;

1) mark both fields to the same "case-sensitive" setting, or

2) use "select id from tableB where not exists (select * from tableA where tablea.id = ucase(tableb.id))" (note use of ucase at the end).

And some of the bear traps;

1) char/varchar columns created via odbc will always be case-sensitive (unless this has changed recently),

2) as your tableB was created via odbc you will not be able to modify this 'case-sensitive' setting via the datadictionary (the old tool cannot alter schema elements created in the new) ... big reason to use the old tool - if for no reason other than to save you being shot by the legacy development team when they next come to modify the schema.

The incomplete data type support (timestamp, float, char etc) will hit you at some point. You mentioned that the p4gl code does not modify these fields ... however you later mention that the 'triggers' do ... they count as p4gl (i assume here you are talking about the p4gl triggers and not the java equiv?).

To keep it safe use the DataDictionary for all schema changes - and brace yourself for more pain if you start moving into more complex queries (always check your query plans).

- Colin

Posted by Thomas Mercer-Hursh on 01-Apr-2008 22:31

Yet another reason to argue for SQL and ABL parity. The big irony, of course, is that ABL should be able to do things that SQL cannot ... as it can ... but to have the reverse be true as well is really odd.

But, I do agree with you that the thing to do here is make any schema changes whenever possible via the data dictionary or incremental .df files. Only then does one have full control and only then does one insure access by the ABL.

Today, one might think this was a SQL only thing, but tomorrow you may be kicking yourself because the fields can't be accessed with ABL. Make the fields with ABL tools and you will be able to do both. Make them with SQL and ... well, you should be getting the picture by now.

Posted by Admin on 01-Apr-2008 22:53

Why was the SQL engine separated in the first place.

Why should the same sql statement issued in the two engines return different results, different data type support, and biggest of all ... different triggers/procedure support (p4gl triggers should always have been executed on/via the server and not just an extension of the client).

And why cant you cast an overwidth character field rather than change the source fields with in the schema.

And then the license user counts - as the broker stays alive after the user disconnects you can quickly find yourself running out as your total pool is divided between the two engines.

The direction of the original SQL89 interface was in many ways a better approach.

If that was moved forward, add in the recent engine enhancement, add in SQL92 compliance life would be a lot simpler. Invoke a stored procedure would have also provided an alternative approach to web services (parameters in, dataset out) but through a single set of p4gl code.

Posted by Admin on 08-Apr-2008 17:00

For whatever reason the only way this will work is by copying the field from the other table using the data dictionary. And later I plan to use the delta file method to propogate these additions to other database instances.

Posted by Thomas Mercer-Hursh on 08-Apr-2008 17:10

Copy is certainly easier, but I am confident that you could have gotten there just creating the field from scratch in the data dictionary.

The real bottom line here is that only the data dictionary provides full control over all options so the best way to maintain schema is with the data dictionary, whether it be manual changes or importing a .df. SQL is just never going to give you complete control.

Posted by Admin on 08-Apr-2008 19:28

Hi Tom,

Did you compare the 'case-sensitive' state (tick-box @ bottom of data-dictionary field edit screen) of both fields ... only ask so as to complete the thread for the next person running through this thread. (It may already be too late as I guess the field is now long gone) ;)

This thread is closed