Table-report quetsions about data types etc

M

magmag

New Member
Hello!

I have managed to get a report of all the tables in a database but I do not understand everything. I am more used to SQL-server and needs clarification of a few things:

1) I have a field that is inte (integer) and has the format '>>>9', what does this mean?
2) Some fields are of datatype inte[9]. Does that mean that it is in fact an array of nine integer values? I am not used to that in SQL-server.
3) My character fields have lots of different formats. I do not understand all of them. For example these to formats 'M9999-999999' and 'x9999-999999' becomes characters of different length when you convert them to SQL-server.

I have tried to find published information about this i.e. PDF-files but have not found any. If anyone knows where I can find them so would I be very thankful.

Regards
Magnus
 
Internally the Progress database stores all data as variable length fields.

Formats are for display purposes only. Thus '>>>9' will display numbers that are right-justified, 4 characters wide and will show at least one digit.

Yes, integer[9] would mean an array of 9 integers.

I'm not sure what your question #3 is asking.
 
Tom, wasn't there an issue with certain field sizes when they are converted to SQL. I can't remember the entire contents of the conversation, but I remember it had something to do with field length sizes when converting to SQL - it could be the opposite though, SQL - Progress.

Magmag, have you looked into using the dbtool "dbtool /dir/dbname", it will allow you to evaluate SQL Width issues.
 
In my Progress Database there are two different tables that have the field "fbet". There are relationships between these tables with "fbet" as the key. On my report of all the tables "fbet" is of datatype "char". The format in one of the tables is set to 'M9999-999999' and on the other 'x9999-999999'.
I also dumped the definitions as a SQL-server script so that I could create the tables on a SQL-server and get an automatic diagram over the database. When I create the tables in SQL-server the fields are created as "varchar(10)" and "varchar(11)". The length is different between the two tables. When I try to make a relationship in SQL-server I get an error since both the fields in a relation is supposed to be of the same type AND length.
So I guess my question is: Why do these fields become different in length? How can there be a relationship based on character-fields of different length in Progress?

Worth noting is also that I found a relation between an Integer field and a Character field in Progress! Are there no rule against this in Progress?

Thanks for the answers on question 1 and 2.

Regards
Magnus
 
comatt1 said:
Magmag, have you looked into using the dbtool "dbtool /dir/dbname", it will allow you to evaluate SQL Width issues.
Click to expand...

No, I have not. Might be something do try. For now I changed the size in SQL-server to varchar(11) on all of them, that works well enough for me to make a database diagram. But it would be interesting to understand why it is like this.
 
Yes there are issues when using the SQL-92 engine to access 4GL tables. SQL wants things to be fixed length and the 4GL doesn't. So there is a utility to "fix" the width for SQL's benefit.

Since I, personally, don't care too much about SQL I don't have gory details but it should be easy enough to find in the docs -- look for DBTOOL.
 
magmag said:
In my Progress Database there are two different tables that have the field "fbet". There are relationships between these tables with "fbet" as the key. On my report of all the tables "fbet" is of datatype "char". The format in one of the tables is set to 'M9999-999999' and on the other 'x9999-999999'.
Click to expand...

Remember -- formats are for display purposes only. They have absolutely nothing to do with what is stored in the field. And they have even less to do with relationships between fields.

In a FORMAT PHRASE "x" stands for any character and "9" for any digit ("9" will always display at least a "0"). In the first table the programmer apparently wants to always display an "M". In the second table other values are apparently possible

I also dumped the definitions as a SQL-server script so that I could create the tables on a SQL-server and get an automatic diagram over the database. When I create the tables in SQL-server the fields are created as "varchar(10)" and "varchar(11)". The length is different between the two tables. When I try to make a relationship in SQL-server I get an error since both the fields in a relation is supposed to be of the same type AND length.
So I guess my question is: Why do these fields become different in length? How can there be a relationship based on character-fields of different length in Progress?
Click to expand...

Progress doesn't care about field length.

Worth noting is also that I found a relation between an Integer field and a Character field in Progress! Are there no rule against this in Progress?
Click to expand...

"Relationships" are a figment of the report's imagination... Progress itself really doesn't do "relationships" in the SQL sense. So you sometimes see odd things like this :awink:
 
TomBascom said:
Remember -- formats are for display purposes only. They have absolutely nothing to do with what is stored in the field. And they have even less to do with relationships between fields.

In a FORMAT PHRASE "x" stands for any character and "9" for any digit ("9" will always display at least a "0"). In the first table the programmer apparently wants to always display an "M". In the second table other values are apparently possible
Click to expand...

--- "M" isn't a supported format character, Progress supports:
$
'
.
0
9
D
G
L
...

http://www.psdn.com/library/servlet/KbServlet/download/1093-102-901/e89.pdf
 
Okay!
I guess the reason one of the fields becomes a varchar(10) is that Progress understands "M9999-999999" as store a character string containing 10 numbers and put an M in front of it and a '-' in position 5 when displaying the content. Therefore there are only 10 interesting characters in this field.

Thanks alot!!
 
No. Progress does not understand the format in that way.

But it does recognize that it is likely that the programmer is doing something like that and since it has no better information with which to create a SQL definition it just uses the width of the format.

So you end up with the same result.
 
You must log in or register to reply here.
Back
Top