Kbase 15882: How to change data type of a database field ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/10/1998 |
|
How to change data type of a database field ?
DRAFT COPY - Currently under review and edit.
INTRODUCTION:
=============
This Technical Support document describes how to change a data type of
a database field.
WHY YOU NEED TO KNOW THIS:
===========================
Sometimes it can happen that you will need/want to change a data type
of a database field. This action cannot be done using standard
Progress Data Dictionary (Data Administration).
PROCEDURAL APPROACH:
====================
If the database is empty and/or you do not mind loosing all data in
the specific field. You can just delete the field you want to change
and create a new one with the same preferences except DATA TYPE.
If you do mind loosing data follow the steps below:
---------------------------------------------------
- run Progress with connected database
- run:
output to filename.d.
for each table-name: export recid(table-name) table-name.field-name.
end.
output close.
- this dumps the content of the field
- delete the field and create a new one with the same preferences
except DATA TYPE - NOTE: ALL DATA IS LOST FROM THE FIELD !!!
- run:
input from filename.d.
define variable temp as original-data-type no-undo.
define variable rectmp as recid no-undo.
repeat:
import rectmp temp.
find first table-name where recid(table-name) = input(rectmp).
assign table-name.field-name = conversion-function(temp).
release table-name.
find next table-name exclusive-lock.
end.
input close.
- this loads the data back into the database
CONVERSION-FUNCTION is any function which converts between any data
types: STRING, DECIMAL, INTEGER, DATE, etc.
NOTES:
------
Before you change the data type of any field you should consider what
impact it can have on your JOIN relationships. If two tables contain
fields with exactly same name but different data type these are not
considered as join fields so your table JOIN is gone. Two tables to be
considered as joined must contain fields with exactly the same name
and data type and at least one of them must be indexed.
ONLINE PROCEDURES OR UTILITIES:
===============================
OnLine Help for conversion functions and all statements used in the
procedures is available.
REFERENCES TO WRITTEN DOCUMENTATION:
====================================
For more information about conversion functions see LANG. REF. Doc.
For more information about creating/deleting fields see Basic Devel.
Tools Doc. Chapter - Data Dictionary Tasks
Progress Software Technical Support Note # 15882