Change field type with method in v11

[David Lieb]

In v11.4, is there a way to change the type of a field programatically, e.g.
Alpha to Text. (Actually a lot of fields, otherwise I would use the
structure editor.)

I was thinking of using BEGIN SQL...ALTER TABLE...END SQL, but as far as I
can tell you can add or delete a field that way but not modify it.

Thanks,

David
**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[garrio]

Hi David,
You can DROP TABLE to.  I believe that could do the trick.
Good luck.
Garri

> From: david_lieb@...
> Date: Fri, 6 Nov 2009 07:41:37 -0500
> To: 4d_tech@...
> CC:
> Subject: Change field type with method in v11
>
> In v11.4, is there a way to change the type of a field programatically, e.g.
> Alpha to Text. (Actually a lot of fields, otherwise I would use the
> structure editor.)
>
> I was thinking of using BEGIN SQL...ALTER TABLE...END SQL, but as far as I
> can tell you can add or delete a field that way but not modify it.
>
> Thanks,
>
> David
> **********************************************************************
> Get the speed and power of 4D v11 SQL
> before upgrade prices increase - http://www.4d.com
>    
>
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4D.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4D_Tech-Unsubscribe@...
> **********************************************************************

     
_________________________________________________________________
Windows 7: Unclutter your desktop.
http://go.microsoft.com/?linkid=9690331&ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_evergreen:112009**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com


4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Lieb]

Hi Garri,

I don't follow? How would DROP TABLE help to change the type of a field?

I should also add that I don't want to lose the field data, otherwise I
would could delete a field and add one with a different type.

Thanks,

David

On Fri, Nov 6, 2009 at 11:46 AM, Garri Ogata wrote:

> Hi David,
> You can DROP TABLE to.  I believe that could do the trick.
> Good luck.
> Garri
>
> > From: david_lieb@...
> > Date: Fri, 6 Nov 2009 07:41:37 -0500
> > To: 4d_tech@...
> > CC:
> > Subject: Change field type with method in v11
> >
> > In v11.4, is there a way to change the type of a field programatically,
> e.g.
> > Alpha to Text. (Actually a lot of fields, otherwise I would use the
> > structure editor.)
> >
> > I was thinking of using BEGIN SQL...ALTER TABLE...END SQL, but as far as
> I
> > can tell you can add or delete a field that way but not modify it.
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Atanas Atanassov]

Hi David,

You can try to export the structure to a XML file. Alpha fields and  
Text fields are from type 10. You need to delete only the length of  
the alpha field and it become a text field. Then create a new  
structure from the xml source.



On Nov 6, 2009, at 4:41 AM, David Lieb wrote:

> In v11.4, is there a way to change the type of a field  
> programatically, e.g.
> Alpha to Text. (Actually a lot of fields, otherwise I would use the
> structure editor.)
>
> I was thinking of using BEGIN SQL...ALTER TABLE...END SQL, but as  
> far as I
> can tell you can add or delete a field that way but not modify it.
>

Atanas Atanassov
Technical Support Team Member
4D, Inc.

-----------------------------------------------------------------
Upgrade to 4D v11 SQL before 4D 2004
license and upgrade prices increase - http://www.4d.com
-----------------------------------------------------------------
**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Joshua Fletcher]

You can't change the type of a field in SQL, but you can drop and add
columns with the ALTER TABLE command.

So one scenario might be:

-Add a column via SQL.
-Copy the data from the old column to the new column (you have to resave all
the data anyway to convert it to the new type).
-Drop the old column.

Kind regards,
 
Josh Fletcher
 
--
Josh Fletcher
4D Technical Services Team Member
http://www.4d.com

> -----Original Message-----
> From: David Lieb [mailto:david_lieb@...]
> Sent: Friday, November 06, 2009 9:17 AM
> To: 4d_tech@...
> Subject: RE: Change field type with method in v11
>
> Hi Garri,
>
> I don't follow? How would DROP TABLE help to change the type of a field?
>
> I should also add that I don't want to lose the field data, otherwise I
> would could delete a field and add one with a different type.
>
> Thanks,
>
> David

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Lieb]

On Fri, Nov 6, 2009 at 12:59 PM, Atanas Atanassov wrote:

