In the progress database the format for a character type is only for display purposes. So if I define a new field and leave the default I end with
ADD FIELD "MyFirstCharDefault" OF "TestCharFields" AS character
FORMAT "x(8)"
INITIAL ""
POSITION 2
MAX-WIDTH 16
ORDER 10
If I make the display bigger to save an address or something and put on a screen with a editor type input then the user can type more than the 60 characters.
ADD FIELD "MySecondCharBIG" OF "TestCharFields" AS character
FORMAT "x(60)"
INITIAL ""
POSITION 3
MAX-WIDTH 120
ORDER 20
If I now connect to my DB using ODBC/JDBC I will receive an error if there is data in the field that is too BIG as SQL databases uses the format as the size for storage size on disc.
CREATE testCharFields.
ASSIGN MyFirstChar = "abcdefghijk"
MySecondChar = "001abcdefghiujklmnopqrstuvwxyz002abcdefghiujklmnopqrstuvwxyz003abcdefghiujklmnopqrstuvwxyz004abcdefghiujklmnopqrstuvwxyz".
Now trying to extract with JDBC driver to BI tool. It will work as there is less than 120 chars (MAX-WIDTH 120)
SELECT DISTINCT
"TestCharFields"."MyFirstCharDefault",
"TestCharFields"."MySecondCharBIG"
FROM "PUB"."TestCharFields"
|
I then modify the data to
MySecondChar = "001abcdefghiujklmnopqrstuvwxyz002abcdefghiujklmnopqrstuvwxyz003abcdefghiujklmnopqrstuvwxyz004abcdefghiujklmnopqrstuvwxyz005abcdefghiujklmnopqrstuvwxyz ".
Error retrieving results: java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Column MySecondCharBIG in table PUB.TestCharFields has value exceeding its max length or precision.
java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Column MySecondCharBIG in table PUB.TestCharFields has value exceeding its max length or precision.
Now I can run DBTool and use option 2 to fix the problem, but on the SQL DB side they still cannot handle the data.
So what I want to know is , would it not be possible to truncate the data before the Data Direct driver makes the result set available ?
I don’t think this can be default behaviour but maybe a parameter ?
I think using the Data Direct Open Access Driver it will be possible to build your own result set in ABL but this would mean lots of extra work
to create your own driver and will require C or Java skills.
The option of putting functions in your SELECT is also there so you can use
SELECT SUBSTR(MySecondChar,1) *** somehow this knows to use the MAX-WIDTH as the length ***
SELECT SUBSTR(MySecondChar,1,30) *** this will only give the first 30 characters ***
But not sure how this will impact on performace. Especially if it is indexed fields.
Thank you
A Venter
I'm not sure what you are really asking here. There is no parameter to truncate ... and it would be a bit dangerous if there were since one would not know that data had been lost. Regular running of dbutil is the right way to address the problem ... that or, even better, design you application to make sure that no more than MAX-WIDTH characters are stored in the field. This is pretty basic business logic which one would expect an application to enforce.
fyi, there is a startup parameter for the 4GL runtime that tells it to enforce the max-width for character columns. it is not enabled by default because doing so will break many 4GL applications.
Please note that OE sql does not really use the Format as the size for values in a table column. Sql uses the sql width value that can be set by the MAX-WIDTH attribute in a .df. The initial width setting is derived from the Format .
This means that you can change the Format and MAX-WIDTH independently - make the format right for ABL, make the max width right for sql use.
There is also an ability in the ABL Data Dictionary to change the sql width. There is a special screen just for this purpose. You probably know about this, but just in case.....
When you use DbTool, or the Data Dictionary, to change the sql width, the running sql server does not pickup this change right away. In order to have greater transaction concurrency, changing the width does not do any complex schema locking and schema timestamp update. If you now sql, you can force the server to learn about the new width by doing a harmless sql GRANT statement and then doing COMMIT for the transaction doing the Grant. The Grant bumps the schema timestamp.
hope this helps, .... steve pittman [sql software architect]
adding to what Steve said, note that the Format is simply a default DISPLAY FORMAT, nothing more.
Hallo
The problem is that you don't always have control over all third parties. So if data comes from a third party you save it and it is now "too big". Progress RDBMS wont have a problem in saving the data.
But external parties also consume your data. So now they get the error that the data is too big. This causes problems as especially in BI people rely on their reports being there.
So if I could make a rule that said truncate the data then the user will get his report (with some data missing) but this might be better than no report at all.
Once I have done planning and all third parties can update their side I can run DBTool to fix the MAX-WIDTH.
If I just script DBTool to run every morning and increase then third party expecting a MAX of 100 and now receiving 120 will crash on his side.
Has anyone seen DataDirect Open Access with Pass through option in action. If I understand it correctly then I can write ABL to create the resultset (apply my rule) and to the third party it will still look like ODBC/JDBC driver supplying results ?
Thank you
A Venter
Then, the point of focus should be your import process. If you want to impose length restrictions, that is the place to do it. It is no different than doing any other kind of validation to make sure that you don't put invalid data in your DB.
Thanks a lot for all the feedback. Will consider all the options mentioned and structure a solution around that.
Cheers
A Venter
Glad to see our Community members provide you with options that may help... Thanks to all for helping out Abri...