Integer field - Change to String

[Katherine Hainley]

Way back in the day, via Web Screen Painter > DB Schema > ISS table, a custom field (zCustomer_Zipcode) was created as an Integer field type and has worked great until now.

 

We now have customers from a different state where the zip code begins with a 0 (zero). When we create Issue tickets and enter a zip code that begins with a 0, it is removed when we save the ticket. I believe this is due to it being an Integer and not String.

 

Is there a way for me to change the field type from Integer to String easily and not corrupt any data I already have entered into the field for my Issue tickets?

 

 

thank you

TheKatherine

[Scott Johnson]

Hi Katherine,

 

Please reference the post below:

 

https://communities.ca.com/message/241897549?commentID=241897549#comment-241897549 

 

As noted here please test this out in a NON PROD environment, and take a backup before performing any changes, anytime data already exists there is a chance it can become corrupt.

 

Thanks,

Scott

[Chi Chen]

you could pdm_extract that custom field value + id to a file...change the type from integer to string, both in SDM and mdb...then pdm_load the data back...this way, no matter what happens on dbms side you won't lose a bit of data

[Kyle_R]

Yes. This plus Scott's answer are the full solution.

 

Well, that and take a full backup before all of this is started, of course.

[Katherine Hainley]

Chi Chen,

 

Can you help me out with the pdm_extract and pdm_load steps ... I am not that familiar with those commands (have only used them once before).

 

Here is the information I have about my custom field if it helps:

 

my select statement

      select *
      from dbo.wspcol
      where display_name LIKE 'zCustomer%'

 

my results

      id = 400370
      persid = wspcol:400370
      table_name = iss
      column_name = zCustomer_ZipCode
      status = 3
      schema_name = zCustomer_ZipCode
      display_name = zCustomer_ZipCode
      type = 1             *** will be changed to 2 based upon a step in the other instructions from Scott
      string_len = 0
      is_wsp = 1

 

 

thank you

TheKatherine

[Chi Chen]

Katherine, IF you don't have many records in issue table(for example, not a million records), you could

--run

pdm_extract -f "select id, zCustomer_Zipcode from Issue" > mydata.txt

--go ahead change zCustomer_Zipcode data type in SDM and database

--run

pdm_extract -u -f mydata.txt

However, if you have too many records, you would better do this from database side and your DBA can assist you with this. Doing pdm_load with a million records will take too long time.

Thanks _Chi

[Katherine Hainley]

Chi,

 

Thanks for all the information, I am going to work with my DBA on creating a new field and then copying the data from the current field to the new field via the backend.

 

 

thank you

TheKatherine