> Hi David,
>
> You can try to export the structure to a XML file. Alpha fields and
> Text fields are from type 10. You need to delete only the length of
> the alpha field and it become a text field. Then create a new
> structure from the xml source.
>
> On Nov 6, 2009, at 4:41 AM, David Lieb wrote:
>
> > In v11.4, is there a way to change the type of a field
> > programatically, e.g.
> > Alpha to Text. (Actually a lot of fields, otherwise I would use the
> > structure editor.)
>

Thanks Atanas. Once I have the new structure, is there any way to copy its
fields back to the original structure so that field types will be changed in
the original structure (rather than having new fields created)?

David
**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[garrio]

Hi David,
It sounded like you needed to export the data, drop the table, recreate the table with whatever field types you want now and then import.  That was just an idea since Alter table wouldn't work for you.  I'm doing something like this for my v11 application.  Changing all the field types from Alpha to text and all my variable declarations from string to text.  Well good luck with whichever way you decide to go.
Garri

> From: david_lieb@...
> Date: Fri, 6 Nov 2009 12:17:04 -0500
> To: 4d_tech@...
> CC:
> Subject: RE: Change field type with method in v11
>
> Hi Garri,
>
> I don't follow? How would DROP TABLE help to change the type of a field?
>
> I should also add that I don't want to lose the field data, otherwise I
> would could delete a field and add one with a different type.
>
> Thanks,
>
> David
>
> On Fri, Nov 6, 2009 at 11:46 AM, Garri Ogata wrote:
>
> > Hi David,
> > You can DROP TABLE to.  I believe that could do the trick.
> > Good luck.
> > Garri
> >
> > > From: david_lieb@...
> > > Date: Fri, 6 Nov 2009 07:41:37 -0500
> > > To: 4d_tech@...
> > > CC:
> > > Subject: Change field type with method in v11
> > >
> > > In v11.4, is there a way to change the type of a field programatically,
> > e.g.
> > > Alpha to Text. (Actually a lot of fields, otherwise I would use the
> > > structure editor.)
> > >
> > > I was thinking of using BEGIN SQL...ALTER TABLE...END SQL, but as far as
> > I
> > > can tell you can add or delete a field that way but not modify it.
> >
> **********************************************************************
> Get the speed and power of 4D v11 SQL
> before upgrade prices increase - http://www.4d.com
>    
>
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4D.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4D_Tech-Unsubscribe@...
> **********************************************************************

     
_________________________________________________________________
Bing brings you maps, menus, and reviews organized in one place.
http://www.bing.com/search?q=restaurants&form=MFESRP&publ=WLHMTAG&crea=TEXT_MFESRP_Local_MapsMenu_Resturants_1x1**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com


4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Tim Nevels]

On Nov 6, 2009, at 10:46 AM, David Lieb wrote:

> In v11.4, is there a way to change the type of a field  
> programatically, e.g.
> Alpha to Text. (Actually a lot of fields, otherwise I would use the
> structure editor.)
>
> I was thinking of using BEGIN SQL...ALTER TABLE...END SQL, but as  
> far as I
> can tell you can add or delete a field that way but not modify it.

Hi David,

I think that is exactly the way to do it -- with ALTER TABLE.  I did a  
little testing and found a very interesting behavior with 4D v11.

4D v11 allows us to delete a field -- ALTER TABLE DROP.  And we can  
also add a field -- ALTER TABLE ADD.  And when adding a field, 4D v11  
will reuse any previously deleted fields.  So if you DROP a field and  
then immediately ADD a field, the resulting field will have the same  
field number as the deleted field.  So you have in effect changed the  
field type. Remember that you lose all field properties likes choice  
lists and other checkboxes in the field properties window. You can  
reset some of the properties with SQL commands, but not all of them.  
So be aware of this limitation.

Here is a ChangeFieldType method I wrote that was inspired by your  
post.  You sometimes need to close and then reopen the structure  
window to see the field type change if you run the method from the  
Design environment. I did not test this with indexed fields -- so I do  
not know if there is any issues with indexes.


If (False)  ` ===========================================
          ` PROJECT METHOD: ChangeFieldType
       
          ` PARAMETERS: $1 = pointer to field
          ` $2 = field type number
          ` $3 = alpha field length (optional)
       
          ` DESCRIPTION: Changes the type of a field using SQL commands.
       
          ` CREATED BY: Tim Nevels, Innovative Solutions ©2009
          ` DATE: 11/6/2009
          ` LAST MODIFIED:
End if   ` ============================================

