Jump to content


Photo
- - - - -

Migrate from PRO ISAM to SQL


7 replies to this topic

#1 Joy Chapman

Joy Chapman

    Newbie

  • Members
  • Pip
  • 2 posts
  • Gender:Female

Posted 15 April 2016 - 04:42 PM

At my company we have a pretty old version of PRO-IV.  I need a way to migrate the data from the PRO-ISAM files to a SQL database.  Does anyone know where\how to start?

 

Any advice would be most appreciated.



#2 Ross Bevin

Ross Bevin

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 226 posts
  • Gender:Male
  • Location:Manzanillo, Colima, Mexico

Posted 15 April 2016 - 06:06 PM

Hi Joy,

 

It's quite simple. This is what I would do for each file.

 

1. Copy the ProISAM ProIV filedef to a file with say a small 's' appended to the name. E.g. GL_MSTR to GL_MSTRs.

2. In the file definition header change GL_MSTRs to file type SQLSERVE. Set the physical name as GL_MSTR. You will also have to go into the storage tab and set the Ext Type, format and alternate name for each field. Alpha fields are ALPHA and numeric fields are NUMBER. For NUMBER the format is <total field length including precision>.<precision>. If the number can be a negative append the format with an "S". E.g. S8.2 (can store a max of 999999.99-)

3. In the file definition window use the SQL icons to create and save the script to a text file for GL_MSTRs.

4. In SQL Server Management Studio open the script previously created and execute it in the right database to create the table.

5. Create an update function that reads all of GL_MSTR in look mode and adds to GL_MSTRs in add mode.

6. Delete file definition GL_MSTR.

7. Rename file definition GL_MSTRs as GL_MSTR.

8. Regen functions that use GL_MSTR.

 

You will need to update your c:\windows\pro4v8.ini file with the appropriate SQL database settings. You will also need to create an ODBC driver on the server that has the ProIV kernel; it needs to point to the SQL Server database.

 

When I did this for our system in 2003 I automated the process by writing a series of functions that used the filedef bootstrap files to create one SQL table creation script file. I also wrote a function that automated the file renaming. If your system is large you may want to consider this route.

 

A word of caution with moving to SQL Server. Depending on how your system is written/structured you may encounter a lot of table locking. This is especially true for control files where you say get the next available invoice number. In ProISAM you can have a logical update that gets and increments the number. After the record is written it is unlocked and other sessions can read and update it. This is not true for SQL tables. Typically you will have to either exit the function to commit the records or execute a commit command in logic. Executing the commit command can have unwanted consequences with other tables you are processing. Because of this we continue to maintain our control files in ProISAM.

 

Hope this helps!

 

Regards

Ross



#3 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 395 posts
  • Gender:Male
  • Location:Florida,USA

Posted 15 April 2016 - 06:43 PM

What version of PROIV are you on Joy ??


Things should be made as simple as possible, but not simpler

#4 Ngoni

Ngoni

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 19 April 2016 - 07:45 AM

l am also interested in the solution to migration of proiv version 1.5r13 1997. Its running on unix machine SCO 5.0.7.



#5 Ross Bevin

Ross Bevin

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 226 posts
  • Gender:Male
  • Location:Manzanillo, Colima, Mexico

Posted 19 April 2016 - 01:10 PM

Hi Ngoni,

 

If memory serves me right I think 1.5 came out much earlier than 1997; more like 1992-93. To migrate to the latest version of ProIV you will need to do this in version steps as you can't upgrade directly from 1.5 to 8.2. You may want to consider using NorthgateArinso to migrate your source code as they probably have the older versions installed. Of course SCO is dead so you will need to decide what platform you want to run on; Linux or Windows. If you move to Windows you will have to find an alternate method to handle printing.

 

Regards

Ross



#6 Mark Fuller

Mark Fuller

    Newbie

  • Members
  • Pip
  • 2 posts
  • Gender:Male

Posted 25 April 2016 - 10:28 PM

Hi Joy, 

 

Are you looking to migrate the application and data or just the data?

 

Either way, PROIV can help you. We have migrated several recently and I would be happy to have a discussion around your requirements further if you wish.

 

As Ross has already alluded to, this can be automated and we have built several tools to assist clients in doing this.

 

Please email me at mark dot fuller at ngahr .com if you wish to discuss further.

 

Mark



#7 Mark Fuller

Mark Fuller

    Newbie

  • Members
  • Pip
  • 2 posts
  • Gender:Male

Posted 25 April 2016 - 10:32 PM

Ngoni,

 

As Ross has kindly pointed out, we may be able to assist with a migration. If you wish you can reach out to me at 

 

mark dot fuller at ngahr .com 

 

and we can discuss further.

 

Regards

Mark



#8 Joy Chapman

Joy Chapman

    Newbie

  • Members
  • Pip
  • 2 posts
  • Gender:Female

Posted 26 April 2016 - 04:05 PM

How old is it?  Well, very old... feel free to laugh:

 04/26/16              McDonnell Information Systems                CAT//PTS_TR
                                                                         *
                                                      **    *          **
   pppppppppppppp   nnnnnnnnnnnnn     oooooooooooooo  **   **         **
   PPPPPPPPPPPPPPPP RRRRRRRRRRRRRRR  OOOOOOOOOOOOOOOO **   **        **
   PPP          PPP RRR         RRR  OOO          OOO **   **       **
   PPPppppppppppPPP RRRnnnnnnnnnRRR  OOO          OOO **   **      **
   PPPPPPPPPPPPPP   RRRRRRRRRRRRR    OOO          OOO **   **     **
   PPP              RRR        RRR   OOO          OOO **   **    **
   PPP              RRR         RRR  OOOooooooooooOOO **   **   **
   PPP              RRR          RRR  OOOOOOOOOOOOOO  **   ** **
                                                      *     **

 Version : 2.2000  Revision : r02.3.0 May 09, 94 Bootstrap Revision : 2.2.007





Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users