Jump to content


Photo
- - - - -

Incremental data


7 replies to this topic

#1 Balaram

Balaram

    Member

  • Members
  • PipPip
  • 20 posts
  • Gender:Male
  • Location:INDIA

Posted 04 January 2007 - 07:07 AM

Hello everybody!!!

I need to extract incremental data from native PRO IV .For example , i have 100 records , i extract them and send it across as a CSV file .Now after some time i have 10 records being added to that file and 10 records being updated , Now i would have 20 new records(added and updated).How do i go about accessing only those updated and added record .Is there any way out to access only those incremental data and send it across as a CSV file in native PRO IV??

#2 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 04 January 2007 - 07:21 AM

Hi,

No, there is no automatic way of detecting those records.

You would have to add a flag to the file to say if it needs exporting or not (or a date/ time field), and update that if you change the record or add a record.

Then the extract could detemind which records to export....

Rob.

#3 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 04 January 2007 - 05:11 PM

Balaram,

If Deletes are a possibility, then you'll have to code specifically for that as well.

Depending on the scope of the project, you could look at moving the table to SQL and using replication as a solution. However, this would be a significant learning curve...

Regards,

Joseph

#4 Balaram

Balaram

    Member

  • Members
  • PipPip
  • 20 posts
  • Gender:Male
  • Location:INDIA

Posted 04 January 2007 - 05:30 PM

Balaram,

If Deletes are a possibility, then you'll have to code specifically for that as well.

Depending on the scope of the project, you could look at moving the table to SQL and using replication as a solution. However, this would be a significant learning curve...

Regards,

Joseph


Thanks Rob and Joseph,

Joseph i am ready to try my hand out at moving the table to SQL ,But does it really work ??if it does then how should i go about writing the code .

P.S :i am working on Native PRO IV and not SL or VIP.

#5 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 04 January 2007 - 06:25 PM

Balaram,

Let me start backwards...

Does it work?

Yes.

Moving the table to SQL. I'll briefly touch on the overview of this. You should be able to find more details by searching through the archive.

We've only setup replication with SQL server, so I'll give you SQL Server specific info on replication. Others may have had success with PostgreSQL or Oracle.

To move the table to SQL, you'll want to copy the definition and then set the external file type to SQLSERVE. In the table definition itself, set the alternate column names (Expand mode in @F) to whatever you would like them to be called in SQL Server. Avoid using .'s in your alternate column names as these are restricted.

Create a SQL database. Create your table within the SQL database.

Set up your application to connect to your SQL database. (Basically, add the values below that are appropriate for what you are looking to do.)



pro4.ini

SQLSERVE_SELECT_FOR_UPDATE=TRUE
SQL_NOSIG=Y
LOCKED_ROWS_RETURNED=Y
[Database - SQLDEFAULT]
PRODB_CHARSET=7
FILETYPE=SQLSERVE
CONNECTION=username/password
SQL_DBNAME=databasename

In your ODBC Data Source Administrator, under System DSN, create an entry for your databasename.

Now, write an update to read from your old (ProISAM) file def and write to your new (SQLSERVE) file def. I'd recommend setting an iteration count of 1,000.

If all has gone well, your data is now in a SQL table. (And no, I don't expect that everything has gone without problem as simply as stated above!)

In SQL Server Manager, you can now create a merge replication publication of just that table. Suggestion: allow anonymous subscriptions.

Then, whenever you want to update your table on the remote environment, connect to the server and execute your subscription.

If you've never dealt with SQL tables from ProIV, I would budget about 10 - 15 hours to overcome that hurdle.
If you've never dealt with merge replication, I would budget 15 - 20 hours to overcome that hurdle.

Good luck!

Joseph

#6 Ajaym

Ajaym

    Newbie

  • Members
  • Pip
  • 6 posts
  • Gender:Male
  • Location:California, USA

Posted 05 January 2007 - 01:20 AM

How about writing a trigger, once converted to SQL Server
Ajay Mathur

#7 Paul Blew

Paul Blew

    Member

  • Members
  • PipPip
  • 24 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 05 January 2007 - 08:45 AM

Hi Ajaym,

Here's a template for a SQL Server trigger. The SET NOCOUNT lines are essential - without them the PRO-IV session will lock up the second time the trigger fires due to a bug in ODBC.

[codebox]
IF EXISTS (SELECT name FROM sysobjects WHERE name = '<TRIGGER_NAME>' AND type = 'TR')
DROP TRIGGER [<TRIGGER_NAME>]
GO

CREATE TRIGGER [<TRIGGER_NAME>] ON [dbo].[<TABLE_NAME>]
FOR INSERT, UPDATE, DELETE AS
DECLARE
@NEW_VAR1 VARCHAR(10),
@OLD_VAR2 VARCHAR(10)
BEGIN
SET NOCOUNT ON

SELECT @NEW_VAR1 = [<COLUMN_NAME>]
FROM [inserted]

SELECT @OLD_VAR1 = [<COLUMN_NAME>]
FROM [deleted]

-- your trigger processing goes here

SET NOCOUNT OFF
END
GO
[/codebox]


Hope this helps

Paul

#8 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 05 January 2007 - 02:42 PM

Balaram,

The trigger solution looks much simpler than replication.

If your remote database will always be on line and accessible, I would go with the trigger. If your remote database is not consistently on the network, then I don't know if the triggers are a solution or not.

Perhaps Paul or Ajaym could comment.

Regards,

Joseph



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users