C_POINTER($1;$field_p)
C_LONGINT($2;$fieldType_l)
C_LONGINT($3;$fieldLength_l)

$field_p:=$1
$fieldType_l:=$2
If (Count parameters>=3)
        $fieldLength_l:=$3
Else
        $fieldLength_l:=255
End if

C_TEXT($tableName_t;$fieldName_t;$fieldType_t;$sql_t)

   ` get table and field name
$tableName_t:=Table name(Table($field_p))
$fieldName_t:=Field name($field_p)

   ` convert 4D field type to SQL text representation
Case of
        : ($fieldType_l=Is Text )
                $fieldType_t:="VARCHAR"
               
        : ($fieldType_l=Is Real )
                $fieldType_t:="REAL"
               
        : ($fieldType_l=Is Float )
                $fieldType_t:="FLOAT"
               
        : ($fieldType_l=Is Integer )
                $fieldType_t:="INT16"
               
        : ($fieldType_l=Is LongInt )
                $fieldType_t:="INT32"
               
        : ($fieldType_l=Is Integer 64 bits )
                $fieldType_t:="INT64"
               
        : ($fieldType_l=Is Date )
                $fieldType_t:="TIMESTAMP"
               
        : ($fieldType_l=Is Time )
                $fieldType_t:="DURATION"
               
        : ($fieldType_l=Is Boolean )
                $fieldType_t:="BOOLEAN"
               
        : ($fieldType_l=Is Picture )
                $fieldType_t:="PICTURE"
               
        : ($fieldType_l=Is BLOB )
                $fieldType_t:="BLOB"
               
        Else   ` assume Alpha
                $fieldType_t:="VARCHAR ("+String($fieldLength_l)+")"
End case

   ` build SQL statements
$sql_t:="ALTER TABLE "+$tableName_t+" DROP "+$fieldName_t+"; "
$sql_t:=$sql_t+"ALTER TABLE "+$tableName_t+" ADD "+$fieldName_t+" "+
$fieldType_t+"; "

   ` drop/add column with new field type
Begin SQL
        EXECUTE IMMEDIATE :$sql_t
End SQL


Tim

********************************************
Tim Nevels
Innovative Solutions
785-749-3444
timnevels@...
********************************************

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com


4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Lieb]

I guess that'll work. Thanks Josh.

David

On Fri, Nov 6, 2009 at 4:11 PM, Josh Fletcher wrote:

> You can't change the type of a field in SQL, but you can drop and add
> columns with the ALTER TABLE command.
>
> So one scenario might be:
>
> -Add a column via SQL.
> -Copy the data from the old column to the new column (you have to resave
> all
> the data anyway to convert it to the new type).
> -Drop the old column
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Lieb]

Ok, I see now. Thanks.

David

On Fri, Nov 6, 2009 at 4:11 PM, Garri Ogata wrote:

> It sounded like you needed to export the data, drop the table, recreate the
> table with whatever field types you want now and then import.  That was just
> an idea since Alter table wouldn't work for you.  I'm doing something like
> this for my v11 application.  Changing all the field types from Alpha to
> text and all my variable declarations from string to text.  Well good luck
> with whichever way you decide to go.
> Garri
> > From: david_lieb@...
> > Date: Fri, 6 Nov 2009 12:17:04 -0500
> > To: 4d_tech@...
> > CC:
> > Subject: RE: Change field type with method in v11
> >
> > Hi Garri,
> >
> > I don't follow? How would DROP TABLE help to change the type of a field?
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Lieb]

Hi Tim,

Thanks for the idea and the code! A couple thoughts:

1. Other than changing Alpha to Text or vice-versa, after dropping and
adding the field, it might be good to use Josh Fletcher's suggestion to copy
the data, in this case from the field to itself.

2. I'll probably extract the 4D to SQL field type part into a separate
method that might be useful in other contexts.

Thanks again!

David

On Sat, Nov 7, 2009 at 3:00 AM, Tim Nevels wrote:

> On Nov 6, 2009, at 10:46 AM, David Lieb wrote:
>
> > In v11.4, is there a way to change the type of a field
> > programatically, e.g.
> > Alpha to Text. (Actually a lot of fields, otherwise I would use the
> > structure editor.)
> >
> > I was thinking of using BEGIN SQL...ALTER TABLE...END SQL, but as
> > far as I
> > can tell you can add or delete a field that way but not modify it.
>
> Hi David,
>
> I think that is exactly the way to do it -- with ALTER TABLE.  I did a
> little testing and found a very interesting behavior with 4D v11.
>
> 4D v11 allows us to delete a field -- ALTER TABLE DROP.  And we can
> also add a field -- ALTER TABLE ADD.  And when adding a field, 4D v11
> will reuse any previously deleted fields.  So if you DROP a field and
> then immediately ADD a field, the resulting field will have the same
> field number as the deleted field.  So you have in effect changed the
> field type. Remember that you lose all field properties likes choice
> lists and other checkboxes in the field properties window. You can
> reset some of the properties with SQL commands, but not all of them.
> So be aware of this limitation.
>
> Here is a ChangeFieldType method I wrote that was inspired by your
> post.  You sometimes need to close and then reopen the structure
> window to see the field type change if you run the method from the
> Design environment. I did not test this with indexed fields -- so I do
> not know if there is any issues with indexes.
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Tim Nevels]

On Nov 8, 2009, at 2:00 AM, David Lieb wrote:

> Hi Tim,
>
> Thanks for the idea and the code! A couple thoughts:
>
> 1. Other than changing Alpha to Text or vice-versa, after dropping and
> adding the field, it might be good to use Josh Fletcher's suggestion  
> to copy
> the data, in this case from the field to itself.

Why?  What exactly is Josh suggesting?  I must have missed  
something.   4D will automatically convert the data in the record from  
the old field type to the new field type when necessary.  It's one of  
the best features in 4D -- automatic data file conversion.

What my method is doing has the exact same result as if you were in  
the Design environment and just changed the type of a field in the  
structure editor.  We do it all the time and never worry about what is  
going to happen to the data file.  4D will take care of it.  Same  
situation here.

Tim

********************************************
Tim Nevels
Innovative Solutions
785-749-3444
timnevels@...
********************************************

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Lieb]

On Mon, Nov 9, 2009 at 12:58 AM, Tim Nevels wrote:

>
> On Nov 8, 2009, at 2:00 AM, David Lieb wrote:
> ...
> > 1. Other than changing Alpha to Text or vice-versa, after dropping and
> > adding the field, it might be good to use Josh Fletcher's suggestion
> > to copy
> > the data, in this case from the field to itself.
>
> Why?  What exactly is Josh suggesting?  I must have missed
> something.   4D will automatically convert the data in the record from
> the old field type to the new field type when necessary.  It's one of
> the best features in 4D -- automatic data file conversion.
>
> What my method is doing has the exact same result as if you were in
> the Design environment and just changed the type of a field in the
> structure editor.  We do it all the time and never worry about what is
> going to happen to the data file.  4D will take care of it.  Same
> situation here.
>

It isn't necessary to update the data when you change the field type
because, as you say, 4D will do it as needed when it accesses records, so I
just thought of it as doing it all up front. And it's certainly easy enough
to include it in your method. If you have lots of data in those fields and
it will add an inconvenient amount of time to the conversion, you could skip
it.

Also, I thought I remembered reading on this list a few years ago about some
problem arising in some cases of structure/data type mismatches and I know
Data Check has a check for this.

David
**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Lieb]

On Sat, Nov 7, 2009 at 3:00 AM, Tim Nevels wrote:
>
> > In v11.4, is there a way to change the type of a field
> > programatically...
>

> Hi David,
>
> I think that is exactly the way to do it -- with ALTER TABLE.  I did a
> little testing and found a very interesting behavior with 4D v11.
>
> 4D v11 allows us to delete a field -- ALTER TABLE DROP.  And we can
> also add a field -- ALTER TABLE ADD.  And when adding a field, 4D v11
> will reuse any previously deleted fields.  So if you DROP a field and
> then immediately ADD a field, the resulting field will have the same
> field number as the deleted field.  So you have in effect changed the
> field type.
>

I've been testing this out and it works, but with one big caveat, which may
be implicit in what you said, but I'll spell it out here because it bit me:

This will fail if you have an unused field number in a table and the field
you're trying to change has a higher field number. If so, then the when you
DROP the field and immediately ADD it back, it will fill the lower numbered
hole, not the one you just created with DROP. Thus the field's number will
change.

David
**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************